

CREATE TABLE `account_types` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(60) NOT NULL,
  `nature` enum('debit','credit') NOT NULL DEFAULT 'debit',
  `category` enum('asset','liability','equity','income','expense') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('1','Cash & Bank','debit','asset');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('2','Accounts Receivable','debit','asset');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('3','Inventory / Stock','debit','asset');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('4','Fixed Assets','debit','asset');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('5','Accounts Payable','credit','liability');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('6','Loans Payable','credit','liability');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('7','Owner Equity','credit','equity');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('8','Sales Revenue','credit','income');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('9','Other Income','credit','income');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('10','Cost of Goods Sold','debit','expense');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('11','Operating Expenses','debit','expense');
INSERT INTO `account_types` (`id`,`name`,`nature`,`category`) VALUES ('12','Other Expenses','debit','expense');


CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `account_type_id` int(11) NOT NULL DEFAULT 1,
  `code` varchar(20) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `opening_balance` decimal(14,2) DEFAULT 0.00,
  `balance_type` enum('debit','credit') DEFAULT 'debit',
  `is_system` tinyint(1) DEFAULT 0,
  `status` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `account_type_id` (`account_type_id`),
  CONSTRAINT `accounts_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `accounts_ibfk_2` FOREIGN KEY (`account_type_id`) REFERENCES `account_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('1','1','1','1001','Cash in Hand',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('2','1','1','1002','Bank Account',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('3','1','2','1100','Accounts Receivable',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('4','1','3','1200','Inventory / Stock',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('5','1','5','2001','Accounts Payable',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('6','1','7','3001','Owner Capital',NULL,'0.00','credit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('7','1','8','4001','Sales Revenue',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('8','1','9','4002','Other Income',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('9','1','10','5001','Cost of Goods Sold',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('10','1','11','6001','Salary & Wages',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('11','1','11','6002','Rent Expense',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('12','1','11','6003','Utilities',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('13','1','11','6004','Transport & Delivery',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('14','1','12','6005','Miscellaneous Expenses',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('15','2','1','1001','Cash in Hand',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('16','2','1','1002','Bank Account',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('17','2','2','1100','Accounts Receivable',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('18','2','3','1200','Inventory / Stock',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('19','2','5','2001','Accounts Payable',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('20','2','7','3001','Owner Capital',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('21','2','8','4001','Sales Revenue',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('22','2','9','4002','Other Income',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('23','2','10','5001','Cost of Goods Sold',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('24','2','11','6001','Salary & Wages',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('25','2','11','6002','Rent Expense',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('26','2','11','6003','Utilities',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('27','2','11','6004','Transport & Delivery',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('28','2','12','6005','Miscellaneous Expenses',NULL,'0.00','debit','1','1','2026-05-12 08:10:49');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('29','1','1','1003','Mobile Banking A/C',NULL,'0.00','debit','1','1','2026-04-28 21:55:12');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('30','1','4','1201','Machinery',NULL,'0.00','debit','0','1','2026-05-16 12:21:11');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('31','1','4','1202','Transport Vehicles',NULL,'26000.00','debit','0','1','2026-05-16 12:22:28');
INSERT INTO `accounts` (`id`,`company_id`,`account_type_id`,`code`,`name`,`parent_id`,`opening_balance`,`balance_type`,`is_system`,`status`,`created_at`) VALUES ('32','1','1','5002','Auto 5002',NULL,'0.00','debit','0','1','2026-05-18 12:37:35');


CREATE TABLE `activity_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `agent_ledger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) DEFAULT NULL,
  `agent_id` int(11) DEFAULT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `type` enum('commission_due','payment') NOT NULL,
  `amount` decimal(12,2) NOT NULL,
  `note` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `barcode_registry` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `barcode` varchar(60) NOT NULL COMMENT 'The barcode value',
  `barcode_type` enum('internal','manufacturer','ean13','ean8','code128','qr','upc') NOT NULL DEFAULT 'internal',
  `serial_no` varchar(100) DEFAULT NULL COMMENT 'Tied to a specific serial number (optional)',
  `batch_no` varchar(80) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `printed_at` timestamp NULL DEFAULT NULL COMMENT 'When this barcode label was last printed',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_barcode` (`company_id`,`barcode`),
  KEY `product_id` (`product_id`),
  KEY `company_id` (`company_id`),
  KEY `barcode` (`barcode`),
  CONSTRAINT `barcode_registry_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `barcode_registry_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('1','1','Single Jersey',NULL,'1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('2','1','Pique / Polo',NULL,'1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('3','1','Fleece',NULL,'1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('4','1','Interlock',NULL,'1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('5','1','Rib',NULL,'1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('6','1','Terry',NULL,'1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('7','1','Single Jersey',NULL,'1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('8','1','Pique / Polo',NULL,'1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('9','1','Fleece',NULL,'1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('10','1','Interlock',NULL,'1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('11','1','Rib',NULL,'1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('12','1','Terry',NULL,'1','2026-04-27 17:05:47');


CREATE TABLE `challan_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `challan_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) DEFAULT 0.00,
  `note` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `challan_id` (`challan_id`),
  CONSTRAINT `challan_items_ibfk_1` FOREIGN KEY (`challan_id`) REFERENCES `challans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `challans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `challan_no` varchar(30) NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `party_id` int(11) DEFAULT NULL,
  `challan_date` date NOT NULL,
  `delivery_date` date DEFAULT NULL,
  `delivery_address` text DEFAULT NULL,
  `vehicle_no` varchar(30) DEFAULT NULL,
  `driver_name` varchar(80) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('pending','delivered','cancelled') DEFAULT 'pending',
  `converted_to_sale` tinyint(1) DEFAULT 0,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `co_challan_no` (`company_id`,`challan_no`),
  KEY `company_id` (`company_id`),
  KEY `party_id` (`party_id`),
  KEY `sale_id` (`sale_id`),
  CONSTRAINT `challans_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `challans_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL,
  CONSTRAINT `challans_ibfk_3` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `commission_agent` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `agent_name` varchar(150) NOT NULL,
  `agent_phone` varchar(30) NOT NULL,
  `agent_email` varchar(150) DEFAULT NULL,
  `agent_address` text DEFAULT NULL,
  `agent_company` varchar(150) DEFAULT NULL,
  `commission_rate` int(100) DEFAULT NULL,
  `company_id` int(100) DEFAULT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `commission_agent` (`id`,`agent_name`,`agent_phone`,`agent_email`,`agent_address`,`agent_company`,`commission_rate`,`company_id`,`status`,`created_at`) VALUES ('1','Abu khan','01113456789','','','','2','1','Active','2026-05-14 06:27:48');
INSERT INTO `commission_agent` (`id`,`agent_name`,`agent_phone`,`agent_email`,`agent_address`,`agent_company`,`commission_rate`,`company_id`,`status`,`created_at`) VALUES ('2','Babu khan','02223456789','','','','2','1','Active','2026-05-14 06:28:06');
INSERT INTO `commission_agent` (`id`,`agent_name`,`agent_phone`,`agent_email`,`agent_address`,`agent_company`,`commission_rate`,`company_id`,`status`,`created_at`) VALUES ('3','Chalu khan','03333456789','rahim@gmail.com','','','2','1','Active','2026-05-14 06:28:30');
INSERT INTO `commission_agent` (`id`,`agent_name`,`agent_phone`,`agent_email`,`agent_address`,`agent_company`,`commission_rate`,`company_id`,`status`,`created_at`) VALUES ('4','Dhalna Khan','014444444444','','','','4','1','Active','2026-05-14 10:02:47');
INSERT INTO `commission_agent` (`id`,`agent_name`,`agent_phone`,`agent_email`,`agent_address`,`agent_company`,`commission_rate`,`company_id`,`status`,`created_at`) VALUES ('5','Rafiq','2654+613','','','','1','1','Active','2026-05-15 02:09:53');
INSERT INTO `commission_agent` (`id`,`agent_name`,`agent_phone`,`agent_email`,`agent_address`,`agent_company`,`commission_rate`,`company_id`,`status`,`created_at`) VALUES ('6','Rafiq','01554585555','','','','7','1','Inactive','2026-05-15 02:10:37');


CREATE TABLE `companies` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `companies` (`id`,`name`,`address`,`phone`,`email`,`logo`,`created_at`) VALUES ('1','ClickITnexT','Dhaka, Bangladesh','+8801410777545','info@clickitnext.com','uploads/1776788786_6216.jpg','2026-04-21 12:26:26');
INSERT INTO `companies` (`id`,`name`,`address`,`phone`,`email`,`logo`,`created_at`) VALUES ('2','Fibre Nest Knitware Ltd.','Ducati Road, Kathgora, Zirabo, Ashulia, Savar, Dhaka','01332925064','info@fibrenestknitware.com','uploads/1777236445_2020c2c6.jpg','2026-04-26 16:47:25');


CREATE TABLE `company_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `setting_key` varchar(80) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_setting` (`company_id`,`setting_key`),
  CONSTRAINT `company_settings_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=743 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('1','1','timezone','Asia/Dhaka','2026-05-05 05:55:15');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('2','1','date_format','d M Y','2026-04-30 04:53:29');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('3','1','fiscal_year','01-01','2026-04-30 04:53:29');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('4','1','low_stock_notify','1','2026-04-30 04:53:29');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('5','1','due_reminder_days','7','2026-04-30 04:53:29');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('6','1','default_tax','0','2026-04-30 04:53:29');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('7','1','vat_registered','1','2026-05-05 05:55:45');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('8','1','vat_number','','2026-04-30 04:53:29');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('9','1','language','en','2026-05-05 05:55:39');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('597','2','timezone','Asia/Dhaka','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('598','2','date_format','d M Y','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('599','2','fiscal_year','01-01','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('600','2','low_stock_notify','1','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('601','2','due_reminder_days','7','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('602','2','default_tax','0','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('603','2','vat_registered','0','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('604','2','vat_number','','2026-05-12 08:09:23');
INSERT INTO `company_settings` (`id`,`company_id`,`setting_key`,`setting_value`,`updated_at`) VALUES ('605','2','language','en','2026-05-12 08:09:23');


CREATE TABLE `expense_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `type` enum('expense','income') DEFAULT 'expense',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `account_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `expense_categories_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('1','1','Salary & Wages','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('2','1','Rent','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('3','1','Utilities','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('4','1','Transport','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('5','1','Marketing','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('6','1','Office Supplies','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('7','1','Maintenance','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('8','1','Miscellaneous','expense','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('9','1','Sales Revenue','income','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('10','1','Service Income','income','2026-04-28 22:10:00','0');
INSERT INTO `expense_categories` (`id`,`company_id`,`name`,`type`,`created_at`,`account_id`) VALUES ('11','1','Other Income','income','2026-04-28 22:10:00','0');


CREATE TABLE `expenses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `type` enum('expense','income') DEFAULT 'expense',
  `category_id` int(11) DEFAULT NULL,
  `title` varchar(150) NOT NULL,
  `amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `entry_date` date NOT NULL,
  `payment_method` enum('cash','bank','mobile','cheque','other') DEFAULT 'cash',
  `reference_no` varchar(60) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `journal_entry_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `expenses_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `expense_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `expenses` (`id`,`company_id`,`type`,`category_id`,`title`,`amount`,`entry_date`,`payment_method`,`reference_no`,`notes`,`created_by`,`journal_entry_id`,`created_at`) VALUES ('10','1','expense','2','Office Rent','20000.00','2026-05-30','cash','657567','rent of may 2026','4','70','2026-05-30 05:29:27');


CREATE TABLE `fabric_colors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(60) NOT NULL,
  `hex_code` varchar(10) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fabric_colors_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('1','1','White','#FFFFFF','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('2','1','Black','#000000','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('3','1','Navy Blue','#001F5B','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('4','1','Grey Melange','#9E9E9E','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('5','1','Red','#E53935','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('6','1','Royal Blue','#1565C0','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('7','1','Bottle Green','#1B5E20','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('8','1','Yellow','#F9A825','2026-04-27 05:51:18');
INSERT INTO `fabric_colors` (`id`,`company_id`,`name`,`hex_code`,`created_at`) VALUES ('9','1','Orange','#E65100','2026-04-27 05:51:18');


CREATE TABLE `fabric_composition` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fabric_composition_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `fabric_composition` (`id`,`company_id`,`name`,`created_at`) VALUES ('1','1','100% Cotton','2026-04-27 05:51:18');
INSERT INTO `fabric_composition` (`id`,`company_id`,`name`,`created_at`) VALUES ('2','1','100% Polyester','2026-04-27 05:51:18');
INSERT INTO `fabric_composition` (`id`,`company_id`,`name`,`created_at`) VALUES ('3','1','95% Cotton 5% Lycra','2026-04-27 05:51:18');
INSERT INTO `fabric_composition` (`id`,`company_id`,`name`,`created_at`) VALUES ('4','1','80% Cotton 20% Polyester','2026-04-27 05:51:18');
INSERT INTO `fabric_composition` (`id`,`company_id`,`name`,`created_at`) VALUES ('5','1','60% Cotton 40% Polyester','2026-04-27 05:51:18');
INSERT INTO `fabric_composition` (`id`,`company_id`,`name`,`created_at`) VALUES ('6','1','100% Viscose','2026-04-27 05:51:18');


CREATE TABLE `fabric_gsm` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `value` varchar(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fabric_gsm_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('1','1','120','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('2','1','140','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('3','1','160','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('4','1','180','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('5','1','200','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('6','1','220','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('7','1','240','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('8','1','260','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('9','1','280','2026-04-27 05:51:18');
INSERT INTO `fabric_gsm` (`id`,`company_id`,`value`,`created_at`) VALUES ('10','1','300','2026-04-27 05:51:18');


CREATE TABLE `fabric_rolls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `lot_no` varchar(50) DEFAULT NULL,
  `roll_no` varchar(50) DEFAULT NULL,
  `length` decimal(10,2) DEFAULT NULL,
  `remaining_length` decimal(10,2) DEFAULT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`),
  KEY `warehouse_id` (`warehouse_id`),
  CONSTRAINT `fabric_rolls_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fabric_rolls_ibfk_2` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `fabric_width` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `value` varchar(30) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `fabric_width_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('1','1','58\"','2026-04-27 05:51:18');
INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('2','1','60\"','2026-04-27 05:51:18');
INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('3','1','62\"','2026-04-27 05:51:18');
INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('4','1','64\"','2026-04-27 05:51:18');
INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('5','1','66\"','2026-04-27 05:51:18');
INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('6','1','68\"','2026-04-27 05:51:18');
INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('7','1','70\"','2026-04-27 05:51:18');
INSERT INTO `fabric_width` (`id`,`company_id`,`value`,`created_at`) VALUES ('8','1','Open Width','2026-04-27 05:51:18');


CREATE TABLE `invoice_settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `invoice_prefix` varchar(20) DEFAULT 'INV',
  `bill_prefix` varchar(20) DEFAULT 'BILL',
  `invoice_start_number` int(11) DEFAULT 1,
  `show_logo` tinyint(1) DEFAULT 1,
  `show_company_address` tinyint(1) DEFAULT 1,
  `show_party_address` tinyint(1) DEFAULT 1,
  `show_tax_line` tinyint(1) DEFAULT 1,
  `show_discount_line` tinyint(1) DEFAULT 1,
  `default_tax_percent` decimal(6,2) DEFAULT 0.00,
  `default_payment_terms` int(11) DEFAULT 0,
  `invoice_footer_text` text DEFAULT NULL,
  `invoice_terms` text DEFAULT NULL,
  `invoice_notes` text DEFAULT NULL,
  `paper_size` enum('A4','letter','thermal80') DEFAULT 'A4',
  `currency_symbol` varchar(10) DEFAULT '?',
  `currency_code` varchar(5) DEFAULT 'BDT',
  `date_format` varchar(20) DEFAULT 'd M Y',
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_inv_settings` (`company_id`),
  CONSTRAINT `invoice_settings_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `invoice_settings` (`id`,`company_id`,`invoice_prefix`,`bill_prefix`,`invoice_start_number`,`show_logo`,`show_company_address`,`show_party_address`,`show_tax_line`,`show_discount_line`,`default_tax_percent`,`default_payment_terms`,`invoice_footer_text`,`invoice_terms`,`invoice_notes`,`paper_size`,`currency_symbol`,`currency_code`,`date_format`,`updated_at`) VALUES ('1','1','INV','BILL','1','1','1','1','1','1','0.00','0','Thank you for your business','Goods once sold are not returnable','Only Cash pay','A4','$','USD','d M Y','2026-05-30 12:11:25');
INSERT INTO `invoice_settings` (`id`,`company_id`,`invoice_prefix`,`bill_prefix`,`invoice_start_number`,`show_logo`,`show_company_address`,`show_party_address`,`show_tax_line`,`show_discount_line`,`default_tax_percent`,`default_payment_terms`,`invoice_footer_text`,`invoice_terms`,`invoice_notes`,`paper_size`,`currency_symbol`,`currency_code`,`date_format`,`updated_at`) VALUES ('2','2','INV','BILL','1','1','1','1','1','1','0.00','0',NULL,NULL,NULL,'A4','?','BDT','d M Y','2026-04-30 04:53:29');


CREATE TABLE `journal_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `entry_no` varchar(30) NOT NULL,
  `entry_date` date NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `ref_type` varchar(30) DEFAULT NULL,
  `ref_id` int(11) DEFAULT NULL,
  `total_debit` decimal(14,2) DEFAULT 0.00,
  `total_credit` decimal(14,2) DEFAULT 0.00,
  `status` enum('draft','posted') DEFAULT 'posted',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `journal_entries_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=76 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('54','1','JE-20260010','2026-05-26','Purchase Bill BILL-20260001',NULL,NULL,'50000.00','50000.00','posted','4','2026-05-26 12:12:53');
INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('61','1','JE-20260012','2026-05-27','Purchase Return PR-20260001',NULL,NULL,'12500.00','12500.00','posted','4','2026-05-27 01:05:41');
INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('62','1','JE-20260013','2026-05-27','Sales Invoice INV-20260001',NULL,NULL,'90000.00','90000.00','posted','4','2026-05-27 04:28:50');
INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('68','1','JE-20260017','2026-05-27','Sales Return SR-20260001',NULL,NULL,'24000.00','12000.00','posted','4','2026-05-27 06:32:10');
INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('69','1','JE-20260018','2026-05-27','Sales Invoice INV-20260002',NULL,NULL,'24000.00','24000.00','posted','4','2026-05-27 06:41:25');
INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('70','1','JE-20260019','2026-05-30','Expense: Office Rent','expense',NULL,'20000.00','20000.00','posted','4','2026-05-30 05:29:27');
INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('74','1','JE-20260020','2026-06-02','Purchase Bill BILL-20260002',NULL,NULL,'40000.00','40000.00','posted','4','2026-06-02 05:23:23');
INSERT INTO `journal_entries` (`id`,`company_id`,`entry_no`,`entry_date`,`description`,`ref_type`,`ref_id`,`total_debit`,`total_credit`,`status`,`created_by`,`created_at`) VALUES ('75','1','JE-20260021','2026-06-02','Purchase Bill BILL-20260003',NULL,NULL,'3200.00','3200.00','posted','4','2026-06-02 05:42:40');


CREATE TABLE `journal_lines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `journal_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `description` varchar(200) DEFAULT NULL,
  `debit` decimal(14,2) DEFAULT 0.00,
  `credit` decimal(14,2) DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `journal_id` (`journal_id`),
  KEY `account_id` (`account_id`),
  CONSTRAINT `journal_lines_ibfk_1` FOREIGN KEY (`journal_id`) REFERENCES `journal_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `journal_lines_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=177 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('123','54','4','Purchase Inventory','50000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('124','54','5','Purchase due','0.00','50000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('137','61','1','Refund received from supplier','12500.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('138','61','4','Purchase return inventory','0.00','12500.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('139','62','3','Customer due','45000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('140','62','7','Sales revenue','0.00','45000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('141','62','9','Cost of goods sold','45000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('142','62','4','Inventory reduction','0.00','45000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('158','68','32','Sales return','12000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('159','68','1','Refund to customer','0.00','12000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('160','68','4','Stock returned','12000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('161','69','3','Customer due','12000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('162','69','7','Sales revenue','0.00','12000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('163','69','9','Cost of goods sold','12000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('164','69','4','Inventory reduction','0.00','12000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('165','70','11','Expense: Office Rent','20000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('166','70','1','Expense: Office Rent','0.00','20000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('173','74','4','Purchase Inventory','40000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('174','74','1','Payment for purchase','0.00','40000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('175','75','4','Purchase Inventory','3200.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('176','75','1','Payment for purchase','0.00','3200.00');


CREATE TABLE `notification_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `type` enum('email','sms') NOT NULL DEFAULT 'email',
  `channel` enum('due_reminder','low_stock','manual') NOT NULL DEFAULT 'manual',
  `party_id` int(11) DEFAULT NULL,
  `recipient` varchar(120) NOT NULL,
  `subject` varchar(200) DEFAULT NULL,
  `status` enum('sent','failed','skipped') DEFAULT 'sent',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `parties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `type` enum('customer','supplier','both') DEFAULT 'customer',
  `name` varchar(150) NOT NULL,
  `group_id` int(11) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `credit_limit` decimal(12,2) DEFAULT 0.00,
  `opening_balance` decimal(12,2) DEFAULT 0.00,
  `balance_type` enum('debit','credit') DEFAULT 'debit',
  `tax_number` varchar(60) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `type` (`type`),
  KEY `group_id` (`group_id`),
  CONSTRAINT `parties_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `parties_ibfk_2` FOREIGN KEY (`group_id`) REFERENCES `party_groups` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `parties` (`id`,`company_id`,`type`,`name`,`group_id`,`phone`,`email`,`address`,`credit_limit`,`opening_balance`,`balance_type`,`tax_number`,`notes`,`status`,`created_at`) VALUES ('1','1','customer','Pakiza Textiles Miles Ltd.','1','01333333333','info@pakiza.com','Savar, Dhaka','0.00','0.00','debit','','','1','2026-04-27 17:14:48');
INSERT INTO `parties` (`id`,`company_id`,`type`,`name`,`group_id`,`phone`,`email`,`address`,`credit_limit`,`opening_balance`,`balance_type`,`tax_number`,`notes`,`status`,`created_at`) VALUES ('2','1','supplier','Pretty Composite Textiles Ltd.','3','01555555555','info@prettygroupbd.com','Ashulia, Dhaka','0.00','0.00','debit','','','1','2026-04-28 00:52:19');
INSERT INTO `parties` (`id`,`company_id`,`type`,`name`,`group_id`,`phone`,`email`,`address`,`credit_limit`,`opening_balance`,`balance_type`,`tax_number`,`notes`,`status`,`created_at`) VALUES ('3','1','both','Rahman Composite Textiles Ltd.','4','01410777545','info@pakiza.com','Dhaka','0.00','0.00','debit','','','1','2026-05-15 12:48:27');


CREATE TABLE `party_groups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `party_groups_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `party_groups` (`id`,`company_id`,`name`,`created_at`) VALUES ('1','1','Retail Customer','2026-04-27 17:05:47');
INSERT INTO `party_groups` (`id`,`company_id`,`name`,`created_at`) VALUES ('2','1','Wholesale Customer','2026-04-27 17:05:47');
INSERT INTO `party_groups` (`id`,`company_id`,`name`,`created_at`) VALUES ('3','1','Local Supplier','2026-04-27 17:05:47');
INSERT INTO `party_groups` (`id`,`company_id`,`name`,`created_at`) VALUES ('4','1','Import Supplier','2026-04-27 17:05:47');


CREATE TABLE `payments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `type` enum('receive','pay','refund_out','refund') NOT NULL DEFAULT 'receive',
  `party_id` int(11) DEFAULT NULL,
  `ref_id` int(11) DEFAULT NULL,
  `ref_type` enum('sale','purchase','sale_return','purchase_return','advance','other') DEFAULT 'other',
  `amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `method` enum('cash','bank','mobile','cheque','adjustment') DEFAULT 'cash',
  `refund_method` varchar(30) DEFAULT NULL,
  `payment_date` date NOT NULL,
  `reference_no` varchar(60) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `party_id` (`party_id`),
  CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`refund_method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('56','1','refund','2','36','purchase_return','12500.00','adjustment',NULL,'2026-05-27',NULL,'Refund on return PR-20260001','4','2026-05-27 01:05:41');
INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`refund_method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('62','1','refund_out','3','19','sale_return','12000.00','cash',NULL,'2026-05-27',NULL,'Sales return adjustment SR-20260001','4','2026-05-27 06:32:10');
INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`refund_method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('66','1','pay','2','39','purchase','40000.00','cash',NULL,'2026-06-02',NULL,NULL,'4','2026-06-02 05:23:23');
INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`refund_method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('67','1','pay','3','40','purchase','3200.00','cash',NULL,'2026-06-02',NULL,NULL,'4','2026-06-02 05:42:40');


CREATE TABLE `product_batches` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `batch_no` varchar(80) NOT NULL COMMENT 'Batch or Lot number',
  `lot_no` varchar(80) DEFAULT NULL,
  `purchase_id` int(11) DEFAULT NULL,
  `qty_received` decimal(12,3) NOT NULL DEFAULT 0.000,
  `qty_remaining` decimal(12,3) NOT NULL DEFAULT 0.000,
  `mfg_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `status` enum('active','consumed','expired','recalled') NOT NULL DEFAULT 'active',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_product_batch` (`company_id`,`product_id`,`batch_no`),
  KEY `product_id` (`product_id`),
  KEY `company_id` (`company_id`),
  KEY `purchase_id` (`purchase_id`),
  KEY `expiry_date` (`expiry_date`),
  KEY `status` (`status`),
  CONSTRAINT `product_batches_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_batches_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `product_batches` (`id`,`company_id`,`product_id`,`batch_no`,`lot_no`,`purchase_id`,`qty_received`,`qty_remaining`,`mfg_date`,`expiry_date`,`status`,`notes`,`created_at`) VALUES ('4','1','5','2025-101125','101125','39','125.000','125.000',NULL,'2028-02-02','active',NULL,'2026-06-02 05:23:23');
INSERT INTO `product_batches` (`id`,`company_id`,`product_id`,`batch_no`,`lot_no`,`purchase_id`,`qty_received`,`qty_remaining`,`mfg_date`,`expiry_date`,`status`,`notes`,`created_at`) VALUES ('5','1','5','7567','7857857','40','10.000','10.000',NULL,'2026-06-25','active',NULL,'2026-06-02 05:42:40');


CREATE TABLE `product_serials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `serial_no` varchar(100) NOT NULL,
  `batch_no` varchar(80) DEFAULT NULL COMMENT 'Optional batch reference',
  `purchase_id` int(11) DEFAULT NULL COMMENT 'Which purchase this came in on',
  `sale_id` int(11) DEFAULT NULL COMMENT 'Which sale this went out on (NULL = in stock)',
  `status` enum('in_stock','sold','returned','damaged','expired') NOT NULL DEFAULT 'in_stock',
  `mfg_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `warranty_end` date DEFAULT NULL COMMENT 'Auto-calculated on sale',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_product_serial` (`company_id`,`product_id`,`serial_no`),
  KEY `product_id` (`product_id`),
  KEY `company_id` (`company_id`),
  KEY `purchase_id` (`purchase_id`),
  KEY `sale_id` (`sale_id`),
  KEY `status` (`status`),
  KEY `expiry_date` (`expiry_date`),
  CONSTRAINT `product_serials_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `product_serials_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(150) NOT NULL,
  `sku` varchar(60) DEFAULT NULL,
  `manufacturer_barcode` varchar(100) DEFAULT NULL,
  `internal_barcode` varchar(100) DEFAULT NULL,
  `barcode_type` enum('manufacturer','internal') DEFAULT 'manufacturer',
  `category_id` int(11) DEFAULT NULL,
  `unit_id` int(11) DEFAULT NULL,
  `gsm_id` int(11) DEFAULT NULL,
  `width_id` int(11) DEFAULT NULL,
  `color_id` int(11) DEFAULT NULL,
  `composition_id` int(11) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `buy_price` decimal(12,2) DEFAULT 0.00,
  `barcode_manufacturer` varchar(60) DEFAULT NULL COMMENT 'Manufacturer/EAN/UPC barcode',
  `barcode_internal` varchar(60) DEFAULT NULL COMMENT 'Internal barcode assigned by your company',
  `tracking_type` enum('none','serial','batch','lot') NOT NULL DEFAULT 'none' COMMENT 'How this product is tracked',
  `has_expiry` tinyint(1) NOT NULL DEFAULT 0 COMMENT 'Does this product have an expiry date?',
  `warranty_days` int(11) DEFAULT NULL COMMENT 'Default warranty in days (0 = no warranty)',
  `sell_price` decimal(12,2) DEFAULT 0.00,
  `stock_qty` decimal(12,2) DEFAULT 0.00,
  `min_stock` decimal(12,2) DEFAULT 0.00,
  `image` varchar(255) DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_sku` (`company_id`,`sku`),
  UNIQUE KEY `uq_manufacturer_barcode` (`manufacturer_barcode`),
  UNIQUE KEY `uq_internal_barcode` (`internal_barcode`),
  KEY `category_id` (`category_id`),
  KEY `unit_id` (`unit_id`),
  KEY `idx_manufacturer_barcode` (`manufacturer_barcode`),
  KEY `idx_internal_barcode` (`internal_barcode`),
  KEY `idx_barcode_mfr` (`barcode_manufacturer`),
  KEY `idx_barcode_int` (`barcode_internal`),
  KEY `idx_sku` (`sku`),
  CONSTRAINT `products_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `products_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `products_ibfk_3` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `products` (`id`,`company_id`,`name`,`sku`,`manufacturer_barcode`,`internal_barcode`,`barcode_type`,`category_id`,`unit_id`,`gsm_id`,`width_id`,`color_id`,`composition_id`,`description`,`buy_price`,`barcode_manufacturer`,`barcode_internal`,`tracking_type`,`has_expiry`,`warranty_days`,`sell_price`,`stock_qty`,`min_stock`,`image`,`status`,`created_at`) VALUES ('4','1','Single Jercey 100% cotton','sg100','789456','INT515347','internal','1','10','1','1','9','1','','500.00',NULL,NULL,'none','0',NULL,'600.00','0.00','0.00','','1','2026-05-19 23:19:25');
INSERT INTO `products` (`id`,`company_id`,`name`,`sku`,`manufacturer_barcode`,`internal_barcode`,`barcode_type`,`category_id`,`unit_id`,`gsm_id`,`width_id`,`color_id`,`composition_id`,`description`,`buy_price`,`barcode_manufacturer`,`barcode_internal`,`tracking_type`,`has_expiry`,`warranty_days`,`sell_price`,`stock_qty`,`min_stock`,`image`,`status`,`created_at`) VALUES ('5','1','Double Jersey 300 GSM Yellow','DJ-300-YW',NULL,NULL,'manufacturer','1','10','10','8','8','1','0','320.00','895454785214','INT131353893','batch','1',NULL,'350.00','135.00','0.00','0','1','2026-05-31 22:35:46');
INSERT INTO `products` (`id`,`company_id`,`name`,`sku`,`manufacturer_barcode`,`internal_barcode`,`barcode_type`,`category_id`,`unit_id`,`gsm_id`,`width_id`,`color_id`,`composition_id`,`description`,`buy_price`,`barcode_manufacturer`,`barcode_internal`,`tracking_type`,`has_expiry`,`warranty_days`,`sell_price`,`stock_qty`,`min_stock`,`image`,`status`,`created_at`) VALUES ('6','1','Silk Special Cotton Jersey 80 GSM','SS-80',NULL,NULL,'manufacturer','6','7','1','8','9','1','0','800.00','8888','INT727965306','serial','1','365','1000.00','0.00','0.00','0','1','2026-06-02 06:48:55');


CREATE TABLE `proforma_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `proforma_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(10,2) DEFAULT 0.00,
  `total` decimal(14,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `proforma_id` (`proforma_id`),
  CONSTRAINT `proforma_items_ibfk_1` FOREIGN KEY (`proforma_id`) REFERENCES `proformas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `proforma_mail_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `proforma_id` int(11) DEFAULT NULL,
  `to_email` varchar(255) DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `sent_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `proformas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `proforma_no` varchar(30) NOT NULL,
  `party_id` int(11) DEFAULT NULL,
  `proforma_date` date NOT NULL,
  `valid_until` date DEFAULT NULL,
  `reference` varchar(100) DEFAULT NULL,
  `subtotal` decimal(14,2) DEFAULT 0.00,
  `discount_type` enum('flat','percent') DEFAULT 'flat',
  `discount_value` decimal(10,2) DEFAULT 0.00,
  `discount_amount` decimal(14,2) DEFAULT 0.00,
  `tax_percent` decimal(6,2) DEFAULT 0.00,
  `tax_amount` decimal(14,2) DEFAULT 0.00,
  `shipping` decimal(10,2) DEFAULT 0.00,
  `grand_total` decimal(14,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `terms` text DEFAULT NULL,
  `status` enum('draft','sent','accepted','rejected','converted') DEFAULT 'draft',
  `converted_sale_id` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `co_proforma_no` (`company_id`,`proforma_no`),
  KEY `company_id` (`company_id`),
  KEY `party_id` (`party_id`),
  CONSTRAINT `proformas_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `proformas_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `purchase_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `purchase_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(10,2) DEFAULT 0.00,
  `total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `batch_no` varchar(80) DEFAULT NULL COMMENT 'Batch number entered at receiving',
  `lot_no` varchar(80) DEFAULT NULL COMMENT 'Lot number',
  `serial_nos` text DEFAULT NULL COMMENT 'Comma-separated serial numbers',
  `mfg_date` date DEFAULT NULL COMMENT 'Manufacturing date',
  `expiry_date` date DEFAULT NULL COMMENT 'Expiry date for this batch/lot',
  PRIMARY KEY (`id`),
  KEY `purchase_id` (`purchase_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `purchase_items_ibfk_1` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE CASCADE,
  CONSTRAINT `purchase_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `purchase_items` (`id`,`purchase_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`discount`,`total`,`batch_no`,`lot_no`,`serial_nos`,`mfg_date`,`expiry_date`) VALUES ('34','34','4','Single Jercey 100% cotton','ROL','100.000','500.00','0.00','50000.00',NULL,NULL,NULL,NULL,NULL);
INSERT INTO `purchase_items` (`id`,`purchase_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`discount`,`total`,`batch_no`,`lot_no`,`serial_nos`,`mfg_date`,`expiry_date`) VALUES ('39','39','5','Double Jersey 300 GSM Yellow','ROL','125.000','320.00','0.00','40000.00','2025-101125','101125','',NULL,'2028-02-02');
INSERT INTO `purchase_items` (`id`,`purchase_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`discount`,`total`,`batch_no`,`lot_no`,`serial_nos`,`mfg_date`,`expiry_date`) VALUES ('40','40','5','Double Jersey 300 GSM Yellow','ROL','10.000','320.00','0.00','3200.00','7567','7857857','',NULL,'2026-06-25');


CREATE TABLE `purchase_return_exchange_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(14,2) NOT NULL DEFAULT 0.00,
  PRIMARY KEY (`id`),
  KEY `return_id` (`return_id`),
  CONSTRAINT `purchase_return_exchange_items_ibfk_1` FOREIGN KEY (`return_id`) REFERENCES `purchase_returns` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `purchase_return_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `reason` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `return_id` (`return_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `purchase_return_items_ibfk_1` FOREIGN KEY (`return_id`) REFERENCES `purchase_returns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `purchase_return_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `purchase_return_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`,`reason`) VALUES ('34','36','4','Single Jercey 100% cotton','ROL','25.000','500.00','12500.00',NULL);
INSERT INTO `purchase_return_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`,`reason`) VALUES ('35','37','4','Single Jercey 100% cotton','ROL','15.000','500.00','7500.00',NULL);


CREATE TABLE `purchase_returns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `return_no` varchar(30) NOT NULL,
  `purchase_id` int(11) DEFAULT NULL,
  `party_id` int(11) DEFAULT NULL,
  `return_date` date NOT NULL,
  `total_amount` decimal(14,2) DEFAULT 0.00,
  `reason` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'approved',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `return_type` enum('refund','exchange_same','exchange_diff') NOT NULL DEFAULT 'refund',
  `refund_amount` decimal(14,2) DEFAULT 0.00,
  `refund_method` varchar(30) DEFAULT 'cash',
  `exchange_value` decimal(14,2) DEFAULT 0.00,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_return_no` (`company_id`,`return_no`),
  KEY `purchase_id` (`purchase_id`),
  KEY `party_id` (`party_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `purchase_returns_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `purchase_returns_ibfk_2` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE SET NULL,
  CONSTRAINT `purchase_returns_ibfk_3` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `purchase_returns` (`id`,`company_id`,`return_no`,`purchase_id`,`party_id`,`return_date`,`total_amount`,`reason`,`notes`,`status`,`created_by`,`created_at`,`return_type`,`refund_amount`,`refund_method`,`exchange_value`) VALUES ('36','1','PR-20260001','34','2','2026-05-27','12500.00','quality issue','','approved','4','2026-05-27 01:05:41','refund','12500.00','adjustment','0.00');
INSERT INTO `purchase_returns` (`id`,`company_id`,`return_no`,`purchase_id`,`party_id`,`return_date`,`total_amount`,`reason`,`notes`,`status`,`created_by`,`created_at`,`return_type`,`refund_amount`,`refund_method`,`exchange_value`) VALUES ('37','1','PR-20260002','34','2','2026-05-27','7500.00','wrong specification','','approved','4','2026-05-27 01:16:56','exchange_same','0.00','cash','7500.00');


CREATE TABLE `purchases` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `bill_no` varchar(30) NOT NULL,
  `ref_no` varchar(60) DEFAULT NULL,
  `party_id` int(11) DEFAULT NULL,
  `purchase_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(14,2) DEFAULT 0.00,
  `discount_type` enum('flat','percent') DEFAULT 'flat',
  `discount_value` decimal(10,2) DEFAULT 0.00,
  `discount_amount` decimal(14,2) DEFAULT 0.00,
  `tax_percent` decimal(6,2) DEFAULT 0.00,
  `tax_amount` decimal(14,2) DEFAULT 0.00,
  `shipping` decimal(10,2) DEFAULT 0.00,
  `grand_total` decimal(14,2) DEFAULT 0.00,
  `paid_amount` decimal(14,2) DEFAULT 0.00,
  `due_amount` decimal(14,2) DEFAULT 0.00,
  `payment_method` enum('cash','bank','mobile','credit','cheque') DEFAULT 'cash',
  `payment_status` enum('paid','partial','due','draft') DEFAULT 'due',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_bill` (`company_id`,`bill_no`),
  KEY `party_id` (`party_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `purchases_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `purchases_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `purchases` (`id`,`company_id`,`bill_no`,`ref_no`,`party_id`,`purchase_date`,`due_date`,`subtotal`,`discount_type`,`discount_value`,`discount_amount`,`tax_percent`,`tax_amount`,`shipping`,`grand_total`,`paid_amount`,`due_amount`,`payment_method`,`payment_status`,`notes`,`created_by`,`created_at`) VALUES ('34','1','BILL-20260001','555','2','2026-05-26',NULL,'50000.00','','0.00','0.00','0.00','0.00','0.00','50000.00','12500.00','37500.00','credit','partial','','4','2026-05-26 12:12:53');
INSERT INTO `purchases` (`id`,`company_id`,`bill_no`,`ref_no`,`party_id`,`purchase_date`,`due_date`,`subtotal`,`discount_type`,`discount_value`,`discount_amount`,`tax_percent`,`tax_amount`,`shipping`,`grand_total`,`paid_amount`,`due_amount`,`payment_method`,`payment_status`,`notes`,`created_by`,`created_at`) VALUES ('39','1','BILL-20260002','pretty-7865','2','2026-06-02',NULL,'40000.00','','0.00','0.00','0.00','0.00','0.00','40000.00','40000.00','0.00','cash','paid','','4','2026-06-02 05:23:23');
INSERT INTO `purchases` (`id`,`company_id`,`bill_no`,`ref_no`,`party_id`,`purchase_date`,`due_date`,`subtotal`,`discount_type`,`discount_value`,`discount_amount`,`tax_percent`,`tax_amount`,`shipping`,`grand_total`,`paid_amount`,`due_amount`,`payment_method`,`payment_status`,`notes`,`created_by`,`created_at`) VALUES ('40','1','BILL-20260003','','3','2026-06-02',NULL,'3200.00','','0.00','0.00','0.00','0.00','0.00','3200.00','3200.00','0.00','cash','paid','','4','2026-06-02 05:42:40');


CREATE TABLE `role_permissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_id` int(11) NOT NULL,
  `module` varchar(60) NOT NULL,
  `can_view` tinyint(1) DEFAULT 0,
  `can_create` tinyint(1) DEFAULT 0,
  `can_edit` tinyint(1) DEFAULT 0,
  `can_delete` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_module` (`role_id`,`module`),
  CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('21','5','sales','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('40','6','dashboard','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('41','6','sales','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('42','6','purchase','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('43','6','billing','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('44','6','inventory','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('45','6','products','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('46','6','fabric_special','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('47','6','accounts','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('48','6','expenses','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('49','6','reports','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('50','6','parties','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('51','6','hr','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('52','6','users','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('53','6','roles','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('54','4','inventory','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('55','4','products','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('56','4','fabric_special','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('57','2','dashboard','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('58','2','sales','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('59','2','purchase','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('60','2','billing','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('61','2','inventory','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('62','2','products','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('63','2','fabric_special','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('64','3','accounts','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('65','3','expenses','1','1','1','1');
INSERT INTO `role_permissions` (`id`,`role_id`,`module`,`can_view`,`can_create`,`can_edit`,`can_delete`) VALUES ('66','3','reports','1','1','1','1');


CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `color` varchar(20) DEFAULT '#6b7a95',
  `is_system` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `roles` (`id`,`role_name`,`description`,`color`,`is_system`,`created_at`) VALUES ('1','SystemAdmin','Full access to all modules and companies','#ef4444','1','2026-04-27 04:03:29');
INSERT INTO `roles` (`id`,`role_name`,`description`,`color`,`is_system`,`created_at`) VALUES ('2','SalesManager','','#00c9a7','0','2026-04-27 04:03:29');
INSERT INTO `roles` (`id`,`role_name`,`description`,`color`,`is_system`,`created_at`) VALUES ('3','Accountant','','#ec4899','0','2026-04-27 04:03:29');
INSERT INTO `roles` (`id`,`role_name`,`description`,`color`,`is_system`,`created_at`) VALUES ('4','StoreKeeper','','#6b7a95','0','2026-04-27 04:03:29');
INSERT INTO `roles` (`id`,`role_name`,`description`,`color`,`is_system`,`created_at`) VALUES ('5','Salesman','','#6b7a95','0','2026-04-27 04:03:29');
INSERT INTO `roles` (`id`,`role_name`,`description`,`color`,`is_system`,`created_at`) VALUES ('6','Admin','','#6b7a95','0','2026-04-27 04:03:29');


CREATE TABLE `sale_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sale_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `discount` decimal(10,2) DEFAULT 0.00,
  `total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `serial_no` varchar(100) DEFAULT NULL COMMENT 'Serial number sold',
  `batch_no` varchar(80) DEFAULT NULL COMMENT 'Batch sold from',
  `warranty_days` int(11) DEFAULT NULL COMMENT 'Warranty period in days',
  `warranty_end_date` date DEFAULT NULL COMMENT 'Auto-calculated warranty end',
  PRIMARY KEY (`id`),
  KEY `sale_id` (`sale_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `sale_items_ibfk_1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sale_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `sale_items` (`id`,`sale_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`discount`,`total`,`serial_no`,`batch_no`,`warranty_days`,`warranty_end_date`) VALUES ('38','34','4','Single Jercey 100% cotton','Roll','75.000','600.00','0.00','45000.00',NULL,NULL,NULL,NULL);
INSERT INTO `sale_items` (`id`,`sale_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`discount`,`total`,`serial_no`,`batch_no`,`warranty_days`,`warranty_end_date`) VALUES ('39','35','4','Single Jercey 100% cotton','Roll','20.000','600.00','0.00','12000.00',NULL,NULL,NULL,NULL);


CREATE TABLE `sales` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `invoice_no` varchar(30) NOT NULL,
  `party_id` int(11) DEFAULT NULL,
  `agent_id` int(100) DEFAULT NULL,
  `commission_rate` decimal(10,2) DEFAULT NULL,
  `commission_amount` decimal(10,2) DEFAULT NULL,
  `sale_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `subtotal` decimal(14,2) DEFAULT 0.00,
  `discount_type` enum('flat','percent') DEFAULT 'flat',
  `discount_value` decimal(10,2) DEFAULT 0.00,
  `discount_amount` decimal(14,2) DEFAULT 0.00,
  `tax_percent` decimal(6,2) DEFAULT 0.00,
  `tax_amount` decimal(14,2) DEFAULT 0.00,
  `shipping` decimal(10,2) DEFAULT 0.00,
  `grand_total` decimal(14,2) DEFAULT 0.00,
  `paid_amount` decimal(14,2) DEFAULT 0.00,
  `due_amount` decimal(14,2) DEFAULT 0.00,
  `payment_method` enum('cash','bank','mobile','credit','cheque') DEFAULT 'cash',
  `payment_status` enum('paid','partial','due','draft') DEFAULT 'due',
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_invoice` (`company_id`,`invoice_no`),
  KEY `party_id` (`party_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `sales_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sales_ibfk_2` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `sales` (`id`,`company_id`,`invoice_no`,`party_id`,`agent_id`,`commission_rate`,`commission_amount`,`sale_date`,`due_date`,`subtotal`,`discount_type`,`discount_value`,`discount_amount`,`tax_percent`,`tax_amount`,`shipping`,`grand_total`,`paid_amount`,`due_amount`,`payment_method`,`payment_status`,`notes`,`created_by`,`created_at`) VALUES ('34','1','INV-20260001','3',NULL,'0.00','0.00','2026-05-27',NULL,'45000.00','flat','0.00','0.00','0.00','0.00','0.00','45000.00','12000.00','33000.00','credit','partial','','4','2026-05-27 04:28:50');
INSERT INTO `sales` (`id`,`company_id`,`invoice_no`,`party_id`,`agent_id`,`commission_rate`,`commission_amount`,`sale_date`,`due_date`,`subtotal`,`discount_type`,`discount_value`,`discount_amount`,`tax_percent`,`tax_amount`,`shipping`,`grand_total`,`paid_amount`,`due_amount`,`payment_method`,`payment_status`,`notes`,`created_by`,`created_at`) VALUES ('35','1','INV-20260002','3',NULL,'0.00','0.00','2026-05-27',NULL,'12000.00','flat','0.00','0.00','0.00','0.00','0.00','12000.00','0.00','12000.00','cash','due','','4','2026-05-27 06:41:25');


CREATE TABLE `sales_return_exchange_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(255) DEFAULT NULL,
  `unit` varchar(50) DEFAULT NULL,
  `qty` decimal(10,3) DEFAULT NULL,
  `rate` decimal(10,2) DEFAULT NULL,
  `total` decimal(12,2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `sales_return_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `return_id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total` decimal(14,2) NOT NULL DEFAULT 0.00,
  `reason` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `return_id` (`return_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `sales_return_items_ibfk_1` FOREIGN KEY (`return_id`) REFERENCES `sales_returns` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sales_return_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `sales_return_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`,`reason`) VALUES ('21','19','4','Single Jercey 100% cotton','Roll','20.000','600.00','12000.00',NULL);


CREATE TABLE `sales_returns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `return_no` varchar(30) NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `party_id` int(11) DEFAULT NULL,
  `return_date` date NOT NULL,
  `total_amount` decimal(14,2) DEFAULT 0.00,
  `reason` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'approved',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `return_type` varchar(20) DEFAULT NULL,
  `refund_amount` decimal(12,2) DEFAULT NULL,
  `refund_method` varchar(50) DEFAULT NULL,
  `exchange_value` decimal(12,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_return_no` (`company_id`,`return_no`),
  KEY `sale_id` (`sale_id`),
  KEY `party_id` (`party_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `sales_returns_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `sales_returns_ibfk_2` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE SET NULL,
  CONSTRAINT `sales_returns_ibfk_3` FOREIGN KEY (`party_id`) REFERENCES `parties` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `sales_returns` (`id`,`company_id`,`return_no`,`sale_id`,`party_id`,`return_date`,`total_amount`,`reason`,`notes`,`status`,`created_by`,`created_at`,`return_type`,`refund_amount`,`refund_method`,`exchange_value`) VALUES ('19','1','SR-20260001','34','3','2026-05-27','12000.00','','','approved','4','2026-05-27 06:32:10','refund','12000.00','cash','0.00');


CREATE TABLE `stock_adjustment_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `adjustment_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `product_name` varchar(150) NOT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `qty_before` decimal(12,3) DEFAULT 0.000,
  `qty_adjusted` decimal(12,3) NOT NULL DEFAULT 0.000,
  `qty_after` decimal(12,3) DEFAULT 0.000,
  `cost_price` decimal(12,2) DEFAULT 0.00,
  `reason` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `adjustment_id` (`adjustment_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `stock_adjustment_items_ibfk_1` FOREIGN KEY (`adjustment_id`) REFERENCES `stock_adjustments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_adjustment_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `stock_adjustments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `adj_no` varchar(30) NOT NULL,
  `adj_date` date NOT NULL,
  `type` enum('in','out') NOT NULL DEFAULT 'in',
  `reason_category` enum('damage','expired','found','correction','transfer','sample','other') DEFAULT 'correction',
  `notes` text DEFAULT NULL,
  `total_qty` decimal(12,3) DEFAULT 0.000,
  `total_value` decimal(14,2) DEFAULT 0.00,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `company_adj_no` (`company_id`,`adj_no`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `stock_adjustments_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `stock_ledger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `type` enum('in','out','adjustment','opening') DEFAULT 'in',
  `ref_type` enum('sale','purchase','adjustment','opening') DEFAULT 'opening',
  `ref_id` int(11) DEFAULT NULL,
  `qty` decimal(12,3) NOT NULL DEFAULT 0.000,
  `rate` decimal(12,2) DEFAULT 0.00,
  `balance_qty` decimal(12,3) DEFAULT 0.000,
  `notes` varchar(200) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `stock_ledger_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE,
  CONSTRAINT `stock_ledger_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=214 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('177','1','4','in','purchase','34','100.000','500.00','100.000',NULL,'2026-05-26 12:12:53');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('188','1','4','out','','36','25.000','500.00','75.000',NULL,'2026-05-27 01:05:41');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('189','1','4','out','','37','15.000','500.00','60.000',NULL,'2026-05-27 01:16:56');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('190','1','4','in','','37','15.000','500.00','75.000',NULL,'2026-05-27 01:16:56');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('191','1','4','out','sale','34','75.000','600.00','0.000',NULL,'2026-05-27 04:28:50');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('200','1','4','in','','18','20.000','600.00','20.000',NULL,'2026-05-27 05:33:27');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('201','1','4','out','','18','20.000','0.00','0.000',NULL,'2026-05-27 06:31:58');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('202','1','4','in','','19','20.000','600.00','20.000',NULL,'2026-05-27 06:32:10');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('203','1','4','out','sale','35','20.000','600.00','0.000',NULL,'2026-05-27 06:41:25');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('204','1','5','in','purchase','35','200.000','320.00','200.000',NULL,'2026-06-02 01:28:14');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('205','1','5','out','','35','200.000','0.00','0.000',NULL,'2026-06-02 01:44:16');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('206','1','5','in','purchase','36','200.000','320.00','200.000',NULL,'2026-06-02 01:45:15');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('207','1','5','out','','36','200.000','0.00','0.000',NULL,'2026-06-02 03:31:48');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('208','1','5','in','purchase','37','99.000','320.00','99.000',NULL,'2026-06-02 03:47:33');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('209','1','5','out','','37','99.000','0.00','0.000',NULL,'2026-06-02 03:50:14');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('210','1','5','in','purchase','38','44.000','320.00','44.000',NULL,'2026-06-02 05:14:03');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('211','1','5','out','','38','44.000','0.00','0.000',NULL,'2026-06-02 05:15:06');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('212','1','5','in','purchase','39','125.000','320.00','125.000',NULL,'2026-06-02 05:23:23');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('213','1','5','in','purchase','40','10.000','320.00','135.000',NULL,'2026-06-02 05:42:40');


CREATE TABLE `stock_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) DEFAULT NULL,
  `warehouse_id` int(11) DEFAULT NULL,
  `type` enum('purchase','sale','adjustment','transfer') DEFAULT NULL,
  `qty` decimal(10,2) DEFAULT NULL,
  `reference_id` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT NULL,
  `debit` decimal(12,2) DEFAULT NULL,
  `credit` decimal(12,2) DEFAULT NULL,
  `reference` varchar(50) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `short_name` varchar(20) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `units_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `units` (`id`,`company_id`,`name`,`short_name`,`created_at`) VALUES ('7','1','Meter','MTR','2026-04-27 05:48:44');
INSERT INTO `units` (`id`,`company_id`,`name`,`short_name`,`created_at`) VALUES ('9','1','Piece','PCS','2026-04-27 05:48:44');
INSERT INTO `units` (`id`,`company_id`,`name`,`short_name`,`created_at`) VALUES ('10','1','Roll','ROL','2026-04-27 05:48:44');
INSERT INTO `units` (`id`,`company_id`,`name`,`short_name`,`created_at`) VALUES ('11','1','Kilogram','KG','2026-04-27 05:51:18');
INSERT INTO `units` (`id`,`company_id`,`name`,`short_name`,`created_at`) VALUES ('13','1','Yard','YRD','2026-04-27 05:51:18');


CREATE ALGORITHM=UNDEFINED DEFINER=`clicsbwa`@`localhost` SQL SECURITY DEFINER VIEW `v_expired_stock` AS select `pb`.`company_id` AS `company_id`,`p`.`name` AS `product_name`,`p`.`id` AS `product_id`,'batch' AS `tracking_type`,`pb`.`batch_no` AS `batch_no`,NULL AS `serial_no`,`pb`.`expiry_date` AS `expiry_date`,`pb`.`qty_remaining` AS `qty`,to_days(curdate()) - to_days(`pb`.`expiry_date`) AS `days_expired` from (`product_batches` `pb` join `products` `p` on(`pb`.`product_id` = `p`.`id`)) where `pb`.`status` = 'active' and `pb`.`expiry_date` is not null and `pb`.`expiry_date` < curdate() union all select `ps`.`company_id` AS `company_id`,`p`.`name` AS `product_name`,`p`.`id` AS `product_id`,'serial' AS `tracking_type`,`ps`.`batch_no` AS `batch_no`,`ps`.`serial_no` AS `serial_no`,`ps`.`expiry_date` AS `expiry_date`,1 AS `qty`,to_days(curdate()) - to_days(`ps`.`expiry_date`) AS `days_expired` from (`product_serials` `ps` join `products` `p` on(`ps`.`product_id` = `p`.`id`)) where `ps`.`status` = 'in_stock' and `ps`.`expiry_date` is not null and `ps`.`expiry_date` < curdate();



CREATE ALGORITHM=UNDEFINED DEFINER=`clicsbwa`@`localhost` SQL SECURITY DEFINER VIEW `v_expiry_alerts` AS select `pb`.`company_id` AS `company_id`,`p`.`name` AS `product_name`,`p`.`id` AS `product_id`,'batch' AS `tracking_type`,`pb`.`batch_no` AS `batch_no`,NULL AS `serial_no`,`pb`.`expiry_date` AS `expiry_date`,`pb`.`qty_remaining` AS `qty`,to_days(`pb`.`expiry_date`) - to_days(curdate()) AS `days_to_expiry` from (`product_batches` `pb` join `products` `p` on(`pb`.`product_id` = `p`.`id`)) where `pb`.`status` = 'active' and `pb`.`expiry_date` is not null and `pb`.`expiry_date` >= curdate() union all select `ps`.`company_id` AS `company_id`,`p`.`name` AS `product_name`,`p`.`id` AS `product_id`,'serial' AS `tracking_type`,`ps`.`batch_no` AS `batch_no`,`ps`.`serial_no` AS `serial_no`,`ps`.`expiry_date` AS `expiry_date`,1 AS `qty`,to_days(`ps`.`expiry_date`) - to_days(curdate()) AS `days_to_expiry` from (`product_serials` `ps` join `products` `p` on(`ps`.`product_id` = `p`.`id`)) where `ps`.`status` = 'in_stock' and `ps`.`expiry_date` is not null and `ps`.`expiry_date` >= curdate();

INSERT INTO `v_expiry_alerts` (`company_id`,`product_name`,`product_id`,`tracking_type`,`batch_no`,`serial_no`,`expiry_date`,`qty`,`days_to_expiry`) VALUES ('1','Double Jersey 300 GSM Yellow','5','batch','2025-101125',NULL,'2028-02-02','125.000','610');
INSERT INTO `v_expiry_alerts` (`company_id`,`product_name`,`product_id`,`tracking_type`,`batch_no`,`serial_no`,`expiry_date`,`qty`,`days_to_expiry`) VALUES ('1','Double Jersey 300 GSM Yellow','5','batch','7567',NULL,'2026-06-25','10.000','23');


CREATE ALGORITHM=UNDEFINED DEFINER=`clicsbwa`@`localhost` SQL SECURITY DEFINER VIEW `v_warranty_status` AS select `si`.`id` AS `sale_item_id`,`s`.`company_id` AS `company_id`,`s`.`id` AS `sale_id`,`s`.`invoice_no` AS `invoice_no`,`s`.`sale_date` AS `sale_date`,`p`.`name` AS `product_name`,`p`.`id` AS `product_id`,`pt`.`name` AS `customer_name`,`pt`.`phone` AS `customer_phone`,`si`.`serial_no` AS `serial_no`,`si`.`warranty_days` AS `warranty_days`,`si`.`warranty_end_date` AS `warranty_end_date`,to_days(`si`.`warranty_end_date`) - to_days(curdate()) AS `days_remaining`,case when `si`.`warranty_end_date` is null then 'no_warranty' when `si`.`warranty_end_date` < curdate() then 'expired' when to_days(`si`.`warranty_end_date`) - to_days(curdate()) <= 30 then 'expiring_soon' else 'active' end AS `warranty_status` from (((`sale_items` `si` join `sales` `s` on(`si`.`sale_id` = `s`.`id`)) join `products` `p` on(`si`.`product_id` = `p`.`id`)) left join `parties` `pt` on(`s`.`party_id` = `pt`.`id`)) where `si`.`warranty_days` is not null and `si`.`warranty_days` > 0;



CREATE TABLE `warehouses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `location` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;



CREATE TABLE `warranty_claims` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `sale_id` int(11) DEFAULT NULL,
  `sale_item_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `party_id` int(11) DEFAULT NULL COMMENT 'Customer',
  `serial_no` varchar(100) DEFAULT NULL,
  `claim_date` date NOT NULL,
  `warranty_end` date DEFAULT NULL,
  `issue_desc` text DEFAULT NULL,
  `resolution` text DEFAULT NULL,
  `status` enum('open','in_progress','resolved','rejected') NOT NULL DEFAULT 'open',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `sale_id` (`sale_id`),
  KEY `product_id` (`product_id`),
  KEY `party_id` (`party_id`),
  KEY `status` (`status`),
  CONSTRAINT `warranty_claims_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

