/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

-- Fixed dump: FOREIGN_KEY_CHECKS disabled so alphabetical CREATE order
-- (e.g. attendance before companies/employees/users) does not fail with errno 150.

DROP TABLE IF EXISTS `attendance`;

CREATE TABLE `attendance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `attendance_date` date NOT NULL,
  `shift_name` varchar(60) DEFAULT NULL,
  `check_in` datetime DEFAULT NULL,
  `check_out` datetime DEFAULT NULL,
  `worked_hours` decimal(9,2) NOT NULL DEFAULT 0.00,
  `late_minutes` int(10) unsigned NOT NULL DEFAULT 0,
  `overtime_hours` decimal(9,2) NOT NULL DEFAULT 0.00,
  `status` enum('present','absent','leave','holiday','weekend','half_day') NOT NULL DEFAULT 'present',
  `source` enum('manual','biometric','import','system') NOT NULL DEFAULT 'manual',
  `remarks` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_employee_attendance_date` (`employee_id`,`attendance_date`),
  KEY `idx_attendance_company_date` (`company_id`,`attendance_date`),
  KEY `fk_attendance_creator` (`created_by`),
  CONSTRAINT `fk_attendance_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_attendance_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_attendance_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `audit_logs` */

DROP TABLE IF EXISTS `audit_logs`;

CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(80) NOT NULL,
  `entity_type` varchar(120) NOT NULL,
  `entity_id` varchar(80) DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_entity` (`entity_type`,`entity_id`),
  KEY `idx_audit_created` (`created_at`),
  KEY `fk_audit_company` (`company_id`),
  KEY `fk_audit_user` (`user_id`),
  CONSTRAINT `fk_audit_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bank_transactions` */

DROP TABLE IF EXISTS `bank_transactions`;

CREATE TABLE `bank_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `transaction_number` varchar(60) NOT NULL,
  `transaction_date` date NOT NULL,
  `transaction_type` enum('deposit','withdrawal','transfer_in','transfer_out','charge','interest') NOT NULL,
  `bank_account_id` bigint(20) unsigned NOT NULL,
  `contra_account_id` bigint(20) unsigned NOT NULL,
  `bank_name` varchar(120) DEFAULT NULL,
  `bank_account_number` varchar(80) DEFAULT NULL,
  `cheque_number` varchar(80) DEFAULT NULL,
  `amount` decimal(18,4) NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `status` enum('posted','reconciled','cancelled') NOT NULL DEFAULT 'posted',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_number` (`transaction_number`),
  KEY `fk_bank_company` (`company_id`),
  KEY `fk_bank_account` (`bank_account_id`),
  KEY `fk_bank_contra` (`contra_account_id`),
  KEY `fk_bank_journal` (`journal_entry_id`),
  KEY `fk_bank_creator` (`created_by`),
  CONSTRAINT `fk_bank_account` FOREIGN KEY (`bank_account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_bank_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_bank_contra` FOREIGN KEY (`contra_account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_bank_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_bank_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_approvals` */

DROP TABLE IF EXISTS `bom_approvals`;

CREATE TABLE `bom_approvals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `approval_level` varchar(80) NOT NULL,
  `status` enum('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  `approver_id` bigint(20) unsigned NOT NULL,
  `comments` varchar(500) DEFAULT NULL,
  `action_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_bom_approval_version` (`bom_version_id`,`status`),
  KEY `fk_bom_approval_user` (`approver_id`),
  CONSTRAINT `fk_bom_approval_user` FOREIGN KEY (`approver_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_bom_approval_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_colors` */

DROP TABLE IF EXISTS `bom_colors`;

CREATE TABLE `bom_colors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `color_id` bigint(20) unsigned NOT NULL,
  `color_code_override` varchar(50) DEFAULT NULL,
  `extra_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bom_version_color` (`bom_version_id`,`color_id`),
  KEY `fk_bom_color_color` (`color_id`),
  CONSTRAINT `fk_bom_color_color` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`),
  CONSTRAINT `fk_bom_color_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_consumptions` */

DROP TABLE IF EXISTS `bom_consumptions`;

CREATE TABLE `bom_consumptions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `bom_material_id` bigint(20) unsigned NOT NULL,
  `color_id` bigint(20) unsigned DEFAULT NULL,
  `size_id` bigint(20) unsigned DEFAULT NULL,
  `net_consumption` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `wastage_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `gross_consumption` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `notes` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bom_consumption_version` (`bom_version_id`),
  KEY `fk_bom_consumption_material` (`bom_material_id`),
  KEY `fk_bom_consumption_color` (`color_id`),
  KEY `fk_bom_consumption_size` (`size_id`),
  CONSTRAINT `fk_bom_consumption_color` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`),
  CONSTRAINT `fk_bom_consumption_material` FOREIGN KEY (`bom_material_id`) REFERENCES `bom_materials` (`id`),
  CONSTRAINT `fk_bom_consumption_size` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`id`),
  CONSTRAINT `fk_bom_consumption_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_details` */

DROP TABLE IF EXISTS `bom_details`;

CREATE TABLE `bom_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `parent_detail_id` bigint(20) unsigned DEFAULT NULL,
  `line_type` enum('material','operation','sub_bom','note') NOT NULL DEFAULT 'material',
  `description` varchar(255) NOT NULL,
  `quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `wastage_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `total_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `remarks` varchar(500) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bom_detail_version` (`bom_version_id`,`sort_order`),
  KEY `fk_bom_detail_parent` (`parent_detail_id`),
  KEY `fk_bom_detail_uom` (`uom_id`),
  CONSTRAINT `fk_bom_detail_parent` FOREIGN KEY (`parent_detail_id`) REFERENCES `bom_details` (`id`),
  CONSTRAINT `fk_bom_detail_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`),
  CONSTRAINT `fk_bom_detail_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_headers` */

DROP TABLE IF EXISTS `bom_headers`;

CREATE TABLE `bom_headers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `bom_number` varchar(50) NOT NULL,
  `title` varchar(180) NOT NULL,
  `style_id` bigint(20) unsigned DEFAULT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `base_quantity` decimal(18,4) NOT NULL DEFAULT 1.0000,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `current_version_number` int(10) unsigned NOT NULL DEFAULT 1,
  `status` enum('draft','active','inactive','obsolete') NOT NULL DEFAULT 'draft',
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_bom_number` (`company_id`,`bom_number`),
  KEY `idx_bom_header_status` (`company_id`,`status`),
  KEY `fk_bom_header_style` (`style_id`),
  KEY `fk_bom_header_product` (`product_id`),
  KEY `fk_bom_header_buyer` (`buyer_id`),
  KEY `fk_bom_header_uom` (`uom_id`),
  KEY `fk_bom_header_creator` (`created_by`),
  CONSTRAINT `fk_bom_header_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_bom_header_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_bom_header_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_bom_header_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_bom_header_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`),
  CONSTRAINT `fk_bom_header_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_materials` */

DROP TABLE IF EXISTS `bom_materials`;

CREATE TABLE `bom_materials` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `bom_detail_id` bigint(20) unsigned DEFAULT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `material_code` varchar(80) DEFAULT NULL,
  `material_name` varchar(180) NOT NULL,
  `material_type` varchar(80) DEFAULT NULL,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `consumption` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `wastage_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `unit_price` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `total_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bom_material_version` (`bom_version_id`,`sort_order`),
  KEY `fk_bom_material_detail` (`bom_detail_id`),
  KEY `fk_bom_material_supplier` (`supplier_id`),
  KEY `fk_bom_material_uom` (`uom_id`),
  CONSTRAINT `fk_bom_material_detail` FOREIGN KEY (`bom_detail_id`) REFERENCES `bom_details` (`id`),
  CONSTRAINT `fk_bom_material_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_bom_material_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`),
  CONSTRAINT `fk_bom_material_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_operations` */

DROP TABLE IF EXISTS `bom_operations`;

CREATE TABLE `bom_operations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `bom_detail_id` bigint(20) unsigned DEFAULT NULL,
  `operation_id` bigint(20) unsigned DEFAULT NULL,
  `sequence_number` int(10) unsigned NOT NULL DEFAULT 0,
  `description` varchar(180) NOT NULL,
  `smv` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `rate_per_minute` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `total_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `machine_type_id` bigint(20) unsigned DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bom_operation_version` (`bom_version_id`,`sequence_number`),
  KEY `fk_bom_operation_detail` (`bom_detail_id`),
  KEY `fk_bom_operation_operation` (`operation_id`),
  KEY `fk_bom_operation_machine_type` (`machine_type_id`),
  CONSTRAINT `fk_bom_operation_detail` FOREIGN KEY (`bom_detail_id`) REFERENCES `bom_details` (`id`),
  CONSTRAINT `fk_bom_operation_machine_type` FOREIGN KEY (`machine_type_id`) REFERENCES `machine_types` (`id`),
  CONSTRAINT `fk_bom_operation_operation` FOREIGN KEY (`operation_id`) REFERENCES `operations` (`id`),
  CONSTRAINT `fk_bom_operation_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_sizes` */

DROP TABLE IF EXISTS `bom_sizes`;

CREATE TABLE `bom_sizes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `size_id` bigint(20) unsigned NOT NULL,
  `ratio` decimal(12,4) NOT NULL DEFAULT 1.0000,
  `extra_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bom_version_size` (`bom_version_id`,`size_id`),
  KEY `fk_bom_size_size` (`size_id`),
  CONSTRAINT `fk_bom_size_size` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`id`),
  CONSTRAINT `fk_bom_size_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bom_versions` */

DROP TABLE IF EXISTS `bom_versions`;

CREATE TABLE `bom_versions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bom_header_id` bigint(20) unsigned NOT NULL,
  `version_number` int(10) unsigned NOT NULL,
  `effective_date` date NOT NULL,
  `change_reason` varchar(500) DEFAULT NULL,
  `status` enum('draft','submitted','approved','rejected','obsolete') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_bom_version` (`bom_header_id`,`version_number`),
  KEY `fk_bom_version_creator` (`created_by`),
  KEY `fk_bom_version_approver` (`approved_by`),
  CONSTRAINT `fk_bom_version_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_bom_version_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_bom_version_header` FOREIGN KEY (`bom_header_id`) REFERENCES `bom_headers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `brands` */

DROP TABLE IF EXISTS `brands`;

CREATE TABLE `brands` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `fk_brand_buyer` (`buyer_id`),
  CONSTRAINT `fk_brand_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `bundle_cards` */

DROP TABLE IF EXISTS `bundle_cards`;

CREATE TABLE `bundle_cards` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `cutting_detail_id` bigint(20) unsigned NOT NULL,
  `bundle_number` varchar(80) NOT NULL,
  `color_id` bigint(20) unsigned DEFAULT NULL,
  `size_id` bigint(20) unsigned DEFAULT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `barcode` varchar(150) DEFAULT NULL,
  `qr_code` varchar(255) DEFAULT NULL,
  `current_stage` varchar(80) DEFAULT NULL,
  `production_line_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('created','issued','in_process','completed','hold','rejected') NOT NULL DEFAULT 'created',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `bundle_number` (`bundle_number`),
  UNIQUE KEY `barcode` (`barcode`),
  UNIQUE KEY `qr_code` (`qr_code`),
  KEY `fk_bundle_card_production` (`production_order_id`),
  KEY `fk_bundle_card_cutting_detail` (`cutting_detail_id`),
  KEY `fk_bundle_card_color` (`color_id`),
  KEY `fk_bundle_card_size` (`size_id`),
  KEY `fk_bundle_card_line` (`production_line_id`),
  CONSTRAINT `fk_bundle_card_color` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`),
  CONSTRAINT `fk_bundle_card_cutting_detail` FOREIGN KEY (`cutting_detail_id`) REFERENCES `cutting_details` (`id`),
  CONSTRAINT `fk_bundle_card_line` FOREIGN KEY (`production_line_id`) REFERENCES `production_lines` (`id`),
  CONSTRAINT `fk_bundle_card_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_bundle_card_size` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `buyer_categories` */

DROP TABLE IF EXISTS `buyer_categories`;

CREATE TABLE `buyer_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `buyer_order_types` */

DROP TABLE IF EXISTS `buyer_order_types`;

CREATE TABLE `buyer_order_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `buyer_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `narration` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_buyer_order_type_buyer` (`buyer_id`),
  CONSTRAINT `fk_buyer_order_type_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `buyer_sample_types` */

DROP TABLE IF EXISTS `buyer_sample_types`;

CREATE TABLE `buyer_sample_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `buyer_id` bigint(20) unsigned NOT NULL,
  `name` varchar(120) NOT NULL,
  `depends_on_id` bigint(20) unsigned DEFAULT NULL,
  `comments_required` enum('yes','no','not_mandatory') NOT NULL DEFAULT 'not_mandatory',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_buyer_sample_buyer` (`buyer_id`),
  KEY `fk_buyer_sample_depends` (`depends_on_id`),
  CONSTRAINT `fk_buyer_sample_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_buyer_sample_depends` FOREIGN KEY (`depends_on_id`) REFERENCES `buyer_sample_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `buyer_seasons` */

DROP TABLE IF EXISTS `buyer_seasons`;

CREATE TABLE `buyer_seasons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `buyer_id` bigint(20) unsigned NOT NULL,
  `name` varchar(100) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_buyer_season_buyer` (`buyer_id`),
  CONSTRAINT `fk_buyer_season_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `buyer_ship_countries` */

DROP TABLE IF EXISTS `buyer_ship_countries`;

CREATE TABLE `buyer_ship_countries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `buyer_id` bigint(20) unsigned NOT NULL,
  `buyer_order_type_id` bigint(20) unsigned NOT NULL,
  `country_id` bigint(20) unsigned DEFAULT NULL,
  `country_code` varchar(10) DEFAULT NULL,
  `cutoff_day` varchar(10) DEFAULT NULL,
  `cutoff_day_name` varchar(20) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_buyer_ship_country_buyer` (`buyer_id`),
  KEY `fk_buyer_ship_country_order_type` (`buyer_order_type_id`),
  KEY `fk_buyer_ship_country_country` (`country_id`),
  CONSTRAINT `fk_buyer_ship_country_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_buyer_ship_country_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`),
  CONSTRAINT `fk_buyer_ship_country_order_type` FOREIGN KEY (`buyer_order_type_id`) REFERENCES `buyer_order_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `buyers` */

DROP TABLE IF EXISTS `buyers`;

