/*!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 */; DROP TABLE IF EXISTS `attendance`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `audit_logs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bank_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_approvals`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_colors`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_consumptions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_headers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_materials`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_operations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_sizes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bom_versions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `brands`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `bundle_cards`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `buyer_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `buyer_order_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `buyer_sample_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `buyer_seasons`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `buyer_ship_countries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `buyers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cash_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `chart_of_accounts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `colors`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=72 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `commercial_costs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `commercial_invoices`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `companies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `company_branches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `containers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `converter_unit_names`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cost_centers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cost_components`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cost_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cost_headers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cost_revisions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `costing_op_headers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `costing_op_line_options`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `costing_op_lines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `costing_op_options`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `costing_op_wash_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `countries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `currencies`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cutting_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `cutting_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `dashboard_cache`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `defect_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `departments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `designations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `embroidery_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `employees`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `end_users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `fabric_contents`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `fabric_inspections`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `fabric_rolls`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `factories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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 AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `final_qc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `finishing_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `fit_masters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=62 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `general_ledger`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `goods_receipts`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `inline_qc`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `issue_notes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `item_batches`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `item_code`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `item_code` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `code` varchar(30) NOT NULL, `item_code_description` text DEFAULT NULL, `prefix_id` bigint(20) unsigned DEFAULT NULL, `item_type` varchar(80) DEFAULT NULL, `supplier_item_code` varchar(100) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, `composition` varchar(255) DEFAULT NULL, `composition_json` text DEFAULT NULL, `construction` varchar(120) DEFAULT NULL, `weight` varchar(50) DEFAULT NULL, `weight_type` varchar(50) DEFAULT NULL, `color_id` bigint(20) unsigned DEFAULT NULL, `finish_id` bigint(20) unsigned DEFAULT NULL, `finish` varchar(120) DEFAULT NULL, `width_1` varchar(50) DEFAULT NULL, `width_type_1` varchar(50) DEFAULT NULL, `width_2` varchar(50) DEFAULT NULL, `width_type_2` varchar(50) DEFAULT NULL, `tex` varchar(50) DEFAULT NULL, `tkt` varchar(50) DEFAULT NULL, `yarn_count` varchar(50) DEFAULT NULL, `length_value` varchar(50) DEFAULT NULL, `length_type` varchar(50) DEFAULT NULL, `zipper_no` varchar(80) DEFAULT NULL, `lock_type` varchar(80) DEFAULT NULL, `end_type` varchar(80) DEFAULT NULL, `zipper_tape_color_id` bigint(20) unsigned DEFAULT NULL, `default_uom_id` bigint(20) unsigned DEFAULT NULL, `future_1` varchar(120) DEFAULT NULL, `future_2` varchar(120) DEFAULT NULL, `future_3` varchar(120) DEFAULT NULL, `future_4` varchar(120) DEFAULT NULL, `future_5` varchar(120) DEFAULT NULL, `future_6` varchar(120) DEFAULT NULL, `other_information` text DEFAULT NULL, `supplier_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_item_code` (`code`), KEY `idx_item_code_prefix` (`prefix_id`), KEY `idx_item_code_supplier` (`supplier_id`), KEY `idx_item_code_type` (`item_type`), KEY `fk_item_code_color` (`color_id`), KEY `fk_item_code_finish` (`finish_id`), KEY `fk_item_code_uom` (`default_uom_id`), KEY `idx_item_code_zipper_tape` (`zipper_tape_color_id`), CONSTRAINT `fk_item_code_color` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`), CONSTRAINT `fk_item_code_finish` FOREIGN KEY (`finish_id`) REFERENCES `metal_finishes` (`id`), CONSTRAINT `fk_item_code_prefix` FOREIGN KEY (`prefix_id`) REFERENCES `prefix_masters` (`id`), CONSTRAINT `fk_item_code_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`), CONSTRAINT `fk_item_code_uom` FOREIGN KEY (`default_uom_id`) REFERENCES `unit_masters` (`id`), CONSTRAINT `fk_item_code_zipper_tape_color` FOREIGN KEY (`zipper_tape_color_id`) REFERENCES `zipper_tape_colors` (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `item_serials`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `journal_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `journal_entries`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `labor_costs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `leave_applications`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `line_production`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `machine_costs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `machine_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `machines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `material_costs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `material_requirements`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `metal_finishes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `migrations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `mrp_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `mrp_headers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `operations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `overhead_costs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `overtime`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `packing_lists`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `packing_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `payables`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `payroll`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=237 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `prefix_masters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `printing_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `product_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `product_codes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `product_images`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `product_measurements`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `product_subcategories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `product_types`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `product_variants`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `production_costs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `production_lines`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `production_losses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `production_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `products`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `profit_analysis`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `purchase_order_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `purchase_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `purchase_requisition_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `purchase_requisitions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `purchase_returns`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `quality_reports`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `receivables`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `receive_notes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `report_templates`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `role_permissions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `salary_components`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `saved_filters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `seasons`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `sewing_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `shipment_items`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `shipments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `shortage_reports`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `sizes`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `stock_adjustments`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `stock_balances`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `stock_reservations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `stock_transactions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `stock_transfers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `style_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `style_versions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `styles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `supplier_categories`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `supplier_ledgers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `supplier_quotations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `suppliers`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `teams`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `tech_packs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `thread_details`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `trial_balance`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `unit_conversions`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `unit_masters`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `uoms`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `user_roles`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `users`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `utility_costs`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `warehouse_locations`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `warehouses`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `washing_orders`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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; /*!40101 SET character_set_client = @saved_cs_client */; DROP TABLE IF EXISTS `zipper_tape_colors`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; 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 character_set_client = @saved_cs_client */; /*!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 */;