CREATE TABLE `users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`openId` varchar(64) NOT NULL,
	`name` text,
	`email` varchar(320),
	`loginMethod` varchar(64),
	`role` enum('user','admin') NOT NULL DEFAULT 'user',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`lastSignedIn` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_openId_unique` UNIQUE(`openId`)
);
CREATE TABLE `accounts` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(191) NOT NULL,
	`type` enum('cash','bank') NOT NULL DEFAULT 'cash',
	`balance` decimal(14,2) NOT NULL DEFAULT '0',
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `accounts_id` PRIMARY KEY(`id`)
);

CREATE TABLE `attendance` (
	`id` int AUTO_INCREMENT NOT NULL,
	`employeeId` int NOT NULL,
	`date` date NOT NULL,
	`checkIn` varchar(8),
	`checkOut` varchar(8),
	`workHours` decimal(5,2) DEFAULT '0',
	`status` enum('present','absent','late','leave','holiday') NOT NULL DEFAULT 'present',
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `attendance_id` PRIMARY KEY(`id`)
);

CREATE TABLE `customers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(191) NOT NULL,
	`company` varchar(191),
	`phone` varchar(32),
	`email` varchar(320),
	`address` text,
	`taxNumber` varchar(64),
	`balance` decimal(14,2) NOT NULL DEFAULT '0',
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `customers_id` PRIMARY KEY(`id`)
);

CREATE TABLE `departments` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(191) NOT NULL,
	`description` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `departments_id` PRIMARY KEY(`id`)
);

CREATE TABLE `employees` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(32),
	`name` varchar(191) NOT NULL,
	`jobTitle` varchar(191),
	`departmentId` int,
	`phone` varchar(32),
	`email` varchar(320),
	`address` text,
	`nationalId` varchar(32),
	`hireDate` date,
	`baseSalary` decimal(12,2) NOT NULL DEFAULT '0',
	`status` enum('active','inactive') NOT NULL DEFAULT 'active',
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `employees_id` PRIMARY KEY(`id`)
);

CREATE TABLE `invoice_items` (
	`id` int AUTO_INCREMENT NOT NULL,
	`invoiceId` int NOT NULL,
	`productId` int NOT NULL,
	`productName` varchar(191),
	`quantity` decimal(12,2) NOT NULL,
	`unitPrice` decimal(12,2) NOT NULL,
	`total` decimal(14,2) NOT NULL,
	CONSTRAINT `invoice_items_id` PRIMARY KEY(`id`)
);

CREATE TABLE `invoices` (
	`id` int AUTO_INCREMENT NOT NULL,
	`invoiceNumber` varchar(32) NOT NULL,
	`type` enum('sale','purchase','quote') NOT NULL,
	`customerId` int,
	`supplierId` int,
	`date` date NOT NULL,
	`subtotal` decimal(14,2) NOT NULL DEFAULT '0',
	`taxRate` decimal(5,2) NOT NULL DEFAULT '0',
	`taxAmount` decimal(14,2) NOT NULL DEFAULT '0',
	`discount` decimal(14,2) NOT NULL DEFAULT '0',
	`total` decimal(14,2) NOT NULL DEFAULT '0',
	`paidAmount` decimal(14,2) NOT NULL DEFAULT '0',
	`status` enum('draft','confirmed','paid','partial','cancelled') NOT NULL DEFAULT 'draft',
	`notes` text,
	`createdBy` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `invoices_id` PRIMARY KEY(`id`)
);

CREATE TABLE `leaves` (
	`id` int AUTO_INCREMENT NOT NULL,
	`employeeId` int NOT NULL,
	`type` enum('annual','sick','unpaid','emergency','other') NOT NULL DEFAULT 'annual',
	`startDate` date NOT NULL,
	`endDate` date NOT NULL,
	`days` int NOT NULL DEFAULT 1,
	`reason` text,
	`status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `leaves_id` PRIMARY KEY(`id`)
);

CREATE TABLE `payrolls` (
	`id` int AUTO_INCREMENT NOT NULL,
	`employeeId` int NOT NULL,
	`month` int NOT NULL,
	`year` int NOT NULL,
	`baseSalary` decimal(12,2) NOT NULL DEFAULT '0',
	`allowances` decimal(12,2) NOT NULL DEFAULT '0',
	`deductions` decimal(12,2) NOT NULL DEFAULT '0',
	`bonus` decimal(12,2) NOT NULL DEFAULT '0',
	`netSalary` decimal(12,2) NOT NULL DEFAULT '0',
	`status` enum('draft','approved','paid') NOT NULL DEFAULT 'draft',
	`paidAt` timestamp,
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `payrolls_id` PRIMARY KEY(`id`)
);

CREATE TABLE `products` (
	`id` int AUTO_INCREMENT NOT NULL,
	`code` varchar(64),
	`name` varchar(191) NOT NULL,
	`category` varchar(191),
	`unit` varchar(32) DEFAULT 'قطعة',
	`costPrice` decimal(12,2) NOT NULL DEFAULT '0',
	`sellPrice` decimal(12,2) NOT NULL DEFAULT '0',
	`quantity` decimal(12,2) NOT NULL DEFAULT '0',
	`minQuantity` decimal(12,2) NOT NULL DEFAULT '5',
	`warehouseId` int,
	`description` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `products_id` PRIMARY KEY(`id`)
);

CREATE TABLE `settings` (
	`id` int AUTO_INCREMENT NOT NULL,
	`companyName` varchar(191) DEFAULT 'شركتي',
	`currency` varchar(16) DEFAULT 'ج.م',
	`taxRate` decimal(5,2) DEFAULT '14',
	`address` text,
	`phone` varchar(32),
	`email` varchar(320),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `settings_id` PRIMARY KEY(`id`)
);

CREATE TABLE `stock_movements` (
	`id` int AUTO_INCREMENT NOT NULL,
	`productId` int NOT NULL,
	`type` enum('in','out','adjustment') NOT NULL,
	`quantity` decimal(12,2) NOT NULL,
	`reason` varchar(191),
	`referenceType` varchar(64),
	`referenceId` int,
	`notes` text,
	`createdBy` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `stock_movements_id` PRIMARY KEY(`id`)
);

CREATE TABLE `suppliers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(191) NOT NULL,
	`company` varchar(191),
	`phone` varchar(32),
	`email` varchar(320),
	`address` text,
	`taxNumber` varchar(64),
	`balance` decimal(14,2) NOT NULL DEFAULT '0',
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `suppliers_id` PRIMARY KEY(`id`)
);

CREATE TABLE `transactions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`type` enum('income','expense') NOT NULL,
	`category` varchar(191) NOT NULL,
	`amount` decimal(14,2) NOT NULL,
	`date` date NOT NULL,
	`description` text,
	`referenceType` varchar(64),
	`referenceId` int,
	`accountId` int,
	`createdBy` int,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `transactions_id` PRIMARY KEY(`id`)
);

CREATE TABLE `warehouses` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(191) NOT NULL,
	`location` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `warehouses_id` PRIMARY KEY(`id`)
);

ALTER TABLE `users` MODIFY COLUMN `role` enum('user','admin','accountant','warehouse_manager','hr_manager','employee') NOT NULL DEFAULT 'user';