CREATE TABLE `buyers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `buyer_category_id` bigint(20) unsigned DEFAULT NULL,
  `country_id` bigint(20) unsigned DEFAULT NULL,
  `currency_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `contact_person` varchar(120) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `commercial_address` text DEFAULT NULL,
  `commercial_country_id` bigint(20) unsigned DEFAULT NULL,
  `commercial_phone` varchar(50) DEFAULT NULL,
  `commercial_contact_person` varchar(120) DEFAULT NULL,
  `commercial_email` varchar(150) DEFAULT NULL,
  `liaison_address` text DEFAULT NULL,
  `liaison_country_id` bigint(20) unsigned DEFAULT NULL,
  `liaison_phone` varchar(50) DEFAULT NULL,
  `liaison_contact_person` varchar(120) DEFAULT NULL,
  `liaison_email` varchar(150) DEFAULT NULL,
  `website` varchar(180) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_buyer_code` (`company_id`,`code`),
  KEY `fk_buyer_category` (`buyer_category_id`),
  KEY `fk_buyer_country` (`country_id`),
  KEY `fk_buyer_currency` (`currency_id`),
  KEY `fk_buyer_commercial_country` (`commercial_country_id`),
  KEY `fk_buyer_liaison_country` (`liaison_country_id`),
  CONSTRAINT `fk_buyer_category` FOREIGN KEY (`buyer_category_id`) REFERENCES `buyer_categories` (`id`),
  CONSTRAINT `fk_buyer_commercial_country` FOREIGN KEY (`commercial_country_id`) REFERENCES `countries` (`id`),
  CONSTRAINT `fk_buyer_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_buyer_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`),
  CONSTRAINT `fk_buyer_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_buyer_liaison_country` FOREIGN KEY (`liaison_country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cash_transactions` */

DROP TABLE IF EXISTS `cash_transactions`;

CREATE TABLE `cash_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `transaction_number` varchar(60) NOT NULL,
  `transaction_date` date NOT NULL,
  `transaction_type` enum('receipt','payment') NOT NULL,
  `cash_account_id` bigint(20) unsigned NOT NULL,
  `contra_account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(18,4) NOT NULL,
  `party_type` varchar(40) DEFAULT NULL,
  `party_name` varchar(150) DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `status` enum('posted','cancelled') NOT NULL DEFAULT 'posted',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_number` (`transaction_number`),
  KEY `fk_cash_company` (`company_id`),
  KEY `fk_cash_account` (`cash_account_id`),
  KEY `fk_cash_contra` (`contra_account_id`),
  KEY `fk_cash_journal` (`journal_entry_id`),
  KEY `fk_cash_creator` (`created_by`),
  CONSTRAINT `fk_cash_account` FOREIGN KEY (`cash_account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_cash_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_cash_contra` FOREIGN KEY (`contra_account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_cash_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_cash_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `chart_of_accounts` */

DROP TABLE IF EXISTS `chart_of_accounts`;

CREATE TABLE `chart_of_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(40) NOT NULL,
  `name` varchar(150) NOT NULL,
  `account_type` enum('asset','liability','equity','revenue','expense') NOT NULL,
  `account_subtype` varchar(80) DEFAULT NULL,
  `normal_balance` enum('debit','credit') NOT NULL,
  `is_control_account` tinyint(1) NOT NULL DEFAULT 0,
  `allow_posting` tinyint(1) NOT NULL DEFAULT 1,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_account_code` (`company_id`,`code`),
  KEY `fk_account_parent` (`parent_id`),
  CONSTRAINT `fk_account_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_account_parent` FOREIGN KEY (`parent_id`) REFERENCES `chart_of_accounts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `colors` */

DROP TABLE IF EXISTS `colors`;

CREATE TABLE `colors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(100) NOT NULL,
  `tcx_code` varchar(50) DEFAULT NULL,
  `tpx_code` varchar(50) DEFAULT NULL,
  `hex_code` char(7) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `commercial_costs` */

DROP TABLE IF EXISTS `commercial_costs`;

CREATE TABLE `commercial_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `cost_detail_id` bigint(20) unsigned DEFAULT NULL,
  `charge_type` varchar(100) NOT NULL,
  `calculation_method` enum('percentage','fixed','per_unit') NOT NULL DEFAULT 'percentage',
  `basis_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_commercial_cost_header` (`cost_header_id`),
  KEY `fk_commercial_cost_detail` (`cost_detail_id`),
  CONSTRAINT `fk_commercial_cost_detail` FOREIGN KEY (`cost_detail_id`) REFERENCES `cost_details` (`id`),
  CONSTRAINT `fk_commercial_cost_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `commercial_invoices` */

DROP TABLE IF EXISTS `commercial_invoices`;

CREATE TABLE `commercial_invoices` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `shipment_id` bigint(20) unsigned NOT NULL,
  `invoice_number` varchar(60) NOT NULL,
  `invoice_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `currency_id` bigint(20) unsigned DEFAULT NULL,
  `exchange_rate` decimal(18,6) NOT NULL DEFAULT 1.000000,
  `merchandise_value` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `freight_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `insurance_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `discount_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `invoice_total` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `payment_terms` varchar(150) DEFAULT NULL,
  `bank_details` varchar(500) DEFAULT NULL,
  `status` enum('draft','issued','paid','cancelled') NOT NULL DEFAULT 'draft',
  `notes` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `fk_commercial_invoice_company` (`company_id`),
  KEY `fk_commercial_invoice_shipment` (`shipment_id`),
  KEY `fk_commercial_invoice_buyer` (`buyer_id`),
  KEY `fk_commercial_invoice_currency` (`currency_id`),
  KEY `fk_commercial_invoice_creator` (`created_by`),
  CONSTRAINT `fk_commercial_invoice_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_commercial_invoice_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_commercial_invoice_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_commercial_invoice_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_commercial_invoice_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `companies` */

DROP TABLE IF EXISTS `companies`;

CREATE TABLE `companies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `legal_name` varchar(200) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `tax_number` varchar(80) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `company_branches` */

DROP TABLE IF EXISTS `company_branches`;

CREATE TABLE `company_branches` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_branch_code` (`company_id`,`code`),
  CONSTRAINT `fk_branch_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `containers` */

DROP TABLE IF EXISTS `containers`;

CREATE TABLE `containers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `shipment_id` bigint(20) unsigned NOT NULL,
  `container_number` varchar(60) NOT NULL,
  `container_type` enum('20ft','40ft','40hc','45hc','air_uld','truck','other') NOT NULL DEFAULT '40ft',
  `seal_number` varchar(80) DEFAULT NULL,
  `package_count` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `gross_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `net_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `loading_date` date DEFAULT NULL,
  `status` enum('planned','loading','sealed','dispatched','arrived','unloaded') NOT NULL DEFAULT 'planned',
  `remarks` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_shipment_container` (`shipment_id`,`container_number`),
  CONSTRAINT `fk_container_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `converter_unit_names` */

DROP TABLE IF EXISTS `converter_unit_names`;

CREATE TABLE `converter_unit_names` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(50) NOT NULL,
  `category` enum('quantity','length','weight') NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cost_centers` */

DROP TABLE IF EXISTS `cost_centers`;

CREATE TABLE `cost_centers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_cost_center_code` (`company_id`,`code`),
  CONSTRAINT `fk_cost_center_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cost_components` */

DROP TABLE IF EXISTS `cost_components`;

CREATE TABLE `cost_components` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `component_type` enum('material','labor','machine','utility','overhead','commercial','other') NOT NULL,
  `calculation_method` enum('quantity_rate','percentage','fixed','per_unit') NOT NULL DEFAULT 'quantity_rate',
  `default_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_cost_component` (`company_id`,`code`),
  CONSTRAINT `fk_cost_component_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cost_details` */

DROP TABLE IF EXISTS `cost_details`;

CREATE TABLE `cost_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `cost_component_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `consumption` decimal(18,6) NOT NULL DEFAULT 1.000000,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `unit_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `wastage_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `remarks` varchar(500) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_cost_detail_header` (`cost_header_id`,`sort_order`),
  KEY `fk_cost_detail_component` (`cost_component_id`),
  KEY `fk_cost_detail_uom` (`uom_id`),
  CONSTRAINT `fk_cost_detail_component` FOREIGN KEY (`cost_component_id`) REFERENCES `cost_components` (`id`),
  CONSTRAINT `fk_cost_detail_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`),
  CONSTRAINT `fk_cost_detail_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cost_headers` */

DROP TABLE IF EXISTS `cost_headers`;

CREATE TABLE `cost_headers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `cost_number` varchar(50) NOT NULL,
  `title` varchar(180) NOT NULL,
  `style_id` bigint(20) unsigned DEFAULT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `currency_id` bigint(20) unsigned NOT NULL,
  `order_quantity` decimal(18,4) NOT NULL DEFAULT 1.0000,
  `exchange_rate` decimal(18,6) NOT NULL DEFAULT 1.000000,
  `revision_number` int(10) unsigned NOT NULL DEFAULT 1,
  `effective_date` date NOT NULL,
  `status` enum('draft','submitted','approved','rejected','obsolete') NOT NULL DEFAULT 'draft',
  `material_total` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `conversion_total` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `commercial_total` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `overhead_total` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `cost_per_unit` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `target_price` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_cost_number` (`company_id`,`cost_number`),
  KEY `idx_cost_header_status` (`company_id`,`status`),
  KEY `fk_cost_header_style` (`style_id`),
  KEY `fk_cost_header_product` (`product_id`),
  KEY `fk_cost_header_buyer` (`buyer_id`),
  KEY `fk_cost_header_currency` (`currency_id`),
  KEY `fk_cost_header_creator` (`created_by`),
  KEY `fk_cost_header_approver` (`approved_by`),
  CONSTRAINT `fk_cost_header_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_cost_header_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_cost_header_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_cost_header_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_cost_header_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_cost_header_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_cost_header_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cost_revisions` */

DROP TABLE IF EXISTS `cost_revisions`;

CREATE TABLE `cost_revisions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `revision_number` int(10) unsigned NOT NULL,
  `reason` varchar(500) NOT NULL,
  `snapshot` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`snapshot`)),
  `revised_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cost_revision` (`cost_header_id`,`revision_number`),
  KEY `fk_cost_revision_user` (`revised_by`),
  CONSTRAINT `fk_cost_revision_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`),
  CONSTRAINT `fk_cost_revision_user` FOREIGN KEY (`revised_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `costing_op_headers` */

DROP TABLE IF EXISTS `costing_op_headers`;

CREATE TABLE `costing_op_headers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `op_number` varchar(60) NOT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `brand_id` bigint(20) unsigned DEFAULT NULL,
  `season_id` bigint(20) unsigned DEFAULT NULL,
  `style_id` bigint(20) unsigned DEFAULT NULL,
  `style_name` varchar(180) DEFAULT NULL,
  `style_number` varchar(100) DEFAULT NULL,
  `fabric_description` varchar(500) NOT NULL,
  `wash_description` varchar(500) DEFAULT NULL,
  `size_range` varchar(100) DEFAULT NULL,
  `base_quantity` decimal(18,4) NOT NULL DEFAULT 1.0000,
  `productivity_per_hour` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `material_lcm_percent` decimal(9,4) NOT NULL DEFAULT 6.0000,
  `wash_lcm_percent` decimal(9,4) NOT NULL DEFAULT 1.0000,
  `pre_cost_per_dozen` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `net_profit_per_dozen` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `worker_wage_per_piece` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `commission_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `commission_method` enum('inverse','additive') NOT NULL DEFAULT 'inverse',
  `currency_id` bigint(20) unsigned NOT NULL,
  `status` enum('draft','confirmed','approved','obsolete') NOT NULL DEFAULT 'draft',
  `notes` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `op_number` (`op_number`),
  KEY `fk_costing_op_company` (`company_id`),
  KEY `fk_costing_op_buyer` (`buyer_id`),
  KEY `fk_costing_op_brand` (`brand_id`),
  KEY `fk_costing_op_season` (`season_id`),
  KEY `fk_costing_op_style` (`style_id`),
  KEY `fk_costing_op_currency` (`currency_id`),
  KEY `fk_costing_op_creator` (`created_by`),
  CONSTRAINT `fk_costing_op_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
  CONSTRAINT `fk_costing_op_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_costing_op_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_costing_op_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_costing_op_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_costing_op_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`),
  CONSTRAINT `fk_costing_op_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `costing_op_line_options` */

DROP TABLE IF EXISTS `costing_op_line_options`;

CREATE TABLE `costing_op_line_options` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `costing_op_line_id` bigint(20) unsigned NOT NULL,
  `costing_op_option_id` bigint(20) unsigned NOT NULL,
  `wastage_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `costing_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `confirmed_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `actual_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_costing_op_line_option` (`costing_op_line_id`,`costing_op_option_id`),
  KEY `fk_costing_op_line_option_option` (`costing_op_option_id`),
  CONSTRAINT `fk_costing_op_line_option_line` FOREIGN KEY (`costing_op_line_id`) REFERENCES `costing_op_lines` (`id`),
  CONSTRAINT `fk_costing_op_line_option_option` FOREIGN KEY (`costing_op_option_id`) REFERENCES `costing_op_options` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `costing_op_lines` */

DROP TABLE IF EXISTS `costing_op_lines`;

CREATE TABLE `costing_op_lines` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `costing_op_id` bigint(20) unsigned NOT NULL,
  `item_group` enum('fabric','trim','service') NOT NULL,
  `item_name` varchar(150) NOT NULL,
  `reference_description` varchar(500) DEFAULT NULL,
  `consumption_for` enum('piece','dozen','hundred','gross','thousand') NOT NULL DEFAULT 'piece',
  `consumption_unit` varchar(30) DEFAULT NULL,
  `costing_price` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `costing_consumption` decimal(18,6) NOT NULL DEFAULT 1.000000,
  `confirmed_price` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `confirmed_consumption` decimal(18,6) NOT NULL DEFAULT 1.000000,
  `actual_price` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `actual_consumption` decimal(18,6) NOT NULL DEFAULT 1.000000,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_costing_op_line_header` (`costing_op_id`),
  CONSTRAINT `fk_costing_op_line_header` FOREIGN KEY (`costing_op_id`) REFERENCES `costing_op_headers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `costing_op_options` */

DROP TABLE IF EXISTS `costing_op_options`;

CREATE TABLE `costing_op_options` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `costing_op_id` bigint(20) unsigned NOT NULL,
  `option_name` varchar(80) NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `is_confirmed` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_costing_op_option` (`costing_op_id`,`option_name`),
  CONSTRAINT `fk_costing_op_option_header` FOREIGN KEY (`costing_op_id`) REFERENCES `costing_op_headers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `costing_op_wash_details` */

DROP TABLE IF EXISTS `costing_op_wash_details`;

CREATE TABLE `costing_op_wash_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `costing_op_id` bigint(20) unsigned NOT NULL,
  `costing_op_option_id` bigint(20) unsigned NOT NULL,
  `description` varchar(250) NOT NULL,
  `productivity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `costing_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `costing_profit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `confirmed_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `confirmed_profit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `actual_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `actual_profit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_costing_op_wash_header` (`costing_op_id`),
  KEY `fk_costing_op_wash_option` (`costing_op_option_id`),
  CONSTRAINT `fk_costing_op_wash_header` FOREIGN KEY (`costing_op_id`) REFERENCES `costing_op_headers` (`id`),
  CONSTRAINT `fk_costing_op_wash_option` FOREIGN KEY (`costing_op_option_id`) REFERENCES `costing_op_options` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `countries` */

DROP TABLE IF EXISTS `countries`;

CREATE TABLE `countries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `alpha_3` varchar(3) DEFAULT NULL,
  `numeric_code` varchar(3) DEFAULT NULL,
  `name` varchar(150) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=253 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `currencies` */

DROP TABLE IF EXISTS `currencies`;

CREATE TABLE `currencies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `country_id` bigint(20) unsigned DEFAULT NULL,
  `country_region` varchar(120) DEFAULT NULL,
  `symbol` varchar(10) DEFAULT NULL,
  `decimal_places` tinyint(3) unsigned NOT NULL DEFAULT 2,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `fk_currency_country` (`country_id`),
  CONSTRAINT `fk_currency_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cutting_details` */

DROP TABLE IF EXISTS `cutting_details`;

CREATE TABLE `cutting_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `cutting_order_id` bigint(20) unsigned NOT NULL,
  `color_id` bigint(20) unsigned DEFAULT NULL,
  `size_id` bigint(20) unsigned DEFAULT NULL,
  `marker_number` varchar(80) DEFAULT NULL,
  `lay_number` varchar(80) DEFAULT NULL,
  `layer_count` int(10) unsigned NOT NULL DEFAULT 0,
  `planned_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `cut_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_cutting_detail_production` (`production_order_id`),
  KEY `fk_cutting_detail_order` (`cutting_order_id`),
  KEY `fk_cutting_detail_color` (`color_id`),
  KEY `fk_cutting_detail_size` (`size_id`),
  CONSTRAINT `fk_cutting_detail_color` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`),
  CONSTRAINT `fk_cutting_detail_order` FOREIGN KEY (`cutting_order_id`) REFERENCES `cutting_orders` (`id`),
  CONSTRAINT `fk_cutting_detail_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_cutting_detail_size` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `cutting_orders` */

DROP TABLE IF EXISTS `cutting_orders`;

CREATE TABLE `cutting_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `cutting_order_number` varchar(60) NOT NULL,
  `fabric_item_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `planned_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `cut_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `planned_date` date NOT NULL,
  `completed_date` date DEFAULT NULL,
  `status` enum('draft','released','in_progress','completed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `cutting_order_number` (`cutting_order_number`),
  KEY `fk_cutting_order_production` (`production_order_id`),
  KEY `fk_cutting_order_fabric` (`fabric_item_id`),
  KEY `fk_cutting_order_warehouse` (`warehouse_id`),
  KEY `fk_cutting_order_creator` (`created_by`),
  CONSTRAINT `fk_cutting_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_cutting_order_fabric` FOREIGN KEY (`fabric_item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_cutting_order_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_cutting_order_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `dashboard_cache` */

DROP TABLE IF EXISTS `dashboard_cache`;

CREATE TABLE `dashboard_cache` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `cache_key` varchar(150) NOT NULL,
  `cache_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`cache_data`)),
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NULL DEFAULT NULL,
  `generated_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_dashboard_cache` (`company_id`,`cache_key`),
  KEY `fk_dashboard_cache_generator` (`generated_by`),
  CONSTRAINT `fk_dashboard_cache_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_dashboard_cache_generator` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `defect_types` */

DROP TABLE IF EXISTS `defect_types`;

CREATE TABLE `defect_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(40) NOT NULL,
  `name` varchar(150) NOT NULL,
  `defect_category` enum('fabric','cutting','sewing','washing','printing','embroidery','finishing','packing','general') NOT NULL DEFAULT 'general',
  `severity` enum('minor','major','critical') NOT NULL DEFAULT 'minor',
  `default_points` decimal(9,4) NOT NULL DEFAULT 1.0000,
  `description` varchar(500) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_defect_code` (`company_id`,`code`),
  CONSTRAINT `fk_defect_type_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `departments` */

DROP TABLE IF EXISTS `departments`;

CREATE TABLE `departments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_department_code` (`company_id`,`code`),
  CONSTRAINT `fk_department_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `designations` */

DROP TABLE IF EXISTS `designations`;

CREATE TABLE `designations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `grade` varchar(30) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_designation_code` (`company_id`,`code`),
  CONSTRAINT `fk_designation_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `embroidery_orders` */

DROP TABLE IF EXISTS `embroidery_orders`;

CREATE TABLE `embroidery_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `process_number` varchar(60) NOT NULL,
  `embroidery_type` varchar(100) NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `stitch_count` int(10) unsigned NOT NULL DEFAULT 0,
  `planned_quantity` decimal(18,4) NOT NULL,
  `output_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `process_date` date NOT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('draft','sent','in_process','received','completed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `process_number` (`process_number`),
  KEY `fk_embroidery_order_production` (`production_order_id`),
  KEY `fk_embroidery_order_supplier` (`supplier_id`),
  KEY `fk_embroidery_order_creator` (`created_by`),
  CONSTRAINT `fk_embroidery_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_embroidery_order_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_embroidery_order_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `employees` */

DROP TABLE IF EXISTS `employees`;

CREATE TABLE `employees` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `designation_id` bigint(20) unsigned DEFAULT NULL,
  `team_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `joining_date` date DEFAULT NULL,
  `employment_type` enum('permanent','contract','temporary') NOT NULL DEFAULT 'permanent',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_employee_code` (`company_id`,`code`),
  KEY `fk_employee_designation` (`designation_id`),
  KEY `fk_employee_team` (`team_id`),
  CONSTRAINT `fk_employee_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_employee_designation` FOREIGN KEY (`designation_id`) REFERENCES `designations` (`id`),
  CONSTRAINT `fk_employee_team` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `end_users` */

DROP TABLE IF EXISTS `end_users`;

CREATE TABLE `end_users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `narration` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `fabric_contents` */

DROP TABLE IF EXISTS `fabric_contents`;

CREATE TABLE `fabric_contents` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `fabric_inspections` */

DROP TABLE IF EXISTS `fabric_inspections`;

CREATE TABLE `fabric_inspections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `inspection_number` varchar(60) NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `fabric_roll_id` bigint(20) unsigned DEFAULT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `inspection_date` date NOT NULL,
  `inspection_method` enum('4_point','10_point','visual','lab') NOT NULL DEFAULT '4_point',
  `inspected_quantity` decimal(18,6) NOT NULL,
  `sample_quantity` decimal(18,6) NOT NULL,
  `defect_type_id` bigint(20) unsigned DEFAULT NULL,
  `defect_count` int(10) unsigned NOT NULL DEFAULT 0,
  `total_defect_points` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `points_per_100_units` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `maximum_allowed_points` decimal(18,4) NOT NULL DEFAULT 40.0000,
  `grade` varchar(20) DEFAULT NULL,
  `result` enum('pass','fail','hold') NOT NULL,
  `defect_summary` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`defect_summary`)),
  `remarks` varchar(500) DEFAULT NULL,
  `inspected_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `inspection_number` (`inspection_number`),
  KEY `fk_fabric_inspection_company` (`company_id`),
  KEY `fk_fabric_inspection_item` (`item_id`),
  KEY `fk_fabric_inspection_batch` (`item_batch_id`),
  KEY `fk_fabric_inspection_roll` (`fabric_roll_id`),
  KEY `fk_fabric_inspection_supplier` (`supplier_id`),
  KEY `fk_fabric_inspection_warehouse` (`warehouse_id`),
  KEY `fk_fabric_inspection_defect` (`defect_type_id`),
  KEY `fk_fabric_inspection_user` (`inspected_by`),
  CONSTRAINT `fk_fabric_inspection_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_fabric_inspection_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_fabric_inspection_defect` FOREIGN KEY (`defect_type_id`) REFERENCES `defect_types` (`id`),
  CONSTRAINT `fk_fabric_inspection_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_fabric_inspection_roll` FOREIGN KEY (`fabric_roll_id`) REFERENCES `fabric_rolls` (`id`),
  CONSTRAINT `fk_fabric_inspection_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_fabric_inspection_user` FOREIGN KEY (`inspected_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_fabric_inspection_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `fabric_rolls` */

DROP TABLE IF EXISTS `fabric_rolls`;

CREATE TABLE `fabric_rolls` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `roll_number` varchar(100) NOT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `color_id` bigint(20) unsigned DEFAULT NULL,
  `width_value` decimal(12,4) DEFAULT NULL,
  `width_uom` varchar(20) DEFAULT NULL,
  `gsm` decimal(12,4) DEFAULT NULL,
  `original_quantity` decimal(18,6) NOT NULL,
  `available_quantity` decimal(18,6) NOT NULL,
  `reserved_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `quality_grade` varchar(30) DEFAULT NULL,
  `status` enum('available','reserved','issued','finished','hold','rejected') NOT NULL DEFAULT 'available',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roll_number` (`roll_number`),
  KEY `fk_fabric_roll_item` (`item_id`),
  KEY `fk_fabric_roll_batch` (`item_batch_id`),
  KEY `fk_fabric_roll_warehouse` (`warehouse_id`),
  KEY `fk_fabric_roll_location` (`warehouse_location_id`),
  KEY `fk_fabric_roll_color` (`color_id`),
  CONSTRAINT `fk_fabric_roll_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_fabric_roll_color` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`),
  CONSTRAINT `fk_fabric_roll_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_fabric_roll_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_fabric_roll_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `factories` */

DROP TABLE IF EXISTS `factories`;

CREATE TABLE `factories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `branch_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `address` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_factory_code` (`company_id`,`code`),
  KEY `fk_factory_branch` (`branch_id`),
  CONSTRAINT `fk_factory_branch` FOREIGN KEY (`branch_id`) REFERENCES `company_branches` (`id`),
  CONSTRAINT `fk_factory_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `final_qc` */

DROP TABLE IF EXISTS `final_qc`;

CREATE TABLE `final_qc` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `inspection_number` varchar(60) NOT NULL,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `inspection_date` datetime NOT NULL,
  `aql_level` decimal(5,2) NOT NULL DEFAULT 2.50,
  `lot_quantity` decimal(18,4) NOT NULL,
  `sample_quantity` decimal(18,4) NOT NULL,
  `inspected_quantity` decimal(18,4) NOT NULL,
  `passed_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `defect_type_id` bigint(20) unsigned DEFAULT NULL,
  `defect_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `allowed_defects` int(10) unsigned NOT NULL DEFAULT 0,
  `defect_rate_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `result` enum('pass','fail','hold') NOT NULL,
  `shipment_release` tinyint(1) NOT NULL DEFAULT 0,
  `corrective_action` varchar(500) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  `inspected_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `inspection_number` (`inspection_number`),
  KEY `fk_final_qc_company` (`company_id`),
  KEY `fk_final_qc_production` (`production_order_id`),
  KEY `fk_final_qc_defect` (`defect_type_id`),
  KEY `fk_final_qc_user` (`inspected_by`),
  KEY `fk_final_qc_approver` (`approved_by`),
  CONSTRAINT `fk_final_qc_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_final_qc_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_final_qc_defect` FOREIGN KEY (`defect_type_id`) REFERENCES `defect_types` (`id`),
  CONSTRAINT `fk_final_qc_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_final_qc_user` FOREIGN KEY (`inspected_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `finishing_orders` */

DROP TABLE IF EXISTS `finishing_orders`;

CREATE TABLE `finishing_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `process_number` varchar(60) NOT NULL,
  `planned_quantity` decimal(18,4) NOT NULL,
  `input_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `ironed_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `inspected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `passed_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `process_date` date NOT NULL,
  `status` enum('draft','in_progress','completed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `process_number` (`process_number`),
  KEY `fk_finishing_order_production` (`production_order_id`),
  KEY `fk_finishing_order_creator` (`created_by`),
  CONSTRAINT `fk_finishing_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_finishing_order_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `fit_masters` */

DROP TABLE IF EXISTS `fit_masters`;

CREATE TABLE `fit_masters` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `general_ledger` */

DROP TABLE IF EXISTS `general_ledger`;

CREATE TABLE `general_ledger` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `journal_detail_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `posting_date` date NOT NULL,
  `journal_number` varchar(60) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `debit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `credit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `signed_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_ledger_journal_detail` (`journal_detail_id`),
  KEY `idx_ledger_account_date` (`company_id`,`account_id`,`posting_date`),
  KEY `fk_ledger_journal` (`journal_entry_id`),
  KEY `fk_ledger_account` (`account_id`),
  CONSTRAINT `fk_ledger_account` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_ledger_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_ledger_detail` FOREIGN KEY (`journal_detail_id`) REFERENCES `journal_details` (`id`),
  CONSTRAINT `fk_ledger_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `goods_receipts` */

DROP TABLE IF EXISTS `goods_receipts`;

CREATE TABLE `goods_receipts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `receipt_number` varchar(60) NOT NULL,
  `purchase_order_id` bigint(20) unsigned NOT NULL,
  `purchase_order_item_id` bigint(20) unsigned NOT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `supplier_id` bigint(20) unsigned NOT NULL,
  `receipt_date` date NOT NULL,
  `received_quantity` decimal(18,6) NOT NULL,
  `accepted_quantity` decimal(18,6) NOT NULL,
  `rejected_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `batch_number` varchar(100) DEFAULT NULL,
  `challan_number` varchar(100) DEFAULT NULL,
  `status` enum('received','inspected','posted','cancelled') NOT NULL DEFAULT 'received',
  `received_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `receipt_number` (`receipt_number`),
  KEY `fk_gr_company` (`company_id`),
  KEY `fk_gr_po` (`purchase_order_id`),
  KEY `fk_gr_po_item` (`purchase_order_item_id`),
  KEY `fk_gr_warehouse` (`warehouse_id`),
  KEY `fk_gr_location` (`warehouse_location_id`),
  KEY `fk_gr_supplier` (`supplier_id`),
  KEY `fk_gr_receiver` (`received_by`),
  CONSTRAINT `fk_gr_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_gr_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_gr_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`),
  CONSTRAINT `fk_gr_po_item` FOREIGN KEY (`purchase_order_item_id`) REFERENCES `purchase_order_items` (`id`),
  CONSTRAINT `fk_gr_receiver` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_gr_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_gr_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `inline_qc` */

DROP TABLE IF EXISTS `inline_qc`;

CREATE TABLE `inline_qc` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `inspection_number` varchar(60) NOT NULL,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `production_line_id` bigint(20) unsigned DEFAULT NULL,
  `bundle_card_id` bigint(20) unsigned DEFAULT NULL,
  `operation_id` bigint(20) unsigned DEFAULT NULL,
  `inspection_date` datetime NOT NULL,
  `inspected_quantity` decimal(18,4) NOT NULL,
  `passed_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rework_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `defect_type_id` bigint(20) unsigned DEFAULT NULL,
  `defect_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `dhu_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `pass_rate_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `result` enum('pass','fail','hold') NOT NULL,
  `corrective_action` varchar(500) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  `inspected_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `inspection_number` (`inspection_number`),
  KEY `fk_inline_qc_company` (`company_id`),
  KEY `fk_inline_qc_production` (`production_order_id`),
  KEY `fk_inline_qc_line` (`production_line_id`),
  KEY `fk_inline_qc_bundle` (`bundle_card_id`),
  KEY `fk_inline_qc_operation` (`operation_id`),
  KEY `fk_inline_qc_defect` (`defect_type_id`),
  KEY `fk_inline_qc_user` (`inspected_by`),
  CONSTRAINT `fk_inline_qc_bundle` FOREIGN KEY (`bundle_card_id`) REFERENCES `bundle_cards` (`id`),
  CONSTRAINT `fk_inline_qc_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_inline_qc_defect` FOREIGN KEY (`defect_type_id`) REFERENCES `defect_types` (`id`),
  CONSTRAINT `fk_inline_qc_line` FOREIGN KEY (`production_line_id`) REFERENCES `production_lines` (`id`),
  CONSTRAINT `fk_inline_qc_operation` FOREIGN KEY (`operation_id`) REFERENCES `operations` (`id`),
  CONSTRAINT `fk_inline_qc_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_inline_qc_user` FOREIGN KEY (`inspected_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `issue_notes` */

DROP TABLE IF EXISTS `issue_notes`;

CREATE TABLE `issue_notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `issue_number` varchar(60) NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `item_serial_id` bigint(20) unsigned DEFAULT NULL,
  `fabric_roll_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `issue_date` date NOT NULL,
  `quantity` decimal(18,6) NOT NULL,
  `issue_to_type` varchar(80) NOT NULL,
  `issue_to_id` bigint(20) unsigned DEFAULT NULL,
  `reference` varchar(120) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  `status` enum('draft','posted','cancelled') NOT NULL DEFAULT 'posted',
  `issued_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `issue_number` (`issue_number`),
  KEY `fk_issue_note_company` (`company_id`),
  KEY `fk_issue_note_item` (`item_id`),
  KEY `fk_issue_note_batch` (`item_batch_id`),
  KEY `fk_issue_note_serial` (`item_serial_id`),
  KEY `fk_issue_note_roll` (`fabric_roll_id`),
  KEY `fk_issue_note_warehouse` (`warehouse_id`),
  KEY `fk_issue_note_location` (`warehouse_location_id`),
  KEY `fk_issue_note_user` (`issued_by`),
  CONSTRAINT `fk_issue_note_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_issue_note_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_issue_note_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_issue_note_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_issue_note_roll` FOREIGN KEY (`fabric_roll_id`) REFERENCES `fabric_rolls` (`id`),
  CONSTRAINT `fk_issue_note_serial` FOREIGN KEY (`item_serial_id`) REFERENCES `item_serials` (`id`),
  CONSTRAINT `fk_issue_note_user` FOREIGN KEY (`issued_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_issue_note_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `item_batches` */

DROP TABLE IF EXISTS `item_batches`;

CREATE TABLE `item_batches` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` bigint(20) unsigned NOT NULL,
  `batch_number` varchar(100) NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `manufacture_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `received_date` date DEFAULT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('active','quarantine','expired','closed') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_item_batch` (`item_id`,`batch_number`),
  KEY `fk_item_batch_supplier` (`supplier_id`),
  CONSTRAINT `fk_item_batch_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_item_batch_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `item_serials` */

DROP TABLE IF EXISTS `item_serials`;

CREATE TABLE `item_serials` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `serial_number` varchar(150) NOT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('available','reserved','issued','damaged','returned') NOT NULL DEFAULT 'available',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `serial_number` (`serial_number`),
  KEY `fk_item_serial_item` (`item_id`),
  KEY `fk_item_serial_batch` (`item_batch_id`),
  KEY `fk_item_serial_warehouse` (`warehouse_id`),
  KEY `fk_item_serial_location` (`warehouse_location_id`),
  CONSTRAINT `fk_item_serial_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_item_serial_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_item_serial_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_item_serial_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `items` */

DROP TABLE IF EXISTS `items`;

CREATE TABLE `items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `item_code` varchar(80) NOT NULL,
  `name` varchar(180) NOT NULL,
  `item_category` varchar(80) DEFAULT NULL,
  `item_type` enum('fabric','trim','accessory','packaging','chemical','finished_good','other') NOT NULL DEFAULT 'other',
  `uom_id` bigint(20) unsigned NOT NULL,
  `tracking_method` enum('none','batch','serial','roll') NOT NULL DEFAULT 'none',
  `reorder_level` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `minimum_stock` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `maximum_stock` decimal(18,6) DEFAULT NULL,
  `standard_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_item_code` (`company_id`,`item_code`),
  KEY `fk_item_uom` (`uom_id`),
  CONSTRAINT `fk_item_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_item_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `journal_details` */

DROP TABLE IF EXISTS `journal_details`;

CREATE TABLE `journal_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `line_number` int(10) unsigned NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `debit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `credit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `cost_center_id` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_journal_line` (`journal_entry_id`,`line_number`),
  KEY `fk_journal_detail_account` (`account_id`),
  KEY `fk_journal_detail_cost_center` (`cost_center_id`),
  CONSTRAINT `fk_journal_detail_account` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_journal_detail_cost_center` FOREIGN KEY (`cost_center_id`) REFERENCES `cost_centers` (`id`),
  CONSTRAINT `fk_journal_detail_header` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `journal_entries` */

DROP TABLE IF EXISTS `journal_entries`;

CREATE TABLE `journal_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `journal_number` varchar(60) NOT NULL,
  `journal_date` date NOT NULL,
  `journal_type` enum('general','cash','bank','receivable','payable','adjustment','opening','closing') NOT NULL DEFAULT 'general',
  `reference_type` varchar(60) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `total_debit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_credit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `status` enum('draft','posted','reversed','cancelled') NOT NULL DEFAULT 'draft',
  `posted_by` bigint(20) unsigned DEFAULT NULL,
  `posted_at` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `journal_number` (`journal_number`),
  KEY `idx_journal_company_date` (`company_id`,`journal_date`),
  KEY `fk_journal_poster` (`posted_by`),
  KEY `fk_journal_creator` (`created_by`),
  CONSTRAINT `fk_journal_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_journal_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_journal_poster` FOREIGN KEY (`posted_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `labor_costs` */

DROP TABLE IF EXISTS `labor_costs`;

CREATE TABLE `labor_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `cost_detail_id` bigint(20) unsigned DEFAULT NULL,
  `operation_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(180) NOT NULL,
  `smv` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `operator_count` decimal(12,4) NOT NULL DEFAULT 1.0000,
  `rate_per_minute` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_labor_cost_header` (`cost_header_id`),
  KEY `fk_labor_cost_detail` (`cost_detail_id`),
  KEY `fk_labor_cost_operation` (`operation_id`),
  CONSTRAINT `fk_labor_cost_detail` FOREIGN KEY (`cost_detail_id`) REFERENCES `cost_details` (`id`),
  CONSTRAINT `fk_labor_cost_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`),
  CONSTRAINT `fk_labor_cost_operation` FOREIGN KEY (`operation_id`) REFERENCES `operations` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `leave_applications` */

DROP TABLE IF EXISTS `leave_applications`;

CREATE TABLE `leave_applications` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `application_number` varchar(60) NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `leave_type` enum('annual','casual','sick','maternity','paternity','unpaid','other') NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `total_days` decimal(7,2) NOT NULL,
  `reason` varchar(500) NOT NULL,
  `status` enum('pending','approved','rejected','cancelled') NOT NULL DEFAULT 'pending',
  `is_paid` tinyint(1) NOT NULL DEFAULT 1,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `approval_notes` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `application_number` (`application_number`),
  KEY `idx_leave_company_dates` (`company_id`,`start_date`,`end_date`),
  KEY `fk_leave_employee` (`employee_id`),
  KEY `fk_leave_approver` (`approved_by`),
  KEY `fk_leave_creator` (`created_by`),
  CONSTRAINT `fk_leave_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_leave_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_leave_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_leave_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `line_production` */

DROP TABLE IF EXISTS `line_production`;

CREATE TABLE `line_production` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `sewing_order_id` bigint(20) unsigned NOT NULL,
  `production_date` date NOT NULL,
  `shift_name` varchar(50) DEFAULT NULL,
  `operation_id` bigint(20) unsigned DEFAULT NULL,
  `input_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `output_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rework_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `operator_count` int(10) unsigned NOT NULL DEFAULT 0,
  `working_minutes` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `earned_minutes` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `efficiency_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_line_production_order` (`production_order_id`),
  KEY `fk_line_production_sewing` (`sewing_order_id`),
  KEY `fk_line_production_operation` (`operation_id`),
  KEY `fk_line_production_creator` (`created_by`),
  CONSTRAINT `fk_line_production_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_line_production_operation` FOREIGN KEY (`operation_id`) REFERENCES `operations` (`id`),
  CONSTRAINT `fk_line_production_order` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_line_production_sewing` FOREIGN KEY (`sewing_order_id`) REFERENCES `sewing_orders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `machine_costs` */

DROP TABLE IF EXISTS `machine_costs`;

CREATE TABLE `machine_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `cost_detail_id` bigint(20) unsigned DEFAULT NULL,
  `machine_id` bigint(20) unsigned DEFAULT NULL,
  `machine_type_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(180) NOT NULL,
  `minutes` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `rate_per_minute` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_machine_cost_header` (`cost_header_id`),
  KEY `fk_machine_cost_detail` (`cost_detail_id`),
  KEY `fk_machine_cost_machine` (`machine_id`),
  KEY `fk_machine_cost_type` (`machine_type_id`),
  CONSTRAINT `fk_machine_cost_detail` FOREIGN KEY (`cost_detail_id`) REFERENCES `cost_details` (`id`),
  CONSTRAINT `fk_machine_cost_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`),
  CONSTRAINT `fk_machine_cost_machine` FOREIGN KEY (`machine_id`) REFERENCES `machines` (`id`),
  CONSTRAINT `fk_machine_cost_type` FOREIGN KEY (`machine_type_id`) REFERENCES `machine_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `machine_types` */

DROP TABLE IF EXISTS `machine_types`;

CREATE TABLE `machine_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `machines` */

