SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `bill_items` (
	`id` varchar(36) NOT NULL,
	`bill_id` varchar(36) NOT NULL,
	`item_id` varchar(36),
	`item_name` varchar(255) NOT NULL,
	`hsn_code` varchar(20),
	`unit` varchar(20),
	`quantity` decimal(10,2) NOT NULL,
	`unit_price` decimal(10,2) NOT NULL,
	`discount_percent` decimal(5,2) DEFAULT '0.00',
	`tax_rate` decimal(5,2) DEFAULT '0.00',
	`total_price` decimal(12,2) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `bill_items_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `bills` (
	`id` varchar(36) NOT NULL,
	`type` enum('sale','purchase') NOT NULL,
	`bill_class` enum('tax','rough') NOT NULL,
	`bill_number` varchar(50) NOT NULL,
	`party_id` varchar(36),
	`date` timestamp NOT NULL,
	`sub_total` decimal(12,2) NOT NULL,
	`discount_amount` decimal(12,2) DEFAULT '0.00',
	`tax_amount` decimal(12,2) DEFAULT '0.00',
	`round_off` decimal(10,2) DEFAULT '0.00',
	`total_amount` decimal(12,2) NOT NULL,
	`amount_paid` decimal(12,2) DEFAULT '0.00',
	`status` enum('paid','unpaid','partial','draft') NOT NULL,
	`eway_bill_number` varchar(50),
	`vehicle_number` varchar(20),
	`notes` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `bills_id` PRIMARY KEY(`id`),
	CONSTRAINT `bills_bill_number_unique` UNIQUE(`bill_number`)
);
CREATE TABLE IF NOT EXISTS `expense_categories` (
	`id` varchar(36) NOT NULL,
	`name` varchar(255) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `expense_categories_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `expenses` (
	`id` varchar(36) NOT NULL,
	`category_id` varchar(36) NOT NULL,
	`date` timestamp NOT NULL,
	`amount` decimal(12,2) NOT NULL,
	`payment_mode` enum('cash','bank','upi','card') NOT NULL,
	`description` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `expenses_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `inventory_categories` (
	`id` varchar(36) NOT NULL,
	`name` varchar(255) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `inventory_categories_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `inventory_items` (
	`id` varchar(36) NOT NULL,
	`category_id` varchar(36),
	`item_type` enum('purchase','sale') NOT NULL DEFAULT 'purchase',
	`name` varchar(255) NOT NULL,
	`name_tamil` varchar(255),
	`tags` text,
	`barcode` varchar(255),
	`hsn_code` varchar(20),
	`unit` varchar(20) NOT NULL,
	`purchase_price` decimal(10,2) NOT NULL,
	`is_featured` boolean DEFAULT false,
	`retail_price` decimal(10,2),
	`wholesale_price` decimal(10,2),
	`stock_quantity` decimal(10,2) DEFAULT '0.00',
	`low_stock_alert` decimal(10,2) DEFAULT '0.00',
	`tax_rate` decimal(5,2) DEFAULT '0.00',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `inventory_items_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `parties` (
	`id` varchar(36) NOT NULL,
	`type` enum('customer','supplier') NOT NULL,
	`name` varchar(255) NOT NULL,
	`email` varchar(255),
	`phone` varchar(20),
	`address` text,
	`gstin` varchar(15),
	`vehicle_number` varchar(20),
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `parties_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `party_ledgers` (
	`id` varchar(36) NOT NULL,
	`party_id` varchar(36) NOT NULL,
	`transaction_date` timestamp NOT NULL,
	`type` enum('sale','purchase','payment_in','payment_out','opening') NOT NULL,
	`reference_id` varchar(36),
	`debit` decimal(12,2) DEFAULT '0.00',
	`credit` decimal(12,2) DEFAULT '0.00',
	`balance` decimal(12,2) NOT NULL,
	`description` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `party_ledgers_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `payments` (
	`id` varchar(36) NOT NULL,
	`bill_id` varchar(36),
	`party_id` varchar(36) NOT NULL,
	`type` enum('in','out') NOT NULL,
	`date` timestamp NOT NULL,
	`amount` decimal(12,2) NOT NULL,
	`payment_mode` enum('cash','bank','upi','card') NOT NULL,
	`reference_number` varchar(100),
	`notes` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `payments_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `print_templates` (
	`id` varchar(36) NOT NULL,
	`name` varchar(255) NOT NULL,
	`content` text NOT NULL,
	`is_default` boolean DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `print_templates_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `sessions` (
	`id` varchar(36) NOT NULL,
	`user_id` varchar(36) NOT NULL,
	`expires_at` timestamp NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `sessions_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `settings` (
	`id` varchar(36) NOT NULL,
	`key` varchar(255) NOT NULL,
	`value` json,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `settings_id` PRIMARY KEY(`id`),
	CONSTRAINT `settings_key_unique` UNIQUE(`key`)
);
CREATE TABLE IF NOT EXISTS `tax_details` (
	`id` varchar(36) NOT NULL,
	`bill_id` varchar(36) NOT NULL,
	`tax_type` enum('CGST','SGST','IGST','CESS') NOT NULL,
	`tax_rate` decimal(5,2) NOT NULL,
	`taxable_amount` decimal(12,2) NOT NULL,
	`tax_amount` decimal(12,2) NOT NULL,
	CONSTRAINT `tax_details_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `taxes` (
	`id` varchar(36) NOT NULL,
	`name` varchar(50) NOT NULL,
	`rate` decimal(5,2) NOT NULL,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `taxes_id` PRIMARY KEY(`id`)
);
CREATE TABLE IF NOT EXISTS `users` (
	`id` varchar(36) NOT NULL,
	`username` varchar(255) NOT NULL,
	`password_hash` varchar(255) NOT NULL,
	`role` enum('admin','staff') NOT NULL DEFAULT 'staff',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp NULL DEFAULT NULL,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_username_unique` UNIQUE(`username`)
);
CREATE INDEX `party_idx` ON `party_ledgers` (`party_id`);
CREATE INDEX `date_idx` ON `party_ledgers` (`transaction_date`);
CREATE INDEX `party_idx` ON `bills` (`party_id`);
CREATE INDEX `date_idx` ON `bills` (`date`);
CREATE INDEX `type_idx` ON `bills` (`type`);
CREATE INDEX `bill_idx` ON `bill_items` (`bill_id`);
CREATE INDEX `item_idx` ON `bill_items` (`item_id`);
CREATE INDEX `party_idx` ON `payments` (`party_id`);
CREATE INDEX `bill_idx` ON `payments` (`bill_id`);
CREATE INDEX `bill_idx` ON `tax_details` (`bill_id`);
CREATE INDEX `category_idx` ON `expenses` (`category_id`);
CREATE INDEX `date_idx` ON `expenses` (`date`);