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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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','','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=4 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_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','','1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('2','1','Pique / Polo','','1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('3','1','Fleece','','1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('4','1','Interlock','','1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('5','1','Rib','','1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('6','1','Terry','','1','2026-04-27 05:51:18');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('7','1','Single Jersey','','1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('8','1','Pique / Polo','','1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('9','1','Fleece','','1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('10','1','Interlock','','1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('11','1','Rib','','1','2026-04-27 17:05:47');
INSERT INTO `categories` (`id`,`company_id`,`name`,`description`,`status`,`created_at`) VALUES ('12','1','Terry','','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','Active','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=670 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=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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=49 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','৳','BDT','d M Y','2026-05-05 06:05:27');
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','','','','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=39 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 ('34','1','JE-20260006','2026-05-16','Purchase Bill BILL-20260001','','','20000.00','20000.00','posted','4','2026-05-16 12:29:02');
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 ('36','1','JE-20260007','2026-05-18','Sales Invoice INV-20260001','','','24000.00','24000.00','posted','4','2026-05-18 03:50: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 ('38','1','JE-20260008','2026-05-18','Sales Return SR-20260004','','','1200.00','600.00','posted','4','2026-05-18 12:37:35');


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=76 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('64','34','4','Purchase Inventory','20000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('65','34','1','Payment for purchase','0.00','20000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('68','36','3','Customer due','12000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('69','36','7','Sales revenue','0.00','12000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('70','36','9','Cost of goods sold','12000.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('71','36','4','Inventory reduction','0.00','12000.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('73','38','32','Sales return','600.00','0.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('74','38','1','Refund to customer','0.00','600.00');
INSERT INTO `journal_lines` (`id`,`journal_id`,`account_id`,`description`,`debit`,`credit`) VALUES ('75','38','4','Stock returned','600.00','0.00');


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','200000.00','0.00','','','','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','','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') NOT NULL,
  `party_id` int(11) DEFAULT NULL,
  `ref_id` int(11) DEFAULT NULL,
  `ref_type` enum('sale','purchase','advance','other') DEFAULT 'other',
  `amount` decimal(14,2) NOT NULL DEFAULT 0.00,
  `method` enum('cash','bank','mobile','cheque') DEFAULT 'cash',
  `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=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('31','1','pay','2','30','purchase','20000.00','cash','2026-05-16','','','4','2026-05-16 12:31:55');
INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('32','1','','','4','','6000.00','cash','2026-05-18','','Refund to customer on return SR-20260001','4','2026-05-18 03:51:33');
INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('33','1','','','8','','1200.00','cash','2026-05-18','','Refund to customer on return SR-20260003','4','2026-05-18 12:31:36');
INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('34','1','','','9','','1200.00','cash','2026-05-18','','Refund to customer on return SR-20260003','4','2026-05-18 12:35:57');
INSERT INTO `payments` (`id`,`company_id`,`type`,`party_id`,`ref_id`,`ref_type`,`amount`,`method`,`payment_date`,`reference_no`,`notes`,`created_by`,`created_at`) VALUES ('35','1','','','10','','600.00','cash','2026-05-18','','Refund to customer on return SR-20260004','4','2026-05-18 12:37:35');


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,
  `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,
  `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`),
  KEY `category_id` (`category_id`),
  KEY `unit_id` (`unit_id`),
  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=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `products` (`id`,`company_id`,`name`,`sku`,`category_id`,`unit_id`,`gsm_id`,`width_id`,`color_id`,`composition_id`,`description`,`buy_price`,`sell_price`,`stock_qty`,`min_stock`,`image`,`status`,`created_at`) VALUES ('2','1','Single Jersey 180 GSM White','SJ-180-WHT','1','10','4','8','1','1','','100.00','120.00','325.00','0.00','','1','2026-04-27 16:19:39');
INSERT INTO `products` (`id`,`company_id`,`name`,`sku`,`category_id`,`unit_id`,`gsm_id`,`width_id`,`color_id`,`composition_id`,`description`,`buy_price`,`sell_price`,`stock_qty`,`min_stock`,`image`,`status`,`created_at`) VALUES ('3','1','Single Jersey 200 GSM 95 contton','sk0','1','10','','7','8','1','','50.00','60.00','50.00','0.00','','1','2026-04-28 08:18:00');


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,
  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=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `purchase_items` (`id`,`purchase_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`discount`,`total`) VALUES ('30','30','2','Single Jersey 180 GSM White','ROL','200.000','100.00','0.00','20000.00');


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=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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=27 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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=31 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 ('30','1','BILL-20260001','','2','2026-05-16','','20000.00','','0.00','0.00','0.00','0.00','0.00','20000.00','20000.00','0.00','cash','paid','','4','2026-05-16 12:31:55');


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=57 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');


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,
  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=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `sale_items` (`id`,`sale_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`discount`,`total`) VALUES ('21','19','2','Single Jersey 180 GSM White','Roll','100.000','120.00','0.00','12000.00');


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=20 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 ('19','1','INV-20260001','3','','0.00','0.00','2026-05-18','','12000.00','flat','0.00','0.00','0.00','0.00','0.00','12000.00','0.00','12000.00','cash','due','','4','2026-05-18 03:50:23');


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;

INSERT INTO `sales_return_exchange_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`) VALUES ('1','6','3','Single Jersey 200 GSM 95 contton','ROL','50.000','60.00','3000.00');
INSERT INTO `sales_return_exchange_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`) VALUES ('2','7','3','Single Jersey 200 GSM 95 contton','ROL','50.000','60.00','3000.00');


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=13 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 ('6','4','2','Single Jersey 180 GSM White','Roll','50.000','120.00','6000.00','');
INSERT INTO `sales_return_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`,`reason`) VALUES ('7','5','2','Single Jersey 180 GSM White','Roll','25.000','120.00','3000.00','');
INSERT INTO `sales_return_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`,`reason`) VALUES ('11','9','2','Single Jersey 180 GSM White','Roll','10.000','120.00','1200.00','');
INSERT INTO `sales_return_items` (`id`,`return_id`,`product_id`,`product_name`,`unit`,`qty`,`rate`,`total`,`reason`) VALUES ('12','10','2','Single Jersey 180 GSM White','Roll','5.000','120.00','600.00','');


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=11 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 ('4','1','SR-20260001','19','','0000-00-00','6000.00','','','approved','4','2026-05-18 03:51:33','refund','6000.00','cash','0.00');
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 ('5','1','SR-20260002','19','','0000-00-00','3000.00','','','approved','4','2026-05-18 03:52:58','exchange_same','0.00','cash','3000.00');
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 ('9','1','SR-20260003','19','','0000-00-00','1200.00','','','approved','4','2026-05-18 12:35:57','refund','1200.00','cash','0.00');
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 ('10','1','SR-20260004','19','','0000-00-00','600.00','','','approved','4','2026-05-18 12:37:35','refund','600.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=154 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 ('136','1','2','in','purchase','29','200.000','100.00','200.000','','2026-05-16 12:29:02');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('137','1','2','out','','29','200.000','0.00','0.000','','2026-05-16 12:30:21');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('138','1','2','in','purchase','30','200.000','100.00','360.000','','2026-05-16 12:31:55');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('139','1','2','out','sale','19','100.000','120.00','260.000','','2026-05-18 03:50:23');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('140','1','2','in','','4','50.000','120.00','310.000','','2026-05-18 03:51:33');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('141','1','2','in','','5','25.000','120.00','335.000','','2026-05-18 03:52:58');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('142','1','2','out','','5','25.000','120.00','310.000','','2026-05-18 03:52:58');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('143','1','2','in','','6','25.000','120.00','335.000','','2026-05-18 03:53:48');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('144','1','2','out','','6','25.000','0.00','310.000','','2026-05-18 03:54:47');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('145','1','3','in','','6','50.000','0.00','50.000','','2026-05-18 03:54:47');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('146','1','2','in','','7','25.000','120.00','335.000','','2026-05-18 03:55:11');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('147','1','3','out','','7','50.000','60.00','0.000','','2026-05-18 03:55:11');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('148','1','2','out','','7','25.000','0.00','310.000','','2026-05-18 12:28:40');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('149','1','3','in','','7','50.000','0.00','50.000','','2026-05-18 12:28:40');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('150','1','2','in','','8','10.000','120.00','320.000','','2026-05-18 12:31:36');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('151','1','2','out','','8','10.000','0.00','310.000','','2026-05-18 12:35:40');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('152','1','2','in','','9','10.000','120.00','320.000','','2026-05-18 12:35:57');
INSERT INTO `stock_ledger` (`id`,`company_id`,`product_id`,`type`,`ref_type`,`ref_id`,`qty`,`rate`,`balance_qty`,`notes`,`created_at`) VALUES ('153','1','2','in','','10','5.000','120.00','325.000','','2026-05-18 12:37:35');


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 TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `role_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `status` tinyint(4) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

INSERT INTO `users` (`id`,`name`,`username`,`email`,`password`,`role_id`,`company_id`,`status`,`created_at`) VALUES ('4','Admin','admin','admin@gmail.com','$2y$10$.Rp6Mevf4x06PNXjBfT4.uuLoCtAw3dUFTi0KEPbQ3TsURpxaTkiC','1','1','1','2026-04-21 12:36:56');
INSERT INTO `users` (`id`,`name`,`username`,`email`,`password`,`role_id`,`company_id`,`status`,`created_at`) VALUES ('5','Engr. Md Al Amin Hossen','alamin','alamin@fibrenextknitware.com','$2y$10$vwCvBLs7wAp2MpNx1PvPm.2H/whTJIKMFo5nSf5l6HDX/M6PWGSje','6','2','1','2026-04-26 17:08:52');
INSERT INTO `users` (`id`,`name`,`username`,`email`,`password`,`role_id`,`company_id`,`status`,`created_at`) VALUES ('6','Yeamin','yeamin','yeamin@prettygroupbd.com','$2y$10$NocDnMEccBoobtEvtSvmU.rXp/p8cmWPffvCx8.WDHkeY3fpWQYaG','2','1','1','2026-04-27 07:42:13');


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;