DROP TABLE IF EXISTS `machines`;

CREATE TABLE `machines` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `factory_id` bigint(20) unsigned DEFAULT NULL,
  `machine_type_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `brand` varchar(100) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `status` enum('active','inactive','maintenance') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_machine_code` (`company_id`,`code`),
  KEY `fk_machine_factory` (`factory_id`),
  KEY `fk_machine_type` (`machine_type_id`),
  CONSTRAINT `fk_machine_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_machine_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`),
  CONSTRAINT `fk_machine_type` FOREIGN KEY (`machine_type_id`) REFERENCES `machine_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `material_costs` */

DROP TABLE IF EXISTS `material_costs`;

CREATE TABLE `material_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `cost_detail_id` bigint(20) unsigned DEFAULT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `material_code` varchar(80) DEFAULT NULL,
  `material_name` varchar(180) NOT NULL,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `consumption` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `wastage_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `unit_price` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_material_cost_header` (`cost_header_id`),
  KEY `fk_material_cost_detail` (`cost_detail_id`),
  KEY `fk_material_cost_supplier` (`supplier_id`),
  KEY `fk_material_cost_uom` (`uom_id`),
  CONSTRAINT `fk_material_cost_detail` FOREIGN KEY (`cost_detail_id`) REFERENCES `cost_details` (`id`),
  CONSTRAINT `fk_material_cost_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`),
  CONSTRAINT `fk_material_cost_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_material_cost_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `material_requirements` */

DROP TABLE IF EXISTS `material_requirements`;

CREATE TABLE `material_requirements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mrp_header_id` bigint(20) unsigned NOT NULL,
  `mrp_detail_id` bigint(20) unsigned NOT NULL,
  `requirement_type` enum('gross','net','safety','replacement') NOT NULL DEFAULT 'net',
  `gross_requirement` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `on_hand_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `scheduled_receipt_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `reserved_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `net_requirement` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `planned_order_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `required_date` date NOT NULL,
  `planned_order_date` date DEFAULT NULL,
  `lead_time_days` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_material_requirement_detail` (`mrp_detail_id`),
  KEY `fk_material_requirement_header` (`mrp_header_id`),
  CONSTRAINT `fk_material_requirement_detail` FOREIGN KEY (`mrp_detail_id`) REFERENCES `mrp_details` (`id`),
  CONSTRAINT `fk_material_requirement_header` FOREIGN KEY (`mrp_header_id`) REFERENCES `mrp_headers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `metal_finishes` */

DROP TABLE IF EXISTS `metal_finishes`;

CREATE TABLE `metal_finishes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `migrations` */

DROP TABLE IF EXISTS `migrations`;

CREATE TABLE `migrations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) NOT NULL,
  `executed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `migration` (`migration`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `mrp_details` */

DROP TABLE IF EXISTS `mrp_details`;

CREATE TABLE `mrp_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mrp_header_id` bigint(20) unsigned NOT NULL,
  `bom_material_id` bigint(20) unsigned DEFAULT NULL,
  `material_code` varchar(80) DEFAULT NULL,
  `material_name` varchar(180) NOT NULL,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `required_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `available_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `reserved_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `shortage_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `required_date` date NOT NULL,
  `preferred_supplier_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('required','partially_reserved','reserved','ordered','closed') NOT NULL DEFAULT 'required',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_mrp_detail_header` (`mrp_header_id`,`sort_order`),
  KEY `fk_mrp_detail_bom_material` (`bom_material_id`),
  KEY `fk_mrp_detail_uom` (`uom_id`),
  KEY `fk_mrp_detail_supplier` (`preferred_supplier_id`),
  CONSTRAINT `fk_mrp_detail_bom_material` FOREIGN KEY (`bom_material_id`) REFERENCES `bom_materials` (`id`),
  CONSTRAINT `fk_mrp_detail_header` FOREIGN KEY (`mrp_header_id`) REFERENCES `mrp_headers` (`id`),
  CONSTRAINT `fk_mrp_detail_supplier` FOREIGN KEY (`preferred_supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_mrp_detail_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `mrp_headers` */

DROP TABLE IF EXISTS `mrp_headers`;

CREATE TABLE `mrp_headers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `mrp_number` varchar(50) NOT NULL,
  `title` varchar(180) NOT NULL,
  `bom_header_id` bigint(20) unsigned NOT NULL,
  `bom_version_id` bigint(20) unsigned NOT NULL,
  `style_id` bigint(20) unsigned DEFAULT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `demand_quantity` decimal(18,4) NOT NULL DEFAULT 1.0000,
  `demand_date` date NOT NULL,
  `planning_date` date NOT NULL,
  `status` enum('draft','calculated','approved','closed','cancelled') NOT NULL DEFAULT 'draft',
  `total_materials` int(10) unsigned NOT NULL DEFAULT 0,
  `total_required_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_shortage_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_mrp_number` (`company_id`,`mrp_number`),
  KEY `idx_mrp_header_status` (`company_id`,`status`),
  KEY `fk_mrp_header_bom` (`bom_header_id`),
  KEY `fk_mrp_header_bom_version` (`bom_version_id`),
  KEY `fk_mrp_header_style` (`style_id`),
  KEY `fk_mrp_header_product` (`product_id`),
  KEY `fk_mrp_header_warehouse` (`warehouse_id`),
  KEY `fk_mrp_header_creator` (`created_by`),
  KEY `fk_mrp_header_approver` (`approved_by`),
  CONSTRAINT `fk_mrp_header_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_mrp_header_bom` FOREIGN KEY (`bom_header_id`) REFERENCES `bom_headers` (`id`),
  CONSTRAINT `fk_mrp_header_bom_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`),
  CONSTRAINT `fk_mrp_header_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_mrp_header_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_mrp_header_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_mrp_header_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`),
  CONSTRAINT `fk_mrp_header_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `operations` */

DROP TABLE IF EXISTS `operations`;

CREATE TABLE `operations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `machine_type_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `operation_group` varchar(80) DEFAULT NULL,
  `default_smv` decimal(10,4) NOT NULL DEFAULT 0.0000,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_operation_code` (`company_id`,`code`),
  KEY `fk_operation_machine_type` (`machine_type_id`),
  CONSTRAINT `fk_operation_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_operation_machine_type` FOREIGN KEY (`machine_type_id`) REFERENCES `machine_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `overhead_costs` */

DROP TABLE IF EXISTS `overhead_costs`;

CREATE TABLE `overhead_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `cost_detail_id` bigint(20) unsigned DEFAULT NULL,
  `overhead_type` varchar(100) NOT NULL,
  `calculation_method` enum('percentage','fixed','per_unit') NOT NULL DEFAULT 'percentage',
  `basis_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_overhead_cost_header` (`cost_header_id`),
  KEY `fk_overhead_cost_detail` (`cost_detail_id`),
  CONSTRAINT `fk_overhead_cost_detail` FOREIGN KEY (`cost_detail_id`) REFERENCES `cost_details` (`id`),
  CONSTRAINT `fk_overhead_cost_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `overtime` */

DROP TABLE IF EXISTS `overtime`;

CREATE TABLE `overtime` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `overtime_number` varchar(60) NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `overtime_date` date NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `hours` decimal(9,2) NOT NULL,
  `hourly_rate` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `multiplier` decimal(7,4) NOT NULL DEFAULT 1.0000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `reason` varchar(500) DEFAULT NULL,
  `status` enum('pending','approved','rejected','paid') NOT NULL DEFAULT 'pending',
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `overtime_number` (`overtime_number`),
  KEY `idx_overtime_company_date` (`company_id`,`overtime_date`),
  KEY `fk_overtime_employee` (`employee_id`),
  KEY `fk_overtime_approver` (`approved_by`),
  KEY `fk_overtime_creator` (`created_by`),
  CONSTRAINT `fk_overtime_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_overtime_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_overtime_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_overtime_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `packing_lists` */

DROP TABLE IF EXISTS `packing_lists`;

CREATE TABLE `packing_lists` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `shipment_id` bigint(20) unsigned NOT NULL,
  `packing_list_number` varchar(60) NOT NULL,
  `packing_date` date NOT NULL,
  `package_count` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_pieces` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `gross_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `net_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `dimensions` varchar(150) DEFAULT NULL,
  `marks_and_numbers` varchar(250) DEFAULT NULL,
  `status` enum('draft','issued','revised','cancelled') NOT NULL DEFAULT 'draft',
  `notes` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `packing_list_number` (`packing_list_number`),
  KEY `fk_packing_list_company` (`company_id`),
  KEY `fk_packing_list_shipment` (`shipment_id`),
  KEY `fk_packing_list_creator` (`created_by`),
  CONSTRAINT `fk_packing_list_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_packing_list_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_packing_list_shipment` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `packing_orders` */

DROP TABLE IF EXISTS `packing_orders`;

CREATE TABLE `packing_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `packing_number` varchar(60) NOT NULL,
  `planned_quantity` decimal(18,4) NOT NULL,
  `packed_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `carton_quantity` int(10) unsigned NOT NULL DEFAULT 0,
  `pieces_per_carton` int(10) unsigned NOT NULL DEFAULT 0,
  `packing_date` date NOT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('draft','in_progress','completed','dispatched','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `packing_number` (`packing_number`),
  KEY `fk_packing_order_production` (`production_order_id`),
  KEY `fk_packing_order_warehouse` (`warehouse_id`),
  KEY `fk_packing_order_creator` (`created_by`),
  CONSTRAINT `fk_packing_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_packing_order_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_packing_order_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `payables` */

DROP TABLE IF EXISTS `payables`;

CREATE TABLE `payables` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `document_number` varchar(60) NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `document_type` enum('bill','payment','credit_note','debit_note') NOT NULL,
  `document_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `payable_account_id` bigint(20) unsigned NOT NULL,
  `offset_account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(18,4) NOT NULL,
  `balance_amount` decimal(18,4) NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `status` enum('open','partial','settled','overdue','cancelled') NOT NULL DEFAULT 'open',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `document_number` (`document_number`),
  KEY `fk_payable_company` (`company_id`),
  KEY `fk_payable_supplier` (`supplier_id`),
  KEY `fk_payable_account` (`payable_account_id`),
  KEY `fk_payable_offset` (`offset_account_id`),
  KEY `fk_payable_journal` (`journal_entry_id`),
  KEY `fk_payable_creator` (`created_by`),
  CONSTRAINT `fk_payable_account` FOREIGN KEY (`payable_account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_payable_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_payable_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_payable_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  CONSTRAINT `fk_payable_offset` FOREIGN KEY (`offset_account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_payable_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `payroll` */

DROP TABLE IF EXISTS `payroll`;

CREATE TABLE `payroll` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `payroll_number` varchar(60) NOT NULL,
  `employee_id` bigint(20) unsigned NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `payable_days` decimal(7,2) NOT NULL DEFAULT 0.00,
  `present_days` decimal(7,2) NOT NULL DEFAULT 0.00,
  `paid_leave_days` decimal(7,2) NOT NULL DEFAULT 0.00,
  `unpaid_leave_days` decimal(7,2) NOT NULL DEFAULT 0.00,
  `basic_salary` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_earnings` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `overtime_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `gross_salary` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_deductions` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `net_salary` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `component_summary` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`component_summary`)),
  `status` enum('draft','approved','paid','cancelled') NOT NULL DEFAULT 'draft',
  `payment_date` date DEFAULT NULL,
  `payment_reference` varchar(100) DEFAULT NULL,
  `generated_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `payroll_number` (`payroll_number`),
  UNIQUE KEY `uq_employee_payroll_period` (`employee_id`,`period_start`,`period_end`),
  KEY `fk_payroll_company` (`company_id`),
  KEY `fk_payroll_generator` (`generated_by`),
  KEY `fk_payroll_approver` (`approved_by`),
  CONSTRAINT `fk_payroll_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_payroll_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_payroll_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`),
  CONSTRAINT `fk_payroll_generator` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `permissions` */

DROP TABLE IF EXISTS `permissions`;

CREATE TABLE `permissions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(100) NOT NULL,
  `name` varchar(120) NOT NULL,
  `module` varchar(80) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=190 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `prefix_masters` */

DROP TABLE IF EXISTS `prefix_masters`;

CREATE TABLE `prefix_masters` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `examples` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `printing_orders` */

DROP TABLE IF EXISTS `printing_orders`;

CREATE TABLE `printing_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `process_number` varchar(60) NOT NULL,
  `print_type` varchar(100) NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `color_count` int(10) unsigned NOT NULL DEFAULT 0,
  `planned_quantity` decimal(18,4) NOT NULL,
  `output_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `process_date` date NOT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('draft','sent','in_process','received','completed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `process_number` (`process_number`),
  KEY `fk_printing_order_production` (`production_order_id`),
  KEY `fk_printing_order_supplier` (`supplier_id`),
  KEY `fk_printing_order_creator` (`created_by`),
  CONSTRAINT `fk_printing_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_printing_order_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_printing_order_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_categories` */

DROP TABLE IF EXISTS `product_categories`;

CREATE TABLE `product_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_codes` */

DROP TABLE IF EXISTS `product_codes`;

CREATE TABLE `product_codes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `product_variant_id` bigint(20) unsigned DEFAULT NULL,
  `code_type` enum('internal','buyer','barcode','qr') NOT NULL DEFAULT 'internal',
  `code_value` varchar(150) NOT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code_value` (`code_value`),
  KEY `fk_product_code_product` (`product_id`),
  KEY `fk_product_code_variant` (`product_variant_id`),
  CONSTRAINT `fk_product_code_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_product_code_variant` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_images` */

DROP TABLE IF EXISTS `product_images`;

CREATE TABLE `product_images` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `product_variant_id` bigint(20) unsigned DEFAULT NULL,
  `image_path` varchar(500) NOT NULL,
  `alt_text` varchar(200) DEFAULT NULL,
  `image_type` enum('main','front','back','detail','other') NOT NULL DEFAULT 'other',
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_product_image_product` (`product_id`),
  KEY `fk_product_image_variant` (`product_variant_id`),
  CONSTRAINT `fk_product_image_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_product_image_variant` FOREIGN KEY (`product_variant_id`) REFERENCES `product_variants` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_measurements` */

DROP TABLE IF EXISTS `product_measurements`;

CREATE TABLE `product_measurements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `style_version_id` bigint(20) unsigned DEFAULT NULL,
  `size_id` bigint(20) unsigned DEFAULT NULL,
  `uom_id` bigint(20) unsigned NOT NULL,
  `point_name` varchar(150) NOT NULL,
  `specification` decimal(12,4) NOT NULL,
  `tolerance_plus` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `tolerance_minus` decimal(12,4) NOT NULL DEFAULT 0.0000,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_measurement_product` (`product_id`),
  KEY `fk_measurement_style_version` (`style_version_id`),
  KEY `fk_measurement_size` (`size_id`),
  KEY `fk_measurement_uom` (`uom_id`),
  CONSTRAINT `fk_measurement_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_measurement_size` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`id`),
  CONSTRAINT `fk_measurement_style_version` FOREIGN KEY (`style_version_id`) REFERENCES `style_versions` (`id`),
  CONSTRAINT `fk_measurement_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_subcategories` */

DROP TABLE IF EXISTS `product_subcategories`;

CREATE TABLE `product_subcategories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_category_id` bigint(20) unsigned NOT NULL,
  `product_type_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `fk_product_subcategory_category` (`product_category_id`),
  KEY `fk_product_subcategory_type` (`product_type_id`),
  CONSTRAINT `fk_product_subcategory_category` FOREIGN KEY (`product_category_id`) REFERENCES `product_categories` (`id`),
  CONSTRAINT `fk_product_subcategory_type` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_types` */

DROP TABLE IF EXISTS `product_types`;

CREATE TABLE `product_types` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_category_id` bigint(20) unsigned DEFAULT NULL,
  `product_subcategory_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `fk_product_type_category` (`product_category_id`),
  KEY `fk_product_type_subcategory` (`product_subcategory_id`),
  CONSTRAINT `fk_product_type_category` FOREIGN KEY (`product_category_id`) REFERENCES `product_categories` (`id`),
  CONSTRAINT `fk_product_type_subcategory` FOREIGN KEY (`product_subcategory_id`) REFERENCES `product_subcategories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=127 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_variants` */

DROP TABLE IF EXISTS `product_variants`;

CREATE TABLE `product_variants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `color_id` bigint(20) unsigned DEFAULT NULL,
  `size_id` bigint(20) unsigned DEFAULT NULL,
  `sku` varchar(80) NOT NULL,
  `barcode` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sku` (`sku`),
  UNIQUE KEY `barcode` (`barcode`),
  UNIQUE KEY `uq_product_color_size` (`product_id`,`color_id`,`size_id`),
  KEY `fk_variant_color` (`color_id`),
  KEY `fk_variant_size` (`size_id`),
  CONSTRAINT `fk_variant_color` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`),
  CONSTRAINT `fk_variant_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_variant_size` FOREIGN KEY (`size_id`) REFERENCES `sizes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `production_costs` */

DROP TABLE IF EXISTS `production_costs`;

CREATE TABLE `production_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `cost_type` enum('material','labor','machine','utility','subcontract','overhead','other') NOT NULL,
  `stage` varchar(80) DEFAULT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `unit_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `currency_id` bigint(20) unsigned DEFAULT NULL,
  `reference_type` varchar(80) DEFAULT NULL,
  `reference_id` bigint(20) unsigned DEFAULT NULL,
  `cost_date` date NOT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_production_cost_order` (`production_order_id`),
  KEY `fk_production_cost_currency` (`currency_id`),
  KEY `fk_production_cost_creator` (`created_by`),
  CONSTRAINT `fk_production_cost_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_production_cost_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_production_cost_order` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `production_lines` */

DROP TABLE IF EXISTS `production_lines`;

CREATE TABLE `production_lines` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `factory_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `line_type` varchar(50) DEFAULT NULL,
  `daily_capacity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `status` enum('active','inactive','maintenance') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_production_line` (`company_id`,`code`),
  KEY `fk_production_line_factory` (`factory_id`),
  CONSTRAINT `fk_production_line_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_production_line_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `production_losses` */

DROP TABLE IF EXISTS `production_losses`;

CREATE TABLE `production_losses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `stage` varchar(80) NOT NULL,
  `loss_type` enum('wastage','damage','defect','rejection','shrinkage','other') NOT NULL,
  `quantity` decimal(18,6) NOT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `loss_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `reason` varchar(500) NOT NULL,
  `action_taken` varchar(500) DEFAULT NULL,
  `loss_date` date NOT NULL,
  `reported_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_production_loss_order` (`production_order_id`),
  KEY `fk_production_loss_reporter` (`reported_by`),
  CONSTRAINT `fk_production_loss_order` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_production_loss_reporter` FOREIGN KEY (`reported_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `production_orders` */

DROP TABLE IF EXISTS `production_orders`;

CREATE TABLE `production_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `production_order_number` varchar(60) NOT NULL,
  `title` varchar(180) NOT NULL,
  `style_id` bigint(20) unsigned DEFAULT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `bom_version_id` bigint(20) unsigned DEFAULT NULL,
  `order_quantity` decimal(18,4) NOT NULL,
  `planned_start_date` date NOT NULL,
  `planned_end_date` date NOT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `input_warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `output_warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `produced_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `status` enum('draft','released','in_progress','completed','closed','cancelled') NOT NULL DEFAULT 'draft',
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_production_order` (`company_id`,`production_order_number`),
  KEY `fk_production_order_style` (`style_id`),
  KEY `fk_production_order_product` (`product_id`),
  KEY `fk_production_order_buyer` (`buyer_id`),
  KEY `fk_production_order_bom_version` (`bom_version_id`),
  KEY `fk_production_order_input_warehouse` (`input_warehouse_id`),
  KEY `fk_production_order_output_warehouse` (`output_warehouse_id`),
  KEY `fk_production_order_creator` (`created_by`),
  KEY `fk_production_order_approver` (`approved_by`),
  CONSTRAINT `fk_production_order_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_production_order_bom_version` FOREIGN KEY (`bom_version_id`) REFERENCES `bom_versions` (`id`),
  CONSTRAINT `fk_production_order_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_production_order_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_production_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_production_order_input_warehouse` FOREIGN KEY (`input_warehouse_id`) REFERENCES `warehouses` (`id`),
  CONSTRAINT `fk_production_order_output_warehouse` FOREIGN KEY (`output_warehouse_id`) REFERENCES `warehouses` (`id`),
  CONSTRAINT `fk_production_order_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_production_order_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `products` */

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `style_id` bigint(20) unsigned DEFAULT NULL,
  `product_type_id` bigint(20) unsigned DEFAULT NULL,
  `product_subcategory_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('draft','active','inactive','archived') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_product_code` (`company_id`,`code`),
  KEY `fk_product_style` (`style_id`),
  KEY `fk_product_type` (`product_type_id`),
  KEY `fk_product_subcategory` (`product_subcategory_id`),
  CONSTRAINT `fk_product_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_product_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`),
  CONSTRAINT `fk_product_subcategory` FOREIGN KEY (`product_subcategory_id`) REFERENCES `product_subcategories` (`id`),
  CONSTRAINT `fk_product_type` FOREIGN KEY (`product_type_id`) REFERENCES `product_types` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `profit_analysis` */

DROP TABLE IF EXISTS `profit_analysis`;

CREATE TABLE `profit_analysis` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `total_cost` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `cost_per_unit` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `selling_price` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `profit_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `profit_margin_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `markup_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `break_even_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `cost_header_id` (`cost_header_id`),
  CONSTRAINT `fk_profit_analysis_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `purchase_order_items` */

DROP TABLE IF EXISTS `purchase_order_items`;

CREATE TABLE `purchase_order_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_order_id` bigint(20) unsigned NOT NULL,
  `purchase_requisition_item_id` bigint(20) unsigned DEFAULT NULL,
  `material_code` varchar(80) DEFAULT NULL,
  `material_name` varchar(180) NOT NULL,
  `specification` varchar(500) DEFAULT NULL,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `ordered_quantity` decimal(18,6) NOT NULL,
  `unit_price` decimal(18,6) NOT NULL,
  `discount_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `tax_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `line_total` decimal(18,4) NOT NULL,
  `received_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `returned_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('open','partially_received','received','closed','cancelled') NOT NULL DEFAULT 'open',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_po_item_header` (`purchase_order_id`),
  KEY `fk_po_item_pr_item` (`purchase_requisition_item_id`),
  KEY `fk_po_item_uom` (`uom_id`),
  CONSTRAINT `fk_po_item_header` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`),
  CONSTRAINT `fk_po_item_pr_item` FOREIGN KEY (`purchase_requisition_item_id`) REFERENCES `purchase_requisition_items` (`id`),
  CONSTRAINT `fk_po_item_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `purchase_orders` */

DROP TABLE IF EXISTS `purchase_orders`;

CREATE TABLE `purchase_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `purchase_requisition_id` bigint(20) unsigned DEFAULT NULL,
  `purchase_order_number` varchar(50) NOT NULL,
  `supplier_id` bigint(20) unsigned NOT NULL,
  `currency_id` bigint(20) unsigned NOT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `order_date` date NOT NULL,
  `expected_delivery_date` date DEFAULT NULL,
  `status` enum('draft','approved','sent','partially_received','received','closed','cancelled') NOT NULL DEFAULT 'draft',
  `subtotal` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `discount_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `tax_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `shipping_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `payment_terms` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_purchase_order_number` (`company_id`,`purchase_order_number`),
  KEY `fk_po_pr` (`purchase_requisition_id`),
  KEY `fk_po_supplier` (`supplier_id`),
  KEY `fk_po_currency` (`currency_id`),
  KEY `fk_po_warehouse` (`warehouse_id`),
  KEY `fk_po_creator` (`created_by`),
  KEY `fk_po_approver` (`approved_by`),
  CONSTRAINT `fk_po_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_po_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_po_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_po_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_po_pr` FOREIGN KEY (`purchase_requisition_id`) REFERENCES `purchase_requisitions` (`id`),
  CONSTRAINT `fk_po_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_po_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `purchase_requisition_items` */

DROP TABLE IF EXISTS `purchase_requisition_items`;

CREATE TABLE `purchase_requisition_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_requisition_id` bigint(20) unsigned NOT NULL,
  `mrp_detail_id` bigint(20) unsigned DEFAULT NULL,
  `material_code` varchar(80) DEFAULT NULL,
  `material_name` varchar(180) NOT NULL,
  `specification` varchar(500) DEFAULT NULL,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `requested_quantity` decimal(18,6) NOT NULL,
  `estimated_unit_price` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `estimated_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `preferred_supplier_id` bigint(20) unsigned DEFAULT NULL,
  `ordered_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('open','quoted','ordered','closed','cancelled') NOT NULL DEFAULT 'open',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_pr_item_header` (`purchase_requisition_id`),
  KEY `fk_pr_item_mrp_detail` (`mrp_detail_id`),
  KEY `fk_pr_item_uom` (`uom_id`),
  KEY `fk_pr_item_supplier` (`preferred_supplier_id`),
  CONSTRAINT `fk_pr_item_header` FOREIGN KEY (`purchase_requisition_id`) REFERENCES `purchase_requisitions` (`id`),
  CONSTRAINT `fk_pr_item_mrp_detail` FOREIGN KEY (`mrp_detail_id`) REFERENCES `mrp_details` (`id`),
  CONSTRAINT `fk_pr_item_supplier` FOREIGN KEY (`preferred_supplier_id`) REFERENCES `suppliers` (`id`),
  CONSTRAINT `fk_pr_item_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `purchase_requisitions` */

DROP TABLE IF EXISTS `purchase_requisitions`;

CREATE TABLE `purchase_requisitions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `requisition_number` varchar(50) NOT NULL,
  `title` varchar(180) NOT NULL,
  `mrp_header_id` bigint(20) unsigned DEFAULT NULL,
  `department_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `required_date` date NOT NULL,
  `priority` enum('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
  `status` enum('draft','submitted','approved','rejected','ordered','closed','cancelled') NOT NULL DEFAULT 'draft',
  `estimated_total` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `notes` text DEFAULT NULL,
  `requested_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_requisition_number` (`company_id`,`requisition_number`),
  KEY `fk_pr_mrp` (`mrp_header_id`),
  KEY `fk_pr_department` (`department_id`),
  KEY `fk_pr_warehouse` (`warehouse_id`),
  KEY `fk_pr_requester` (`requested_by`),
  KEY `fk_pr_approver` (`approved_by`),
  CONSTRAINT `fk_pr_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_pr_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_pr_department` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`),
  CONSTRAINT `fk_pr_mrp` FOREIGN KEY (`mrp_header_id`) REFERENCES `mrp_headers` (`id`),
  CONSTRAINT `fk_pr_requester` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_pr_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `purchase_returns` */

DROP TABLE IF EXISTS `purchase_returns`;

CREATE TABLE `purchase_returns` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `return_number` varchar(60) NOT NULL,
  `goods_receipt_id` bigint(20) unsigned NOT NULL,
  `purchase_order_id` bigint(20) unsigned NOT NULL,
  `purchase_order_item_id` bigint(20) unsigned NOT NULL,
  `supplier_id` bigint(20) unsigned NOT NULL,
  `return_date` date NOT NULL,
  `return_quantity` decimal(18,6) NOT NULL,
  `return_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `reason` varchar(500) NOT NULL,
  `status` enum('draft','approved','dispatched','credited','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `return_number` (`return_number`),
  KEY `fk_purchase_return_company` (`company_id`),
  KEY `fk_purchase_return_receipt` (`goods_receipt_id`),
  KEY `fk_purchase_return_po` (`purchase_order_id`),
  KEY `fk_purchase_return_po_item` (`purchase_order_item_id`),
  KEY `fk_purchase_return_supplier` (`supplier_id`),
  KEY `fk_purchase_return_creator` (`created_by`),
  KEY `fk_purchase_return_approver` (`approved_by`),
  CONSTRAINT `fk_purchase_return_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_purchase_return_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_purchase_return_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_purchase_return_po` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`),
  CONSTRAINT `fk_purchase_return_po_item` FOREIGN KEY (`purchase_order_item_id`) REFERENCES `purchase_order_items` (`id`),
  CONSTRAINT `fk_purchase_return_receipt` FOREIGN KEY (`goods_receipt_id`) REFERENCES `goods_receipts` (`id`),
  CONSTRAINT `fk_purchase_return_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `quality_reports` */

DROP TABLE IF EXISTS `quality_reports`;

CREATE TABLE `quality_reports` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `report_number` varchar(60) NOT NULL,
  `report_type` enum('fabric','inline','final','production_summary','defect_analysis') NOT NULL,
  `production_order_id` bigint(20) unsigned DEFAULT NULL,
  `date_from` date NOT NULL,
  `date_to` date NOT NULL,
  `total_inspected` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_passed` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_rejected` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_defects` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `defect_rate_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `pass_rate_percent` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `summary_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`summary_data`)),
  `status` enum('generated','reviewed','approved','archived') NOT NULL DEFAULT 'generated',
  `generated_by` bigint(20) unsigned NOT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `reviewed_by` bigint(20) unsigned DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `report_number` (`report_number`),
  KEY `fk_quality_report_company` (`company_id`),
  KEY `fk_quality_report_production` (`production_order_id`),
  KEY `fk_quality_report_generator` (`generated_by`),
  KEY `fk_quality_report_reviewer` (`reviewed_by`),
  CONSTRAINT `fk_quality_report_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_quality_report_generator` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_quality_report_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_quality_report_reviewer` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `receivables` */

DROP TABLE IF EXISTS `receivables`;

CREATE TABLE `receivables` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `document_number` varchar(60) NOT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `document_type` enum('invoice','receipt','credit_note','debit_note') NOT NULL,
  `document_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `receivable_account_id` bigint(20) unsigned NOT NULL,
  `offset_account_id` bigint(20) unsigned NOT NULL,
  `amount` decimal(18,4) NOT NULL,
  `balance_amount` decimal(18,4) NOT NULL,
  `reference_number` varchar(100) DEFAULT NULL,
  `description` varchar(500) NOT NULL,
  `journal_entry_id` bigint(20) unsigned NOT NULL,
  `status` enum('open','partial','settled','overdue','cancelled') NOT NULL DEFAULT 'open',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `document_number` (`document_number`),
  KEY `fk_receivable_company` (`company_id`),
  KEY `fk_receivable_buyer` (`buyer_id`),
  KEY `fk_receivable_account` (`receivable_account_id`),
  KEY `fk_receivable_offset` (`offset_account_id`),
  KEY `fk_receivable_journal` (`journal_entry_id`),
  KEY `fk_receivable_creator` (`created_by`),
  CONSTRAINT `fk_receivable_account` FOREIGN KEY (`receivable_account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_receivable_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_receivable_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_receivable_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_receivable_journal` FOREIGN KEY (`journal_entry_id`) REFERENCES `journal_entries` (`id`),
  CONSTRAINT `fk_receivable_offset` FOREIGN KEY (`offset_account_id`) REFERENCES `chart_of_accounts` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `receive_notes` */

DROP TABLE IF EXISTS `receive_notes`;

CREATE TABLE `receive_notes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `receive_number` varchar(60) NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `item_serial_id` bigint(20) unsigned DEFAULT NULL,
  `fabric_roll_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `receive_date` date NOT NULL,
  `quantity` decimal(18,6) NOT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `source_type` varchar(80) NOT NULL,
  `source_id` bigint(20) unsigned DEFAULT NULL,
  `reference` varchar(120) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  `status` enum('draft','posted','cancelled') NOT NULL DEFAULT 'posted',
  `received_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `receive_number` (`receive_number`),
  KEY `fk_receive_note_company` (`company_id`),
  KEY `fk_receive_note_item` (`item_id`),
  KEY `fk_receive_note_batch` (`item_batch_id`),
  KEY `fk_receive_note_serial` (`item_serial_id`),
  KEY `fk_receive_note_roll` (`fabric_roll_id`),
  KEY `fk_receive_note_warehouse` (`warehouse_id`),
  KEY `fk_receive_note_location` (`warehouse_location_id`),
  KEY `fk_receive_note_user` (`received_by`),
  CONSTRAINT `fk_receive_note_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_receive_note_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_receive_note_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_receive_note_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_receive_note_roll` FOREIGN KEY (`fabric_roll_id`) REFERENCES `fabric_rolls` (`id`),
  CONSTRAINT `fk_receive_note_serial` FOREIGN KEY (`item_serial_id`) REFERENCES `item_serials` (`id`),
  CONSTRAINT `fk_receive_note_user` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_receive_note_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `report_templates` */

DROP TABLE IF EXISTS `report_templates`;

CREATE TABLE `report_templates` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(150) NOT NULL,
  `module` varchar(80) NOT NULL,
  `report_type` enum('table','summary','chart','document','export') NOT NULL DEFAULT 'table',
  `description` varchar(500) DEFAULT NULL,
  `definition_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`definition_json`)),
  `layout_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`layout_json`)),
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_report_template` (`company_id`,`code`),
  KEY `fk_report_template_creator` (`created_by`),
  CONSTRAINT `fk_report_template_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_report_template_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `role_permissions` */

DROP TABLE IF EXISTS `role_permissions`;

CREATE TABLE `role_permissions` (
  `role_id` bigint(20) unsigned NOT NULL,
  `permission_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`permission_id`),
  KEY `fk_role_permission_permission` (`permission_id`),
  CONSTRAINT `fk_role_permission_permission` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_role_permission_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `roles` */

DROP TABLE IF EXISTS `roles`;

CREATE TABLE `roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `is_system` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_role_scope_code` (`company_id`,`code`),
  CONSTRAINT `fk_role_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `salary_components` */

DROP TABLE IF EXISTS `salary_components`;

CREATE TABLE `salary_components` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `employee_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(40) NOT NULL,
  `name` varchar(150) NOT NULL,
  `component_type` enum('earning','deduction') NOT NULL,
  `calculation_type` enum('fixed','percentage') NOT NULL DEFAULT 'fixed',
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `percentage` decimal(9,4) NOT NULL DEFAULT 0.0000,
  `taxable` tinyint(1) NOT NULL DEFAULT 0,
  `recurring` tinyint(1) NOT NULL DEFAULT 1,
  `effective_from` date DEFAULT NULL,
  `effective_to` date DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_salary_component_employee` (`company_id`,`employee_id`,`status`),
  KEY `fk_salary_component_employee` (`employee_id`),
  CONSTRAINT `fk_salary_component_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_salary_component_employee` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `saved_filters` */

DROP TABLE IF EXISTS `saved_filters`;

CREATE TABLE `saved_filters` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `resource` varchar(100) NOT NULL,
  `name` varchar(150) NOT NULL,
  `filter_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`filter_data`)),
  `sort_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`sort_data`)),
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_user_saved_filter` (`user_id`,`resource`,`name`),
  KEY `fk_saved_filter_company` (`company_id`),
  CONSTRAINT `fk_saved_filter_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_saved_filter_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `seasons` */

DROP TABLE IF EXISTS `seasons`;

CREATE TABLE `seasons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(100) NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `sewing_orders` */

DROP TABLE IF EXISTS `sewing_orders`;

CREATE TABLE `sewing_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `sewing_order_number` varchar(60) NOT NULL,
  `production_line_id` bigint(20) unsigned NOT NULL,
  `planned_quantity` decimal(18,4) NOT NULL,
  `input_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `output_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `planned_start_date` date NOT NULL,
  `planned_end_date` date NOT NULL,
  `status` enum('draft','released','in_progress','completed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sewing_order_number` (`sewing_order_number`),
  KEY `fk_sewing_order_production` (`production_order_id`),
  KEY `fk_sewing_order_line` (`production_line_id`),
  KEY `fk_sewing_order_creator` (`created_by`),
  CONSTRAINT `fk_sewing_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_sewing_order_line` FOREIGN KEY (`production_line_id`) REFERENCES `production_lines` (`id`),
  CONSTRAINT `fk_sewing_order_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `shipment_items` */

DROP TABLE IF EXISTS `shipment_items`;

CREATE TABLE `shipment_items` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `shipment_id` bigint(20) unsigned NOT NULL,
  `production_order_id` bigint(20) unsigned DEFAULT NULL,
  `product_id` bigint(20) unsigned DEFAULT NULL,
  `style_id` bigint(20) unsigned DEFAULT NULL,
  `description` varchar(250) NOT NULL,
  `quantity` decimal(18,4) NOT NULL,
  `unit_price` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `line_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `carton_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `gross_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `net_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `country_of_origin` varchar(100) DEFAULT NULL,
  `hs_code` varchar(40) DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_shipment_item_header` (`shipment_id`),
  KEY `fk_shipment_item_production` (`production_order_id`),
  KEY `fk_shipment_item_product` (`product_id`),
  KEY `fk_shipment_item_style` (`style_id`),
  CONSTRAINT `fk_shipment_item_header` FOREIGN KEY (`shipment_id`) REFERENCES `shipments` (`id`),
  CONSTRAINT `fk_shipment_item_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
  CONSTRAINT `fk_shipment_item_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_shipment_item_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `shipments` */

DROP TABLE IF EXISTS `shipments`;

CREATE TABLE `shipments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `shipment_number` varchar(60) NOT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned DEFAULT NULL,
  `shipment_date` date NOT NULL,
  `expected_delivery_date` date DEFAULT NULL,
  `actual_delivery_date` date DEFAULT NULL,
  `shipment_mode` enum('sea','air','road','rail','courier') NOT NULL DEFAULT 'sea',
  `incoterm` varchar(20) DEFAULT NULL,
  `origin_port` varchar(120) DEFAULT NULL,
  `destination_port` varchar(120) DEFAULT NULL,
  `vessel_flight_number` varchar(100) DEFAULT NULL,
  `tracking_number` varchar(120) DEFAULT NULL,
  `currency_id` bigint(20) unsigned DEFAULT NULL,
  `exchange_rate` decimal(18,6) NOT NULL DEFAULT 1.000000,
  `total_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_cartons` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `gross_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `net_weight` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `total_value` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `status` enum('draft','booked','packed','dispatched','in_transit','delivered','cancelled') NOT NULL DEFAULT 'draft',
  `notes` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `shipment_number` (`shipment_number`),
  KEY `idx_shipment_company_date` (`company_id`,`shipment_date`),
  KEY `fk_shipment_buyer` (`buyer_id`),
  KEY `fk_shipment_warehouse` (`warehouse_id`),
  KEY `fk_shipment_currency` (`currency_id`),
  KEY `fk_shipment_creator` (`created_by`),
  CONSTRAINT `fk_shipment_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_shipment_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_shipment_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_shipment_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_shipment_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `shortage_reports` */

DROP TABLE IF EXISTS `shortage_reports`;

CREATE TABLE `shortage_reports` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mrp_header_id` bigint(20) unsigned NOT NULL,
  `mrp_detail_id` bigint(20) unsigned NOT NULL,
  `material_requirement_id` bigint(20) unsigned NOT NULL,
  `shortage_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `shortage_value` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `severity` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `status` enum('open','actioned','resolved','waived') NOT NULL DEFAULT 'open',
  `recommended_action` varchar(500) DEFAULT NULL,
  `resolved_by` bigint(20) unsigned DEFAULT NULL,
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_shortage_report_detail` (`mrp_detail_id`),
  KEY `fk_shortage_report_header` (`mrp_header_id`),
  KEY `fk_shortage_report_requirement` (`material_requirement_id`),
  KEY `fk_shortage_report_resolver` (`resolved_by`),
  CONSTRAINT `fk_shortage_report_detail` FOREIGN KEY (`mrp_detail_id`) REFERENCES `mrp_details` (`id`),
  CONSTRAINT `fk_shortage_report_header` FOREIGN KEY (`mrp_header_id`) REFERENCES `mrp_headers` (`id`),
  CONSTRAINT `fk_shortage_report_requirement` FOREIGN KEY (`material_requirement_id`) REFERENCES `material_requirements` (`id`),
  CONSTRAINT `fk_shortage_report_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `sizes` */

DROP TABLE IF EXISTS `sizes`;

CREATE TABLE `sizes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(100) NOT NULL,
  `size_group` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `stock_adjustments` */

DROP TABLE IF EXISTS `stock_adjustments`;

CREATE TABLE `stock_adjustments` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `adjustment_number` varchar(60) NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `adjustment_date` date NOT NULL,
  `adjustment_type` enum('increase','decrease') NOT NULL,
  `quantity` decimal(18,6) NOT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `reason` varchar(500) NOT NULL,
  `status` enum('draft','posted','cancelled') NOT NULL DEFAULT 'posted',
  `created_by` bigint(20) unsigned NOT NULL,
  `approved_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `adjustment_number` (`adjustment_number`),
  KEY `fk_stock_adjustment_company` (`company_id`),
  KEY `fk_stock_adjustment_item` (`item_id`),
  KEY `fk_stock_adjustment_batch` (`item_batch_id`),
  KEY `fk_stock_adjustment_warehouse` (`warehouse_id`),
  KEY `fk_stock_adjustment_location` (`warehouse_location_id`),
  KEY `fk_stock_adjustment_creator` (`created_by`),
  KEY `fk_stock_adjustment_approver` (`approved_by`),
  CONSTRAINT `fk_stock_adjustment_approver` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_stock_adjustment_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_stock_adjustment_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_stock_adjustment_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_stock_adjustment_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_stock_adjustment_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_stock_adjustment_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `stock_balances` */

DROP TABLE IF EXISTS `stock_balances`;

CREATE TABLE `stock_balances` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `batch_key` bigint(20) unsigned GENERATED ALWAYS AS (ifnull(`item_batch_id`,0)) STORED,
  `location_key` bigint(20) unsigned GENERATED ALWAYS AS (ifnull(`warehouse_location_id`,0)) STORED,
  `quantity_on_hand` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `reserved_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `available_quantity` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `average_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `stock_value` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `last_transaction_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_stock_balance` (`company_id`,`item_id`,`warehouse_id`,`batch_key`,`location_key`),
  KEY `fk_stock_balance_item` (`item_id`),
  KEY `fk_stock_balance_batch` (`item_batch_id`),
  KEY `fk_stock_balance_warehouse` (`warehouse_id`),
  KEY `fk_stock_balance_location` (`warehouse_location_id`),
  CONSTRAINT `fk_stock_balance_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_stock_balance_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_stock_balance_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_stock_balance_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_stock_balance_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `stock_reservations` */

DROP TABLE IF EXISTS `stock_reservations`;

CREATE TABLE `stock_reservations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `mrp_header_id` bigint(20) unsigned NOT NULL,
  `mrp_detail_id` bigint(20) unsigned NOT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `reservation_number` varchar(60) NOT NULL,
  `reserved_quantity` decimal(18,6) NOT NULL,
  `status` enum('active','released','consumed','cancelled') NOT NULL DEFAULT 'active',
  `reference` varchar(120) DEFAULT NULL,
  `reserved_by` bigint(20) unsigned NOT NULL,
  `reserved_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `released_by` bigint(20) unsigned DEFAULT NULL,
  `released_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `reservation_number` (`reservation_number`),
  KEY `idx_stock_reservation_detail` (`mrp_detail_id`,`status`),
  KEY `fk_stock_reservation_header` (`mrp_header_id`),
  KEY `fk_stock_reservation_warehouse` (`warehouse_id`),
  KEY `fk_stock_reservation_location` (`warehouse_location_id`),
  KEY `fk_stock_reservation_user` (`reserved_by`),
  KEY `fk_stock_reservation_releaser` (`released_by`),
  CONSTRAINT `fk_stock_reservation_detail` FOREIGN KEY (`mrp_detail_id`) REFERENCES `mrp_details` (`id`),
  CONSTRAINT `fk_stock_reservation_header` FOREIGN KEY (`mrp_header_id`) REFERENCES `mrp_headers` (`id`),
  CONSTRAINT `fk_stock_reservation_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_stock_reservation_releaser` FOREIGN KEY (`released_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_stock_reservation_user` FOREIGN KEY (`reserved_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_stock_reservation_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `stock_transactions` */

DROP TABLE IF EXISTS `stock_transactions`;

CREATE TABLE `stock_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `item_serial_id` bigint(20) unsigned DEFAULT NULL,
  `fabric_roll_id` bigint(20) unsigned DEFAULT NULL,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `warehouse_location_id` bigint(20) unsigned DEFAULT NULL,
  `transaction_number` varchar(70) NOT NULL,
  `transaction_type` enum('opening','receipt','issue','adjustment_in','adjustment_out','transfer_in','transfer_out','return_in','return_out') NOT NULL,
  `transaction_date` datetime NOT NULL,
  `reference_type` varchar(80) NOT NULL,
  `reference_id` bigint(20) unsigned NOT NULL,
  `reference_number` varchar(100) NOT NULL,
  `quantity_in` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `quantity_out` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `balance_after` decimal(18,6) NOT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `total_value` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `remarks` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_number` (`transaction_number`),
  KEY `idx_stock_transaction_item` (`company_id`,`item_id`,`transaction_date`),
  KEY `idx_stock_transaction_reference` (`reference_type`,`reference_id`),
  KEY `fk_stock_transaction_item` (`item_id`),
  KEY `fk_stock_transaction_batch` (`item_batch_id`),
  KEY `fk_stock_transaction_serial` (`item_serial_id`),
  KEY `fk_stock_transaction_roll` (`fabric_roll_id`),
  KEY `fk_stock_transaction_warehouse` (`warehouse_id`),
  KEY `fk_stock_transaction_location` (`warehouse_location_id`),
  KEY `fk_stock_transaction_user` (`created_by`),
  CONSTRAINT `fk_stock_transaction_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_stock_transaction_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_stock_transaction_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_stock_transaction_location` FOREIGN KEY (`warehouse_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_stock_transaction_roll` FOREIGN KEY (`fabric_roll_id`) REFERENCES `fabric_rolls` (`id`),
  CONSTRAINT `fk_stock_transaction_serial` FOREIGN KEY (`item_serial_id`) REFERENCES `item_serials` (`id`),
  CONSTRAINT `fk_stock_transaction_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_stock_transaction_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `stock_transfers` */

DROP TABLE IF EXISTS `stock_transfers`;

CREATE TABLE `stock_transfers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `transfer_number` varchar(60) NOT NULL,
  `item_id` bigint(20) unsigned NOT NULL,
  `item_batch_id` bigint(20) unsigned DEFAULT NULL,
  `from_warehouse_id` bigint(20) unsigned NOT NULL,
  `from_location_id` bigint(20) unsigned DEFAULT NULL,
  `to_warehouse_id` bigint(20) unsigned NOT NULL,
  `to_location_id` bigint(20) unsigned DEFAULT NULL,
  `transfer_date` date NOT NULL,
  `quantity` decimal(18,6) NOT NULL,
  `status` enum('draft','in_transit','received','cancelled') NOT NULL DEFAULT 'received',
  `reference` varchar(120) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `received_by` bigint(20) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transfer_number` (`transfer_number`),
  KEY `fk_stock_transfer_company` (`company_id`),
  KEY `fk_stock_transfer_item` (`item_id`),
  KEY `fk_stock_transfer_batch` (`item_batch_id`),
  KEY `fk_stock_transfer_from_warehouse` (`from_warehouse_id`),
  KEY `fk_stock_transfer_from_location` (`from_location_id`),
  KEY `fk_stock_transfer_to_warehouse` (`to_warehouse_id`),
  KEY `fk_stock_transfer_to_location` (`to_location_id`),
  KEY `fk_stock_transfer_creator` (`created_by`),
  KEY `fk_stock_transfer_receiver` (`received_by`),
  CONSTRAINT `fk_stock_transfer_batch` FOREIGN KEY (`item_batch_id`) REFERENCES `item_batches` (`id`),
  CONSTRAINT `fk_stock_transfer_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_stock_transfer_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_stock_transfer_from_location` FOREIGN KEY (`from_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_stock_transfer_from_warehouse` FOREIGN KEY (`from_warehouse_id`) REFERENCES `warehouses` (`id`),
  CONSTRAINT `fk_stock_transfer_item` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`),
  CONSTRAINT `fk_stock_transfer_receiver` FOREIGN KEY (`received_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_stock_transfer_to_location` FOREIGN KEY (`to_location_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_stock_transfer_to_warehouse` FOREIGN KEY (`to_warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `style_categories` */

DROP TABLE IF EXISTS `style_categories`;

CREATE TABLE `style_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `style_versions` */

DROP TABLE IF EXISTS `style_versions`;

CREATE TABLE `style_versions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `style_id` bigint(20) unsigned NOT NULL,
  `version_number` varchar(20) NOT NULL,
  `effective_date` date DEFAULT NULL,
  `change_summary` varchar(500) DEFAULT NULL,
  `status` enum('draft','approved','obsolete') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_style_version` (`style_id`,`version_number`),
  CONSTRAINT `fk_style_version_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `styles` */

DROP TABLE IF EXISTS `styles`;

CREATE TABLE `styles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `buyer_id` bigint(20) unsigned DEFAULT NULL,
  `style_category_id` bigint(20) unsigned DEFAULT NULL,
  `product_category_id` bigint(20) unsigned DEFAULT NULL,
  `brand_id` bigint(20) unsigned DEFAULT NULL,
  `season_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(150) NOT NULL,
  `gender` enum('men','women','unisex','kids') DEFAULT NULL,
  `description` text DEFAULT NULL,
  `status` enum('draft','active','inactive','archived') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_style_code` (`company_id`,`code`),
  KEY `fk_style_buyer` (`buyer_id`),
  KEY `fk_style_category` (`style_category_id`),
  KEY `fk_style_product_category` (`product_category_id`),
  KEY `fk_style_brand` (`brand_id`),
  KEY `fk_style_season` (`season_id`),
  CONSTRAINT `fk_style_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
  CONSTRAINT `fk_style_buyer` FOREIGN KEY (`buyer_id`) REFERENCES `buyers` (`id`),
  CONSTRAINT `fk_style_category` FOREIGN KEY (`style_category_id`) REFERENCES `style_categories` (`id`),
  CONSTRAINT `fk_style_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_style_product_category` FOREIGN KEY (`product_category_id`) REFERENCES `product_categories` (`id`),
  CONSTRAINT `fk_style_season` FOREIGN KEY (`season_id`) REFERENCES `seasons` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `supplier_categories` */

DROP TABLE IF EXISTS `supplier_categories`;

CREATE TABLE `supplier_categories` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `supplier_ledgers` */

DROP TABLE IF EXISTS `supplier_ledgers`;

CREATE TABLE `supplier_ledgers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `supplier_id` bigint(20) unsigned NOT NULL,
  `transaction_date` date NOT NULL,
  `transaction_type` enum('purchase_order','goods_receipt','purchase_return','payment','adjustment') NOT NULL,
  `reference_type` varchar(80) NOT NULL,
  `reference_id` bigint(20) unsigned NOT NULL,
  `reference_number` varchar(80) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `debit_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `credit_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `balance_amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `currency_id` bigint(20) unsigned NOT NULL,
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_supplier_ledger` (`company_id`,`supplier_id`,`transaction_date`),
  KEY `fk_supplier_ledger_supplier` (`supplier_id`),
  KEY `fk_supplier_ledger_currency` (`currency_id`),
  KEY `fk_supplier_ledger_creator` (`created_by`),
  CONSTRAINT `fk_supplier_ledger_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_supplier_ledger_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_supplier_ledger_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_supplier_ledger_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `supplier_quotations` */

DROP TABLE IF EXISTS `supplier_quotations`;

CREATE TABLE `supplier_quotations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `purchase_requisition_id` bigint(20) unsigned NOT NULL,
  `purchase_requisition_item_id` bigint(20) unsigned NOT NULL,
  `supplier_id` bigint(20) unsigned NOT NULL,
  `quotation_number` varchar(80) NOT NULL,
  `quotation_date` date NOT NULL,
  `valid_until` date DEFAULT NULL,
  `quoted_quantity` decimal(18,6) NOT NULL,
  `unit_price` decimal(18,6) NOT NULL,
  `total_amount` decimal(18,4) NOT NULL,
  `currency_id` bigint(20) unsigned NOT NULL,
  `delivery_days` int(10) unsigned NOT NULL DEFAULT 0,
  `payment_terms` varchar(255) DEFAULT NULL,
  `status` enum('received','selected','rejected','expired') NOT NULL DEFAULT 'received',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_supplier_quotation_pr` (`purchase_requisition_id`,`supplier_id`),
  KEY `fk_quotation_company` (`company_id`),
  KEY `fk_quotation_pr_item` (`purchase_requisition_item_id`),
  KEY `fk_quotation_supplier` (`supplier_id`),
  KEY `fk_quotation_currency` (`currency_id`),
  KEY `fk_quotation_creator` (`created_by`),
  CONSTRAINT `fk_quotation_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_quotation_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_quotation_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
  CONSTRAINT `fk_quotation_pr` FOREIGN KEY (`purchase_requisition_id`) REFERENCES `purchase_requisitions` (`id`),
  CONSTRAINT `fk_quotation_pr_item` FOREIGN KEY (`purchase_requisition_item_id`) REFERENCES `purchase_requisition_items` (`id`),
  CONSTRAINT `fk_quotation_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `suppliers` */

DROP TABLE IF EXISTS `suppliers`;

CREATE TABLE `suppliers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `supplier_category_id` bigint(20) unsigned DEFAULT NULL,
  `country_id` bigint(20) unsigned DEFAULT NULL,
  `currency_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(150) NOT NULL,
  `short_name` varchar(80) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `cc_email` varchar(150) DEFAULT NULL,
  `supply_item` varchar(120) DEFAULT NULL,
  `lead_time_days` smallint(5) unsigned DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `transit_time` varchar(50) DEFAULT NULL,
  `contact_person` varchar(120) DEFAULT NULL,
  `cc_person` varchar(120) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `tax_number` varchar(80) DEFAULT NULL,
  `payment_terms_days` smallint(5) unsigned DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_supplier_code` (`company_id`,`code`),
  KEY `fk_supplier_category` (`supplier_category_id`),
  KEY `fk_supplier_country` (`country_id`),
  KEY `fk_supplier_currency` (`currency_id`),
  CONSTRAINT `fk_supplier_category` FOREIGN KEY (`supplier_category_id`) REFERENCES `supplier_categories` (`id`),
  CONSTRAINT `fk_supplier_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_supplier_country` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`),
  CONSTRAINT `fk_supplier_currency` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `teams` */

DROP TABLE IF EXISTS `teams`;

CREATE TABLE `teams` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_team_code` (`company_id`,`code`),
  CONSTRAINT `fk_team_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `tech_packs` */

DROP TABLE IF EXISTS `tech_packs`;

CREATE TABLE `tech_packs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `style_id` bigint(20) unsigned NOT NULL,
  `style_version_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(50) NOT NULL,
  `title` varchar(180) NOT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `revision_date` date DEFAULT NULL,
  `status` enum('draft','approved','obsolete') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`),
  KEY `fk_tech_pack_style` (`style_id`),
  KEY `fk_tech_pack_style_version` (`style_version_id`),
  CONSTRAINT `fk_tech_pack_style` FOREIGN KEY (`style_id`) REFERENCES `styles` (`id`),
  CONSTRAINT `fk_tech_pack_style_version` FOREIGN KEY (`style_version_id`) REFERENCES `style_versions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `thread_details` */

DROP TABLE IF EXISTS `thread_details`;

CREATE TABLE `thread_details` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `brand` varchar(120) NOT NULL,
  `tex` varchar(50) DEFAULT NULL,
  `tkt` varchar(50) DEFAULT NULL,
  `yarn_count` varchar(50) DEFAULT NULL,
  `length_m` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `trial_balance` */

DROP TABLE IF EXISTS `trial_balance`;

CREATE TABLE `trial_balance` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `snapshot_number` varchar(60) NOT NULL,
  `date_from` date NOT NULL,
  `date_to` date NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `opening_debit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `opening_credit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `period_debit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `period_credit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `closing_debit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `closing_credit` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `generated_by` bigint(20) unsigned NOT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_trial_snapshot` (`company_id`,`snapshot_number`),
  KEY `fk_trial_account` (`account_id`),
  KEY `fk_trial_generator` (`generated_by`),
  CONSTRAINT `fk_trial_account` FOREIGN KEY (`account_id`) REFERENCES `chart_of_accounts` (`id`),
  CONSTRAINT `fk_trial_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_trial_generator` FOREIGN KEY (`generated_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `unit_conversions` */

DROP TABLE IF EXISTS `unit_conversions`;

CREATE TABLE `unit_conversions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `from_unit_id` bigint(20) unsigned NOT NULL,
  `to_unit_id` bigint(20) unsigned NOT NULL,
  `factor` decimal(24,10) NOT NULL DEFAULT 1.0000000000,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_unit_conversion_pair` (`from_unit_id`,`to_unit_id`),
  KEY `fk_unit_conversion_to` (`to_unit_id`),
  CONSTRAINT `fk_unit_conversion_from` FOREIGN KEY (`from_unit_id`) REFERENCES `converter_unit_names` (`id`),
  CONSTRAINT `fk_unit_conversion_to` FOREIGN KEY (`to_unit_id`) REFERENCES `converter_unit_names` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `unit_masters` */

DROP TABLE IF EXISTS `unit_masters`;

CREATE TABLE `unit_masters` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `item_category` varchar(80) NOT NULL,
  `unit_code` varchar(30) NOT NULL,
  `unit_name` varchar(50) NOT NULL,
  `uom_id` bigint(20) unsigned DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_unit_master_item_unit` (`item_category`,`unit_code`),
  KEY `fk_unit_master_uom` (`uom_id`),
  CONSTRAINT `fk_unit_master_uom` FOREIGN KEY (`uom_id`) REFERENCES `uoms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `uoms` */

DROP TABLE IF EXISTS `uoms`;

CREATE TABLE `uoms` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(100) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `user_roles` */

DROP TABLE IF EXISTS `user_roles`;

CREATE TABLE `user_roles` (
  `user_id` bigint(20) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`user_id`,`role_id`),
  KEY `fk_user_role_role` (`role_id`),
  CONSTRAINT `fk_user_role_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_role_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `users` */

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned DEFAULT NULL,
  `name` varchar(120) NOT NULL,
  `email` varchar(190) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` enum('active','inactive','locked') NOT NULL DEFAULT 'active',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `fk_user_company` (`company_id`),
  CONSTRAINT `fk_user_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `utility_costs` */

DROP TABLE IF EXISTS `utility_costs`;

CREATE TABLE `utility_costs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `cost_header_id` bigint(20) unsigned NOT NULL,
  `cost_detail_id` bigint(20) unsigned DEFAULT NULL,
  `utility_type` varchar(80) NOT NULL,
  `consumption` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `unit_name` varchar(30) DEFAULT NULL,
  `unit_rate` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `amount` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_utility_cost_header` (`cost_header_id`),
  KEY `fk_utility_cost_detail` (`cost_detail_id`),
  CONSTRAINT `fk_utility_cost_detail` FOREIGN KEY (`cost_detail_id`) REFERENCES `cost_details` (`id`),
  CONSTRAINT `fk_utility_cost_header` FOREIGN KEY (`cost_header_id`) REFERENCES `cost_headers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `warehouse_locations` */

DROP TABLE IF EXISTS `warehouse_locations`;

CREATE TABLE `warehouse_locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `warehouse_id` bigint(20) unsigned NOT NULL,
  `parent_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `location_type` enum('zone','rack','shelf','bin') NOT NULL DEFAULT 'bin',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_warehouse_location_code` (`warehouse_id`,`code`),
  KEY `fk_location_parent` (`parent_id`),
  CONSTRAINT `fk_location_parent` FOREIGN KEY (`parent_id`) REFERENCES `warehouse_locations` (`id`),
  CONSTRAINT `fk_location_warehouse` FOREIGN KEY (`warehouse_id`) REFERENCES `warehouses` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `warehouses` */

DROP TABLE IF EXISTS `warehouses`;

CREATE TABLE `warehouses` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `company_id` bigint(20) unsigned NOT NULL,
  `factory_id` bigint(20) unsigned DEFAULT NULL,
  `code` varchar(30) NOT NULL,
  `name` varchar(120) NOT NULL,
  `warehouse_type` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_company_warehouse_code` (`company_id`,`code`),
  KEY `fk_warehouse_factory` (`factory_id`),
  CONSTRAINT `fk_warehouse_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  CONSTRAINT `fk_warehouse_factory` FOREIGN KEY (`factory_id`) REFERENCES `factories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `washing_orders` */

DROP TABLE IF EXISTS `washing_orders`;

CREATE TABLE `washing_orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `production_order_id` bigint(20) unsigned NOT NULL,
  `process_number` varchar(60) NOT NULL,
  `wash_type` varchar(100) NOT NULL,
  `supplier_id` bigint(20) unsigned DEFAULT NULL,
  `planned_quantity` decimal(18,4) NOT NULL,
  `input_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `output_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `rejected_quantity` decimal(18,4) NOT NULL DEFAULT 0.0000,
  `process_date` date NOT NULL,
  `unit_cost` decimal(18,6) NOT NULL DEFAULT 0.000000,
  `status` enum('draft','sent','in_process','received','completed','cancelled') NOT NULL DEFAULT 'draft',
  `created_by` bigint(20) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `process_number` (`process_number`),
  KEY `fk_washing_order_production` (`production_order_id`),
  KEY `fk_washing_order_supplier` (`supplier_id`),
  KEY `fk_washing_order_creator` (`created_by`),
  CONSTRAINT `fk_washing_order_creator` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_washing_order_production` FOREIGN KEY (`production_order_id`) REFERENCES `production_orders` (`id`),
  CONSTRAINT `fk_washing_order_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `zipper_tape_colors` */

DROP TABLE IF EXISTS `zipper_tape_colors`;

CREATE TABLE `zipper_tape_colors` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `name` varchar(100) NOT NULL,
  `tcx_code` varchar(50) DEFAULT NULL,
  `tpx_code` varchar(50) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
