-- FCQC Full Database Setup - Consolidated Migration & Seed
-- Generated: 2026-07-22 12:25:21
-- Total files: 53

-- ========================================
-- IMPORTANT: Run this file on a fresh fcqc_database
-- ========================================

CREATE DATABASE IF NOT EXISTS fcqc_database CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE fcqc_database;


-- ============================================================
-- FILE: setup_db.sql
-- ============================================================

-- FCQC Database Setup
-- Run this file first to create the database, then run the individual migration files

CREATE DATABASE IF NOT EXISTS fcqc_database
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE fcqc_database;


-- ============================================================
-- FILE: migration_core.sql
-- ============================================================

-- ============================================================
-- FCQC - Core Tables Migration
-- Companies, Users, Roles, Permissions, RBAC, Audit, Inspectors
-- ============================================================


-- -----------------------------------------------------------
-- 1. companies
-- -----------------------------------------------------------
CREATE TABLE `companies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `slug` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_person` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `logo_path` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `country` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT '',
  `timezone` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'UTC',
  `language` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'en',
  `certificates` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `status` enum('active','inactive','suspended') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `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 `company_name` (`company_name`),
  UNIQUE KEY `slug` (`slug`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 2. user_groups
-- -----------------------------------------------------------
CREATE TABLE `user_groups` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 3. app_roles
-- -----------------------------------------------------------
CREATE TABLE `app_roles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 4. users
-- -----------------------------------------------------------
CREATE TABLE `users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned DEFAULT NULL,
  `employee_code` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT '',
  `role_id` int unsigned DEFAULT NULL,
  `group_id` int unsigned DEFAULT NULL,
  `status` enum('active','inactive') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `last_login_at` timestamp NULL DEFAULT NULL,
  `avatar_url` varchar(500) COLLATE utf8mb4_unicode_ci 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 `email` (`email`),
  KEY `company_id` (`company_id`),
  KEY `role_id` (`role_id`),
  KEY `group_id` (`group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 5. permissions
-- -----------------------------------------------------------
CREATE TABLE `permissions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `group_name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'general',
  `description` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 6. role_permissions
-- -----------------------------------------------------------
CREATE TABLE `role_permissions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `role_id` int unsigned NOT NULL,
  `permission_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_id` (`role_id`,`permission_id`),
  KEY `permission_id` (`permission_id`),
  CONSTRAINT `rp_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `app_roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `rp_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 7. quality_inspectors
-- -----------------------------------------------------------
CREATE TABLE `quality_inspectors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `employee_code` varchar(100) DEFAULT '',
  `designation` varchar(255) DEFAULT '',
  `certification` varchar(255) DEFAULT '',
  `joined_date` date DEFAULT NULL,
  `performance_score` decimal(5,2) NOT NULL DEFAULT 0.00,
  `total_inspections` int NOT NULL DEFAULT 0,
  `total_defects_found` int NOT NULL DEFAULT 0,
  `status` enum('active','inactive','on_leave') NOT NULL DEFAULT 'active',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- 8. audit_log
-- -----------------------------------------------------------
CREATE TABLE `audit_log` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `entity_type` varchar(100) NOT NULL DEFAULT '',
  `entity_id` int unsigned DEFAULT NULL,
  `action` varchar(50) NOT NULL DEFAULT '',
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) DEFAULT '',
  `field_name` varchar(100) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `ip_address` varchar(45) DEFAULT '',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `entity_type` (`entity_type`,`entity_id`),
  KEY `user_id` (`user_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ============================================================
-- FILE: seed_core.sql
-- ============================================================

-- ============================================================
-- FCQC - Core Seed Data
-- Admin user, roles, permissions, test company, inspectors
-- ============================================================

-- -----------------------------------------------------------
-- 1. User Groups
-- -----------------------------------------------------------
INSERT IGNORE INTO `user_groups` (`name`, `label`) VALUES
('management', 'Management'),
('merchandising', 'Merchandising'),
('production', 'Production'),
('quality', 'Quality'),
('planning', 'Planning'),
('procurement', 'Procurement'),
('finance', 'Finance'),
('hr', 'HR');

-- -----------------------------------------------------------
-- 2. Permissions
-- -----------------------------------------------------------
INSERT IGNORE INTO `permissions` (`code`, `label`, `group_name`, `description`) VALUES
-- Core
('dashboard.view', 'View Dashboard', 'General', 'Access the main dashboard'),
('profile.view', 'View Profile', 'General', 'View and edit own profile'),
-- Quality
('quality.view', 'View Quality', 'Quality', 'Access quality module'),
('quality.inspect', 'Perform Inspections', 'Quality', 'Execute inspection sessions'),
('quality.approve', 'Approve Inspections', 'Quality', 'Approve or reject inspection results'),
('quality.manage', 'Manage Quality', 'Quality', 'Configure templates, rules, and plans'),
-- Reports
('reports.view', 'View Reports', 'Reports', 'Access report section'),
-- Notifications
('notifications.view', 'View Notifications', 'Notifications', 'View notifications'),
-- Admin
('roles.manage', 'Manage Roles & Permissions', 'Administration', 'Create, edit, and assign roles and permissions'),
('users.view', 'View Users', 'Administration', 'View user list'),
('users.create', 'Create Users', 'Administration', 'Create new users'),
('users.edit', 'Edit Users', 'Administration', 'Edit existing users'),
('users.delete', 'Delete Users', 'Administration', 'Delete users');

-- -----------------------------------------------------------
-- 3. Roles
-- -----------------------------------------------------------
INSERT IGNORE INTO `app_roles` (`name`, `label`, `description`) VALUES
('admin', 'Administrator', 'Full system access'),
('quality_manager', 'Quality Manager', 'Manages quality inspections and team'),
('quality_inspector', 'Quality Inspector', 'Executes inspections'),
('quality_viewer', 'Quality Viewer', 'Read-only access to quality data');

-- -----------------------------------------------------------
-- 4. Role-Permission assignments
-- -----------------------------------------------------------
-- Admin gets all permissions
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p WHERE r.name = 'admin';

-- Quality Manager
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name = 'quality_manager'
  AND p.code IN ('dashboard.view', 'profile.view', 'quality.view', 'quality.inspect', 'quality.approve', 'quality.manage', 'reports.view', 'notifications.view');

-- Quality Inspector
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name = 'quality_inspector'
  AND p.code IN ('dashboard.view', 'profile.view', 'quality.view', 'quality.inspect', 'notifications.view');

-- Quality Viewer
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name = 'quality_viewer'
  AND p.code IN ('dashboard.view', 'profile.view', 'quality.view', 'reports.view');

-- -----------------------------------------------------------
-- 5. Company
-- -----------------------------------------------------------
INSERT IGNORE INTO `companies` (`company_name`, `slug`, `contact_person`, `email`, `country`, `timezone`, `status`) VALUES
('FCQC Test Corp', 'fcqc-test', 'Admin User', 'admin@fcqc.com', 'Bangladesh', 'Asia/Dhaka', 'active');

-- -----------------------------------------------------------
-- 6. Admin User (password: "password")
-- -----------------------------------------------------------
INSERT IGNORE INTO `users` (`company_id`, `employee_code`, `name`, `email`, `password_hash`, `role_id`, `status`)
SELECT c.id, 'EMP-0000', 'Admin', 'admin@fcqc.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', r.id, 'active'
FROM companies c, app_roles r
WHERE c.slug = 'fcqc-test' AND r.name = 'admin';

-- -----------------------------------------------------------
-- 7. Sample Quality Inspectors
-- -----------------------------------------------------------
INSERT IGNORE INTO `quality_inspectors` (`name`, `employee_code`, `designation`, `certification`, `status`) VALUES
('Rafiq Hasan', 'QC-001', 'Senior Inspector', 'AQL Level II', 'active'),
('Fatima Begum', 'QC-002', 'Inspector', 'AQL Level I', 'active'),
('Kamal Hossain', 'QC-003', 'Junior Inspector', 'In Training', 'active'),
('Nasrin Akter', 'QC-004', 'Lead Inspector', 'AQL Level III', 'active');


-- ============================================================
-- FILE: migration_master_data.sql
-- ============================================================


CREATE TABLE IF NOT EXISTS `master_departments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `description` text,
  `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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_vendors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `type` enum('fabric','trim','accessory','chemical','packaging','service','logistics','other') NOT NULL DEFAULT 'other',
  `contact_person` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text,
  `status` enum('active','inactive','blacklisted') NOT NULL DEFAULT 'active',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_buyers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text,
  `country` 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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_styles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `buyer_id` int unsigned DEFAULT NULL,
  `style_no` varchar(100) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `image_path` 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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `buyer_id` (`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_buyer_pos` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned DEFAULT NULL,
  `po_no` varchar(100) NOT NULL,
  `buyer_id` int unsigned DEFAULT NULL,
  `order_date` date DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `size_series_id` int unsigned DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `description` text,
  `po_type` varchar(100) DEFAULT NULL,
  `total_qty` int unsigned DEFAULT NULL,
  `per_pcs_rate` decimal(10,2) DEFAULT NULL,
  `currency_id` int unsigned DEFAULT NULL,
  `exchange_rate` decimal(10,4) 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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `style_id` (`style_id`),
  KEY `buyer_id` (`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_buyer_po_documents` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `po_id` int unsigned NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `original_name` varchar(255) NOT NULL,
  `file_size` int unsigned DEFAULT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `uploaded_by` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_size_series` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `name` varchar(255) NOT NULL,
  `series_code` varchar(50) DEFAULT NULL,
  `category_id` int unsigned DEFAULT NULL,
  `standard_id` int unsigned DEFAULT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `description` text,
  `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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `category_id` (`category_id`),
  KEY `standard_id` (`standard_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `master_size_series` (`id`, `company_id`, `name`, `series_code`, `category_id`, `standard_id`, `short_name`, `description`, `status`) VALUES
(1, 1, 'S/M/L/XL', 'SS-001', 1, 9, NULL, 'Standard small to extra large', 'active'),
(2, 1, 'S/M/L', 'SS-002', 1, 9, NULL, 'Standard small to large', 'active'),
(3, 1, '30/32/34', 'SS-003', 1, 1, NULL, 'Waist sizes 30 to 34', 'active'),
(4, 1, '38/40/42/44', 'SS-004', 10, 3, NULL, 'European sizes 38 to 44', 'active'),
(5, 1, 'XS/S/M', 'SS-005', 1, 9, NULL, 'Extra small to medium', 'active');

CREATE TABLE IF NOT EXISTS `master_size_series_sizes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `size_series_id` int unsigned NOT NULL,
  `size_value` varchar(50) NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `display_order` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `size_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `sort_order` int unsigned 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_cat_company_code` (`company_id`, `code`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `size_categories` (`id`, `company_id`, `code`, `name`, `description`, `sort_order`, `status`) VALUES
(1, 1, 'ADULT', 'Adult', 'Adult garments', 1, 'active'),
(2, 1, 'MENS', 'Mens', 'Men''s garments', 2, 'active'),
(3, 1, 'LADIES', 'Ladies', 'Women''s garments', 3, 'active'),
(4, 1, 'UNISEX', 'Unisex', 'Unisex garments', 4, 'active'),
(5, 1, 'BOYS', 'Boys', 'Boys clothing', 5, 'active'),
(6, 1, 'GIRLS', 'Girls', 'Girls clothing', 6, 'active'),
(7, 1, 'KIDS', 'Kids', 'Kids clothing', 7, 'active'),
(8, 1, 'BABY', 'Baby', 'Baby clothing', 8, 'active'),
(9, 1, 'INFANT', 'Infant', 'Infant clothing', 9, 'active'),
(10, 1, 'FOOTWEAR', 'Footwear', 'Shoes and footwear', 10, 'active'),
(11, 1, 'ACCESSORIES', 'Accessories', 'Accessories', 11, 'active'),
(12, 1, 'ONE_SIZE', 'One Size', 'One size fits all', 12, 'active'),
(13, 1, 'PLUS_SIZE', 'Plus Size', 'Plus size garments', 13, 'active'),
(14, 1, 'PET_APPAREL', 'Pet Apparel', 'Pet clothing', 14, 'active');

CREATE TABLE IF NOT EXISTS `size_standards` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `sort_order` int unsigned 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_std_company_code` (`company_id`, `code`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `size_standards` (`id`, `company_id`, `code`, `name`, `description`, `sort_order`, `status`) VALUES
(1, 1, 'US', 'US', 'US sizing', 1, 'active'),
(2, 1, 'UK', 'UK', 'UK sizing', 2, 'active'),
(3, 1, 'EU', 'EU', 'European sizing', 3, 'active'),
(4, 1, 'ASIA', 'Asia', 'Asian sizing', 4, 'active'),
(5, 1, 'JP', 'Japan', 'Japanese sizing', 5, 'active'),
(6, 1, 'CN', 'China', 'Chinese sizing', 6, 'active'),
(7, 1, 'AU', 'Australia', 'Australian sizing', 7, 'active'),
(8, 1, 'IN', 'India', 'Indian sizing', 8, 'active'),
(9, 1, 'INTL', 'International', 'International sizing', 9, 'active'),
(10, 1, 'CUSTOM', 'Custom', 'Custom sizing', 10, 'active');

CREATE TABLE IF NOT EXISTS `size_master` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_code` varchar(50) NOT NULL,
  `size_name` varchar(255) NOT NULL,
  `short_name` varchar(50) DEFAULT NULL,
  `category_id` int unsigned DEFAULT NULL,
  `standard_id` int unsigned DEFAULT NULL,
  `display_sequence` int unsigned NOT NULL DEFAULT 0,
  `sort_order` int unsigned 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_sz_dim` (`company_id`, `category_id`, `standard_id`, `size_code`),
  KEY `company_id` (`company_id`),
  KEY `category_id` (`category_id`),
  KEY `standard_id` (`standard_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `size_master` (`id`, `company_id`, `size_code`, `size_name`, `short_name`, `category_id`, `standard_id`, `display_sequence`, `sort_order`, `status`) VALUES
(1, 1, 'XXS', 'Extra Extra Small', 'XXS', 1, NULL, 1, 1, 'active'),
(2, 1, 'XS', 'Extra Small', 'XS', 1, NULL, 2, 2, 'active'),
(3, 1, 'S', 'Small', 'S', 1, NULL, 3, 3, 'active'),
(4, 1, 'M', 'Medium', 'M', 1, NULL, 4, 4, 'active'),
(5, 1, 'L', 'Large', 'L', 1, NULL, 5, 5, 'active'),
(6, 1, 'XL', 'Extra Large', 'XL', 1, NULL, 6, 6, 'active'),
(7, 1, 'XXL', 'Double Extra Large', 'XXL', 1, NULL, 7, 7, 'active'),
(8, 1, '3XL', 'Triple Extra Large', '3XL', 1, NULL, 8, 8, 'active'),
(9, 1, '4XL', 'Quadruple Extra Large', '4XL', 1, NULL, 9, 9, 'active'),
(10, 1, '5XL', 'Quintuple Extra Large', '5XL', 1, NULL, 10, 10, 'active'),
(11, 1, '2Y', '2 Years', '2Y', 7, NULL, 11, 11, 'active'),
(12, 1, '3Y', '3 Years', '3Y', 7, NULL, 12, 12, 'active'),
(13, 1, '4Y', '4 Years', '4Y', 7, NULL, 13, 13, 'active'),
(14, 1, '5Y', '5 Years', '5Y', 7, NULL, 14, 14, 'active'),
(15, 1, '6Y', '6 Years', '6Y', 7, NULL, 15, 15, 'active'),
(16, 1, '7Y', '7 Years', '7Y', 7, NULL, 16, 16, 'active'),
(17, 1, '8Y', '8 Years', '8Y', 7, NULL, 17, 17, 'active'),
(18, 1, '9Y', '9 Years', '9Y', 7, NULL, 18, 18, 'active'),
(19, 1, '10Y', '10 Years', '10Y', 7, NULL, 19, 19, 'active'),
(20, 1, '12Y', '12 Years', '12Y', 7, NULL, 20, 20, 'active'),
(21, 1, '14Y', '14 Years', '14Y', 7, NULL, 21, 21, 'active'),
(22, 1, '16Y', '16 Years', '16Y', 7, NULL, 22, 22, 'active'),
(23, 1, 'NB', 'Newborn', 'NB', 8, NULL, 23, 23, 'active'),
(24, 1, '0-3M', '0-3 Months', '0-3M', 8, NULL, 24, 24, 'active'),
(25, 1, '3-6M', '3-6 Months', '3-6M', 8, NULL, 25, 25, 'active'),
(26, 1, '6-9M', '6-9 Months', '6-9M', 8, NULL, 26, 26, 'active'),
(27, 1, '9-12M', '9-12 Months', '9-12M', 8, NULL, 27, 27, 'active'),
(28, 1, '12-18M', '12-18 Months', '12-18M', 8, NULL, 28, 28, 'active'),
(29, 1, '18-24M', '18-24 Months', '18-24M', 8, NULL, 29, 29, 'active'),
(30, 1, '26', 'Waist 26', '26', 1, 1, 30, 30, 'active'),
(31, 1, '28', 'Waist 28', '28', 1, 1, 31, 31, 'active'),
(32, 1, '30', 'Waist 30', '30', 1, 1, 32, 32, 'active'),
(33, 1, '32', 'Waist 32', '32', 1, 1, 33, 33, 'active'),
(34, 1, '34', 'Waist 34', '34', 1, 1, 34, 34, 'active'),
(35, 1, '36', 'Waist 36', '36', 1, 1, 35, 35, 'active'),
(36, 1, '38', 'Waist 38', '38', 1, 1, 36, 36, 'active'),
(37, 1, '40', 'Waist 40', '40', 1, 1, 37, 37, 'active'),
(38, 1, '42', 'Waist 42', '42', 1, 1, 38, 38, 'active'),
(39, 1, '44', 'Waist 44', '44', 1, 1, 39, 39, 'active'),
(40, 1, '46', 'Waist 46', '46', 1, 1, 40, 40, 'active'),
(41, 1, '35', 'EU 35', '35', 10, 3, 41, 41, 'active'),
(42, 1, '36', 'EU 36', '36', 10, 3, 42, 42, 'active'),
(43, 1, '37', 'EU 37', '37', 10, 3, 43, 43, 'active'),
(44, 1, '38', 'EU 38', '38', 10, 3, 44, 44, 'active'),
(45, 1, '39', 'EU 39', '39', 10, 3, 45, 45, 'active'),
(46, 1, '40', 'EU 40', '40', 10, 3, 46, 46, 'active'),
(47, 1, '41', 'EU 41', '41', 10, 3, 47, 47, 'active'),
(48, 1, '42', 'EU 42', '42', 10, 3, 48, 48, 'active'),
(49, 1, '43', 'EU 43', '43', 10, 3, 49, 49, 'active'),
(50, 1, '44', 'EU 44', '44', 10, 3, 50, 50, 'active'),
(51, 1, '45', 'EU 45', '45', 10, 3, 51, 51, 'active'),
(52, 1, '46', 'EU 46', '46', 10, 3, 52, 52, 'active'),
(53, 1, '47', 'EU 47', '47', 10, 3, 53, 53, 'active'),
(54, 1, '48', 'EU 48', '48', 10, 3, 54, 54, 'active'),
(55, 1, 'OS', 'One Size', 'OS', 12, NULL, 55, 55, 'active');

INSERT IGNORE INTO `master_size_series_sizes` (`id`, `size_series_id`, `size_value`, `display_order`) VALUES
(1, 1, 'S', 1), (2, 1, 'M', 2), (3, 1, 'L', 3), (4, 1, 'XL', 4),
(5, 2, 'S', 1), (6, 2, 'M', 2), (7, 2, 'L', 3),
(8, 3, '30', 1), (9, 3, '32', 2), (10, 3, '34', 3),
(11, 4, '38', 1), (12, 4, '40', 2), (13, 4, '42', 3), (14, 4, '44', 4),
(15, 5, 'XS', 1), (16, 5, 'S', 2), (17, 5, 'M', 3);

-- ----------------------------------------------------------------------
-- Size Management Phase 2: Buyer / Brand / Country size mappings
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `buyer_size_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `buyer_id` int unsigned DEFAULT NULL,
  `buyer_name` varchar(255) DEFAULT NULL,
  `buyer_size` varchar(50) NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `size_code` varchar(50) DEFAULT NULL,
  `display_order` int unsigned 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,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `buyer_id` (`buyer_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `brand_size_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `brand_name` varchar(255) NOT NULL,
  `brand_size` varchar(50) NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `size_code` varchar(50) DEFAULT NULL,
  `display_order` int unsigned 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,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `country_size_mapping` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `size_code` varchar(50) DEFAULT NULL,
  `country_code` varchar(50) NOT NULL,
  `country_size` varchar(50) NOT NULL,
  `display_order` int unsigned 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,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------
-- Size Management Phase 3: Measurement points / series measurements / grade rules
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `measurement_points` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `unit` varchar(20) NOT NULL DEFAULT 'cm',
  `category_id` int unsigned DEFAULT NULL,
  `sort_order` int unsigned 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_mp_company_code` (`company_id`, `code`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `measurement_points` (`id`, `company_id`, `code`, `name`, `description`, `unit`, `category_id`, `sort_order`, `status`) VALUES
(1, 1, 'CHEST', 'Chest Width', 'Width across chest 1 inch below armhole', 'cm', NULL, 1, 'active'),
(2, 1, 'BUST', 'Bust', 'Bust circumference at fullest point', 'cm', NULL, 2, 'active'),
(3, 1, 'WAIST', 'Waist', 'Waist circumference at natural waist', 'cm', NULL, 3, 'active'),
(4, 1, 'HIP', 'Hip', 'Hip circumference at fullest point', 'cm', NULL, 4, 'active'),
(5, 1, 'SHOULDER', 'Shoulder Width', 'Shoulder point to shoulder point', 'cm', NULL, 5, 'active'),
(6, 1, 'SLEEVE_LEN', 'Sleeve Length', 'Shoulder point to sleeve hem', 'cm', NULL, 6, 'active'),
(7, 1, 'BODY_LEN', 'Body Length', 'HPS to bottom hem', 'cm', NULL, 7, 'active'),
(8, 1, 'NECK', 'Neck Width', 'Neck circumference', 'cm', NULL, 8, 'active'),
(9, 1, 'ARMHOLE', 'Armhole', 'Armhole circumference', 'cm', NULL, 9, 'active'),
(10, 1, 'CROSS_BACK', 'Cross Back', 'Across back at mid armhole level', 'cm', NULL, 10, 'active'),
(11, 1, 'INSEAM', 'Inseam Length', 'Crotch to bottom hem', 'cm', NULL, 11, 'active'),
(12, 1, 'THIGH', 'Thigh Width', 'Thigh circumference 2 inches below crotch', 'cm', NULL, 12, 'active'),
(13, 1, 'KNEE', 'Knee Width', 'Knee circumference', 'cm', NULL, 13, 'active'),
(14, 1, 'HEM', 'Bottom Hem Width', 'Bottom hem circumference', 'cm', NULL, 14, 'active'),
(15, 1, 'CUFF', 'Cuff Opening', 'Cuff circumference', 'cm', NULL, 15, 'active'),
(16, 1, 'COLLAR_H', 'Collar Height', 'Collar band height', 'cm', NULL, 16, 'active'),
(17, 1, 'CENTER_FRONT', 'Center Front Length', 'Center front from neck to hem', 'cm', NULL, 17, 'active'),
(18, 1, 'BACK_LEN', 'Back Length', 'Center back from HPS to hem', 'cm', NULL, 18, 'active'),
(19, 1, 'RISE', 'Body Rise', 'Waist front to crotch', 'cm', NULL, 19, 'active'),
(20, 1, 'SWEEP', 'Sweep', 'Garment sweep at bottom', 'cm', NULL, 20, 'active');

CREATE TABLE IF NOT EXISTS `size_series_measurements` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `measurement_point_id` int unsigned NOT NULL,
  `size_code` varchar(50) NOT NULL,
  `value` decimal(10,2) DEFAULT NULL,
  `display_order` int unsigned 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,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `measurement_point_id` (`measurement_point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `grade_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `measurement_point_id` int unsigned DEFAULT NULL,
  `rule_code` varchar(50) DEFAULT NULL,
  `rule_name` varchar(255) DEFAULT NULL,
  `grade_value` decimal(10,2) DEFAULT NULL,
  `unit` varchar(20) NOT NULL DEFAULT 'cm',
  `display_order` int unsigned 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,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `measurement_point_id` (`measurement_point_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `size_series_ai_recommendations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `size_code` varchar(50) NOT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `ratio` decimal(6,2) NOT NULL DEFAULT 0.00,
  `ratio_type` enum('suggested','manual') NOT NULL DEFAULT 'manual',
  `based_on` enum('manual','po_history') NOT NULL DEFAULT 'manual',
  `period_label` varchar(100) DEFAULT NULL,
  `note` text,
  `display_order` int unsigned 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_ai_series_size` (`size_series_id`, `size_code`),
  KEY `size_series_id` (`size_series_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `size_series_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `size_series_id` int unsigned NOT NULL,
  `action` varchar(50) NOT NULL,
  `field` varchar(100) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) DEFAULT NULL,
  `note` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS `master_buyer_departments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `department_code` varchar(20) NOT NULL,
  `department_name` varchar(100) NOT NULL,
  `department_group` varchar(50) DEFAULT NULL,
  `description` text,
  `display_order` int unsigned 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `department_code` (`department_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_buyer_dept_links` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `buyer_id` int unsigned NOT NULL,
  `buyer_department_id` int unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `buyer_id` (`buyer_id`),
  KEY `buyer_department_id` (`buyer_department_id`),
  UNIQUE KEY `buyer_dept` (`buyer_id`, `buyer_department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------
-- Size Management Phase 5: Cross-Module Integration
-- master_styles.size_series_id  (added via ALTER above / FULL_SETUP line ~8501)
-- master_buyer_po_color_sizes.size_master_id (below)
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_buyer_po_color_sizes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `po_id` int unsigned NOT NULL,
  `color` varchar(100) NOT NULL DEFAULT '',
  `size` varchar(100) NOT NULL DEFAULT '',
  `size_master_id` int unsigned DEFAULT NULL,
  `quantity` int unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------
-- Size Management Phase 6: Downstream Module Integration
-- Carries the size_master link into packing, warehouse, and shipment tables.
-- ----------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inventory_serials` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `serial_no` varchar(50) NOT NULL,
  `serial_type` enum('roll','carton','pallet','garment','bundle','accessory_box') NOT NULL DEFAULT 'roll',
  `ref_type` varchar(50) DEFAULT NULL,
  `ref_id` int unsigned DEFAULT NULL,
  `item_id` int unsigned DEFAULT NULL,
  `batch_id` int unsigned DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `shade` varchar(50) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `qty` decimal(14,4) NOT NULL DEFAULT 0.0000,
  `warehouse_id` int unsigned DEFAULT NULL,
  `zone_id` int unsigned DEFAULT NULL,
  `rack_id` int unsigned DEFAULT NULL,
  `bin_id` int unsigned DEFAULT NULL,
  `location_code` varchar(100) DEFAULT NULL,
  `status` enum('expected','received','inspected','available','reserved','allocated','issued','in_production','packed','shipped','blocked') NOT NULL DEFAULT 'expected',
  `first_received_at` datetime DEFAULT NULL,
  `last_moved_at` datetime 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 `company_id` (`company_id`),
  KEY `serial_no` (`serial_no`),
  KEY `item_id` (`item_id`),
  KEY `status` (`status`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `shipment_manifest_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `manifest_id` int unsigned NOT NULL,
  `dispatch_note_id` int unsigned DEFAULT NULL,
  `packing_order_id` int unsigned DEFAULT NULL,
  `finished_goods_receipt_id` int unsigned DEFAULT NULL,
  `production_order_id` int unsigned DEFAULT NULL,
  `unit_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `quantity` int unsigned NOT NULL DEFAULT 0,
  `cartons` int unsigned DEFAULT 0,
  `weight_kg` decimal(14,2) DEFAULT NULL,
  `volume_cbm` decimal(10,3) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `manifest_id` (`manifest_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `shipment_allocations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `reservation_id` int unsigned NOT NULL,
  `stock_id` int unsigned DEFAULT NULL,
  `warehouse_id` int unsigned DEFAULT NULL,
  `location_id` int unsigned DEFAULT NULL,
  `item_id` int unsigned DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `size_master_id` int unsigned DEFAULT NULL,
  `lot_no` varchar(100) DEFAULT NULL,
  `batch_no` varchar(100) DEFAULT NULL,
  `uom_id` int unsigned DEFAULT NULL,
  `allocated_qty` decimal(14,4) NOT NULL DEFAULT 0.0000,
  `picked_qty` decimal(14,4) NOT NULL DEFAULT 0.0000,
  `status` enum('allocated','picked','partially_picked','fulfilled','cancelled') NOT NULL DEFAULT 'allocated',
  `allocated_by` int unsigned DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `reservation_id` (`reservation_id`),
  KEY `stock_id` (`stock_id`),
  KEY `status` (`status`),
  KEY `company_id` (`company_id`),
  KEY `size_master_id` (`size_master_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_colors` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `color_name` varchar(100) NOT NULL,
  `hex_code` varchar(7) NOT NULL DEFAULT '',
  `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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `master_colors` (`id`, `company_id`, `color_name`, `hex_code`, `status`) VALUES
(1, 1, 'White', '#FFFFFF', 'active'),
(2, 1, 'Black', '#000000', 'active'),
(3, 1, 'Navy', '#000080', 'active'),
(4, 1, 'Red', '#FF0000', 'active'),
(5, 1, 'Royal Blue', '#4169E1', 'active'),
(6, 1, 'Sky Blue', '#87CEEB', 'active'),
(7, 1, 'Light Pink', '#FFB6C1', 'active'),
(8, 1, 'Hot Pink', '#FF69B4', 'active'),
(9, 1, 'Maroon', '#800000', 'active'),
(10, 1, 'Burgundy', '#800020', 'active'),
(11, 1, 'Forest Green', '#228B22', 'active'),
(12, 1, 'Olive', '#808000', 'active'),
(13, 1, 'Khaki', '#C3B091', 'active'),
(14, 1, 'Beige', '#F5F5DC', 'active'),
(15, 1, 'Grey', '#808080', 'active'),
(16, 1, 'Charcoal', '#36454F', 'active'),
(17, 1, 'Yellow', '#FFD700', 'active'),
(18, 1, 'Orange', '#FFA500', 'active'),
(19, 1, 'Coral', '#FF7F50', 'active'),
(20, 1, 'Purple', '#800080', 'active'),
(21, 1, 'Lavender', '#E6E6FA', 'active'),
(22, 1, 'Turquoise', '#40E0D0', 'active'),
(23, 1, 'Teal', '#008080', 'active'),
(24, 1, 'Mint', '#98FF98', 'active'),
(25, 1, 'Cream', '#FFFDD0', 'active'),
(26, 1, 'Ivory', '#FFFFF0', 'active'),
(27, 1, 'Brown', '#8B4513', 'active'),
(28, 1, 'Tan', '#D2B48C', 'active'),
(29, 1, 'Camel', '#C19A6B', 'active'),
(30, 1, 'Bottle Green', '#006A4E', 'active');

CREATE TABLE IF NOT EXISTS `master_uom` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `uom_name` varchar(100) NOT NULL,
  `abbreviation` 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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `master_uom` (`id`, `company_id`, `uom_name`, `abbreviation`, `status`) VALUES
(1, 1, 'Piece', 'pc', 'active'),
(2, 1, 'Dozen', 'dz', 'active'),
(3, 1, 'Meter', 'm', 'active'),
(4, 1, 'Centimeter', 'cm', 'active'),
(5, 1, 'Kilogram', 'kg', 'active'),
(6, 1, 'Gram', 'g', 'active'),
(7, 1, 'Liter', 'L', 'active'),
(8, 1, 'Milliliter', 'mL', 'active'),
(9, 1, 'Yard', 'yd', 'active'),
(10, 1, 'Inch', 'in', 'active'),
(11, 1, 'Square Meter', 'sq m', 'active'),
(12, 1, 'Roll', 'rl', 'active'),
(13, 1, 'Carton', 'ctn', 'active'),
(14, 1, 'Pair', 'pr', 'active'),
(15, 1, 'Set', 'set', 'active');

CREATE TABLE IF NOT EXISTS `uom_categories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `category_name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_cat_name` (`company_id`,`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `uom_categories` (`id`, `company_id`, `category_name`, `description`, `sort_order`, `status`) VALUES
(1, 1, 'Length', 'Linear measurement (mm, cm, m, km, in, ft, yd)', 10, 'active'),
(2, 1, 'Weight', 'Mass / weight (mg, g, kg, ton, lb, oz)', 20, 'active'),
(3, 1, 'Quantity', 'Piece-based counts (pcs, set, pair, dozen, box, gross)', 30, 'active'),
(4, 1, 'Area', 'Surface area (sqm, sqft, sqyd)', 40, 'active'),
(5, 1, 'Volume', 'Capacity / volume (ml, ltr, gal)', 50, 'active'),
(6, 1, 'Time', 'Time units (min, hr, day)', 60, 'active'),
(7, 1, 'Packaging', 'Packaging forms (cone, bale, drum, bag, packet, reel)', 70, 'active'),
(8, 1, 'Roll', 'Rolled goods', 80, 'active'),
(9, 1, 'Bundle', 'Bundled goods', 90, 'active'),
(10, 1, 'Carton', 'Cartons / boxes of goods', 100, 'active'),
(11, 1, 'Pallet', 'Pallets of goods', 110, 'active'),
(12, 1, 'Container', 'Shipping containers', 120, 'active'),
(13, 1, 'Others', 'Anything else', 130, 'active');

CREATE TABLE IF NOT EXISTS `uoms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `uom_code` varchar(20) NOT NULL,
  `uom_name` varchar(100) NOT NULL,
  `category_id` int unsigned DEFAULT NULL,
  `base_unit` tinyint(1) NOT NULL DEFAULT 0,
  `description` 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_uom_code` (`company_id`,`uom_code`),
  KEY `category_id` (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `uoms` (`id`, `company_id`, `uom_code`, `uom_name`, `category_id`, `base_unit`, `description`, `status`) VALUES
(1, 1, 'MM', 'Millimeter', 1, 0, NULL, 'active'),
(2, 1, 'CM', 'Centimeter', 1, 0, NULL, 'active'),
(3, 1, 'MTR', 'Meter', 1, 1, 'Base length unit', 'active'),
(4, 1, 'KM', 'Kilometer', 1, 0, NULL, 'active'),
(5, 1, 'IN', 'Inch', 1, 0, NULL, 'active'),
(6, 1, 'FT', 'Foot', 1, 0, NULL, 'active'),
(7, 1, 'YD', 'Yard', 1, 0, NULL, 'active'),
(8, 1, 'MG', 'Milligram', 2, 0, NULL, 'active'),
(9, 1, 'G', 'Gram', 2, 0, NULL, 'active'),
(10, 1, 'KG', 'Kilogram', 2, 1, 'Base weight unit', 'active'),
(11, 1, 'TON', 'Metric Ton', 2, 0, NULL, 'active'),
(12, 1, 'LB', 'Pound', 2, 0, NULL, 'active'),
(13, 1, 'OZ', 'Ounce', 2, 0, NULL, 'active'),
(14, 1, 'PCS', 'Pieces', 3, 1, 'Base quantity unit', 'active'),
(15, 1, 'SET', 'Set', 3, 0, NULL, 'active'),
(16, 1, 'PAIR', 'Pair', 3, 0, NULL, 'active'),
(17, 1, 'DZ', 'Dozen', 3, 0, NULL, 'active'),
(18, 1, 'BOX', 'Box', 3, 0, NULL, 'active'),
(19, 1, 'GROSS', 'Gross', 3, 0, NULL, 'active'),
(20, 1, 'SQM', 'Square Meter', 4, 1, 'Base area unit', 'active'),
(21, 1, 'SQFT', 'Square Foot', 4, 0, NULL, 'active'),
(22, 1, 'SQYD', 'Square Yard', 4, 0, NULL, 'active'),
(23, 1, 'ML', 'Milliliter', 5, 0, NULL, 'active'),
(24, 1, 'LTR', 'Liter', 5, 1, 'Base volume unit', 'active'),
(25, 1, 'GAL', 'Gallon', 5, 0, NULL, 'active'),
(26, 1, 'MIN', 'Minute', 6, 0, NULL, 'active'),
(27, 1, 'HR', 'Hour', 6, 1, 'Base time unit', 'active'),
(28, 1, 'DAY', 'Day', 6, 0, NULL, 'active'),
(29, 1, 'Cone', 'Cone', 7, 0, 'Cone of yarn / thread', 'active'),
(30, 1, 'Bale', 'Bale', 7, 0, NULL, 'active'),
(31, 1, 'Drum', 'Drum', 7, 0, 'Chemical drum', 'active'),
(32, 1, 'Bag', 'Bag', 7, 0, NULL, 'active'),
(33, 1, 'Packet', 'Packet', 7, 0, NULL, 'active'),
(34, 1, 'Reel', 'Reel', 7, 0, NULL, 'active'),
(35, 1, 'Roll', 'Roll', 8, 1, 'Base roll unit', 'active'),
(36, 1, 'Bundle', 'Bundle', 9, 1, 'Base bundle unit', 'active'),
(37, 1, 'Carton', 'Carton', 10, 1, 'Base carton unit', 'active'),
(38, 1, 'Pallet', 'Pallet', 11, 1, 'Base pallet unit', 'active'),
(39, 1, 'Container', 'Container', 12, 1, 'Base container unit', 'active'),
(40, 1, 'NOS', 'Number', 13, 1, 'Base other unit', 'active');

CREATE TABLE IF NOT EXISTS `uom_conversion` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `from_uom_id` int unsigned NOT NULL,
  `to_uom_id` int unsigned NOT NULL,
  `factor` decimal(20,10) NOT NULL,
  `note` varchar(255) 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_conv` (`company_id`,`from_uom_id`,`to_uom_id`),
  KEY `from_uom_id` (`from_uom_id`),
  KEY `to_uom_id` (`to_uom_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `uom_conversion` (`id`, `company_id`, `from_uom_id`, `to_uom_id`, `factor`, `note`) VALUES
(1, 1, 3, 2, 100, '1 m = 100 cm'),
(2, 1, 3, 1, 1000, '1 m = 1000 mm'),
(3, 1, 2, 1, 10, '1 cm = 10 mm'),
(4, 1, 4, 3, 1000, '1 km = 1000 m'),
(5, 1, 7, 3, 0.9144, '1 yd = 0.9144 m'),
(6, 1, 3, 7, 1.09361329834, '1 m = 1.09361 yd'),
(7, 1, 6, 3, 0.3048, '1 ft = 0.3048 m'),
(8, 1, 5, 2, 2.54, '1 in = 2.54 cm'),
(9, 1, 5, 1, 25.4, '1 in = 25.4 mm'),
(10, 1, 10, 9, 1000, '1 kg = 1000 g'),
(11, 1, 11, 10, 1000, '1 t = 1000 kg'),
(12, 1, 9, 8, 1000, '1 g = 1000 mg'),
(13, 1, 12, 10, 0.45359237, '1 lb = 0.4536 kg'),
(14, 1, 13, 9, 28.349523125, '1 oz = 28.35 g'),
(15, 1, 24, 23, 1000, '1 L = 1000 ml'),
(16, 1, 25, 24, 3.785411784, '1 gal = 3.7854 L'),
(17, 1, 17, 14, 12, '1 dozen = 12 pcs'),
(18, 1, 19, 14, 144, '1 gross = 144 pcs'),
(19, 1, 21, 20, 0.09290304, '1 sqft = 0.0929 sqm'),
(20, 1, 22, 20, 0.83612736, '1 sqyd = 0.8361 sqm'),
(21, 1, 20, 21, 10.763910417, '1 sqm = 10.76 sqft'),
(22, 1, 20, 22, 1.195990046, '1 sqm = 1.196 sqyd');

CREATE TABLE IF NOT EXISTS `packaging_conversion` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packaging_uom_id` int unsigned NOT NULL,
  `inner_uom_id` int unsigned NOT NULL,
  `qty_per_pack` decimal(20,6) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_default` 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pack` (`company_id`,`packaging_uom_id`,`inner_uom_id`),
  KEY `packaging_uom_id` (`packaging_uom_id`),
  KEY `inner_uom_id` (`inner_uom_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `packaging_conversion` (`id`, `company_id`, `packaging_uom_id`, `inner_uom_id`, `qty_per_pack`, `description`, `is_default`, `status`) VALUES
(1, 1, 37, 14, 500, '1 carton = 500 pcs', 1, 'active'),
(2, 1, 38, 37, 40, '1 pallet = 40 cartons', 1, 'active'),
(3, 1, 30, 35, 20, '1 bale = 20 rolls', 1, 'active');

CREATE TABLE IF NOT EXISTS `material_uom` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `material_id` int unsigned NOT NULL,
  `base_uom_id` int unsigned DEFAULT NULL,
  `purchase_uom_id` int unsigned DEFAULT NULL,
  `inventory_uom_id` int unsigned DEFAULT NULL,
  `issue_uom_id` int unsigned DEFAULT NULL,
  `sales_uom_id` int 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_mat` (`company_id`,`material_id`),
  KEY `base_uom_id` (`base_uom_id`),
  KEY `purchase_uom_id` (`purchase_uom_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `material_conversion` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `material_id` int unsigned NOT NULL,
  `from_uom_id` int unsigned NOT NULL,
  `to_uom_id` int unsigned NOT NULL,
  `factor` decimal(20,10) NOT NULL,
  `note` varchar(255) 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_matconv` (`company_id`,`material_id`,`from_uom_id`,`to_uom_id`),
  KEY `material_id` (`material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `supplier_uom` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `supplier_id` int unsigned DEFAULT NULL,
  `material_id` int unsigned NOT NULL,
  `from_uom_id` int unsigned NOT NULL,
  `to_uom_id` int unsigned NOT NULL,
  `factor` decimal(20,10) NOT NULL,
  `note` varchar(255) 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_supconv` (`company_id`,`supplier_id`,`material_id`,`from_uom_id`,`to_uom_id`),
  KEY `material_id` (`material_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `uom_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `ref_type` varchar(50) DEFAULT NULL,
  `ref_id` int unsigned DEFAULT NULL,
  `action` varchar(50) NOT NULL,
  `field` varchar(50) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `note` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `uom_ai` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `analysis_type` varchar(50) NOT NULL,
  `ref_type` varchar(50) DEFAULT NULL,
  `ref_id` int unsigned DEFAULT NULL,
  `payload` mediumtext,
  `method` varchar(20) NOT NULL DEFAULT 'heuristic',
  `run_count` int NOT NULL DEFAULT 1,
  `created_by` int 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_ai` (`company_id`,`analysis_type`,`ref_type`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_buyer_po_bom` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `po_id` int unsigned NOT NULL,
  `type` varchar(50) NOT NULL DEFAULT 'other',
  `item` varchar(255) NOT NULL,
  `item_id` int unsigned DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `color_id` int unsigned DEFAULT NULL,
  `consumption` decimal(12,4) unsigned NOT NULL DEFAULT 0,
  `no_of_pcs` int unsigned NOT NULL DEFAULT 1,
  `required_qty` decimal(14,4) unsigned NOT NULL DEFAULT 0,
  `uom_id` int unsigned DEFAULT NULL,
  `per_pcs_rate` decimal(14,4) unsigned NOT NULL DEFAULT 0,
  `total_value` decimal(16,4) unsigned NOT NULL DEFAULT 0,
  `supplier_id` int unsigned DEFAULT NULL,
  `currency_id` int unsigned DEFAULT NULL,
  `display_order` int 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,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`),
  KEY `uom_id` (`uom_id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `item_id` (`item_id`),
  KEY `currency_id` (`currency_id`),
  KEY `color_id` (`color_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_items` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `type` enum('fabric','trim','accessory','chemical','packaging') NOT NULL DEFAULT 'fabric',
  `name` varchar(255) NOT NULL,
  `code` varchar(100) DEFAULT NULL,
  `description` text,
  `uom_id` int 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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `uom_id` (`uom_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `master_items` (`id`, `company_id`, `type`, `name`, `code`, `description`, `uom_id`, `status`) VALUES
(1, 1, 'fabric', 'Cotton Jersey', 'FAB-001', '100% cotton jersey knit fabric', 3, 'active'),
(2, 1, 'fabric', 'Polyester Pique', 'FAB-002', 'Polyester pique knit fabric', 3, 'active'),
(3, 1, 'fabric', 'Nylon Spandex', 'FAB-003', 'Nylon spandex blend', 3, 'active'),
(4, 1, 'trim', 'Zipper Nylon', 'TRM-001', 'Nylon coil zipper', 1, 'active'),
(5, 1, 'trim', 'Button Plastic', 'TRM-002', 'Plastic button 18L', 1, 'active'),
(6, 1, 'accessory', 'Hang Tag', 'ACC-001', 'Branded hang tag', 1, 'active'),
(7, 1, 'accessory', 'Poly Bag', 'ACC-002', 'Poly bag 12x16 inch', 1, 'active'),
(8, 1, 'chemical', 'Dye Fix', 'CHM-001', 'Dye fixing agent', 5, 'active'),
(9, 1, 'packaging', 'Export Carton', 'PKG-001', 'Standard export carton 40x30x20 cm', 1, 'active');

CREATE TABLE IF NOT EXISTS `master_item_sizes` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `item_id` int unsigned NOT NULL,
  `size_label` varchar(100) NOT NULL,
  `size_value` varchar(100) NOT NULL,
  `uom_id` int unsigned DEFAULT NULL,
  `display_order` int unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `item_id` (`item_id`),
  KEY `uom_id` (`uom_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `master_currencies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `code` varchar(3) NOT NULL,
  `name` varchar(100) NOT NULL,
  `symbol` varchar(10) DEFAULT NULL,
  `exchange_rate` decimal(14,6) unsigned NOT NULL DEFAULT 1,
  `is_base` 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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `master_currencies` (`id`, `company_id`, `code`, `name`, `symbol`, `exchange_rate`, `is_base`, `status`) VALUES
(1, 1, 'USD', 'US Dollar', '$', 1.000000, 1, 'active'),
(2, 1, 'EUR', 'Euro', '€', 0.920000, 0, 'active'),
(3, 1, 'GBP', 'British Pound', '£', 0.790000, 0, 'active'),
(4, 1, 'BDT', 'Bangladeshi Taka', '৳', 109.500000, 0, 'active'),
(5, 1, 'INR', 'Indian Rupee', '₹', 83.000000, 0, 'active'),
(6, 1, 'CNY', 'Chinese Yuan', '¥', 7.240000, 0, 'active');

-- -----------------------------------------------------------
-- Currency Rates (date-wise)
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_currency_rates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `rate_date` date NOT NULL,
  `currency_id` int unsigned NOT NULL,
  `exchange_rate` decimal(14,6) unsigned NOT NULL DEFAULT 1,
  `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 `date_currency` (`rate_date`,`currency_id`),
  KEY `currency_id` (`currency_id`),
  KEY `rate_date` (`rate_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Company Settings
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `company_settings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text,
  `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 `company_setting` (`company_id`,`setting_key`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `company_settings` (`company_id`, `setting_key`, `setting_value`) VALUES
(1, 'allow_backdate', '1');

-- -----------------------------------------------------------
-- Table: master_buyer_po_costing — Production costing per PO
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_buyer_po_costing` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `po_id` int unsigned NOT NULL,
  `type` varchar(50) NOT NULL DEFAULT 'other',
  `pcs` decimal(12,4) unsigned NOT NULL DEFAULT 0,
  `extra_pct` decimal(5,2) unsigned NOT NULL DEFAULT 0,
  `total_pcs` decimal(14,4) unsigned NOT NULL DEFAULT 0,
  `rate` decimal(14,4) unsigned NOT NULL DEFAULT 0,
  `currency_id` int unsigned DEFAULT NULL,
  `exchange_rate` decimal(14,4) unsigned NOT NULL DEFAULT 0,
  `total_value` decimal(16,4) unsigned NOT NULL DEFAULT 0,
  `factory_id` int unsigned DEFAULT NULL,
  `display_order` int 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,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`),
  KEY `currency_id` (`currency_id`),
  KEY `factory_id` (`factory_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Table: master_production_types — Production process types
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_production_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `sort_order` int unsigned 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Table: master_factories — Factory master data
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_factories` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `name` varchar(255) NOT NULL,
  `code` varchar(50) DEFAULT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` text,
  `status` enum('active','inactive','blacklisted') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Table: master_buyer_po_shipments — Shipment schedule
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_buyer_po_shipments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `po_id` int unsigned NOT NULL,
  `po_no` varchar(100) DEFAULT NULL,
  `style` varchar(100) DEFAULT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(50) DEFAULT NULL,
  `pcs` int unsigned NOT NULL DEFAULT 0,
  `rate` decimal(10,2) NOT NULL DEFAULT 0,
  `currency_id` int unsigned DEFAULT NULL,
  `exchange_rate` decimal(10,4) NOT NULL DEFAULT 0,
  `total_value` decimal(12,2) NOT NULL DEFAULT 0,
  `destination` varchar(255) DEFAULT NULL,
  `mode_of_shipment` varchar(50) DEFAULT NULL,
  `etd` date DEFAULT NULL,
  `eta` date DEFAULT NULL,
  `display_order` int NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`),
  KEY `currency_id` (`currency_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Table: master_cities — City master data
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_cities` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `city` varchar(255) NOT NULL,
  `country` varchar(255) NOT NULL,
  `continent` 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,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Patch: Add delivery_date and payment_term_id columns to master_buyer_pos
-- Run manually: ALTER TABLE master_buyer_pos ADD COLUMN delivery_date date DEFAULT NULL AFTER order_date;
-- Run manually: ALTER TABLE master_buyer_pos ADD COLUMN payment_term_id int unsigned DEFAULT NULL AFTER currency_id;
-- Run manually: ALTER TABLE master_buyer_pos ADD KEY payment_term_id (payment_term_id);
-- -----------------------------------------------------------

-- -----------------------------------------------------------
-- Table: master_payment_terms — Payment term options
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_payment_terms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `payment_type` varchar(50) DEFAULT NULL,
  `due_days` int DEFAULT NULL,
  `payment_method_id` int unsigned DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `exchange_rate_type` varchar(50) DEFAULT NULL,
  `base_currency` varchar(10) DEFAULT NULL,
  `is_template` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int 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 `company_id` (`company_id`),
  KEY `idx_mpt_type` (`payment_type`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Global Payment Terms Engine — supporting master tables
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_term_types` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_methods` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `is_bank` tinyint(1) NOT NULL DEFAULT 0,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_milestones` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `default_percent` decimal(5,2) DEFAULT NULL,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_installments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `payment_term_id` int unsigned NOT NULL,
  `sequence_no` int NOT NULL DEFAULT 1,
  `milestone` varchar(100) DEFAULT NULL,
  `percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `due_days` int DEFAULT 0,
  `description` varchar(255) 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 `payment_term_id` (`payment_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `buyer_payment_terms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `buyer_id` int unsigned NOT NULL,
  `payment_term_id` int unsigned NOT NULL,
  `credit_limit` decimal(16,2) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `notes` varchar(255) 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 `buyer_id` (`buyer_id`),
  KEY `payment_term_id` (`payment_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `supplier_payment_terms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `supplier_id` int unsigned NOT NULL,
  `payment_term_id` int unsigned NOT NULL,
  `credit_limit` decimal(16,2) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT 0,
  `notes` varchar(255) 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 `supplier_id` (`supplier_id`),
  KEY `payment_term_id` (`payment_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_discounts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `payment_term_id` int unsigned NOT NULL,
  `discount_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `days` int NOT NULL DEFAULT 0,
  `net_days` int NOT NULL DEFAULT 30,
  `description` varchar(255) 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 `payment_term_id` (`payment_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_penalties` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `payment_term_id` int unsigned NOT NULL,
  `interest_rate` decimal(8,4) NOT NULL DEFAULT 0.0000,
  `penalty_percent` decimal(5,2) NOT NULL DEFAULT 0.00,
  `grace_days` int NOT NULL DEFAULT 0,
  `max_penalty` decimal(8,2) DEFAULT NULL,
  `description` varchar(255) 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 `payment_term_id` (`payment_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `payment_term_id` int unsigned NOT NULL,
  `action` varchar(100) NOT NULL,
  `field` varchar(100) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `note` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `payment_term_id` (`payment_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `payment_ai` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `analysis_type` varchar(50) NOT NULL DEFAULT 'recommendation',
  `ref_type` varchar(50) DEFAULT NULL,
  `ref_id` int unsigned DEFAULT NULL,
  `payload` text,
  `method` varchar(100) DEFAULT NULL,
  `run_count` int NOT NULL DEFAULT 1,
  `created_by` int 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`),
  KEY `company_id` (`company_id`),
  KEY `analysis_type` (`analysis_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Table: master_packing_terms — Packing instruction options
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `master_packing_terms` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `name` varchar(100) NOT NULL,
  `code` varchar(20) DEFAULT NULL,
  `instructions` text,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `packing_method_id` int unsigned DEFAULT NULL,
  `unit` varchar(20) DEFAULT NULL,
  `carton_capacity` int DEFAULT NULL,
  `inner_carton_qty` int DEFAULT NULL,
  `folding_method_id` int unsigned DEFAULT NULL,
  `ratio_required` tinyint(1) NOT NULL DEFAULT 0,
  `assortment_required` tinyint(1) NOT NULL DEFAULT 0,
  `polybag_required` tinyint(1) NOT NULL DEFAULT 0,
  `hanger_required` tinyint(1) NOT NULL DEFAULT 0,
  `qr_required` tinyint(1) NOT NULL DEFAULT 0,
  `rfid_required` tinyint(1) NOT NULL DEFAULT 0,
  `barcode_type` varchar(50) DEFAULT NULL,
  `label_template_id` int unsigned DEFAULT NULL,
  `sort_order` int 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 `company_id` (`company_id`),
  KEY `idx_pkt_method` (`packing_method_id`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------------
-- Global Packing Terms Engine — supporting master tables
-- -----------------------------------------------------------
CREATE TABLE IF NOT EXISTS `packing_methods` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_folding_methods` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `instruction` text,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_units` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(20) NOT NULL,
  `name` varchar(50) NOT NULL,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_polybag_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `polybag_required` tinyint(1) NOT NULL DEFAULT 0,
  `polybag_type` varchar(100) DEFAULT NULL,
  `size` varchar(100) DEFAULT NULL,
  `thickness` int DEFAULT NULL,
  `biodegradable` tinyint(1) NOT NULL DEFAULT 0,
  `recycled_pct` int DEFAULT NULL,
  `warning_required` tinyint(1) NOT NULL DEFAULT 0,
  `ventilation_hole` tinyint(1) NOT NULL DEFAULT 0,
  `adhesive_flap` tinyint(1) NOT NULL DEFAULT 0,
  `self_adhesive` tinyint(1) NOT NULL DEFAULT 0,
  `barcode_placement` varchar(100) DEFAULT NULL,
  `notes` varchar(255) 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 `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_hanger_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `hanger_required` tinyint(1) NOT NULL DEFAULT 0,
  `hanger_type` varchar(100) DEFAULT NULL,
  `hanger_color` varchar(50) DEFAULT NULL,
  `hanger_size` varchar(50) DEFAULT NULL,
  `attachment` varchar(50) DEFAULT NULL,
  `notes` varchar(255) 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 `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_inner_carton_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `inner_required` tinyint(1) NOT NULL DEFAULT 0,
  `inner_type` varchar(100) DEFAULT NULL,
  `qty_per_inner` int DEFAULT NULL,
  `dimensions` varchar(100) DEFAULT NULL,
  `weight_limit` decimal(8,2) DEFAULT NULL,
  `barcode` tinyint(1) NOT NULL DEFAULT 0,
  `label` tinyint(1) NOT NULL DEFAULT 0,
  `notes` varchar(255) 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 `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_master_carton_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `carton_type` varchar(100) DEFAULT NULL,
  `length` decimal(8,2) DEFAULT NULL,
  `width` decimal(8,2) DEFAULT NULL,
  `height` decimal(8,2) DEFAULT NULL,
  `max_pcs` int DEFAULT NULL,
  `max_weight` decimal(10,2) DEFAULT NULL,
  `gross_weight` decimal(10,2) DEFAULT NULL,
  `net_weight` decimal(10,2) DEFAULT NULL,
  `cbm` decimal(12,6) DEFAULT NULL,
  `material` varchar(100) DEFAULT NULL,
  `bursting_strength` decimal(8,2) DEFAULT NULL,
  `notes` varchar(255) 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 `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_ratio_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `size` varchar(50) NOT NULL,
  `ratio` int NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_assortment_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `color` varchar(100) DEFAULT NULL,
  `size` varchar(50) DEFAULT NULL,
  `qty` int NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_barcode_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `barcode_type` varchar(50) DEFAULT NULL,
  `qr_required` tinyint(1) NOT NULL DEFAULT 0,
  `rfid_required` tinyint(1) NOT NULL DEFAULT 0,
  `placement` varchar(100) DEFAULT NULL,
  `data_fields` varchar(255) DEFAULT NULL,
  `notes` varchar(255) 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 `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_label_templates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(50) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `sort_order` int 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,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_carton_marking_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `show_buyer` tinyint(1) NOT NULL DEFAULT 1,
  `show_po_number` tinyint(1) NOT NULL DEFAULT 1,
  `show_style` tinyint(1) NOT NULL DEFAULT 1,
  `show_color` tinyint(1) NOT NULL DEFAULT 1,
  `show_size` tinyint(1) NOT NULL DEFAULT 1,
  `show_carton_number` tinyint(1) NOT NULL DEFAULT 1,
  `show_quantity` tinyint(1) NOT NULL DEFAULT 1,
  `show_country_of_origin` tinyint(1) NOT NULL DEFAULT 1,
  `show_gross_weight` tinyint(1) NOT NULL DEFAULT 1,
  `show_net_weight` tinyint(1) NOT NULL DEFAULT 1,
  `show_cbm` tinyint(1) NOT NULL DEFAULT 1,
  `made_in` varchar(100) DEFAULT NULL,
  `numbering_format` varchar(100) DEFAULT NULL,
  `custom_fields` varchar(255) 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 `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `buyer_packing_profiles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `buyer_id` int unsigned NOT NULL,
  `packing_term_id` int unsigned NOT NULL,
  `department` varchar(100) DEFAULT NULL,
  `season` varchar(50) DEFAULT NULL,
  `carton_capacity` int DEFAULT NULL,
  `barcode_type` varchar(50) DEFAULT NULL,
  `label_template_id` int unsigned DEFAULT NULL,
  `notes` varchar(255) 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 `buyer_id` (`buyer_id`),
  KEY `packing_term_id` (`packing_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `style_packing_profiles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `style_id` int unsigned NOT NULL,
  `packing_term_id` int unsigned NOT NULL,
  `carton_capacity` int DEFAULT NULL,
  `carton_qty_per_color` int DEFAULT NULL,
  `notes` varchar(255) 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 `style_id` (`style_id`),
  KEY `packing_term_id` (`packing_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `po_packing_instructions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `po_id` int unsigned NOT NULL,
  `packing_term_id` int unsigned DEFAULT NULL,
  `carton_capacity` int DEFAULT NULL,
  `special_instructions` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `po_id` (`po_id`),
  KEY `packing_term_id` (`packing_term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_history` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `packing_term_id` int unsigned NOT NULL,
  `action` varchar(100) NOT NULL,
  `field` varchar(100) DEFAULT NULL,
  `old_value` text,
  `new_value` text,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `note` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `packing_term_id` (`packing_term_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `packing_ai` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `analysis_type` varchar(50) NOT NULL DEFAULT 'recommendation',
  `ref_type` varchar(50) DEFAULT NULL,
  `ref_id` int unsigned DEFAULT NULL,
  `payload` text,
  `method` varchar(100) DEFAULT NULL,
  `run_count` int NOT NULL DEFAULT 1,
  `created_by` int 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`),
  KEY `company_id` (`company_id`),
  KEY `analysis_type` (`analysis_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



-- ============================================================
-- FILE: seed_master_data.sql
-- ============================================================


INSERT IGNORE INTO `master_departments` (`id`, `company_id`, `name`, `code`, `description`, `status`) VALUES
(1, 1, 'Fabric Store', 'FAB', 'Fabric receiving, inspection and storage', 'active'),
(2, 1, 'Sewing', 'SEW', 'Garment sewing and assembly operations', 'active'),
(3, 1, 'Finishing', 'FIN', 'Final finishing, trimming and packing preparation', 'active'),
(4, 1, 'Production', 'PROD', 'Production planning and control', 'active'),
(5, 1, 'QA', 'QA', 'Quality assurance and inspection', 'active'),
(6, 1, 'Printing', 'PRN', 'Screen printing and heat transfer', 'active'),
(7, 1, 'Washing', 'WSH', 'Garment washing and wet processing', 'active'),
(8, 1, 'Packing', 'PKG', 'Final packing and carton sealing', 'active'),
(9, 1, 'Cutting', 'CUT', 'Fabric spreading and cutting', 'active'),
(10, 1, 'Embroidery', 'EMB', 'Embroidery and embellishment', 'active'),
(11, 1, 'Trims', 'TRM', 'Trims and accessories store', 'active'),
(12, 1, 'Maintenance', 'MNT', 'Machine maintenance and repair', 'active'),
(13, 1, 'Purchase', 'PUR', 'Procurement and purchasing', 'active'),
(14, 1, 'Sales', 'SAL', 'Sales and marketing', 'active'),
(15, 1, 'IT', 'IT', 'Information technology', 'active'),
(16, 1, 'HR', 'HR', 'Human resources and training', 'active'),
(17, 1, 'Safety', 'SFT', 'Health, safety and environment', 'active'),
(18, 1, 'Industrial Eng', 'IE', 'Industrial engineering and method study', 'active'),
(19, 1, 'Engineering', 'ENG', 'Facility and civil engineering', 'active');

INSERT IGNORE INTO `master_vendors` (`id`, `company_id`, `name`, `code`, `type`, `contact_person`, `email`, `phone`, `status`) VALUES
(1, 1, 'XYZ Trims Ltd', 'XYZ-001', 'trim', 'Mr. Rahman', 'rahman@xyztrims.com', '+880-2-1234567', 'active'),
(2, 1, 'ABC Trims Bangladesh', 'ABC-001', 'trim', 'Mr. Hossain', 'hossain@abctrims.com', '+880-2-7654321', 'active'),
(3, 1, 'Global Fabric Mills', 'GFM-001', 'fabric', 'Ms. Sultana', 'sultana@globalfabric.com', '+880-2-5551234', 'active'),
(4, 1, 'Premier Dyeing Ltd', 'PDL-001', 'fabric', 'Mr. Karim', 'karim@premierdyeing.com', '+880-2-5555678', 'active'),
(5, 1, 'ChemiColor Ltd', 'CCL-001', 'chemical', 'Mr. Islam', 'islam@chemicolor.com', '+880-2-5559012', 'active'),
(6, 1, 'PackRight Solutions', 'PRS-001', 'packaging', 'Ms. Akter', 'akter@packright.com', '+880-2-5553456', 'active'),
(7, 1, 'FastCargo Logistics', 'FCL-001', 'logistics', 'Mr. Chowdhury', 'chowdhury@fastcargo.com', '+880-2-5557890', 'active');

INSERT IGNORE INTO `master_buyers` (`id`, `company_id`, `name`, `code`, `contact_person`, `email`, `country`, `status`) VALUES
(1, 1, 'H&M Hennes & Mauritz', 'H&M-001', 'Mr. Andersson', 'andersson@hm.com', 'Sweden', 'active'),
(2, 1, 'Zara (Inditex)', 'ZAR-001', 'Ms. Garcia', 'garcia@zara.com', 'Spain', 'active'),
(3, 1, 'Nike Inc.', 'NIK-001', 'Mr. Johnson', 'johnson@nike.com', 'USA', 'active'),
(4, 1, 'Adidas AG', 'ADI-001', 'Ms. Schmidt', 'schmidt@adidas.com', 'Germany', 'active'),
(5, 1, 'Uniqlo (Fast Retailing)', 'UNI-001', 'Mr. Tanaka', 'tanaka@uniqlo.com', 'Japan', 'active'),
(6, 1, 'Target Corporation', 'TAR-001', 'Ms. Brown', 'brown@target.com', 'USA', 'active');

INSERT IGNORE INTO `master_styles` (
  `id`, `company_id`, `buyer_id`, `style_no`, `buyer_style_no`, `name`, `description`,
  `brand`, `division`, `department`, `product_type`, `gender`, `category`, `fabric_type`, `size_range`,
  `season`, `season_id`, `collection`, `year`,
  `factory_id`, `merchandiser`, `product_developer`,
  `dev_status`, `version`, `status`,
  `fob_target`, `margin_target`, `currency_code`,
  `fit`, `sleeve`, `construction`, `wash`, `print_type`, `embroidery`
) VALUES
-- 1. H&M Basic Cotton T-Shirt
(1, 1, 1, 'HM-101', 'HM-BSS-4420', 'Basic Cotton T-Shirt',
 'Classic crew-neck short sleeve tee in 100% combed cotton jersey. Relaxed fit with reinforced shoulder seams.',
 'H&M Basics', 'H&M Divided', 'Casualwear', 'T-Shirt', 'Unisex', 'Knit', 'Cotton Jersey 180gsm', 'S/M/L/XL/XXL',
 'Summer', 1, 'Basics Collection', 2025,
 3, 'Rahim Uddin', 'Sara Ahmed',
 'approved', 'V3', 'active',
 4.50, 42.00, 'USD',
 'Relaxed Fit', 'Short Sleeve', 'Single needle stitch, reinforced shoulders', 'None', 'None', 'None'),
-- 2. H&M Slim Fit Chino Pants
(2, 1, 1, 'HM-102', 'HM-CHN-8812', 'Slim Fit Chino Pants',
 'Tailored slim-fit chinos in cotton-elastane twill. Flat front, zip fly, back welt pockets.',
 'H&M-divided', 'H&M Man', 'Casualwear', 'Pants', 'Mens', 'Woven', 'Cotton Twill 230gsm', '28/30/32/34/36',
 'Fall/Winter', 2, 'Urban Essentials', 2025,
 1, 'Karim Hassan', 'Sara Ahmed',
 'approved', 'V2', 'active',
 6.80, 38.00, 'USD',
 'Slim Fit', 'Full Length', 'Flat fell seam, reinforced pockets', 'Garment Dyed', 'None', 'None'),
-- 3. Zara Wool Blend Blazer
(3, 1, 2, 'ZR-201', 'ZR-BLZ-2205', 'Wool Blend Blazer',
 'Structured single-breasted blazer in wool-poly blend. Notch lapel, two-button closure, fully lined.',
 'Zara Studio', 'Zara Man', 'Fashionwear', 'Blazer', 'Mens', 'Woven', 'Wool/Poly Blend 280gsm', '38/40/42/44/46',
 'Fall/Winter', 2, 'Studio Collection', 2025,
 1, 'Maria Garcia', 'Luca Bianchi',
 'approved', 'V2', 'active',
 28.00, 55.00, 'USD',
 'Slim Fit', 'Long Sleeve', 'Princess seam, notch collar, full canvas', 'Dry Clean Only', 'None', 'None'),
-- 4. Zara Cashmere Crew Neck Sweater
(4, 1, 2, 'ZR-202', 'ZR-SWR-3310', 'Cashmere Crew Neck Sweater',
 'Luxurious 100% cashmere crew neck sweater in 12-gauge knit. Ribbed cuffs, hem, and collar.',
 'Zara Premium', 'Zara Woman', 'Fashionwear', 'Sweater', 'Womens', 'Sweater', '100% Cashmere 12gg', 'XS/S/M/L/XL',
 'Fall/Winter', 2, 'Premium Essentials', 2025,
 1, 'Maria Garcia', 'Luca Bianchi',
 'sampling', 'V1', 'active',
 38.00, 62.00, 'USD',
 'Regular Fit', 'Long Sleeve', 'Fully fashioned, linking stitch', 'Hand Wash', 'None', 'None'),
-- 5. Nike Dri-FIT Running Shorts
(5, 1, 3, 'NK-301', 'NK-RSH-5501', 'Dri-FIT Running Shorts',
 'Lightweight moisture-wicking running shorts with inner brief. Side pockets and reflective logo.',
 'Nike', 'Nike Running', 'Activewear', 'Shorts', 'Mens', 'Activewear', 'Dri-FIT Polyester 130gsm', 'S/M/L/XL',
 'Spring/Summer', 3, 'Running Series', 2025,
 3, 'Ahmed Khan', 'David Park',
 'approved', 'V4', 'active',
 5.20, 48.00, 'USD',
 'Athletic', 'Short', 'Flatlock seam, split side', 'None', 'Reflective Heat Transfer', 'None'),
-- 6. Nike Air Max Lifestyle Hoodie
(6, 1, 3, 'NK-302', 'NK-HDY-7708', 'Air Max Lifestyle Hoodie',
 'Premium fleece hoodie with kangaroo pocket and embossed Air Max branding. Brushed interior.',
 'Nike Sportswear', 'Nike Lifestyle', 'Knit', 'Hoodie', 'Unisex', 'Knit', 'Cotton/Poly Fleece 320gsm', 'S/M/L/XL/XXL',
 'Fall/Winter', 2, 'Sportswear Essential', 2025,
 3, 'Ahmed Khan', 'David Park',
 'approved', 'V2', 'active',
 8.50, 52.00, 'USD',
 'Regular Fit', 'Long Sleeve', 'Set-in sleeve, rib cuffs and hem', 'None', 'Embossed Logo Print', 'None'),
-- 7. Adidas Ultraboost Running Tights
(7, 1, 4, 'AD-401', 'AD-RTG-1103', 'Ultraboost Running Tights',
 'High-performance compression running tights with Primeknit waistband. Moisture management technology.',
 'Adidas', 'Adidas Running', 'Activewear', 'Tights', 'Unisex', 'Activewear', 'Spandex/Polyester 200gsm', 'XS/S/M/L/XL',
 'Spring/Summer', 3, 'Ultraboost Line', 2025,
 1, 'Thomas Schmidt', 'Yuki Tanaka',
 'approved', 'V3', 'active',
 7.80, 45.00, 'USD',
 'Compression', 'Full Length', 'Flatlock seam, bonded hem', 'None', 'Reflective Logo', 'None'),
-- 8. Uniqlo HeatTech Base Layer
(8, 1, 5, 'UQ-501', 'UQ-HTL-6605', 'HeatTech Base Layer',
 'Ultra-thin heat-retention base layer with moisture wicking. Seamless construction for layering comfort.',
 'Uniqlo', 'Uniqlo LifeWear', 'Knit', 'Long Sleeve Tee', 'Unisex', 'Knit', 'HeatTech Synthetic 140gsm', 'XS/S/M/L/XL/XXL',
 'Fall/Winter', 2, 'HeatTech Core', 2025,
 3, 'Akira Tanaka', 'Kenji Yamamoto',
 'production', 'V5', 'active',
 3.50, 50.00, 'USD',
 'Slim Fit', 'Long Sleeve', 'Seamless knit, flatlock', 'None', 'Heat Seal Label', 'None'),
-- 9. Target Denim Jacket
(9, 1, 6, 'TGT-601', 'TGT-DJN-9902', 'Denim Jacket',
 'Classic trucker-style denim jacket with brass button closure. Vintage wash with whisker detail.',
 'Target Original', 'Target Private Label', 'Denim', 'Jacket', 'Unisex', 'Denim', '12oz Raw Denim', 'XS/S/M/L/XL',
 'Fall/Winter', 2, 'Heritage Denim', 2025,
 5, 'Sarah Brown', 'Mike Johnson',
 'approved', 'V2', 'active',
 12.00, 40.00, 'USD',
 'Regular Fit', 'Long Sleeve', 'Triple needle stitch, riveted pockets', 'Stone Wash', 'None', 'Leather Patch');

INSERT IGNORE INTO `master_buyer_pos` (`id`, `company_id`, `style_id`, `po_no`, `buyer_id`, `order_date`, `delivery_date`, `size`, `department`, `description`, `po_type`, `total_qty`, `payment_term_id`, `status`) VALUES
(1, 1, 1, 'PO-HM-2024-001', 1, '2024-06-01', '2024-08-15', 'S/M/L/XL', 'Casualwear', 'Summer collection basic tees', 'Regular', 12000, 1, 'active'),
(2, 1, 2, 'PO-HM-2024-002', 1, '2024-06-10', '2024-09-01', '30/32/34', 'Casualwear', 'Slim fit chinos fall collection', 'Regular', 8000, 1, 'active'),
(3, 1, 3, 'PO-ZR-2024-001', 2, '2024-06-15', '2024-10-15', '38/40/42/44', 'Fashionwear', 'Fall/winter blazer collection', 'Contract', 5000, 3, 'active'),
(4, 1, 5, 'PO-NK-2024-001', 3, '2024-07-01', '2024-09-30', 'S/M/L', 'Activewear', 'Spring activewear line', 'Regular', 20000, 5, 'active'),
(5, 1, 7, 'PO-AD-2024-001', 4, '2024-07-05', '2024-10-01', 'XS/S/M', 'Activewear', 'Performance running tights', 'Spot', 15000, 6, 'active');

INSERT IGNORE INTO `master_buyer_departments` (`id`, `department_code`, `department_name`, `department_group`, `description`, `display_order`, `status`) VALUES
(1, 'ACT', 'Activewear', 'Sports', 'Gym wear, sportswear, performance apparel', 1, 'active'),
(2, 'ATH', 'Athleisure', 'Sports', 'Everyday sports-inspired clothing', 2, 'active'),
(3, 'CAS', 'Casualwear', 'Casual', 'T-shirts, polos, casual shirts, jeans', 3, 'active'),
(4, 'FAS', 'Fashionwear', 'Fashion', 'Trend-based apparel', 4, 'active'),
(5, 'DEN', 'Denim', 'Bottomwear', 'Jeans, jackets, skirts, shorts', 5, 'active'),
(6, 'KNT', 'Knitwear', 'Fabric Type', 'T-shirts, polos, sweatshirts, hoodies', 6, 'active'),
(7, 'WOV', 'Wovenwear', 'Fabric Type', 'Shirts, trousers, dresses', 7, 'active'),
(8, 'OUT', 'Outerwear', 'Outerwear', 'Jackets, coats, windbreakers', 8, 'active'),
(9, 'INN', 'Innerwear', 'Intimate', 'Briefs, boxers, bras, panties', 9, 'active'),
(10, 'LIN', 'Lingerie', 'Intimate', 'Women\'s intimate apparel', 10, 'active'),
(11, 'SLP', 'Sleepwear', 'Home', 'Nightwear, pajamas', 11, 'active'),
(12, 'LOU', 'Loungewear', 'Home', 'Home comfort clothing', 12, 'active'),
(13, 'SWM', 'Swimwear', 'Swim', 'Swim shorts, swimsuits', 13, 'active'),
(14, 'BCH', 'Beachwear', 'Swim', 'Resort and beach apparel', 14, 'active'),
(15, 'RST', 'Resortwear', 'Vacation', 'Vacation clothing', 15, 'active'),
(16, 'FOR', 'Formalwear', 'Formal', 'Office and business clothing', 16, 'active'),
(17, 'BSC', 'Business Casual', 'Formal', 'Smart casual office wear', 17, 'active'),
(18, 'WRK', 'Workwear', 'Uniform', 'Industrial uniforms, safety clothing', 18, 'active'),
(19, 'UNI', 'Uniforms', 'Uniform', 'School, hospital, corporate uniforms', 19, 'active'),
(20, 'MED', 'Medical Apparel', 'Uniform', 'Scrubs, lab coats', 20, 'active'),
(21, 'HOS', 'Hospitality Uniforms', 'Uniform', 'Hotel and restaurant uniforms', 21, 'active'),
(22, 'SCH', 'School Uniforms', 'Uniform', 'School clothing', 22, 'active'),
(23, 'BBY', 'Babywear', 'Kids', 'Infant garments', 23, 'active'),
(24, 'KID', 'Kidswear', 'Kids', 'Children\'s clothing', 24, 'active'),
(25, 'BOY', 'Boyswear', 'Kids', 'Boys\' apparel', 25, 'active'),
(26, 'GRL', 'Girlswear', 'Kids', 'Girls\' apparel', 26, 'active'),
(27, 'MEN', 'Menswear', 'Gender', 'Men\'s garments', 27, 'active'),
(28, 'WOM', 'Womenswear', 'Gender', 'Women\'s garments', 28, 'active'),
(29, 'MAT', 'Maternity Wear', 'Maternity', 'Pregnancy clothing', 29, 'active'),
(30, 'PLS', 'Plus Size', 'Size', 'Extended size range', 30, 'active'),
(31, 'ADA', 'Adaptive Clothing', 'Accessibility', 'Clothing for people with disabilities', 31, 'active'),
(32, 'THR', 'Thermal Wear', 'Weather', 'Winter base layers', 32, 'active'),
(33, 'WIN', 'Winterwear', 'Weather', 'Cold weather garments', 33, 'active'),
(34, 'RAN', 'Rainwear', 'Weather', 'Waterproof garments', 34, 'active'),
(35, 'SKI', 'Skiwear', 'Sports', 'Snow sports apparel', 35, 'active'),
(36, 'CYC', 'Cycling Apparel', 'Sports', 'Cycling jerseys and shorts', 36, 'active'),
(37, 'RUN', 'Running Apparel', 'Sports', 'Running clothing', 37, 'active'),
(38, 'YOG', 'Yoga Wear', 'Sports', 'Yoga apparel', 38, 'active'),
(39, 'CMP', 'Compression Wear', 'Sports', 'Compression garments', 39, 'active'),
(40, 'PPE', 'Protective Clothing', 'Safety', 'PPE, industrial safety apparel', 40, 'active'),
(41, 'SUS', 'Sustainable Collection', 'Eco', 'Eco-friendly garments', 41, 'active'),
(42, 'ORG', 'Organic Collection', 'Eco', 'Organic cotton garments', 42, 'active'),
(43, 'REC', 'Recycled Collection', 'Eco', 'Recycled fabric garments', 43, 'active'),
(44, 'LUX', 'Luxury Collection', 'Premium', 'Premium apparel', 44, 'active'),
(45, 'BAS', 'Basics', 'Casual', 'Everyday essential garments', 45, 'active'),
(46, 'STR', 'Streetwear', 'Fashion', 'Urban fashion', 46, 'active'),
(47, 'PRM', 'Premium Fashion', 'Premium', 'High-end fashion products', 47, 'active');

INSERT IGNORE INTO `master_buyer_dept_links` (`buyer_id`, `buyer_department_id`) VALUES
-- H&M: trend-driven fast fashion, broad categories
(1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 11), (1, 12), (1, 16), (1, 17), (1, 24), (1, 25), (1, 26), (1, 27), (1, 28), (1, 29), (1, 30), (1, 41), (1, 42), (1, 45),
-- Zara: fast fashion, runway-inspired
(2, 3), (2, 4), (2, 5), (2, 6), (2, 7), (2, 8), (2, 11), (2, 12), (2, 16), (2, 17), (2, 27), (2, 28), (2, 30), (2, 46),
-- Nike: sports and active lifestyle
(3, 1), (3, 2), (3, 8), (3, 13), (3, 27), (3, 28), (3, 30), (3, 35), (3, 36), (3, 37), (3, 38), (3, 39), (3, 41),
-- Adidas: sportswear and athleisure
(4, 1), (4, 2), (4, 8), (4, 13), (4, 27), (4, 28), (4, 30), (4, 35), (4, 36), (4, 37), (4, 38), (4, 39), (4, 41), (4, 43),
-- Uniqlo: basics and casual essentials
(5, 3), (5, 6), (5, 7), (5, 8), (5, 9), (5, 11), (5, 12), (5, 23), (5, 24), (5, 27), (5, 28), (5, 32), (5, 33), (5, 41), (5, 42), (5, 45),
-- Target: mass-market retail, family-oriented
(6, 3), (6, 5), (6, 6), (6, 7), (6, 8), (6, 9), (6, 11), (6, 12), (6, 16), (6, 23), (6, 24), (6, 25), (6, 26), (6, 27), (6, 28), (6, 29), (6, 30), (6, 45);

INSERT IGNORE INTO `master_factories` (`id`, `company_id`, `name`, `code`, `contact_person`, `email`, `phone`, `address`, `status`) VALUES
(1, 1, 'Dhaka Garments Ltd', 'DGL-001', 'Mr. Hasan', 'hasan@dhakagarments.com', '+880-2-1111111', 'Gazipur, Dhaka, Bangladesh', 'active'),
(2, 1, 'Chattogram Export Processing Zone', 'CEPZ-001', 'Ms. Nasrin', 'nasrin@cepz.com', '+880-2-2222222', 'CEPZ, Chattogram, Bangladesh', 'active'),
(3, 1, 'Knit Concern Ltd', 'KCL-001', 'Mr. Rahman', 'rahman@knitconcern.com', '+880-2-3333333', 'Savar, Dhaka, Bangladesh', 'active'),
(4, 1, 'Wash & Dye Solutions', 'WDS-001', 'Mr. Kabir', 'kabir@washdye.com', '+880-2-4444444', 'Narayanganj, Bangladesh', 'active'),
(5, 1, 'PackRight Garments', 'PRG-001', 'Ms. Akhter', 'akhter@packright.com', '+880-2-5555555', 'Tongi, Gazipur, Bangladesh', 'active');

INSERT IGNORE INTO `master_production_types` (`id`, `name`, `code`, `description`, `sort_order`, `status`) VALUES
(1, 'Cutting', 'CUTTING', 'Fabric cutting and spreading', 1, 'active'),
(2, 'CMT', 'CMT', 'Cut-Make-Trim full package', 2, 'active'),
(3, 'CMPT', 'CMPT', 'Cut-Make-Pack-Trim full package', 3, 'active'),
(4, 'Sewing', 'SEWING', 'Garment sewing and assembly', 4, 'active'),
(5, 'Packing', 'PACKING', 'Final packing and cartoning', 5, 'active'),
(6, 'Washing', 'WASHING', 'Garment washing and finishing', 6, 'active'),
(7, 'Dyeing', 'DYEING', 'Fabric or garment dyeing', 7, 'active');

INSERT IGNORE INTO `master_cities` (`id`, `company_id`, `city`, `country`, `continent`, `status`) VALUES
(1, 1, 'Dhaka', 'Bangladesh', 'Asia', 'active'),
(2, 1, 'Chattogram', 'Bangladesh', 'Asia', 'active'),
(3, 1, 'Shanghai', 'China', 'Asia', 'active'),
(4, 1, 'Guangzhou', 'China', 'Asia', 'active'),
(5, 1, 'Shenzhen', 'China', 'Asia', 'active'),
(6, 1, 'Ho Chi Minh City', 'Vietnam', 'Asia', 'active'),
(7, 1, 'Hanoi', 'Vietnam', 'Asia', 'active'),
(8, 1, 'Jakarta', 'Indonesia', 'Asia', 'active'),
(9, 1, 'Mumbai', 'India', 'Asia', 'active'),
(10, 1, 'Bangalore', 'India', 'Asia', 'active'),
(11, 1, 'Colombo', 'Sri Lanka', 'Asia', 'active'),
(12, 1, 'Karachi', 'Pakistan', 'Asia', 'active'),
(13, 1, 'Istanbul', 'Turkey', 'Asia', 'active'),
(14, 1, 'Izmire', 'Turkey', 'Asia', 'active'),
(15, 1, 'Phnom Penh', 'Cambodia', 'Asia', 'active'),
(16, 1, 'Manila', 'Philippines', 'Asia', 'active'),
(17, 1, 'Bangkok', 'Thailand', 'Asia', 'active'),
(18, 1, 'Tokyo', 'Japan', 'Asia', 'active'),
(19, 1, 'Seoul', 'South Korea', 'Asia', 'active'),
(20, 1, 'Los Angeles', 'United States', 'North America', 'active'),
(21, 1, 'New York', 'United States', 'North America', 'active'),
(22, 1, 'Mexico City', 'Mexico', 'North America', 'active'),
(23, 1, 'San Pedro Sula', 'Honduras', 'North America', 'active'),
(24, 1, 'Port-au-Prince', 'Haiti', 'North America', 'active'),
(25, 1, 'London', 'United Kingdom', 'Europe', 'active'),
(26, 1, 'Milan', 'Italy', 'Europe', 'active'),
(27, 1, 'Paris', 'France', 'Europe', 'active'),
(28, 1, 'Barcelona', 'Spain', 'Europe', 'active'),
(29, 1, 'Lisbon', 'Portugal', 'Europe', 'active'),
(30, 1, 'Bucharest', 'Romania', 'Europe', 'active'),
(31, 1, 'Istanbul', 'Turkey', 'Europe', 'active'),
(32, 1, 'Casablanca', 'Morocco', 'Africa', 'active'),
(33, 1, 'Tunis', 'Tunisia', 'Africa', 'active'),
(34, 1, 'Cairo', 'Egypt', 'Africa', 'active'),
(35, 1, 'Addis Ababa', 'Ethiopia', 'Africa', 'active'),
(36, 1, 'Nairobi', 'Kenya', 'Africa', 'active'),
(37, 1, 'Johannesburg', 'South Africa', 'Africa', 'active'),
(38, 1, 'Lagos', 'Nigeria', 'Africa', 'active'),
(39, 1, 'Sydney', 'Australia', 'Australia/Oceania', 'active'),
(40, 1, 'Auckland', 'New Zealand', 'Australia/Oceania', 'active');

INSERT IGNORE INTO `master_buyer_po_costing` (`po_id`, `type`, `pcs`, `extra_pct`, `total_pcs`, `rate`, `currency_id`, `exchange_rate`, `total_value`, `factory_id`, `display_order`) VALUES
(1, 'fabric', 1250, 3, 1287.50, 2.4500, 1, 1, 3154.3750, 3, 1),
(1, 'trim', 1250, 2, 1275.00, 0.8500, 1, 1, 1083.7500, 1, 2),
(1, 'accessory', 1250, 1, 1262.50, 0.3200, 1, 1, 404, 6, 3),
(1, 'packaging', 1250, 0, 1250.00, 0.1500, 1, 1, 187.50, 6, 4),
(2, 'fabric', 800, 3, 824, 2.6500, 1, 1, 2183.60, 4, 1),
(2, 'trim', 800, 2, 816, 0.9200, 1, 1, 750.72, 1, 2);

INSERT IGNORE INTO `master_payment_terms` (`id`, `company_id`, `name`, `code`, `description`, `status`) VALUES
(1, 1, 'Net 30', 'NET30', 'Payment due within 30 days of invoice', 'active'),
(2, 1, 'Net 60', 'NET60', 'Payment due within 60 days of invoice', 'active'),
(3, 1, 'LC at Sight', 'LCS', 'Letter of Credit payable at sight', 'active'),
(4, 1, 'LC Deferred', 'LCD', 'Deferred Letter of Credit', 'active'),
(5, 1, 'TT Advance', 'TTADV', 'Telegraphic Transfer in advance', 'active'),
(6, 1, 'TT After Shipment', 'TTAFT', 'Telegraphic Transfer after shipment', 'active'),
(7, 1, 'Cash Against Docs', 'CAD', 'Cash against shipping documents', 'active'),
(8, 1, 'Cheque', 'CHQ', 'Payment by cheque', 'active');

-- Global Payment Terms Engine — 15 global payment terms (Common list, PT001-PT015)
INSERT IGNORE INTO `master_payment_terms` (`company_id`, `name`, `code`, `description`, `status`, `payment_type`, `due_days`, `sort_order`) VALUES
(1, '100% Advance', 'PT001', 'Full advance payment before production starts', 'active', 'advance', 0, 1),
(1, 'Cash on Delivery', 'PT002', 'Payment collected at the point of delivery', 'active', 'cod', 0, 2),
(1, '7 Days Credit', 'PT003', 'Payment due within 7 days of invoice', 'active', 'credit', 7, 3),
(1, '15 Days Credit', 'PT004', 'Payment due within 15 days of invoice', 'active', 'credit', 15, 4),
(1, '30 Days Credit', 'PT005', 'Payment due within 30 days of invoice', 'active', 'credit', 30, 5),
(1, '45 Days Credit', 'PT006', 'Payment due within 45 days of invoice', 'active', 'credit', 45, 6),
(1, '60 Days Credit', 'PT007', 'Payment due within 60 days of invoice', 'active', 'credit', 60, 7),
(1, '90 Days Credit', 'PT008', 'Payment due within 90 days of invoice', 'active', 'credit', 90, 8),
(1, 'Letter of Credit at Sight', 'PT009', 'LC payable at sight against documents', 'active', 'letter_of_credit', 0, 9),
(1, 'LC 60 Days', 'PT010', 'Deferred LC payable 60 days from shipment', 'active', 'letter_of_credit', 60, 10),
(1, 'TT Advance + Balance Before Shipment', 'PT011', 'Partial TT advance; balance before shipment', 'active', 'tt', 0, 11),
(1, '30% Advance + 70% Before Shipment', 'PT012', 'Installment term: 30% on PO, 70% before shipment', 'active', 'installment', 0, 12),
(1, '50% Advance + 50% After Delivery', 'PT013', 'Installment term: 50% advance, 50% after delivery', 'active', 'installment', 0, 13),
(1, 'Open Account', 'PT014', 'Open account trading on agreed net terms', 'active', 'open_account', 30, 14),
(1, 'Milestone Based', 'PT015', 'Payments tied to defined production milestones', 'active', 'milestone', 0, 15);

-- Global Payment Terms Engine — payment types (15)
INSERT IGNORE INTO `payment_term_types` (`id`, `code`, `name`, `description`, `sort_order`, `status`) VALUES
(1, 'advance', 'Advance', 'Full or partial payment made before goods are shipped', 1, 'active'),
(2, 'cash', 'Cash', 'Cash payment at point of transaction', 2, 'active'),
(3, 'credit', 'Credit', 'Payment due within an agreed number of days', 3, 'active'),
(4, 'cod', 'Cash on Delivery', 'Payment due when goods are delivered', 4, 'active'),
(5, 'cad', 'Cash Against Documents', 'Payment due against shipping documents', 5, 'active'),
(6, 'letter_of_credit', 'Letter of Credit', 'Bank-guaranteed payment instrument', 6, 'active'),
(7, 'bank_transfer', 'Bank Transfer', 'Direct bank-to-bank transfer', 7, 'active'),
(8, 'tt', 'Telegraphic Transfer', 'Wire/telegraphic transfer payment', 8, 'active'),
(9, 'dp', 'Documents against Payment', 'Documents released only against payment', 9, 'active'),
(10, 'da', 'Documents against Acceptance', 'Documents released against accepted draft', 10, 'active'),
(11, 'open_account', 'Open Account', 'Payment on agreed terms without security', 11, 'active'),
(12, 'installment', 'Installment', 'Scheduled staged payments', 12, 'active'),
(13, 'retention', 'Retention', 'Portion withheld until completion/acceptance', 13, 'active'),
(14, 'milestone', 'Milestone Based', 'Payments tied to defined milestones', 14, 'active'),
(15, 'custom', 'Custom', 'Custom negotiated arrangement', 15, 'active');

-- Global Payment Terms Engine — payment methods (12)
INSERT IGNORE INTO `payment_methods` (`id`, `code`, `name`, `is_bank`, `sort_order`, `status`) VALUES
(1, 'bank_transfer', 'Bank Transfer', 1, 1, 'active'),
(2, 'wire_transfer', 'Wire Transfer', 1, 2, 'active'),
(3, 'swift', 'SWIFT', 1, 3, 'active'),
(4, 'neft', 'NEFT', 1, 4, 'active'),
(5, 'rtgs', 'RTGS', 1, 5, 'active'),
(6, 'upi', 'UPI', 1, 6, 'active'),
(7, 'cheque', 'Cheque', 0, 7, 'active'),
(8, 'cash', 'Cash', 0, 8, 'active'),
(9, 'letter_of_credit', 'Letter of Credit', 0, 9, 'active'),
(10, 'paypal', 'PayPal', 0, 10, 'active'),
(11, 'wise', 'Wise', 0, 11, 'active'),
(12, 'credit_card', 'Credit Card', 0, 12, 'active');

-- Global Payment Terms Engine — payment milestones (11)
INSERT IGNORE INTO `payment_milestones` (`id`, `code`, `name`, `default_percent`, `sort_order`, `status`) VALUES
(1, 'po_confirmation', 'PO Confirmation', 20.00, 1, 'active'),
(2, 'lab_dip_approval', 'Lab Dip Approval', 10.00, 2, 'active'),
(3, 'fabric_approval', 'Fabric Approval', 20.00, 3, 'active'),
(4, 'bulk_fabric_receipt', 'Bulk Fabric Receipt', 15.00, 4, 'active'),
(5, 'cutting_start', 'Cutting Start', 10.00, 5, 'active'),
(6, 'sewing_completion', 'Sewing Completion', 10.00, 6, 'active'),
(7, 'final_inspection', 'Final Inspection', 5.00, 7, 'active'),
(8, 'shipment', 'Shipment', 40.00, 8, 'active'),
(9, 'invoice', 'Invoice', 30.00, 9, 'active'),
(10, 'delivery', 'Delivery', 20.00, 10, 'active'),
(11, 'grn_approval', 'GRN Approval', 10.00, 11, 'active');

-- Global Payment Terms Engine — installment rules for installment/milestone terms
INSERT IGNORE INTO `payment_installments` (`company_id`, `payment_term_id`, `sequence_no`, `milestone`, `percent`, `due_days`, `description`) VALUES
(1, (SELECT id FROM master_payment_terms WHERE code='PT012'), 1, 'PO Confirmation', 30.00, 0, '30% advance on PO confirmation'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT012'), 2, 'Shipment', 70.00, 0, '70% balance before shipment'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT013'), 1, 'PO Confirmation', 50.00, 0, '50% advance on PO confirmation'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT013'), 2, 'Delivery', 50.00, 30, '50% balance 30 days after delivery'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT015'), 1, 'PO Confirmation', 20.00, 0, '20% on PO confirmation'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT015'), 2, 'Fabric Approval', 40.00, 0, '40% after fabric approval'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT015'), 3, 'Shipment', 40.00, 0, '40% against shipment documents');

-- Global Payment Terms Engine — early payment discounts
INSERT IGNORE INTO `payment_discounts` (`company_id`, `payment_term_id`, `discount_percent`, `days`, `net_days`, `description`) VALUES
(1, (SELECT id FROM master_payment_terms WHERE code='PT005'), 2.00, 10, 30, '2% discount if paid within 10 days, otherwise 30 days net'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT007'), 2.00, 15, 60, '2% discount if paid within 15 days, otherwise 60 days net'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT014'), 1.50, 10, 30, '1.5% discount if paid within 10 days, otherwise 30 days net');

-- Global Payment Terms Engine — late payment penalties
INSERT IGNORE INTO `payment_penalties` (`company_id`, `payment_term_id`, `interest_rate`, `penalty_percent`, `grace_days`, `max_penalty`, `description`) VALUES
(1, (SELECT id FROM master_payment_terms WHERE code='PT005'), 1.5000, 2.00, 3, 10.00, '1.5% monthly interest, 2% penalty after 3 grace days'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT007'), 1.2500, 2.00, 5, 10.00, '1.25% monthly interest, 2% penalty after 5 grace days'),
(1, (SELECT id FROM master_payment_terms WHERE code='PT008'), 1.2500, 2.00, 5, 10.00, '1.25% monthly interest, 2% penalty after 5 grace days');

-- Global Payment Terms Engine — buyer default mappings
INSERT IGNORE INTO `buyer_payment_terms` (`company_id`, `buyer_id`, `payment_term_id`, `credit_limit`, `currency`, `is_default`, `notes`) VALUES
(1, (SELECT id FROM master_buyers WHERE code='DCT-001'), (SELECT id FROM master_payment_terms WHERE code='PT007'), 250000.00, 'USD', 1, 'Decathlon default: 60 days credit'),
(1, (SELECT id FROM master_buyers WHERE code='ZAR-001'), (SELECT id FROM master_payment_terms WHERE code='PT006'), 200000.00, 'EUR', 1, 'Zara default: 45 days credit'),
(1, (SELECT id FROM master_buyers WHERE code='NIK-001'), (SELECT id FROM master_payment_terms WHERE code='PT009'), 300000.00, 'USD', 1, 'Nike default: LC at sight'),
(1, (SELECT id FROM master_buyers WHERE code='H&M-001'), (SELECT id FROM master_payment_terms WHERE code='PT008'), 250000.00, 'USD', 1, 'H&M default: 90 days credit');

-- Global Payment Terms Engine — supplier default mappings
INSERT IGNORE INTO `supplier_payment_terms` (`company_id`, `supplier_id`, `payment_term_id`, `credit_limit`, `currency`, `is_default`, `notes`) VALUES
(1, (SELECT id FROM supplier_profiles WHERE supplier_name LIKE '%Cairo Quality Textiles%' LIMIT 1), (SELECT id FROM master_payment_terms WHERE code='PT005'), 100000.00, 'USD', 1, 'Default: 30 days credit'),
(1, (SELECT id FROM supplier_profiles WHERE supplier_name LIKE '%Guangzhou Trim Masters%' LIMIT 1), (SELECT id FROM master_payment_terms WHERE code='PT004'), 50000.00, 'USD', 1, 'Default: 15 days credit'),
(1, (SELECT id FROM supplier_profiles WHERE supplier_name LIKE '%Test Garments Ltd%' LIMIT 1), (SELECT id FROM master_payment_terms WHERE code='PT002'), 25000.00, 'USD', 1, 'Default: cash on delivery');

INSERT IGNORE INTO `master_packing_terms` (`id`, `company_id`, `name`, `code`, `instructions`, `status`) VALUES
(1, 1, 'Flat Pack', 'FLAT', 'Garments folded flat, tissue paper between every 2 pieces, 12 pcs per polybag. Use standard export carton 40x30x20 cm.', 'active'),
(2, 1, 'Hanger Pack', 'HANG', 'Garments on hangers, 6 pcs per polybag, hanger locked with plastic clip. Carton size 60x40x25 cm with hanging bar.', 'active'),
(3, 1, 'Folded with Insert', 'FOLD', 'Garments folded with cardboard insert at every fold point. Tissue paper between each piece. 6 pcs per polybag.', 'active'),
(4, 1, 'Polybag Only', 'POLY', 'Individual polybag for each garment. No carton packing. Barcode sticker on polybag. Suitable for local delivery.', 'active'),
(5, 1, 'Bulk Pack', 'BULK', '20 pcs per polybag, no individual folding. Carton inner with divider. For wholesale/bulk orders only.', 'active'),
(6, 1, 'Gift Box Pack', 'GIFT', 'Each garment in individual gift box with tissue paper, ribbon, and branded sticker. Premium packing for retail display.', 'active');

-- Global Packing Terms Engine — packing methods (17)
INSERT IGNORE INTO `packing_methods` (`id`, `code`, `name`, `description`, `sort_order`, `status`) VALUES
(1, 'solid', 'Solid Packing', 'Single color / single size per carton', 1, 'active'),
(2, 'assortment', 'Assortment Packing', 'Multiple colors and/or sizes per carton', 2, 'active'),
(3, 'ratio', 'Ratio Packing', 'Carton packed by size ratio (S:M:L:XL)', 3, 'active'),
(4, 'mixed_size', 'Mixed Size Packing', 'Multiple sizes per carton', 4, 'active'),
(5, 'mixed_color', 'Mixed Color Packing', 'Multiple colors per carton', 5, 'active'),
(6, 'color_wise', 'Color Wise Packing', 'Cartons separated by color', 6, 'active'),
(7, 'size_wise', 'Size Wise Packing', 'Cartons separated by size', 7, 'active'),
(8, 'single_polybag', 'One Piece Per Polybag', 'Each garment in its own polybag', 8, 'active'),
(9, 'multi_polybag', 'Multiple Pieces Per Polybag', 'Several garments per polybag', 9, 'active'),
(10, 'hanger', 'Hanger Packing', 'Garments packed on hangers', 10, 'active'),
(11, 'flat', 'Flat Packing', 'Garments folded flat', 11, 'active'),
(12, 'roll', 'Roll Packing', 'Garments rolled', 12, 'active'),
(13, 'bulk', 'Bulk Packing', 'Bulk packing without individual folding', 13, 'active'),
(14, 'carton', 'Carton Packing', 'Standard carton packing', 14, 'active'),
(15, 'pallet', 'Pallet Packing', 'Cartons consolidated on pallets', 15, 'active'),
(16, 'vacuum', 'Vacuum Packing', 'Vacuum-sealed packing', 16, 'active'),
(17, 'custom', 'Custom Packing', 'Custom buyer-specific packing', 17, 'active');

-- Global Packing Terms Engine — folding methods (8)
INSERT IGNORE INTO `packing_folding_methods` (`id`, `code`, `name`, `instruction`, `sort_order`, `status`) VALUES
(1, 'standard', 'Standard Fold', 'Standard shirt fold', 1, 'active'),
(2, 'half_fold', 'Half Fold', 'Fold garment in half', 2, 'active'),
(3, 'third_fold', 'Third Fold', 'Fold garment in thirds', 3, 'active'),
(4, 'flat_fold', 'Flat Fold', 'Lay flat without folding', 4, 'active'),
(5, 'rolled', 'Rolled', 'Rolled garments', 5, 'active'),
(6, 'hanger', 'Hanger', 'Hanged garments', 6, 'active'),
(7, 'cross_fold', 'Cross Fold', 'Cross fold for knits', 7, 'active'),
(8, 'custom', 'Custom', 'Custom folding instruction', 8, 'active');

-- Global Packing Terms Engine — packing units (6)
INSERT IGNORE INTO `packing_units` (`id`, `code`, `name`, `sort_order`, `status`) VALUES
(1, 'PCS', 'Piece', 1, 'active'),
(2, 'SET', 'Set', 2, 'active'),
(3, 'PAIR', 'Pair', 3, 'active'),
(4, 'DOZ', 'Dozen', 4, 'active'),
(5, 'ROLL', 'Roll', 5, 'active'),
(6, 'KG', 'Kilogram', 6, 'active');

-- Global Packing Terms Engine — label templates (6)
INSERT IGNORE INTO `packing_label_templates` (`id`, `code`, `name`, `description`, `sort_order`, `status`) VALUES
(1, 'TPL-STD', 'Standard', 'Standard carton label', 1, 'active'),
(2, 'TPL-BUY', 'Buyer', 'Buyer-specific carton label', 2, 'active'),
(3, 'TPL-EXP', 'Export', 'Export carton label', 3, 'active'),
(4, 'TPL-WHS', 'Warehouse', 'Warehouse carton label', 4, 'active'),
(5, 'TPL-SHP', 'Shipment', 'Shipment carton label', 5, 'active'),
(6, 'TPL-RFID', 'RFID', 'RFID-enabled carton label', 6, 'active');

-- Global Packing Terms Engine — 20 global packing terms (PK001-PK020)
INSERT IGNORE INTO `master_packing_terms` (`company_id`, `name`, `code`, `instructions`, `status`, `packing_method_id`, `unit`, `carton_capacity`, `inner_carton_qty`, `folding_method_id`, `ratio_required`, `assortment_required`, `polybag_required`, `hanger_required`, `qr_required`, `rfid_required`, `barcode_type`, `label_template_id`, `sort_order`) VALUES
(1, 'Single Piece Polybag', 'PK001', 'One garment per polybag, then master carton', 'active', (SELECT id FROM packing_methods WHERE code='single_polybag'), 'PCS', 24, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 1, 0, 0, 0, 'EAN-13', (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 1),
(1, 'Multiple Piece Polybag', 'PK002', 'Several garments per polybag', 'active', (SELECT id FROM packing_methods WHERE code='multi_polybag'), 'PCS', 48, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 1, 0, 0, 0, 'Code 128', (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 2),
(1, 'Solid Color Packing', 'PK003', 'Single color per carton', 'active', (SELECT id FROM packing_methods WHERE code='solid'), 'PCS', 48, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 0, 0, 'GS1-128', (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 3),
(1, 'Solid Size Packing', 'PK004', 'Single size per carton', 'active', (SELECT id FROM packing_methods WHERE code='solid'), 'PCS', 48, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 4),
(1, 'Ratio Packing', 'PK005', 'Carton packed by size ratio', 'active', (SELECT id FROM packing_methods WHERE code='ratio'), 'PCS', 48, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 1, 0, 0, 0, 0, 0, 'GS1-128', (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 5),
(1, 'Color Assortment', 'PK006', 'Multiple colors per carton', 'active', (SELECT id FROM packing_methods WHERE code='assortment'), 'PCS', 60, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 1, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 6),
(1, 'Size Assortment', 'PK007', 'Multiple sizes per carton', 'active', (SELECT id FROM packing_methods WHERE code='size_wise'), 'PCS', 60, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 1, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 7),
(1, 'Color + Size Assortment', 'PK008', 'Color and size assortment per carton', 'active', (SELECT id FROM packing_methods WHERE code='assortment'), 'PCS', 60, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 1, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 8),
(1, 'Hanger Packing', 'PK009', 'Garments packed on hangers', 'active', (SELECT id FROM packing_methods WHERE code='hanger'), 'PCS', 20, NULL, (SELECT id FROM packing_folding_methods WHERE code='hanger'), 0, 0, 1, 1, 0, 0, 'EAN-13', (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 9),
(1, 'Flat Packing', 'PK010', 'Garments folded flat', 'active', (SELECT id FROM packing_methods WHERE code='flat'), 'PCS', 24, NULL, (SELECT id FROM packing_folding_methods WHERE code='flat_fold'), 0, 0, 1, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 10),
(1, 'Roll Packing', 'PK011', 'Garments rolled', 'active', (SELECT id FROM packing_methods WHERE code='roll'), 'PCS', 10, NULL, (SELECT id FROM packing_folding_methods WHERE code='rolled'), 0, 0, 1, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 11),
(1, 'Bulk Packing', 'PK012', 'Bulk packing without folding', 'active', (SELECT id FROM packing_methods WHERE code='bulk'), 'PCS', 50, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 12),
(1, 'Vacuum Packing', 'PK013', 'Vacuum-sealed garments', 'active', (SELECT id FROM packing_methods WHERE code='vacuum'), 'PCS', 24, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 1, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-WHS'), 13),
(1, 'Inner Carton Packing', 'PK014', 'Inner carton inside master carton', 'active', (SELECT id FROM packing_methods WHERE code='carton'), 'PCS', 48, 12, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-WHS'), 14),
(1, 'Master Carton Packing', 'PK015', 'Direct master carton packing', 'active', (SELECT id FROM packing_methods WHERE code='carton'), 'PCS', 50, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 15),
(1, 'Pallet Packing', 'PK016', 'Cartons consolidated on pallets', 'active', (SELECT id FROM packing_methods WHERE code='pallet'), 'PCS', 100, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-WHS'), 16),
(1, 'Export Packing', 'PK017', 'Standard export packing', 'active', (SELECT id FROM packing_methods WHERE code='carton'), 'PCS', 50, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 0, 0, 'GS1-128', (SELECT id FROM packing_label_templates WHERE code='TPL-EXP'), 17),
(1, 'Buyer Specific Packing', 'PK018', 'Buyer-specific packing instruction', 'active', (SELECT id FROM packing_methods WHERE code='custom'), 'PCS', 50, NULL, (SELECT id FROM packing_folding_methods WHERE code='custom'), 0, 0, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 18),
(1, 'RFID Packing', 'PK019', 'RFID-tagged cartons', 'active', (SELECT id FROM packing_methods WHERE code='carton'), 'PCS', 50, NULL, (SELECT id FROM packing_folding_methods WHERE code='standard'), 0, 0, 0, 0, 1, 1, 'EAN-14', (SELECT id FROM packing_label_templates WHERE code='TPL-RFID'), 19),
(1, 'Custom Packing', 'PK020', 'Custom packing arrangement', 'active', (SELECT id FROM packing_methods WHERE code='custom'), 'PCS', NULL, NULL, (SELECT id FROM packing_folding_methods WHERE code='custom'), 0, 0, 0, 0, 0, 0, NULL, (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 20);

-- Global Packing Terms Engine — polybag rules
INSERT IGNORE INTO `packing_polybag_rules` (`company_id`, `packing_term_id`, `polybag_required`, `polybag_type`, `size`, `thickness`, `biodegradable`, `recycled_pct`, `warning_required`, `ventilation_hole`, `adhesive_flap`, `self_adhesive`, `barcode_placement`, `notes`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK001'), 1, 'PE', '30 x 40 cm', 30, 1, 50, 1, 1, 0, 1, 'Outside Polybag', '1 PCS per polybag, barcode outside'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK002'), 1, 'PE', '35 x 45 cm', 30, 1, 30, 0, 1, 0, 0, NULL, 'Multiple pieces per polybag'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK009'), 1, 'PE', '40 x 50 cm', 40, 1, 20, 1, 0, 0, 1, 'On Polybag', 'Hanger packing polybag'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK010'), 1, 'PE', '30 x 40 cm', 25, 1, 50, 1, 0, 0, 1, NULL, 'Flat packing polybag'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK011'), 1, 'PE', '35 x 50 cm', 40, 1, 30, 0, 1, 0, 0, NULL, 'Roll packing polybag'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK013'), 1, 'PE', '30 x 40 cm', 60, 1, 30, 1, 1, 0, 1, NULL, 'Vacuum sealed polybag');

-- Global Packing Terms Engine — hanger rules
INSERT IGNORE INTO `packing_hanger_rules` (`company_id`, `packing_term_id`, `hanger_required`, `hanger_type`, `hanger_color`, `hanger_size`, `attachment`, `notes`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK009'), 1, 'Plastic', 'Black', 'Adult', 'Loop', 'Standard hanger with loop attachment');

-- Global Packing Terms Engine — inner carton rules
INSERT IGNORE INTO `packing_inner_carton_rules` (`company_id`, `packing_term_id`, `inner_required`, `inner_type`, `qty_per_inner`, `dimensions`, `weight_limit`, `barcode`, `label`, `notes`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK002'), 1, 'Corrugated', 24, '40 x 30 x 25 cm', 20.00, 1, 1, 'Inner of 24 PCS'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK014'), 1, 'Corrugated', 12, '40 x 30 x 25 cm', 20.00, 1, 1, '1 inner carton = 12 PCS');

-- Global Packing Terms Engine — master carton rules
INSERT IGNORE INTO `packing_master_carton_rules` (`company_id`, `packing_term_id`, `carton_type`, `length`, `width`, `height`, `max_pcs`, `max_weight`, `gross_weight`, `net_weight`, `cbm`, `material`, `bursting_strength`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK001'), 'Export Carton', 60, 40, 40, 24, 25.00, 2.50, 2.20, 0.096000, '5-ply Kraft', 12.00),
(1, (SELECT id FROM master_packing_terms WHERE code='PK005'), 'Export Carton', 60, 40, 40, 48, 28.00, 3.00, 2.60, 0.096000, '5-ply Kraft', 12.00),
(1, (SELECT id FROM master_packing_terms WHERE code='PK009'), 'Hanger Carton', 60, 40, 50, 20, 25.00, 3.20, 2.80, 0.120000, '5-ply Kraft', 12.00),
(1, (SELECT id FROM master_packing_terms WHERE code='PK017'), 'Export Carton', 60, 40, 40, 50, 30.00, 3.20, 2.80, 0.096000, '7-ply Export', 14.00),
(1, (SELECT id FROM master_packing_terms WHERE code='PK019'), 'RFID Carton', 60, 40, 40, 50, 30.00, 3.20, 2.80, 0.096000, '7-ply Export', 14.00);

-- Global Packing Terms Engine — ratio rules (PK005: S1 M2 L2 XL1)
INSERT IGNORE INTO `packing_ratio_rules` (`company_id`, `packing_term_id`, `size`, `ratio`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK005'), 'S', 1),
(1, (SELECT id FROM master_packing_terms WHERE code='PK005'), 'M', 2),
(1, (SELECT id FROM master_packing_terms WHERE code='PK005'), 'L', 2),
(1, (SELECT id FROM master_packing_terms WHERE code='PK005'), 'XL', 1);

-- Global Packing Terms Engine — assortment rules
INSERT IGNORE INTO `packing_assortment_rules` (`company_id`, `packing_term_id`, `color`, `size`, `qty`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK006'), 'BLACK', NULL, 12),
(1, (SELECT id FROM master_packing_terms WHERE code='PK006'), 'WHITE', NULL, 12),
(1, (SELECT id FROM master_packing_terms WHERE code='PK006'), 'NAVY', NULL, 12),
(1, (SELECT id FROM master_packing_terms WHERE code='PK007'), NULL, 'S', 10),
(1, (SELECT id FROM master_packing_terms WHERE code='PK007'), NULL, 'M', 20),
(1, (SELECT id FROM master_packing_terms WHERE code='PK007'), NULL, 'L', 20),
(1, (SELECT id FROM master_packing_terms WHERE code='PK007'), NULL, 'XL', 10),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'BLACK', 'S', 2),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'BLACK', 'M', 4),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'BLACK', 'L', 4),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'BLACK', 'XL', 2),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'WHITE', 'S', 2),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'WHITE', 'M', 4),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'WHITE', 'L', 4),
(1, (SELECT id FROM master_packing_terms WHERE code='PK008'), 'WHITE', 'XL', 2);

-- Global Packing Terms Engine — barcode rules
INSERT IGNORE INTO `packing_barcode_rules` (`company_id`, `packing_term_id`, `barcode_type`, `qr_required`, `rfid_required`, `placement`, `data_fields`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK001'), 'EAN-13', 0, 0, 'Garment & Carton', 'GTIN, style, size, color'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK005'), 'GS1-128', 1, 0, 'Carton', 'SSCC, PO, style, size ratio'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK017'), 'GS1-128', 1, 0, 'Carton', 'SSCC, PO, carton number, qty'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK019'), 'EAN-14', 1, 1, 'Carton & RFID Tag', 'GTIN-14, SSCC, RFID EPC');

-- Global Packing Terms Engine — carton marking rules
INSERT IGNORE INTO `packing_carton_marking_rules` (`company_id`, `packing_term_id`, `show_buyer`, `show_po_number`, `show_style`, `show_color`, `show_size`, `show_carton_number`, `show_quantity`, `show_country_of_origin`, `show_gross_weight`, `show_net_weight`, `show_cbm`, `made_in`, `numbering_format`) VALUES
(1, (SELECT id FROM master_packing_terms WHERE code='PK005'), 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 'Made in Bangladesh', 'CTN000001'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK017'), 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 'Made in Bangladesh', 'PO12345-001'),
(1, (SELECT id FROM master_packing_terms WHERE code='PK016'), 1, 1, 1, 1, 0, 1, 1, 1, 1, 1, 1, 'Made in Bangladesh', '1/200');

-- Global Packing Terms Engine — buyer packing profiles
INSERT IGNORE INTO `buyer_packing_profiles` (`company_id`, `buyer_id`, `packing_term_id`, `department`, `season`, `carton_capacity`, `barcode_type`, `label_template_id`, `notes`) VALUES
(1, (SELECT id FROM master_buyers WHERE code='DCT-001'), (SELECT id FROM master_packing_terms WHERE code='PK001'), 'ACTIVEWEAR', 'SS27', 24, 'GS1-128', (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 'Decathlon standard: single piece polybag, buyer template 03'),
(1, (SELECT id FROM master_buyers WHERE code='NIK-001'), (SELECT id FROM master_packing_terms WHERE code='PK005'), 'RUNNING', 'SS27', 48, 'GS1-128', (SELECT id FROM packing_label_templates WHERE code='TPL-STD'), 'Nike ratio packing with GS1-128 carton labels'),
(1, (SELECT id FROM master_buyers WHERE code='ZAR-001'), (SELECT id FROM master_packing_terms WHERE code='PK009'), 'WOMEN', 'SS27', 20, 'EAN-13', (SELECT id FROM packing_label_templates WHERE code='TPL-BUY'), 'Zara hanger packing for retail display');

-- Global Packing Terms Engine — style packing profiles
INSERT IGNORE INTO `style_packing_profiles` (`company_id`, `style_id`, `packing_term_id`, `carton_capacity`, `carton_qty_per_color`, `notes`) VALUES
(1, (SELECT id FROM master_styles WHERE style_no='HM-101'), (SELECT id FROM master_packing_terms WHERE code='PK001'), 24, 24, 'H&M basic tee — single piece polybag'),
(1, (SELECT id FROM master_styles WHERE style_no='NK-301'), (SELECT id FROM master_packing_terms WHERE code='PK005'), 48, NULL, 'Nike running shorts — ratio packing'),
(1, (SELECT id FROM master_styles WHERE style_no='AD-401'), (SELECT id FROM master_packing_terms WHERE code='PK001'), 24, NULL, 'Adidas tights — single piece polybag');



-- ============================================================
-- FILE: seed_quality_roles.sql
-- ============================================================

INSERT IGNORE INTO `app_roles` (`name`, `label`, `description`) VALUES
('director_of_quality', 'Director of Quality / QA Head', 'Oversees quality strategy, buyer communications, and company compliance'),
('asst_quality_manager', 'Asst. Quality Manager / Sr. QA Officer', 'Assists Quality Manager, oversees operational execution of quality protocols'),
('sectional_qc_cutting', 'Sectional QC - Cutting', 'Manages daily cutting floor quality operations and staff'),
('sectional_qc_sewing', 'Sectional QC - Sewing', 'Manages daily sewing line quality operations and staff'),
('sectional_qc_finishing', 'Sectional QC - Finishing', 'Manages daily finishing floor quality operations and staff'),
('sectional_qc_audit', 'Sectional QC - Audit', 'Manages daily audit quality operations and staff'),
('quality_controller', 'Quality Controller / Line QA', 'Stationed at lines to check garments for defects during production'),
('fabric_inspector', 'Fabric / Trim Inspector', 'Checks incoming raw materials for shade, shrinkage, and fabric defects'),
('final_auditor', 'Final Auditor', 'Performs final random inspections on packed garments per AQL standards');


-- ============================================================
-- FILE: migration_tna.sql
-- ============================================================

-- FashCognitive TNA Module - Database Migration
-- Creates all TNA-specific tables referencing fcqc's existing master data tables


-- -----------------------------------------------------------
-- TNA Projects
-- -----------------------------------------------------------
CREATE TABLE `tna_projects` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `target_date` date DEFAULT NULL,
  `po_id` int unsigned DEFAULT NULL,
  `template_id` int unsigned DEFAULT NULL,
  `status` enum('active','completed','cancelled') NOT NULL DEFAULT 'active',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_by` int 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`),
  KEY `po_id` (`po_id`),
  KEY `company_id` (`company_id`),
  KEY `created_by` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Tasks
-- -----------------------------------------------------------
CREATE TABLE `tna_tasks` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text,
  `phase` varchar(100) DEFAULT '',
  `responsible_party` varchar(255) DEFAULT '',
  `status` varchar(50) NOT NULL DEFAULT 'pending',
  `color` varchar(20) NOT NULL DEFAULT 'green',
  `start_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `completed_date` date DEFAULT NULL,
  `planned_start_date` date DEFAULT NULL,
  `planned_end_date` date DEFAULT NULL,
  `actual_start_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `delay_days` int NOT NULL DEFAULT '0',
  `completion_percentage` int NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  `is_key_event` tinyint(1) NOT NULL DEFAULT '0',
  `is_milestone` tinyint(1) NOT NULL DEFAULT '0',
  `is_auto_scheduled` tinyint(1) NOT NULL DEFAULT '0',
  `depends_on_task_id` int unsigned DEFAULT NULL,
  `factory` varchar(255) DEFAULT '',
  `vendor` varchar(255) DEFAULT '',
  `merchandiser` varchar(255) DEFAULT '',
  `qa_responsible` varchar(255) DEFAULT '',
  `remarks` text,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `tna_tasks_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Task Dependencies
-- -----------------------------------------------------------
CREATE TABLE `tna_task_dependencies` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `depends_on_task_id` int unsigned NOT NULL,
  `lag_days` int NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `task_dep` (`task_id`,`depends_on_task_id`),
  KEY `depends_on_task_id` (`depends_on_task_id`),
  CONSTRAINT `tna_task_deps_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tna_task_deps_ibfk_2` FOREIGN KEY (`depends_on_task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Templates
-- -----------------------------------------------------------
CREATE TABLE `tna_templates` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `duration_days` int NOT NULL DEFAULT '60',
  `task_definitions` longtext COMMENT 'JSON array of task definitions',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `buyer_id` int 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`),
  KEY `company_id` (`company_id`),
  KEY `buyer_id` (`buyer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Comments
-- -----------------------------------------------------------
CREATE TABLE `tna_comments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `parent_comment_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tna_comments_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Attachments
-- -----------------------------------------------------------
CREATE TABLE `tna_attachments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `comment_id` int unsigned DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` int unsigned DEFAULT '0',
  `uploaded_by` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `tna_attachments_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Project Comments
-- -----------------------------------------------------------
CREATE TABLE `tna_project_comments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `user_name` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `parent_comment_id` int unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `tna_project_comments_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Project Attachments
-- -----------------------------------------------------------
CREATE TABLE `tna_project_attachments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `comment_id` int unsigned DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` int unsigned DEFAULT '0',
  `uploaded_by` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `comment_id` (`comment_id`),
  CONSTRAINT `tna_project_attachments_ibfk_1` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA QC Log
-- -----------------------------------------------------------
CREATE TABLE `tna_qc_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `task_id` int unsigned NOT NULL,
  `inspection_round` int NOT NULL DEFAULT '1',
  `result` enum('pass','fail') NOT NULL,
  `rejection_category` varchar(100) DEFAULT NULL,
  `rejection_reason` text,
  `inspected_by` varchar(255) DEFAULT NULL,
  `inspected_at` timestamp NULL DEFAULT NULL,
  `rework_deadline` date DEFAULT NULL,
  `rework_notes` text,
  `rework_completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`),
  CONSTRAINT `tna_qc_log_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tna_tasks` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Scheduling Rules
-- -----------------------------------------------------------
CREATE TABLE `tna_scheduling_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `trigger_event` varchar(255) NOT NULL,
  `days_offset` int NOT NULL,
  `duration_days` int NOT NULL DEFAULT '1',
  `is_key_event` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Activity Log
-- -----------------------------------------------------------
CREATE TABLE `tna_activity_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned DEFAULT NULL,
  `task_id` int unsigned DEFAULT NULL,
  `user_id` int unsigned DEFAULT NULL,
  `action` varchar(100) NOT NULL DEFAULT '',
  `description` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `task_id` (`task_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Project Team
-- -----------------------------------------------------------
CREATE TABLE `tna_project_team` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `role` varchar(100) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_user` (`project_id`,`user_id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Team Members (CONSOLIDATED into users table)
-- -----------------------------------------------------------
-- This table was dropped and its columns merged into the users table.
-- See migrate_team_to_users.php for the migration.
-- Columns added to users: department_id, designation, manager_id, factory_id, portal_enabled

-- -----------------------------------------------------------
-- TNA Partners (External Portal)
-- -----------------------------------------------------------
CREATE TABLE `tna_partners` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `company_name` varchar(255) NOT NULL,
  `contact_name` varchar(255) DEFAULT '',
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `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 `email` (`email`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Partner Projects
-- -----------------------------------------------------------
CREATE TABLE `tna_partner_projects` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `partner_id` int unsigned NOT NULL,
  `project_id` int unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `partner_project` (`partner_id`,`project_id`),
  KEY `project_id` (`project_id`),
  CONSTRAINT `tna_partner_projects_ibfk_1` FOREIGN KEY (`partner_id`) REFERENCES `tna_partners` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tna_partner_projects_ibfk_2` FOREIGN KEY (`project_id`) REFERENCES `tna_projects` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Notifications
-- -----------------------------------------------------------
CREATE TABLE `tna_notifications` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `message` text,
  `type` varchar(50) NOT NULL DEFAULT 'general',
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `related_task_id` int unsigned DEFAULT NULL,
  `related_project_id` int unsigned DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT '0',
  `is_archived` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `is_read` (`is_read`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Budgets
-- -----------------------------------------------------------
CREATE TABLE `tna_budgets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `planned_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `actual_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `currency` varchar(10) DEFAULT 'USD',
  `notes` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Expenses
-- -----------------------------------------------------------
CREATE TABLE `tna_expenses` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `description` varchar(500) NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `category` varchar(100) DEFAULT '',
  `vendor` varchar(255) DEFAULT '',
  `receipt_path` varchar(500) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Invoices
-- -----------------------------------------------------------
CREATE TABLE `tna_invoices` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned NOT NULL,
  `invoice_number` varchar(100) NOT NULL,
  `vendor` varchar(255) DEFAULT '',
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `status` enum('draft','sent','paid','overdue','cancelled') NOT NULL DEFAULT 'draft',
  `due_date` date 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 `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Payments
-- -----------------------------------------------------------
CREATE TABLE `tna_payments` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `invoice_id` int unsigned NOT NULL,
  `amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `payment_method` varchar(100) DEFAULT '',
  `reference` varchar(255) DEFAULT '',
  `paid_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Integrations
-- -----------------------------------------------------------
CREATE TABLE `tna_integrations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `type` enum('netsuite','apparelmagic','generic_rest') NOT NULL,
  `api_endpoint` varchar(500) DEFAULT '',
  `api_key` varchar(500) DEFAULT '',
  `api_secret` varchar(500) DEFAULT '',
  `additional_config` text,
  `status` enum('active','inactive','error') NOT NULL DEFAULT 'inactive',
  `sync_interval_minutes` int NOT NULL DEFAULT '60',
  `last_sync_at` timestamp NULL DEFAULT NULL,
  `last_sync_status` varchar(50) DEFAULT NULL,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- TNA Integration Logs
-- -----------------------------------------------------------
CREATE TABLE `tna_integration_logs` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `integration_id` int unsigned NOT NULL,
  `direction` enum('import','export') NOT NULL DEFAULT 'import',
  `entity_type` varchar(100) DEFAULT NULL,
  `status` enum('running','success','failed','partial') NOT NULL DEFAULT 'running',
  `records_total` int NOT NULL DEFAULT '0',
  `records_succeeded` int NOT NULL DEFAULT '0',
  `records_failed` int NOT NULL DEFAULT '0',
  `error_message` text,
  `raw_request` longtext,
  `raw_response` longtext,
  `started_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `integration_id` (`integration_id`),
  CONSTRAINT `tna_integration_logs_ibfk_1` FOREIGN KEY (`integration_id`) REFERENCES `tna_integrations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Analytics Daily Snapshot
-- -----------------------------------------------------------
CREATE TABLE `analytics_daily_snapshot` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned DEFAULT NULL,
  `factory` varchar(255) DEFAULT '',
  `total_tasks` int NOT NULL DEFAULT '0',
  `completed_tasks` int NOT NULL DEFAULT '0',
  `delayed_tasks` int NOT NULL DEFAULT '0',
  `pending_tasks` int NOT NULL DEFAULT '0',
  `avg_delay_days` decimal(8,2) NOT NULL DEFAULT '0.00',
  `completion_percentage` decimal(5,2) NOT NULL DEFAULT '0.00',
  `risk_score` int NOT NULL DEFAULT '0',
  `efficiency` decimal(5,2) DEFAULT NULL,
  `snapshot_date` date NOT NULL,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `project_snapshot` (`project_id`,`snapshot_date`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- AI Predictions
-- -----------------------------------------------------------
CREATE TABLE `ai_predictions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `project_id` int unsigned DEFAULT NULL,
  `prediction_type` varchar(100) NOT NULL,
  `risk_level` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `confidence` decimal(5,2) NOT NULL DEFAULT '0.00',
  `predicted_delay_days` int DEFAULT NULL,
  `recommendation` text,
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `project_id` (`project_id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Alert Rules
-- -----------------------------------------------------------
CREATE TABLE `tna_alert_rules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `entity_type` enum('task','project','po','qc') NOT NULL DEFAULT 'task',
  `condition_field` varchar(100) NOT NULL,
  `condition_operator` enum('eq','neq','gt','gte','lt','lte','in','contains','changed') NOT NULL DEFAULT 'eq',
  `condition_value` varchar(255) NOT NULL,
  `notification_title` varchar(255) NOT NULL,
  `notification_message` text,
  `notification_type` varchar(50) NOT NULL DEFAULT 'general',
  `notification_priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `recipient_type` enum('role','department','user','project_team') DEFAULT NULL,
  `recipient_id` int unsigned DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `last_evaluated_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 `company_id` (`company_id`),
  KEY `idx_entity_type` (`entity_type`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------
-- Workflow Definitions (React Flow)
-- -----------------------------------------------------------
CREATE TABLE `workflow_definitions` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `nodes` longtext COMMENT 'JSON array of React Flow nodes',
  `edges` longtext COMMENT 'JSON array of React Flow edges',
  `status` enum('draft','published') NOT NULL DEFAULT 'draft',
  `company_id` int unsigned NOT NULL DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ============================================================
-- FILE: migration_permissions_tna.sql
-- ============================================================

-- ============================================================
-- FCQC - TNA Module Permissions Seed Data
-- ============================================================

-- -----------------------------------------------------------
-- 1. TNA Permissions
-- -----------------------------------------------------------
INSERT IGNORE INTO `permissions` (`code`, `label`, `group_name`, `description`) VALUES
('tna.view', 'View TNA Projects', 'TNA', 'View TNA projects, calendar, kanban, and eagle eye'),
('tna.create', 'Create TNA Projects', 'TNA', 'Create new TNA projects'),
('tna.edit', 'Edit TNA Projects & Tasks', 'TNA', 'Edit TNA projects and tasks'),
('templates.manage', 'Manage TNA Templates', 'TNA', 'Create, edit, and delete TNA templates'),
('team.view', 'View TNA Team', 'TNA', 'View TNA team members and roles'),
('partners.view', 'View TNA Partners', 'TNA', 'View TNA partner access'),
('predictions.view', 'View AI Predictions', 'TNA', 'View AI delay predictions and recommendations'),
('email_report.view', 'View Email Reports', 'TNA', 'View and send email reports'),
('analytics.view', 'View TNA Analytics', 'TNA', 'View TNA analytics and dashboards'),
('task_report.view', 'View Task Reports', 'TNA', 'View task reports and summaries'),
('styles.view', 'View Styles (TNA)', 'TNA', 'View styles from TNA context'),
('purchase_orders.view', 'View Purchase Orders (TNA)', 'TNA', 'View purchase orders from TNA context'),
('factories.view', 'View Factories (TNA)', 'TNA', 'View factories from TNA context'),
('buyers.view', 'View Buyers (TNA)', 'TNA', 'View buyers from TNA context');

-- -----------------------------------------------------------
-- 2. Assign TNA permissions to admin role
-- -----------------------------------------------------------
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name = 'admin'
  AND p.code IN (
    'tna.view', 'tna.create', 'tna.edit',
    'templates.manage', 'team.view', 'partners.view',
    'predictions.view', 'email_report.view', 'analytics.view',
    'task_report.view', 'styles.view', 'purchase_orders.view',
    'factories.view', 'buyers.view'
  );

-- -----------------------------------------------------------
-- 3. Assign TNA permissions to quality_manager role
-- -----------------------------------------------------------
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name = 'quality_manager'
  AND p.code IN (
    'tna.view', 'tna.create', 'tna.edit',
    'templates.manage', 'team.view', 'partners.view',
    'predictions.view', 'email_report.view', 'analytics.view',
    'task_report.view', 'styles.view', 'purchase_orders.view',
    'factories.view', 'buyers.view'
  );

-- -----------------------------------------------------------
-- 4. Assign TNA permissions to quality_inspector role
-- -----------------------------------------------------------
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name = 'quality_inspector'
  AND p.code IN (
    'tna.view', 'task_report.view',
    'styles.view', 'purchase_orders.view',
    'factories.view', 'buyers.view'
  );

-- -----------------------------------------------------------
-- 5. Assign TNA permissions to quality_viewer role
-- -----------------------------------------------------------
INSERT INTO `role_permissions` (`role_id`, `permission_id`)
SELECT r.id, p.id FROM app_roles r, permissions p
WHERE r.name = 'quality_viewer'
  AND p.code IN (
    'tna.view', 'task_report.view',
    'styles.view', 'purchase_orders.view',
    'factories.view', 'buyers.view'
  );


-- ============================================================
-- FILE: seed_tna_data.sql
-- ============================================================

-- ============================================================
-- FCQC - TNA Module Seed Data
-- ============================================================
-- Creates templates, team members, projects, and tasks
-- based on existing master_buyer_pos data
-- ============================================================

-- -----------------------------------------------------------
-- 1. Team Members (now stored in users table)
-- -----------------------------------------------------------
-- NOTE: Team members were migrated from tna_team_members to users table.
-- See migrate_team_to_users.php for the migration.
-- Department/designation/manager/factory fields are now columns in users.

-- -----------------------------------------------------------
-- 2. TNA Templates
-- -----------------------------------------------------------
INSERT IGNORE INTO `tna_templates` (`name`, `description`, `duration_days`, `task_definitions`, `company_id`, `buyer_id`) VALUES
('Woven Garment - 60 Day Standard', 'Standard 60-day TNA template for woven garment production (jackets, pants, blazers)', 60,
'[
  {"phase":"Order Confirmation","title":"Buyer PO Received","duration_days":1,"days_offset":-60,"is_key_event":true,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"Order Confirmation","duration_days":1,"days_offset":-59,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"Costing Approval","duration_days":1,"days_offset":-58,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"BOM Finalization","duration_days":1,"days_offset":-57,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"TNA Creation","duration_days":1,"days_offset":-56,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Specification Sheet","duration_days":2,"days_offset":-55,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Tech Pack Review","duration_days":2,"days_offset":-53,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Proto Sample Development","duration_days":4,"days_offset":-51,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"Proto Approval","duration_days":2,"days_offset":-47,"is_key_event":true,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Fit Sample","duration_days":2,"days_offset":-45,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"Size Set Sample","duration_days":3,"days_offset":-43,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"PP Sample","duration_days":2,"days_offset":-40,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"PP Meeting","duration_days":1,"days_offset":-38,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Materials","title":"Fabric Booking","duration_days":2,"days_offset":-55,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Merchandiser"},
  {"phase":"Materials","title":"Trim Booking","duration_days":2,"days_offset":-53,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Merchandiser"},
  {"phase":"Materials","title":"Lab Dips","duration_days":4,"days_offset":-50,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Bulk Fabric In-house","duration_days":1,"days_offset":-40,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Bulk Trim In-house","duration_days":1,"days_offset":-39,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Fabric Inspection","duration_days":1,"days_offset":-38,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"QA"},
  {"phase":"Production","title":"Cutting Start","duration_days":2,"days_offset":-36,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Cutting Complete","duration_days":1,"days_offset":-34,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Sewing Start","duration_days":1,"days_offset":-33,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Line Setting","duration_days":1,"days_offset":-33,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Pilot Run","duration_days":1,"days_offset":-31,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Sewing Complete","duration_days":1,"days_offset":-25,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Washing","duration_days":2,"days_offset":-24,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Finishing","duration_days":2,"days_offset":-22,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Packing","duration_days":1,"days_offset":-20,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Quality / QA","title":"Initial Audit","duration_days":1,"days_offset":-22,"is_key_event":false,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Quality / QA","title":"Inline QC","duration_days":2,"days_offset":-19,"is_key_event":false,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Quality / QA","title":"Final Inspection","duration_days":1,"days_offset":-16,"is_key_event":false,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Quality / QA","title":"AQL Pass","duration_days":1,"days_offset":-14,"is_key_event":true,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Logistics / Shipment","title":"Ex-Factory Date","duration_days":1,"days_offset":-12,"is_key_event":true,"is_milestone":true,"color":"purple","description":"","responsible_party":"Logistics"},
  {"phase":"Logistics / Shipment","title":"Carton Dispatch","duration_days":1,"days_offset":-11,"is_key_event":false,"is_milestone":false,"color":"purple","description":"","responsible_party":"Logistics"},
  {"phase":"Logistics / Shipment","title":"Stuffing","duration_days":1,"days_offset":-10,"is_key_event":false,"is_milestone":false,"color":"purple","description":"","responsible_party":"Logistics"},
  {"phase":"Logistics / Shipment","title":"On Vessel Date","duration_days":1,"days_offset":0,"is_key_event":true,"is_milestone":true,"color":"purple","description":"","responsible_party":"Logistics"}
]', 1, 1),
('Knit Garment - 45 Day Express', 'Express 45-day TNA template for knit garment production (t-shirts, shorts, hoodies)', 45,
'[
  {"phase":"Order Confirmation","title":"Buyer PO Received","duration_days":1,"days_offset":-45,"is_key_event":true,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"Order Confirmation","duration_days":1,"days_offset":-44,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"Costing Approval","duration_days":1,"days_offset":-43,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"BOM Finalization","duration_days":1,"days_offset":-42,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"TNA Creation","duration_days":1,"days_offset":-41,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Spec Sheet & Tech Pack","duration_days":2,"days_offset":-40,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Proto Development","duration_days":3,"days_offset":-38,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"Fit & Size Set Approval","duration_days":3,"days_offset":-35,"is_key_event":true,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"PP Sample & Meeting","duration_days":2,"days_offset":-32,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Fabric & Trim Booking","duration_days":2,"days_offset":-40,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Merchandiser"},
  {"phase":"Materials","title":"Lab Dips / Bulk Approvals","duration_days":3,"days_offset":-38,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Bulk Fabric In-house","duration_days":1,"days_offset":-28,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Fabric Inspection","duration_days":1,"days_offset":-27,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"QA"},
  {"phase":"Production","title":"Cutting & Sewing Start","duration_days":1,"days_offset":-25,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Sewing Complete","duration_days":1,"days_offset":-15,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Finishing & Packing","duration_days":2,"days_offset":-14,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Quality / QA","title":"Inline QC","duration_days":1,"days_offset":-20,"is_key_event":false,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Quality / QA","title":"Final Inspection & AQL","duration_days":1,"days_offset":-10,"is_key_event":true,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Logistics / Shipment","title":"Ex-Factory","duration_days":1,"days_offset":-8,"is_key_event":true,"is_milestone":true,"color":"purple","description":"","responsible_party":"Logistics"},
  {"phase":"Logistics / Shipment","title":"Container Stuffing","duration_days":1,"days_offset":-7,"is_key_event":false,"is_milestone":false,"color":"purple","description":"","responsible_party":"Logistics"},
  {"phase":"Logistics / Shipment","title":"On Vessel Date","duration_days":1,"days_offset":0,"is_key_event":true,"is_milestone":true,"color":"purple","description":"","responsible_party":"Logistics"}
]', 1, 3),
('Tailored Garment - 75 Day Premium', 'Premium 75-day TNA template for tailored/structured garments (blazers, coats)', 75,
'[
  {"phase":"Order Confirmation","title":"Buyer PO Received","duration_days":1,"days_offset":-75,"is_key_event":true,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"Order Confirmation","duration_days":1,"days_offset":-74,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"Costing & BOM Approval","duration_days":2,"days_offset":-73,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Order Confirmation","title":"TNA Creation","duration_days":1,"days_offset":-71,"is_key_event":false,"is_milestone":false,"color":"green","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Spec Sheet & Tech Pack","duration_days":3,"days_offset":-70,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Proto Sample Development","duration_days":5,"days_offset":-67,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"Proto Approval","duration_days":3,"days_offset":-62,"is_key_event":true,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Sampling","title":"Fit Sample","duration_days":3,"days_offset":-59,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"Size Set Sample","duration_days":4,"days_offset":-56,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Factory"},
  {"phase":"Sampling","title":"PP Sample & Meeting","duration_days":3,"days_offset":-52,"is_key_event":false,"is_milestone":false,"color":"blue","description":"","responsible_party":"Merchandiser"},
  {"phase":"Materials","title":"Fabric Booking","duration_days":3,"days_offset":-70,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Merchandiser"},
  {"phase":"Materials","title":"Trim Booking","duration_days":2,"days_offset":-67,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Merchandiser"},
  {"phase":"Materials","title":"Lab Dips","duration_days":5,"days_offset":-65,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Bulk Fabric In-house","duration_days":1,"days_offset":-48,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"Factory"},
  {"phase":"Materials","title":"Fabric & Trim Inspection","duration_days":2,"days_offset":-47,"is_key_event":false,"is_milestone":false,"color":"#86efac","description":"","responsible_party":"QA"},
  {"phase":"Production","title":"Cutting Start","duration_days":3,"days_offset":-45,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Cutting Complete","duration_days":1,"days_offset":-42,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Sewing Start","duration_days":1,"days_offset":-41,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Line Setting & Pilot Run","duration_days":2,"days_offset":-40,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Sewing Complete","duration_days":1,"days_offset":-28,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Washing","duration_days":2,"days_offset":-27,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Production","title":"Finishing & Packing","duration_days":3,"days_offset":-25,"is_key_event":false,"is_milestone":false,"color":"orange","description":"","responsible_party":"Factory"},
  {"phase":"Quality / QA","title":"Initial Audit","duration_days":1,"days_offset":-30,"is_key_event":false,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Quality / QA","title":"Inline QC","duration_days":3,"days_offset":-22,"is_key_event":false,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Quality / QA","title":"Final Inspection","duration_days":2,"days_offset":-17,"is_key_event":false,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Quality / QA","title":"AQL Pass","duration_days":1,"days_offset":-15,"is_key_event":true,"is_milestone":false,"color":"red","description":"","responsible_party":"QA"},
  {"phase":"Logistics / Shipment","title":"Ex-Factory Date","duration_days":1,"days_offset":-12,"is_key_event":true,"is_milestone":true,"color":"purple","description":"","responsible_party":"Logistics"},
  {"phase":"Logistics / Shipment","title":"Carton Dispatch & Stuffing","duration_days":2,"days_offset":-11,"is_key_event":false,"is_milestone":false,"color":"purple","description":"","responsible_party":"Logistics"},
  {"phase":"Logistics / Shipment","title":"On Vessel Date","duration_days":1,"days_offset":0,"is_key_event":true,"is_milestone":true,"color":"purple","description":"","responsible_party":"Logistics"}
]', 1, 2);

-- -----------------------------------------------------------
-- 3. TNA Projects
-- -----------------------------------------------------------
INSERT INTO `tna_projects` (`title`, `description`, `target_date`, `po_id`, `template_id`, `status`, `company_id`, `created_by`) VALUES
('Basic Cotton T-Shirt - H&M', 'Production TNA for H&M Basic Cotton T-Shirt PO-HM-2024-001', '2026-08-30', 1, 1, 'active', 1, 1),
('Slim Fit Chino Pants - H&M', 'Production TNA for H&M Slim Fit Chino Pants PO-HM-2024-002', '2026-09-15', 2, 1, 'active', 1, 1),
('Wool Blend Blazer - Zara', 'Production TNA for Zara Wool Blend Blazer PO-ZR-2024-001', '2026-10-30', 3, 3, 'active', 1, 1),
('Dri-FIT Running Shorts - Nike', 'Production TNA for Nike Dri-FIT Running Shorts PO-NK-2024-001', '2026-08-15', 4, 2, 'active', 1, 1),
('Ultraboost Tights - Adidas', 'Production TNA for Adidas Ultraboost Running Tights PO-AD-2024-001', '2026-09-30', 5, 2, 'active', 1, 1);

-- -----------------------------------------------------------
-- 4. TNA Tasks (generated from template JSON for each project)
-- -----------------------------------------------------------

-- Project 1: Basic Cotton T-Shirt (Template ID 1 - Woven 60 Day)
INSERT INTO `tna_tasks` (`project_id`, `title`, `description`, `phase`, `responsible_party`, `status`, `color`, `planned_start_date`, `planned_end_date`, `delay_days`, `completion_percentage`, `sort_order`, `is_key_event`, `is_milestone`, `factory`, `vendor`, `merchandiser`, `qa_responsible`, `company_id`)
SELECT 1, j.title, j.description, j.phase, j.responsible_party,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation','Costing Approval','BOM Finalization','TNA Creation','Specification Sheet','Tech Pack Review') THEN 'completed'
    WHEN j.title IN ('Fabric Booking','Trim Booking') THEN 'in_progress'
    WHEN j.title IN ('Proto Sample Development','Fit Sample') THEN 'in_progress'
    ELSE 'pending'
  END,
  j.color,
  DATE_ADD(CURDATE(), INTERVAL j.days_offset DAY),
  DATE_ADD(CURDATE(), INTERVAL (j.days_offset + j.duration_days) DAY),
  CASE WHEN j.title IN ('Proto Sample Development','Fit Sample') THEN 2 ELSE 0 END,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation','Costing Approval','BOM Finalization','TNA Creation','Specification Sheet','Tech Pack Review') THEN 100
    WHEN j.title IN ('Fabric Booking','Trim Booking') THEN 60
    ELSE 0
  END,
  @rn := @rn + 1, j.is_key_event, j.is_milestone,
  'Dhaka Garments Ltd', '', 'Fatima Begum', 'Nasrin Akter', 1
FROM tna_templates t, JSON_TABLE(t.task_definitions, '$[*]' COLUMNS (
  phase VARCHAR(100) PATH '$.phase', title VARCHAR(255) PATH '$.title',
  description TEXT PATH '$.description', responsible_party VARCHAR(255) PATH '$.responsible_party',
  color VARCHAR(20) PATH '$.color', days_offset INT PATH '$.days_offset',
  duration_days INT PATH '$.duration_days',
  is_key_event BOOLEAN PATH '$.is_key_event', is_milestone BOOLEAN PATH '$.is_milestone'
)) j, (SELECT @rn := 0) r WHERE t.id = 1;

-- Project 2: Slim Fit Chino Pants (Template ID 1 - Woven 60 Day)
INSERT INTO `tna_tasks` (`project_id`, `title`, `description`, `phase`, `responsible_party`, `status`, `color`, `planned_start_date`, `planned_end_date`, `delay_days`, `completion_percentage`, `sort_order`, `is_key_event`, `is_milestone`, `factory`, `vendor`, `merchandiser`, `qa_responsible`, `company_id`)
SELECT 2, j.title, j.description, j.phase, j.responsible_party,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation','Costing Approval') THEN 'completed'
    WHEN j.title IN ('BOM Finalization','TNA Creation') THEN 'in_progress'
    ELSE 'pending'
  END,
  j.color,
  DATE_ADD(CURDATE(), INTERVAL j.days_offset DAY),
  DATE_ADD(CURDATE(), INTERVAL (j.days_offset + j.duration_days) DAY),
  0,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation','Costing Approval') THEN 100
    WHEN j.title IN ('BOM Finalization','TNA Creation') THEN 40
    ELSE 0
  END,
  @rn2 := @rn2 + 1, j.is_key_event, j.is_milestone,
  'Chattogram Export Processing Zone', '', 'Shahidul Islam', 'Nasrin Akter', 1
FROM tna_templates t, JSON_TABLE(t.task_definitions, '$[*]' COLUMNS (
  phase VARCHAR(100) PATH '$.phase', title VARCHAR(255) PATH '$.title',
  description TEXT PATH '$.description', responsible_party VARCHAR(255) PATH '$.responsible_party',
  color VARCHAR(20) PATH '$.color', days_offset INT PATH '$.days_offset',
  duration_days INT PATH '$.duration_days',
  is_key_event BOOLEAN PATH '$.is_key_event', is_milestone BOOLEAN PATH '$.is_milestone'
)) j, (SELECT @rn2 := 0) r WHERE t.id = 1;

-- Project 3: Wool Blend Blazer (Template ID 3 - Tailored 75 Day)
INSERT INTO `tna_tasks` (`project_id`, `title`, `description`, `phase`, `responsible_party`, `status`, `color`, `planned_start_date`, `planned_end_date`, `delay_days`, `completion_percentage`, `sort_order`, `is_key_event`, `is_milestone`, `factory`, `vendor`, `merchandiser`, `qa_responsible`, `company_id`)
SELECT 3, j.title, j.description, j.phase, j.responsible_party, 'pending', j.color,
  DATE_ADD(CURDATE(), INTERVAL j.days_offset DAY),
  DATE_ADD(CURDATE(), INTERVAL (j.days_offset + j.duration_days) DAY),
  0, 0,
  @rn3 := @rn3 + 1, j.is_key_event, j.is_milestone,
  'Knit Concern Ltd', '', 'Nusrat Jahan', 'Nasrin Akter', 1
FROM tna_templates t, JSON_TABLE(t.task_definitions, '$[*]' COLUMNS (
  phase VARCHAR(100) PATH '$.phase', title VARCHAR(255) PATH '$.title',
  description TEXT PATH '$.description', responsible_party VARCHAR(255) PATH '$.responsible_party',
  color VARCHAR(20) PATH '$.color', days_offset INT PATH '$.days_offset',
  duration_days INT PATH '$.duration_days',
  is_key_event BOOLEAN PATH '$.is_key_event', is_milestone BOOLEAN PATH '$.is_milestone'
)) j, (SELECT @rn3 := 0) r WHERE t.id = 3;

-- Project 4: Dri-FIT Running Shorts (Template ID 2 - Knit 45 Day)
INSERT INTO `tna_tasks` (`project_id`, `title`, `description`, `phase`, `responsible_party`, `status`, `color`, `planned_start_date`, `planned_end_date`, `delay_days`, `completion_percentage`, `sort_order`, `is_key_event`, `is_milestone`, `factory`, `vendor`, `merchandiser`, `qa_responsible`, `company_id`)
SELECT 4, j.title, j.description, j.phase, j.responsible_party,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation','Costing Approval','BOM Finalization','TNA Creation','Spec Sheet & Tech Pack') THEN 'completed'
    WHEN j.title IN ('Proto Development','Fabric & Trim Booking') THEN 'in_progress'
    ELSE 'pending'
  END,
  j.color,
  DATE_ADD(CURDATE(), INTERVAL j.days_offset DAY),
  DATE_ADD(CURDATE(), INTERVAL (j.days_offset + j.duration_days) DAY),
  CASE WHEN j.title = 'Proto Development' THEN 1 ELSE 0 END,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation','Costing Approval','BOM Finalization','TNA Creation','Spec Sheet & Tech Pack') THEN 100
    WHEN j.title IN ('Proto Development','Fabric & Trim Booking') THEN 50
    ELSE 0
  END,
  @rn4 := @rn4 + 1, j.is_key_event, j.is_milestone,
  'Dhaka Garments Ltd', '', 'Rafiq Hasan', 'Nasrin Akter', 1
FROM tna_templates t, JSON_TABLE(t.task_definitions, '$[*]' COLUMNS (
  phase VARCHAR(100) PATH '$.phase', title VARCHAR(255) PATH '$.title',
  description TEXT PATH '$.description', responsible_party VARCHAR(255) PATH '$.responsible_party',
  color VARCHAR(20) PATH '$.color', days_offset INT PATH '$.days_offset',
  duration_days INT PATH '$.duration_days',
  is_key_event BOOLEAN PATH '$.is_key_event', is_milestone BOOLEAN PATH '$.is_milestone'
)) j, (SELECT @rn4 := 0) r WHERE t.id = 2;

-- Project 5: Ultraboost Tights (Template ID 2 - Knit 45 Day)
INSERT INTO `tna_tasks` (`project_id`, `title`, `description`, `phase`, `responsible_party`, `status`, `color`, `planned_start_date`, `planned_end_date`, `delay_days`, `completion_percentage`, `sort_order`, `is_key_event`, `is_milestone`, `factory`, `vendor`, `merchandiser`, `qa_responsible`, `company_id`)
SELECT 5, j.title, j.description, j.phase, j.responsible_party,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation') THEN 'completed'
    WHEN j.title = 'Costing Approval' THEN 'in_progress'
    ELSE 'pending'
  END,
  j.color,
  DATE_ADD(CURDATE(), INTERVAL j.days_offset DAY),
  DATE_ADD(CURDATE(), INTERVAL (j.days_offset + j.duration_days) DAY),
  0,
  CASE
    WHEN j.title IN ('Buyer PO Received','Order Confirmation') THEN 100
    WHEN j.title = 'Costing Approval' THEN 30
    ELSE 0
  END,
  @rn5 := @rn5 + 1, j.is_key_event, j.is_milestone,
  'PackRight Garments', '', 'Rafiq Hasan', 'Nasrin Akter', 1
FROM tna_templates t, JSON_TABLE(t.task_definitions, '$[*]' COLUMNS (
  phase VARCHAR(100) PATH '$.phase', title VARCHAR(255) PATH '$.title',
  description TEXT PATH '$.description', responsible_party VARCHAR(255) PATH '$.responsible_party',
  color VARCHAR(20) PATH '$.color', days_offset INT PATH '$.days_offset',
  duration_days INT PATH '$.duration_days',
  is_key_event BOOLEAN PATH '$.is_key_event', is_milestone BOOLEAN PATH '$.is_milestone'
)) j, (SELECT @rn5 := 0) r WHERE t.id = 2;

-- -----------------------------------------------------------
-- 5. Notifications
-- -----------------------------------------------------------
INSERT INTO `tna_notifications` (`user_id`, `title`, `message`, `type`, `priority`, `related_project_id`, `is_read`, `created_at`) VALUES
(1, 'TNA Created: Basic Cotton T-Shirt', 'TNA project for H&M Basic Cotton T-Shirt has been created and tasks are being scheduled.', 'project_created', 'medium', 1, 0, NOW()),
(1, 'Task Completed: Buyer PO Received', 'Buyer PO Received task has been marked completed for Basic Cotton T-Shirt project.', 'task_update', 'low', 1, 0, NOW()),
(1, 'Delay Alert: Proto Sample Development', 'Proto Sample Development for Basic Cotton T-Shirt is 2 days behind schedule.', 'delay_alert', 'high', 1, 0, NOW()),
(1, 'TNA Created: Dri-FIT Running Shorts', 'TNA project for Nike Dri-FIT Running Shorts has been created.', 'project_created', 'medium', 4, 0, NOW()),
(1, 'TNA Created: Ultraboost Tights', 'TNA project for Adidas Ultraboost Running Tights has been created.', 'project_created', 'medium', 5, 0, NOW());

-- -----------------------------------------------------------
-- 6. Project Team assignments
-- -----------------------------------------------------------
INSERT IGNORE INTO `tna_project_team` (`project_id`, `user_id`, `role`) VALUES
(1, 1, 'admin'),
(4, 1, 'admin'),
(5, 1, 'admin');


-- ============================================================
-- FILE: migration_report_layouts.sql
-- ============================================================

-- Report Layouts for custom report builder
CREATE TABLE `report_layouts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `template_id` int unsigned DEFAULT NULL,
  `components` json NOT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `company_id` int unsigned DEFAULT NULL,
  `created_by` int 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`),
  KEY `company_id` (`company_id`),
  KEY `created_by` (`created_by`),
  KEY `template_id` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- FILE: migration_email_log.sql
-- ============================================================

-- -----------------------------------------------------------
-- Email Log Table (referenced by api/tna/mailer.php)
-- -----------------------------------------------------------
CREATE TABLE `email_log` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `recipient` varchar(255) NOT NULL,
  `subject` varchar(500) NOT NULL,
  `body` text,
  `po_id` int unsigned DEFAULT NULL,
  `style_id` int unsigned DEFAULT NULL,
  `task_id` int unsigned DEFAULT NULL,
  `project_id` int unsigned DEFAULT NULL,
  `status` enum('pending','sent','failed') NOT NULL DEFAULT 'pending',
  `error_message` text,
  `sent_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `task_id` (`task_id`),
  KEY `project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- FILE: migration_extended_types.sql
-- ============================================================

-- ============================================================
-- FCQC - Extended Inspection Types & Support Tables
-- Extends enums to cover all 17+ garment inspection stages
-- and creates stage-specific support tables.
-- ============================================================


-- ========================
-- 1. EXTEND ENUMS
-- ========================

-- inspection_templates.template_type
ALTER TABLE `inspection_templates`
MODIFY COLUMN `template_type` ENUM(
  'FRI','DUPRO','fabric','loading',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'FRI';

-- inspection_pages.category
ALTER TABLE `inspection_pages`
MODIFY COLUMN `category` ENUM(
  'universal',
  'fri','dupro','fabric','loading',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment','all'
) NOT NULL DEFAULT 'universal';

-- inspection_assignments.inspection_type
ALTER TABLE `inspection_assignments`
MODIFY COLUMN `inspection_type` ENUM(
  'FRI','DUPRO','fabric','loading',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'FRI';

-- master_defect_library.inspection_type
ALTER TABLE `master_defect_library`
MODIFY COLUMN `inspection_type` ENUM(
  'FRI','DUPRO','fabric','loading','all',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'all';

-- measurement_templates.inspection_type
ALTER TABLE `measurement_templates`
MODIFY COLUMN `inspection_type` ENUM(
  'FRI','DUPRO','fabric','loading','all',
  'supplier_audit','fabric_4point','trims','lab_dip','ppm','pilot_run',
  'cutting','printing','embroidery','sewing_inline','sewing_endline',
  'washing','finishing','needle_detection','packing','carton_drop',
  'container_loading','capa_post_shipment'
) NOT NULL DEFAULT 'all';

-- inspection_fields.field_type - add new field types for stage-specific data
ALTER TABLE `inspection_fields`
MODIFY COLUMN `field_type` ENUM(
  'text','number','select','multi_select','date','photo','signature',
  'defect_grid','measurement_grid','aql_calculator',
  'textarea','checkbox','radio','info_text','divider',
  'roll_table','bundle_table','hourly_check','wash_test','needle_log',
  'drop_test','container_check','supplier_scorecard','color_standard'
) NOT NULL DEFAULT 'text';

-- ========================
-- 2. SUPPORT TABLES
-- ========================

-- -------------------------------------------------------
-- FABRIC INSPECTION ROLLS (4-Point System per roll)
-- -------------------------------------------------------
CREATE TABLE `fabric_inspection_rolls` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `roll_number` VARCHAR(100) NOT NULL COMMENT 'Supplier roll number or internal ID',
  `supplier` VARCHAR(255) DEFAULT NULL,
  `fabric_type` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. Single Jersey, Twill, Denim',
  `color` VARCHAR(100) DEFAULT NULL,
  `roll_width` DECIMAL(10,2) DEFAULT NULL COMMENT 'Actual width in cm/inches',
  `roll_length` DECIMAL(10,2) DEFAULT NULL COMMENT 'Actual length in yards/meters',
  `total_points` INT DEFAULT 0 COMMENT 'Total 4-point deduction points for this roll',
  `points_per_100_sq_yds` DECIMAL(10,2) DEFAULT NULL COMMENT 'Points per 100 square yards',
  `defect_count` INT DEFAULT 0 COMMENT 'Number of defects found on this roll',
  `result` ENUM('PASS','FAIL','SECOND') DEFAULT NULL,
  `result_reason` VARCHAR(500) DEFAULT NULL,
  `fabric_grade` VARCHAR(50) DEFAULT NULL COMMENT 'A, B, C, or D grade',
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_fabric_roll_session` (`session_id`),
  INDEX `idx_fabric_roll_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- CUTTING INSPECTION BUNDLES (bundle/panel-level data)
-- -------------------------------------------------------
CREATE TABLE `cutting_inspection_bundles` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `bundle_number` VARCHAR(100) NOT NULL,
  `lay_number` INT DEFAULT NULL COMMENT 'Lay number in the cutting table',
  `marker_number` VARCHAR(100) DEFAULT NULL COMMENT 'Marker/pattern reference',
  `total_plys` INT DEFAULT NULL COMMENT 'Total number of plies in bundle',
  `inspected_plys` INT DEFAULT NULL COMMENT 'Number of plies inspected',
  `defect_type` VARCHAR(100) DEFAULT NULL COMMENT 'Fusing, notching, edge damage, etc',
  `defect_count` INT DEFAULT 0,
  `result` ENUM('PASS','FAIL','REWORK') DEFAULT NULL,
  `rework_action` VARCHAR(500) DEFAULT NULL,
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_cutting_bundle_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- INLINE INSPECTION CHECKS (hourly traffic-light checks)
-- -------------------------------------------------------
CREATE TABLE `inline_inspection_checks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `check_hour` DATETIME NOT NULL COMMENT 'Timestamp of the hourly check',
  `hour_label` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. Hour 1, Hour 2, or 09:00, 10:00',
  `line_number` VARCHAR(50) DEFAULT NULL COMMENT 'Production line identifier',
  `operator_name` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('GREEN','YELLOW','RED') NOT NULL DEFAULT 'GREEN',
  `defects_found` INT DEFAULT 0,
  `defects_detail` JSON DEFAULT NULL,
  `production_output` INT DEFAULT NULL COMMENT 'Pieces produced in this hour',
  `action_taken` TEXT DEFAULT NULL COMMENT 'If YELLOW or RED, what action was taken',
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_inline_session` (`session_id`),
  INDEX `idx_inline_hour` (`check_hour`),
  INDEX `idx_inline_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- WASHING INSPECTION TESTS
-- -------------------------------------------------------
CREATE TABLE `washing_inspection_tests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `test_type` VARCHAR(100) NOT NULL COMMENT 'e.g. Dimensional Stability, Color Fastness, Shrinkage, Spirality',
  `sample_count` INT DEFAULT 1,
  `wash_cycles` INT DEFAULT 1 COMMENT 'Number of wash cycles',
  `wash_temperature` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. 40°C, 60°C',
  `parameter_name` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. Length Change, Width Change, Twist',
  `spec_min` DECIMAL(10,2) DEFAULT NULL COMMENT 'Minimum acceptable value',
  `spec_max` DECIMAL(10,2) DEFAULT NULL COMMENT 'Maximum acceptable value',
  `actual_value` DECIMAL(10,2) DEFAULT NULL,
  `unit` VARCHAR(20) DEFAULT '%',
  `result` ENUM('PASS','FAIL','BORDERLINE') DEFAULT NULL,
  `grade` VARCHAR(50) DEFAULT NULL COMMENT 'AATCC rating or similar',
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_wash_session` (`session_id`),
  INDEX `idx_wash_type` (`test_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- NEEDLE DETECTION LOGS (Metal detector / needle scan)
-- -------------------------------------------------------
CREATE TABLE `needle_detection_logs` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `machine_id` VARCHAR(100) DEFAULT NULL COMMENT 'Needle detector machine ID/serial',
  `machine_name` VARCHAR(255) DEFAULT NULL,
  `scan_time` DATETIME NOT NULL,
  `total_pieces` INT DEFAULT 0 COMMENT 'Pieces scanned in this batch',
  `alarms` INT DEFAULT 0 COMMENT 'Number of alarms triggered',
  `alarm_details` JSON DEFAULT NULL COMMENT 'Details of each alarm (piece ID, location, type)',
  `found_needles` INT DEFAULT 0 COMMENT 'Confirmed broken needles found',
  `result` ENUM('PASS','FAIL','PARTIAL') DEFAULT NULL,
  `operator_name` VARCHAR(255) DEFAULT NULL,
  `photo` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_needle_session` (`session_id`),
  INDEX `idx_needle_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- CARTON DROP TESTS
-- -------------------------------------------------------
CREATE TABLE `carton_drop_tests` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `carton_id` VARCHAR(100) DEFAULT NULL COMMENT 'Carton reference/number',
  `drop_height` DECIMAL(10,2) DEFAULT NULL COMMENT 'Drop height in cm/inches',
  `drop_orientation` ENUM('face','edge','corner','flat') DEFAULT NULL,
  `drop_count` INT DEFAULT 1,
  `result` ENUM('PASS','FAIL','MINOR_DAMAGE') DEFAULT NULL,
  `damage_description` TEXT DEFAULT NULL,
  `internal_packaging_ok` TINYINT(1) DEFAULT 1,
  `product_damage` TINYINT(1) DEFAULT 0,
  `photo_before` VARCHAR(500) DEFAULT NULL,
  `photo_after` VARCHAR(500) DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_drop_session` (`session_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- CONTAINER LOADING CHECKS (CLI data)
-- -------------------------------------------------------
CREATE TABLE `container_loading_checks` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `container_number` VARCHAR(50) NOT NULL COMMENT 'Container number/ID',
  `seal_number` VARCHAR(100) DEFAULT NULL,
  `loading_date` DATE DEFAULT NULL,
  `container_size` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. 20GP, 40GP, 40HQ, 45HC',
  `truck_plate` VARCHAR(50) DEFAULT NULL,
  `driver_name` VARCHAR(255) DEFAULT NULL,
  `carton_count_loaded` INT DEFAULT 0,
  `carton_count_document` INT DEFAULT 0 COMMENT 'Cartons per packing list',
  `quantity_loaded` INT DEFAULT 0 COMMENT 'Pieces loaded',
  `quantity_document` INT DEFAULT 0 COMMENT 'Pieces per document',
  `container_condition` ENUM('good','minor_damage','major_damage','rejected') DEFAULT NULL,
  `interior_clean` TINYINT(1) DEFAULT 1,
  `floor_condition` TINYINT(1) DEFAULT 1,
  `ventilation_ok` TINYINT(1) DEFAULT 1,
  `stuffing_pattern_ok` TINYINT(1) DEFAULT 1 COMMENT 'Loading pattern correct per plan',
  `temperature` DECIMAL(5,1) DEFAULT NULL COMMENT 'Internal container temp if applicable',
  `humidity` DECIMAL(5,1) DEFAULT NULL COMMENT 'Internal humidity %',
  `photo_interior` VARCHAR(500) DEFAULT NULL,
  `photo_seal` VARCHAR(500) DEFAULT NULL,
  `photo_general` VARCHAR(500) DEFAULT NULL,
  `result` ENUM('PASS','FAIL','CONDITIONAL') DEFAULT NULL,
  `inspector_name` VARCHAR(255) DEFAULT NULL,
  `loading_time_start` TIME DEFAULT NULL,
  `loading_time_end` TIME DEFAULT NULL,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_container_session` (`session_id`),
  INDEX `idx_container_number` (`container_number`),
  INDEX `idx_container_result` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------
-- AQL RESULTS (stored evaluation history per session)
-- -------------------------------------------------------
CREATE TABLE `aql_results` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `profile_id` INT DEFAULT NULL,
  `profile_name` VARCHAR(100) DEFAULT NULL,
  `inspection_level` VARCHAR(10) DEFAULT 'II',
  `lot_size` INT DEFAULT NULL,
  `sample_size` INT NOT NULL,
  `code_letter` VARCHAR(2) DEFAULT NULL,
  `critical_aql` DECIMAL(4,2) DEFAULT 0.00,
  `major_aql` DECIMAL(4,2) DEFAULT 2.50,
  `minor_aql` DECIMAL(4,2) DEFAULT 4.00,
  `critical_found` INT DEFAULT 0,
  `major_found` INT DEFAULT 0,
  `minor_found` INT DEFAULT 0,
  `critical_accept` INT DEFAULT 0,
  `major_accept` INT DEFAULT 0,
  `minor_accept` INT DEFAULT 0,
  `critical_reject` INT DEFAULT 0,
  `major_reject` INT DEFAULT 0,
  `minor_reject` INT DEFAULT 0,
  `result` ENUM('PASS','FAIL') NOT NULL,
  `reason` TEXT DEFAULT NULL,
  `score` DECIMAL(5,2) DEFAULT NULL,
  `calculated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_aql_result_session` (`session_id`),
  INDEX `idx_aql_result_outcome` (`result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



-- ============================================================
-- FILE: migration_aql.sql
-- ============================================================

-- ============================================================
-- FCQC - AQL Module (ANSI/ASQ Z1.4 / ISO 2859-1)
-- Acceptable Quality Level tables + seed data
-- ============================================================


-- 1. LOT SIZE RANGES
CREATE TABLE `aql_lot_size` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `lot_min` INT NOT NULL,
  `lot_max` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aql_lot_size` (`lot_min`, `lot_max`) VALUES
(2, 8),
(9, 15),
(16, 25),
(26, 50),
(51, 90),
(91, 150),
(151, 280),
(281, 500),
(501, 1200),
(1201, 3200),
(3201, 10000),
(10001, 35000),
(35001, 150000),
(150001, 500000),
(500001, 999999999);

-- 2. INSPECTION LEVELS
CREATE TABLE `aql_inspection_levels` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `level_code` VARCHAR(10) NOT NULL,
  `level_type` ENUM('GENERAL', 'SPECIAL') NOT NULL,
  `description` VARCHAR(100)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aql_inspection_levels` (`level_code`, `level_type`, `description`) VALUES
('I', 'GENERAL', 'General Level I - Less strict'),
('II', 'GENERAL', 'General Level II - Standard'),
('III', 'GENERAL', 'General Level III - More strict'),
('S1', 'SPECIAL', 'Special S-1 - Minimal sampling'),
('S2', 'SPECIAL', 'Special S-2 - Small sampling'),
('S3', 'SPECIAL', 'Special S-3 - Limited sampling'),
('S4', 'SPECIAL', 'Special S-4 - Reduced sampling');

-- 3. CODE LETTER MAPPING (lot_size × inspection_level → code_letter)
CREATE TABLE `aql_code_letters` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `lot_id` INT NOT NULL,
  `inspection_level` VARCHAR(10) NOT NULL,
  `code_letter` VARCHAR(2) NOT NULL,
  FOREIGN KEY (`lot_id`) REFERENCES `aql_lot_size`(`id`),
  UNIQUE KEY `unique_mapping` (`lot_id`, `inspection_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Lot 2-8
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(1, 'I', 'A'), (1, 'II', 'A'), (1, 'III', 'B'),
(1, 'S1', 'A'), (1, 'S2', 'A'), (1, 'S3', 'A'), (1, 'S4', 'A');
-- Lot 9-15
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(2, 'I', 'A'), (2, 'II', 'B'), (2, 'III', 'C'),
(2, 'S1', 'A'), (2, 'S2', 'A'), (2, 'S3', 'A'), (2, 'S4', 'B');
-- Lot 16-25
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(3, 'I', 'B'), (3, 'II', 'C'), (3, 'III', 'D'),
(3, 'S1', 'A'), (3, 'S2', 'A'), (3, 'S3', 'B'), (3, 'S4', 'C');
-- Lot 26-50
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(4, 'I', 'C'), (4, 'II', 'D'), (4, 'III', 'E'),
(4, 'S1', 'A'), (4, 'S2', 'A'), (4, 'S3', 'B'), (4, 'S4', 'C');
-- Lot 51-90
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(5, 'I', 'C'), (5, 'II', 'E'), (5, 'III', 'F'),
(5, 'S1', 'A'), (5, 'S2', 'B'), (5, 'S3', 'C'), (5, 'S4', 'D');
-- Lot 91-150
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(6, 'I', 'D'), (6, 'II', 'F'), (6, 'III', 'G'),
(6, 'S1', 'A'), (6, 'S2', 'B'), (6, 'S3', 'C'), (6, 'S4', 'D');
-- Lot 151-280
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(7, 'I', 'E'), (7, 'II', 'G'), (7, 'III', 'H'),
(7, 'S1', 'A'), (7, 'S2', 'B'), (7, 'S3', 'C'), (7, 'S4', 'E');
-- Lot 281-500
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(8, 'I', 'F'), (8, 'II', 'H'), (8, 'III', 'J'),
(8, 'S1', 'A'), (8, 'S2', 'B'), (8, 'S3', 'D'), (8, 'S4', 'E');
-- Lot 501-1200
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(9, 'I', 'G'), (9, 'II', 'J'), (9, 'III', 'K'),
(9, 'S1', 'A'), (9, 'S2', 'C'), (9, 'S3', 'D'), (9, 'S4', 'E');
-- Lot 1201-3200
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(10, 'I', 'H'), (10, 'II', 'K'), (10, 'III', 'L'),
(10, 'S1', 'A'), (10, 'S2', 'C'), (10, 'S3', 'D'), (10, 'S4', 'F');
-- Lot 3201-10000
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(11, 'I', 'J'), (11, 'II', 'L'), (11, 'III', 'M'),
(11, 'S1', 'A'), (11, 'S2', 'C'), (11, 'S3', 'E'), (11, 'S4', 'F');
-- Lot 10001-35000
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(12, 'I', 'K'), (12, 'II', 'M'), (12, 'III', 'N'),
(12, 'S1', 'B'), (12, 'S2', 'C'), (12, 'S3', 'E'), (12, 'S4', 'G');
-- Lot 35001-150000
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(13, 'I', 'L'), (13, 'II', 'N'), (13, 'III', 'P'),
(13, 'S1', 'B'), (13, 'S2', 'C'), (13, 'S3', 'F'), (13, 'S4', 'G');
-- Lot 150001-500000
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(14, 'I', 'M'), (14, 'II', 'P'), (14, 'III', 'Q'),
(14, 'S1', 'B'), (14, 'S2', 'D'), (14, 'S3', 'F'), (14, 'S4', 'H');
-- Lot 500001+
INSERT INTO `aql_code_letters` (`lot_id`, `inspection_level`, `code_letter`) VALUES
(15, 'I', 'N'), (15, 'II', 'Q'), (15, 'III', 'R'),
(15, 'S1', 'B'), (15, 'S2', 'D'), (15, 'S3', 'G'), (15, 'S4', 'H');

-- 4. SAMPLE SIZE (code_letter → sample_size)
CREATE TABLE `aql_sample_size` (
  `code_letter` VARCHAR(2) PRIMARY KEY,
  `sample_size` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aql_sample_size` VALUES
('A', 2), ('B', 3), ('C', 5), ('D', 8), ('E', 13), ('F', 20), ('G', 32),
('H', 50), ('J', 80), ('K', 125), ('L', 200), ('M', 315), ('N', 500),
('P', 800), ('Q', 1250), ('R', 2000);

-- 5. AQL VALUES (common values used in garment industry)
CREATE TABLE `aql_values` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `aql_value` DECIMAL(4,2) NOT NULL,
  `description` VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aql_values` (`aql_value`, `description`) VALUES
(0.0, 'Zero Tolerance (Critical)'),
(0.10, 'Very Strict'),
(0.15, 'Very Strict'),
(0.25, 'Strict'),
(0.40, 'Strict'),
(0.65, 'Moderate'),
(1.0, 'Standard'),
(1.5, 'Standard'),
(2.5, 'Standard Major'),
(4.0, 'Standard Minor'),
(6.5, 'Relaxed');

-- 6. ACCEPTANCE / REJECTION TABLE (sample_size × aql_value → accept_no, reject_no)
CREATE TABLE `aql_acceptance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sample_size` INT NOT NULL,
  `aql_value` DECIMAL(4,2) NOT NULL,
  `accept_no` INT NOT NULL,
  `reject_no` INT NOT NULL,
  UNIQUE KEY `unique_accept` (`sample_size`, `aql_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aql_acceptance` (`sample_size`, `aql_value`, `accept_no`, `reject_no`) VALUES
-- Sample Size 2
(2, 0.0, 0, 1), (2, 0.10, 0, 1), (2, 0.15, 0, 1), (2, 0.25, 0, 1),
(2, 0.40, 0, 1), (2, 0.65, 0, 1), (2, 1.0, 0, 1), (2, 1.5, 0, 1),
(2, 2.5, 0, 1), (2, 4.0, 0, 1), (2, 6.5, 0, 1),
-- Sample Size 3
(3, 0.0, 0, 1), (3, 0.10, 0, 1), (3, 0.15, 0, 1), (3, 0.25, 0, 1),
(3, 0.40, 0, 1), (3, 0.65, 0, 1), (3, 1.0, 0, 1), (3, 1.5, 0, 1),
(3, 2.5, 0, 1), (3, 4.0, 0, 1), (3, 6.5, 0, 1),
-- Sample Size 5
(5, 0.0, 0, 1), (5, 0.10, 0, 1), (5, 0.15, 0, 1), (5, 0.25, 0, 1),
(5, 0.40, 0, 1), (5, 0.65, 0, 1), (5, 1.0, 0, 1), (5, 1.5, 0, 1),
(5, 2.5, 0, 1), (5, 4.0, 0, 1), (5, 6.5, 0, 1),
-- Sample Size 8
(8, 0.0, 0, 1), (8, 0.10, 0, 1), (8, 0.15, 0, 1), (8, 0.25, 0, 1),
(8, 0.40, 0, 1), (8, 0.65, 0, 1), (8, 1.0, 0, 1), (8, 1.5, 0, 1),
(8, 2.5, 0, 1), (8, 4.0, 0, 1), (8, 6.5, 1, 2),
-- Sample Size 13
(13, 0.0, 0, 1), (13, 0.10, 0, 1), (13, 0.15, 0, 1), (13, 0.25, 0, 1),
(13, 0.40, 0, 1), (13, 0.65, 0, 1), (13, 1.0, 0, 1), (13, 1.5, 0, 1),
(13, 2.5, 0, 1), (13, 4.0, 1, 2), (13, 6.5, 1, 2),
-- Sample Size 20
(20, 0.0, 0, 1), (20, 0.10, 0, 1), (20, 0.15, 0, 1), (20, 0.25, 0, 1),
(20, 0.40, 0, 1), (20, 0.65, 0, 1), (20, 1.0, 0, 1), (20, 1.5, 0, 1),
(20, 2.5, 1, 2), (20, 4.0, 1, 2), (20, 6.5, 2, 3),
-- Sample Size 32
(32, 0.0, 0, 1), (32, 0.10, 0, 1), (32, 0.15, 0, 1), (32, 0.25, 0, 1),
(32, 0.40, 0, 1), (32, 0.65, 0, 1), (32, 1.0, 0, 1), (32, 1.5, 1, 2),
(32, 2.5, 1, 2), (32, 4.0, 2, 3), (32, 6.5, 3, 4),
-- Sample Size 50
(50, 0.0, 0, 1), (50, 0.10, 0, 1), (50, 0.15, 0, 1), (50, 0.25, 0, 1),
(50, 0.40, 0, 1), (50, 0.65, 0, 1), (50, 1.0, 1, 2), (50, 1.5, 1, 2),
(50, 2.5, 2, 3), (50, 4.0, 3, 4), (50, 6.5, 5, 6),
-- Sample Size 80
(80, 0.0, 0, 1), (80, 0.10, 0, 1), (80, 0.15, 0, 1), (80, 0.25, 0, 1),
(80, 0.40, 0, 1), (80, 0.65, 1, 2), (80, 1.0, 1, 2), (80, 1.5, 2, 3),
(80, 2.5, 3, 4), (80, 4.0, 5, 6), (80, 6.5, 7, 8),
-- Sample Size 125
(125, 0.0, 0, 1), (125, 0.10, 0, 1), (125, 0.15, 0, 1), (125, 0.25, 0, 1),
(125, 0.40, 1, 2), (125, 0.65, 1, 2), (125, 1.0, 2, 3), (125, 1.5, 3, 4),
(125, 2.5, 5, 6), (125, 4.0, 7, 8), (125, 6.5, 10, 11),
-- Sample Size 200
(200, 0.0, 0, 1), (200, 0.10, 0, 1), (200, 0.15, 0, 1), (200, 0.25, 0, 1),
(200, 0.40, 1, 2), (200, 0.65, 2, 3), (200, 1.0, 3, 4), (200, 1.5, 5, 6),
(200, 2.5, 7, 8), (200, 4.0, 10, 11), (200, 6.5, 14, 15),
-- Sample Size 315
(315, 0.0, 0, 1), (315, 0.10, 0, 1), (315, 0.15, 0, 1), (315, 0.25, 1, 2),
(315, 0.40, 2, 3), (315, 0.65, 3, 4), (315, 1.0, 5, 6), (315, 1.5, 7, 8),
(315, 2.5, 10, 11), (315, 4.0, 14, 15), (315, 6.5, 21, 22),
-- Sample Size 500
(500, 0.0, 0, 1), (500, 0.10, 0, 1), (500, 0.15, 1, 2), (500, 0.25, 1, 2),
(500, 0.40, 3, 4), (500, 0.65, 5, 6), (500, 1.0, 7, 8), (500, 1.5, 10, 11),
(500, 2.5, 14, 15), (500, 4.0, 21, 22), (500, 6.5, 32, 33),
-- Sample Size 800
(800, 0.0, 0, 1), (800, 0.10, 0, 1), (800, 0.15, 1, 2), (800, 0.25, 2, 3),
(800, 0.40, 5, 6), (800, 0.65, 7, 8), (800, 1.0, 10, 11), (800, 1.5, 14, 15),
(800, 2.5, 21, 22), (800, 4.0, 32, 33), (800, 6.5, 32, 33),
-- Sample Size 1250
(1250, 0.0, 0, 1), (1250, 0.10, 1, 2), (1250, 0.15, 1, 2), (1250, 0.25, 3, 4),
(1250, 0.40, 7, 8), (1250, 0.65, 10, 11), (1250, 1.0, 14, 15), (1250, 1.5, 21, 22),
(1250, 2.5, 21, 22), (1250, 4.0, 32, 33), (1250, 6.5, 32, 33),
-- Sample Size 2000
(2000, 0.0, 0, 1), (2000, 0.10, 1, 2), (2000, 0.15, 2, 3), (2000, 0.25, 5, 6),
(2000, 0.40, 10, 11), (2000, 0.65, 14, 15), (2000, 1.0, 21, 22), (2000, 1.5, 32, 33),
(2000, 2.5, 32, 33), (2000, 4.0, 32, 33), (2000, 6.5, 32, 33);

-- 7. AQL PROFILES (standard garment industry presets)
CREATE TABLE `aql_profiles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `description` VARCHAR(255),
  `critical_aql` DECIMAL(4,2) DEFAULT 0.0,
  `major_aql` DECIMAL(4,2) DEFAULT 2.5,
  `minor_aql` DECIMAL(4,2) DEFAULT 4.0,
  `is_default` BOOLEAN DEFAULT FALSE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `aql_profiles` (`name`, `description`, `critical_aql`, `major_aql`, `minor_aql`, `is_default`) VALUES
('Standard (0/2.5/4.0)', 'Most common garment standard (Nike/Adidas/Walmart)', 0.0, 2.5, 4.0, TRUE),
('Strict (0/1.5/2.5)', 'More stringent for high-end buyers', 0.0, 1.5, 2.5, FALSE),
('Relaxed (0/4.0/6.5)', 'Less strict for low-risk products', 0.0, 4.0, 6.5, FALSE),
('Zero Tolerance', '100% inspection - no defects allowed', 0.0, 0.0, 0.0, FALSE);



-- ============================================================
-- FILE: migration_qc_inspection.sql
-- ============================================================

-- FashCognitive Quality OS - Inspection Management Schema
-- Run AFTER migration_qc.sql
-- Implements metadata-driven inspection engine


-- ========================
-- INSPECTION TEMPLATES
-- ========================
CREATE TABLE `inspection_templates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `template_type` ENUM('FRI','DUPRO','fabric','loading') NOT NULL DEFAULT 'FRI',
  `name` VARCHAR(255) NOT NULL,
  `version` INT NOT NULL DEFAULT 1,
  `status` ENUM('draft','active','archived') NOT NULL DEFAULT 'active',
  `config` JSON DEFAULT NULL COMMENT 'Template-level configuration (auto_save_interval, gps_required, etc)',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_template_buyer` (`buyer_id`),
  INDEX `idx_template_type` (`template_type`),
  INDEX `idx_template_company` (`company_id`),
  INDEX `idx_template_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION PAGES
-- ========================
CREATE TABLE `inspection_pages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED NOT NULL,
  `page_key` VARCHAR(100) NOT NULL COMMENT 'Unique key within template (e.g. overview, defects, measurements)',
  `label` VARCHAR(255) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_conditional` TINYINT(1) NOT NULL DEFAULT 0,
  `condition_rule` JSON DEFAULT NULL COMMENT 'JSON rule that determines when this page is shown',
  `category` ENUM('universal','fri','dupro','fabric','loading') NOT NULL DEFAULT 'universal',
  `config` JSON DEFAULT NULL COMMENT 'Page-level config (columns, layout, etc)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`) ON DELETE CASCADE,
  INDEX `idx_page_template` (`template_id`),
  INDEX `idx_page_category` (`category`),
  UNIQUE KEY `uq_page_template_key` (`template_id`, `page_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION FIELDS
-- ========================
CREATE TABLE `inspection_fields` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `page_id` INT UNSIGNED NOT NULL,
  `field_key` VARCHAR(100) NOT NULL,
  `field_type` ENUM('text','number','select','multi_select','date','photo','signature','defect_grid','measurement_grid','aql_calculator','textarea','checkbox','radio','info_text','divider') NOT NULL DEFAULT 'text',
  `label` VARCHAR(255) NOT NULL,
  `is_required` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `validation_rules` JSON DEFAULT NULL COMMENT 'JSON validation rules (min, max, pattern, required_if, etc)',
  `options` JSON DEFAULT NULL COMMENT 'For select/multi_select/radio: array of {label, value}',
  `config` JSON DEFAULT NULL COMMENT 'Field-level config (placeholder, help_text, units, default_value, etc)',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`page_id`) REFERENCES `inspection_pages`(`id`) ON DELETE CASCADE,
  INDEX `idx_field_page` (`page_id`),
  UNIQUE KEY `uq_field_page_key` (`page_id`, `field_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION RULES (JSON-based rules engine)
-- ========================
CREATE TABLE `inspection_rules` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED NOT NULL,
  `priority` INT NOT NULL DEFAULT 0,
  `condition_expression` VARCHAR(500) NOT NULL COMMENT 'Evaluable expression like "critical_defects > 0"',
  `actions` JSON NOT NULL COMMENT 'Array of actions: ["set_status:FAIL", "require_manager_approval", "trigger_CAPA"]',
  `label` VARCHAR(255) DEFAULT NULL COMMENT 'Human-readable description',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`) ON DELETE CASCADE,
  INDEX `idx_rule_template` (`template_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION SESSIONS (core - stores inspection state & data)
-- ========================
CREATE TABLE `inspection_sessions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `assignment_id` INT UNSIGNED DEFAULT NULL,
  `template_id` INT UNSIGNED NOT NULL,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `factory_id` INT UNSIGNED DEFAULT NULL,
  `po_id` INT UNSIGNED DEFAULT NULL,
  `style_id` INT UNSIGNED DEFAULT NULL,
  `inspector_id` INT UNSIGNED DEFAULT NULL,
  `inspector_name` VARCHAR(255) DEFAULT NULL,
  `status` ENUM('assigned','started','draft','in_progress','submitted','under_approval','approved','rejected','cancelled') NOT NULL DEFAULT 'assigned',
  `data` JSON DEFAULT NULL COMMENT 'All field values stored as JSON {page_key: {field_key: value}}',
  `aql_data` JSON DEFAULT NULL COMMENT 'AQL calculation results',
  `decision` ENUM('PASS','CONDITIONAL_PASS','FAIL','RE_INSPECTION','HOLD') DEFAULT NULL,
  `decision_reason` TEXT DEFAULT NULL,
  `gps_lat` DECIMAL(10,7) DEFAULT NULL,
  `gps_lng` DECIMAL(10,7) DEFAULT NULL,
  `gps_accuracy` DECIMAL(10,2) DEFAULT NULL,
  `gps_timestamp` TIMESTAMP NULL DEFAULT NULL,
  `started_at` TIMESTAMP NULL DEFAULT NULL,
  `submitted_at` TIMESTAMP NULL DEFAULT NULL,
  `completed_at` TIMESTAMP NULL DEFAULT NULL,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_session_assignment` (`assignment_id`),
  INDEX `idx_session_template` (`template_id`),
  INDEX `idx_session_buyer` (`buyer_id`),
  INDEX `idx_session_factory` (`factory_id`),
  INDEX `idx_session_po` (`po_id`),
  INDEX `idx_session_inspector` (`inspector_id`),
  INDEX `idx_session_status` (`status`),
  INDEX `idx_session_company` (`company_id`),
  INDEX `idx_session_decision` (`decision`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION ASSIGNMENTS (scheduling)
-- ========================
CREATE TABLE `inspection_assignments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED NOT NULL,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `factory_id` INT UNSIGNED DEFAULT NULL,
  `po_id` INT UNSIGNED DEFAULT NULL,
  `style_id` INT UNSIGNED DEFAULT NULL,
  `inspector_id` INT UNSIGNED DEFAULT NULL,
  `inspector_name` VARCHAR(255) DEFAULT NULL,
  `scheduled_date` DATE DEFAULT NULL,
  `scheduled_time_slot` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. 09:00-13:00',
  `status` ENUM('planned','assigned','in_progress','completed','cancelled','rebooked') NOT NULL DEFAULT 'planned',
  `inspection_type` ENUM('FRI','DUPRO','fabric','loading') NOT NULL DEFAULT 'FRI',
  `priority` ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `notes` TEXT DEFAULT NULL,
  `booking_ref` VARCHAR(50) DEFAULT NULL COMMENT 'Unique booking reference code',
  `capacity_hours` DECIMAL(5,2) DEFAULT NULL COMMENT 'Estimated inspection hours',
  `rebooked_from_id` INT UNSIGNED DEFAULT NULL COMMENT 'If rebooked, points to original assignment',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Soft delete for recycle/restore',
  INDEX `idx_assign_template` (`template_id`),
  INDEX `idx_assign_buyer` (`buyer_id`),
  INDEX `idx_assign_factory` (`factory_id`),
  INDEX `idx_assign_po` (`po_id`),
  INDEX `idx_assign_inspector` (`inspector_id`),
  INDEX `idx_assign_date` (`scheduled_date`),
  INDEX `idx_assign_status` (`status`),
  INDEX `idx_assign_company` (`company_id`),
  INDEX `idx_assign_deleted` (`deleted_at`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`),
  FOREIGN KEY (`rebooked_from_id`) REFERENCES `inspection_assignments`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION DRAFTS (auto-save recovery)
-- ========================
CREATE TABLE `inspection_drafts` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `data` JSON NOT NULL COMMENT 'Snapshot of session data at save time',
  `version` INT NOT NULL DEFAULT 1,
  `field_key` VARCHAR(100) DEFAULT NULL COMMENT 'Last edited field',
  `page_key` VARCHAR(100) DEFAULT NULL COMMENT 'Current page',
  `progress_pct` DECIMAL(5,2) DEFAULT 0 COMMENT 'Completion percentage',
  `device_info` VARCHAR(255) DEFAULT NULL COMMENT 'Browser/device identifier',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_draft_session` (`session_id`),
  INDEX `idx_draft_version` (`session_id`, `version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION SIGNATURES
-- ========================
CREATE TABLE `inspection_signatures` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `role` ENUM('inspector','factory_qa','buyer_qa','regional_manager') NOT NULL,
  `signatory_name` VARCHAR(255) NOT NULL,
  `signatory_title` VARCHAR(255) DEFAULT NULL,
  `signature_data` TEXT NOT NULL COMMENT 'Base64 encoded signature image data',
  `signed_at` TIMESTAMP NULL DEFAULT NULL,
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_id` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_sig_session` (`session_id`),
  INDEX `idx_sig_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION REPORT VERSIONS (immutable)
-- ========================
CREATE TABLE `inspection_report_versions` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `version_number` INT NOT NULL,
  `data_snapshot` JSON NOT NULL COMMENT 'Complete frozen copy of session data at version creation',
  `pdf_path` VARCHAR(500) DEFAULT NULL COMMENT 'Server path to generated PDF',
  `pdf_url` VARCHAR(500) DEFAULT NULL COMMENT 'Public URL for PDF',
  `checksum` VARCHAR(64) NOT NULL COMMENT 'SHA-256 checksum of data_snapshot for tamper verification',
  `qr_code` VARCHAR(500) DEFAULT NULL COMMENT 'QR code data/URL for report verification',
  `version_label` VARCHAR(255) DEFAULT NULL COMMENT 'e.g. "Submitted", "Buyer Comments", "CAPA Updates"',
  `change_summary` TEXT DEFAULT NULL COMMENT 'Summary of changes from previous version',
  `previous_version_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`previous_version_id`) REFERENCES `inspection_report_versions`(`id`),
  INDEX `idx_ver_session` (`session_id`),
  INDEX `idx_ver_version` (`session_id`, `version_number`),
  UNIQUE KEY `uq_version` (`session_id`, `version_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- QUALITY PLANS (buyer-specific)
-- ========================
CREATE TABLE `quality_plans` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `buyer_id` INT UNSIGNED DEFAULT NULL,
  `template_id` INT UNSIGNED DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  `version` INT NOT NULL DEFAULT 1,
  `aql_level` VARCHAR(10) DEFAULT 'II' COMMENT 'AQL inspection level (I, II, III, S1-S4)',
  `aql_critical` DECIMAL(5,2) DEFAULT 0 COMMENT 'Critical defects AQL',
  `aql_major` DECIMAL(5,2) DEFAULT 2.5 COMMENT 'Major defects AQL',
  `aql_minor` DECIMAL(5,2) DEFAULT 4.0 COMMENT 'Minor defects AQL',
  `critical_defects_limit` INT DEFAULT 0 COMMENT 'Zero tolerance typically',
  `major_defects_limit` INT DEFAULT NULL,
  `minor_defects_limit` INT DEFAULT NULL,
  `rules` JSON DEFAULT NULL COMMENT 'Buyer-specific rules overriding inspection_rules',
  `config` JSON DEFAULT NULL COMMENT 'Plan-level configuration',
  `status` ENUM('draft','active','archived') NOT NULL DEFAULT 'active',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_qp_buyer` (`buyer_id`),
  INDEX `idx_qp_template` (`template_id`),
  INDEX `idx_qp_company` (`company_id`),
  INDEX `idx_qp_status` (`status`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- QUALITY PLAN SECTIONS
-- ========================
CREATE TABLE `quality_plan_sections` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `quality_plan_id` INT UNSIGNED NOT NULL,
  `section_key` VARCHAR(100) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `config` JSON DEFAULT NULL COMMENT 'Section-level overrides',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`quality_plan_id`) REFERENCES `quality_plans`(`id`) ON DELETE CASCADE,
  INDEX `idx_qps_plan` (`quality_plan_id`),
  UNIQUE KEY `uq_qps_plan_key` (`quality_plan_id`, `section_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- MASTER DEFECT LIBRARY
-- ========================
CREATE TABLE `master_defect_library` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `category` ENUM('critical','major','minor') NOT NULL DEFAULT 'major',
  `code` VARCHAR(50) NOT NULL COMMENT 'Defect code (e.g. CRK-01, CLR-02)',
  `description` VARCHAR(500) NOT NULL,
  `detailed_description` TEXT DEFAULT NULL,
  `defect_type` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. sewing, color, sizing, fabric, finishing',
  `inspection_type` ENUM('FRI','DUPRO','fabric','loading','all') NOT NULL DEFAULT 'all',
  `photo_required` TINYINT(1) NOT NULL DEFAULT 0,
  `auto_capa` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Auto-generate CAPA when this defect is recorded',
  `capa_template` TEXT DEFAULT NULL COMMENT 'Template text for CAPA',
  `reference_image_url` VARCHAR(500) DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_mdl_category` (`category`),
  INDEX `idx_mdl_type` (`defect_type`),
  INDEX `idx_mdl_inspection_type` (`inspection_type`),
  INDEX `idx_mdl_company` (`company_id`),
  INDEX `idx_mdl_active` (`is_active`),
  UNIQUE KEY `uq_mdl_code` (`code`, `company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- MEASUREMENT TEMPLATES
-- ========================
CREATE TABLE `measurement_templates` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `inspection_type` ENUM('FRI','DUPRO','fabric','loading','all') NOT NULL DEFAULT 'all',
  `point_name` VARCHAR(255) NOT NULL COMMENT 'Measurement point (e.g. Chest Width, Center Back Length)',
  `tolerance_min` DECIMAL(10,2) DEFAULT NULL COMMENT 'Negative tolerance',
  `tolerance_max` DECIMAL(10,2) DEFAULT NULL COMMENT 'Positive tolerance',
  `unit` VARCHAR(20) DEFAULT 'cm' COMMENT 'cm, inch, mm, g, etc',
  `sort_order` INT NOT NULL DEFAULT 0,
  `category` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. body, sleeve, collar',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_mt_type` (`inspection_type`),
  INDEX `idx_mt_category` (`category`),
  INDEX `idx_mt_company` (`company_id`),
  INDEX `idx_mt_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- PHOTO REQUIREMENTS
-- ========================
CREATE TABLE `photo_requirements` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `template_id` INT UNSIGNED DEFAULT NULL,
  `quality_plan_id` INT UNSIGNED DEFAULT NULL,
  `page_key` VARCHAR(100) NOT NULL,
  `field_key` VARCHAR(100) NOT NULL,
  `label` VARCHAR(255) NOT NULL,
  `min_count` INT NOT NULL DEFAULT 0,
  `max_count` INT DEFAULT NULL,
  `require_annotation` TINYINT(1) NOT NULL DEFAULT 0,
  `require_gps` TINYINT(1) NOT NULL DEFAULT 0,
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_pr_template` (`template_id`),
  INDEX `idx_pr_plan` (`quality_plan_id`),
  INDEX `idx_pr_page` (`page_key`),
  FOREIGN KEY (`template_id`) REFERENCES `inspection_templates`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`quality_plan_id`) REFERENCES `quality_plans`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION PHOTOS (actual captured photos)
-- ========================
CREATE TABLE `inspection_photos` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `page_key` VARCHAR(100) NOT NULL,
  `field_key` VARCHAR(100) NOT NULL,
  `photo_path` VARCHAR(500) NOT NULL,
  `photo_url` VARCHAR(500) DEFAULT NULL,
  `annotation_data` JSON DEFAULT NULL COMMENT 'JSON with annotation coordinates and text',
  `gps_lat` DECIMAL(10,7) DEFAULT NULL,
  `gps_lng` DECIMAL(10,7) DEFAULT NULL,
  `captured_at` TIMESTAMP NULL DEFAULT NULL,
  `file_size` INT DEFAULT NULL COMMENT 'File size in bytes',
  `mime_type` VARCHAR(50) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  INDEX `idx_photo_session` (`session_id`),
  INDEX `idx_photo_page` (`page_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- INSPECTION APPROVALS (multi-level approval trail)
-- ========================
CREATE TABLE `inspection_approvals` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED NOT NULL,
  `approval_level` ENUM('inspector','factory_qa','buyer_qa','regional_manager') NOT NULL,
  `approver_id` INT UNSIGNED DEFAULT NULL,
  `approver_name` VARCHAR(255) DEFAULT NULL,
  `decision` ENUM('approved','rejected','pending','conditional') NOT NULL DEFAULT 'pending',
  `comments` TEXT DEFAULT NULL,
  `signature_id` INT UNSIGNED DEFAULT NULL,
  `decided_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`signature_id`) REFERENCES `inspection_signatures`(`id`),
  INDEX `idx_app_session` (`session_id`),
  INDEX `idx_app_level` (`approval_level`),
  INDEX `idx_app_decision` (`decision`),
  UNIQUE KEY `uq_app_level` (`session_id`, `approval_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- CAPA RECORDS (quality-specific, links to inspection failures)
-- ========================
CREATE TABLE `inspection_capa` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `session_id` INT UNSIGNED DEFAULT NULL,
  `defect_id` INT UNSIGNED DEFAULT NULL COMMENT 'Link to master_defect_library if auto-generated',
  `capa_number` VARCHAR(50) NOT NULL COMMENT 'Auto-generated CAPA reference number',
  `title` VARCHAR(500) NOT NULL,
  `root_cause` TEXT DEFAULT NULL,
  `root_cause_category` VARCHAR(100) DEFAULT NULL COMMENT 'e.g. material, process, training, equipment',
  `corrective_action` TEXT DEFAULT NULL,
  `preventive_action` TEXT DEFAULT NULL,
  `assigned_to` INT UNSIGNED DEFAULT NULL,
  `deadline` DATE DEFAULT NULL,
  `status` ENUM('open','in_progress','verified','closed','cancelled') NOT NULL DEFAULT 'open',
  `verification_method` VARCHAR(255) DEFAULT NULL,
  `verification_result` TEXT DEFAULT NULL,
  `verified_by` INT UNSIGNED DEFAULT NULL,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `re_inspection_session_id` INT UNSIGNED DEFAULT NULL COMMENT 'Link to re-inspection session',
  `company_id` INT UNSIGNED DEFAULT NULL,
  `created_by` INT UNSIGNED DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_capa_session` (`session_id`),
  INDEX `idx_capa_defect` (`defect_id`),
  INDEX `idx_capa_status` (`status`),
  INDEX `idx_capa_assigned` (`assigned_to`),
  INDEX `idx_capa_company` (`company_id`),
  FOREIGN KEY (`session_id`) REFERENCES `inspection_sessions`(`id`),
  FOREIGN KEY (`defect_id`) REFERENCES `master_defect_library`(`id`),
  FOREIGN KEY (`re_inspection_session_id`) REFERENCES `inspection_sessions`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========================
-- QUALITY PERMISSIONS
-- ========================
INSERT IGNORE INTO permissions (code, label, group_name, description) VALUES
('quality.view', 'View Quality Module', 'Quality', 'View quality inspection pages and reports'),
('quality.inspect', 'Perform Inspections', 'Quality', 'Create and submit inspection sessions'),
('quality.approve', 'Approve Inspections', 'Quality', 'Approve or reject inspection reports'),
('quality.manage', 'Manage Quality Config', 'Quality', 'Manage templates, rules, defect libraries');

-- Assign quality permissions to admin role
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT (SELECT id FROM app_roles WHERE name = 'admin'), id FROM permissions
WHERE code LIKE 'quality.%';



-- ============================================================
-- FILE: seed_qc_inspection.sql
-- ============================================================

-- =============================================================
-- FashCognitive Quality OS - Seed Data for FRI Inspections
-- =============================================================
-- This seeds a complete Standard FRI Template with:
--   9 universal pages (overview, measurements, defects, aql_calculator,
--     packaging, photos, signatures, notes, decision)
--   5 FRI-specific pages (carton_verification, labeling,
--     shipment_readiness, accessories, color_check)
--   Master defect library, measurement templates, quality plans, rules
-- =============================================================


-- ========================
-- 1. STANDARD FRI TEMPLATE
-- ========================
INSERT IGNORE INTO `inspection_templates` (`id`, `buyer_id`, `template_type`, `name`, `version`, `status`, `config`, `company_id`, `created_by`)
VALUES (1, NULL, 'FRI', 'Standard FRI Template', 1, 'active',
  '{"auto_save_interval": 30, "gps_required": true, "photo_required": true, "fast_mode_enabled": true}',
  NULL, NULL);

-- ========================
-- 2. PAGES (14 total: 9 universal + 5 FRI-specific)
-- ========================
INSERT IGNORE INTO `inspection_pages` (`id`, `template_id`, `page_key`, `label`, `sort_order`, `is_conditional`, `condition_rule`, `category`, `config`) VALUES

-- === UNIVERSAL PAGES ===
(1, 1, 'overview', 'Overview', 1, 0, NULL, 'universal',
  '{"columns": 2, "show_progress": true}'),

(2, 1, 'measurements', 'Body Measurements', 2, 0, NULL, 'universal',
  '{"columns": 1, "show_tolerances": true, "measurement_template_id": 1}'),

(3, 1, 'defects', 'Defect Recording', 3, 0, NULL, 'universal',
  '{"columns": 1, "show_defect_grid": true, "allow_multi_select": true}'),

(4, 1, 'aql_calculator', 'AQL Calculator', 4, 0, NULL, 'universal',
  '{"columns": 1, "auto_calculate": true, "quality_plan_id": 1}'),

(5, 1, 'packaging', 'Packaging Inspection', 5, 0, NULL, 'universal',
  '{"columns": 2, "require_photo": true}'),

(6, 1, 'photos', 'Photo Evidence', 6, 0, NULL, 'universal',
  '{"columns": 1, "min_photos": 4, "max_photos": 20}'),

(7, 1, 'signatures', 'Digital Signatures', 7, 0, NULL, 'universal',
  '{"columns": 1, "require_inspector_sig": true, "require_factory_sig": false}'),

(8, 1, 'notes', 'Inspector Notes', 8, 0, NULL, 'universal',
  '{"columns": 1}'),

(9, 1, 'decision', 'Final Decision', 9, 0, NULL, 'universal',
  '{"columns": 1, "require_decision_reason": true}'),

-- === FRI-SPECIFIC PAGES ===
(10, 1, 'carton_verification', 'Carton Verification', 10, 0, NULL, 'fri',
  '{"columns": 2, "require_photo": true, "measurement_template_id": 2}'),

(11, 1, 'labeling', 'Labeling Check', 11, 0, NULL, 'fri',
  '{"columns": 2, "require_photo": true}'),

(12, 1, 'shipment_readiness', 'Shipment Readiness', 12, 0, NULL, 'fri',
  '{"columns": 2}'),

(13, 1, 'accessories', 'Accessory Verification', 13, 1,
  '{"field": "style.has_accessories", "operator": "eq", "value": true}', 'fri',
  '{"columns": 2}'),

(14, 1, 'color_check', 'Color Assessment', 14, 0, NULL, 'fri',
  '{"columns": 2, "require_photo": true, "require_color_standard": true}');

-- ========================
-- 3. FIELDS (by page)
-- ========================

-- Page 1: Overview
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(1, 1, 'po_number', 'text', 'PO Number', 1, 1,
  '{"required": true, "min_length": 3, "max_length": 50}',
  NULL, '{"placeholder": "Enter PO number", "help_text": "Purchase Order number from buyer"}'),

(2, 1, 'style_number', 'text', 'Style Number', 1, 2,
  '{"required": true, "min_length": 3, "max_length": 50}',
  NULL, '{"placeholder": "Enter style number"}'),

(3, 1, 'buyer_name', 'text', 'Buyer Name', 1, 3,
  '{"required": true}',
  NULL, '{"placeholder": "Enter buyer name"}'),

(4, 1, 'inspector_name', 'text', 'Inspector Name', 1, 4,
  '{"required": true}',
  NULL, '{"placeholder": "Full name"}'),

(5, 1, 'inspection_date', 'date', 'Inspection Date', 1, 5,
  '{"required": true}',
  NULL, '{"default_value": "today"}'),

(6, 1, 'lot_size', 'number', 'Lot Size', 1, 6,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "pcs", "help_text": "Total quantity in this lot"}'),

(7, 1, 'sample_size', 'number', 'Sample Size', 1, 7,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "pcs", "help_text": "Quantity inspected"}'),

(8, 1, 'factory', 'text', 'Factory Location', 1, 8,
  '{"required": true}',
  NULL, '{"placeholder": "Factory name or code"}'),

(9, 1, 'gps_coordinates', 'text', 'GPS Coordinates', 0, 9,
  NULL, NULL, '{"help_text": "Auto-captured from device", "readonly": true}'),

(10, 1, 'has_accessories', 'select', 'Has Accessories?', 1, 10,
  '{"required": true}',
  '[{"label": "Yes", "value": true}, {"label": "No", "value": false}]',
  '{"help_text": "Determines if accessory page is shown"}'),

(11, 1, 'inspection_type', 'select', 'Inspection Type', 0, 11,
  NULL,
  '[{"label": "FRI - Final Random Inspection", "value": "FRI"}, {"label": "DUPRO - During Production", "value": "DUPRO"}, {"label": "Fabric Inspection", "value": "fabric"}, {"label": "Loading Inspection", "value": "loading"}]',
  '{"default_value": "FRI"}'),

(12, 1, 'divider_1', 'divider', '', 0, 12, NULL, NULL, NULL),

(13, 1, 'additional_notes_overview', 'textarea', 'Additional Notes', 0, 13,
  NULL, NULL, '{"placeholder": "Any additional notes about this inspection...", "rows": 3}');

-- Page 2: Measurements
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(14, 2, 'measurement_info', 'info_text', 'Record body measurements below. Measurements outside tolerance will be flagged automatically.', 0, 0,
  NULL, NULL, NULL),

(15, 2, 'measurement_grid', 'measurement_grid', 'Measurement Points', 1, 1,
  '{"required": true}',
  NULL, '{"measurement_template_id": 1, "show_tolerances": true, "allow_photo_per_point": false}'),

(16, 2, 'measurement_notes', 'textarea', 'Measurement Notes', 0, 2,
  NULL, NULL, '{"placeholder": "Any observations about measurements...", "rows": 2}');

-- Page 3: Defects
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(17, 3, 'defect_info', 'info_text', 'Record all defects found during inspection. Categorize by severity (Critical, Major, Minor).', 0, 0,
  NULL, NULL, NULL),

(18, 3, 'defect_grid', 'defect_grid', 'Defect Log', 1, 1,
  '{"required": true}',
  NULL, '{"show_photo_button": true, "allow_multi_select": true, "defect_sources": ["master_defect_library"]}'),

(19, 3, 'defect_summary', 'info_text', 'Defect summary will be auto-calculated after entry.', 0, 2,
  NULL, NULL, NULL),

(20, 3, 'critical_count', 'number', 'Critical Defects', 0, 3,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}'),

(21, 3, 'major_count', 'number', 'Major Defects', 0, 4,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}'),

(22, 3, 'minor_count', 'number', 'Minor Defects', 0, 5,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}'),

(23, 3, 'total_defects', 'number', 'Total Defects', 0, 6,
  '{"min": 0}',
  NULL, '{"readonly": true, "help_text": "Auto-calculated"}');

-- Page 4: AQL Calculator
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(24, 4, 'aql_info', 'info_text', 'AQL (Acceptable Quality Limit) is calculated based on sample size, lot size, and defect counts.', 0, 0,
  NULL, NULL, NULL),

(25, 4, 'aql_calculator', 'aql_calculator', 'AQL Calculation', 1, 1,
  '{"required": true}',
  NULL, '{"quality_plan_id": 1, "auto_calculate": true, "show_accept_reject": true}'),

(26, 4, 'aql_result', 'select', 'AQL Result', 1, 2,
  '{"required": true}',
  '[{"label": "Accept (Pass)", "value": "accept"}, {"label": "Reject (Fail)", "value": "reject"}]',
  '{"help_text": "Based on AQL calculation"}'),

(27, 4, 'aql_notes', 'textarea', 'AQL Notes', 0, 3,
  NULL, NULL, '{"placeholder": "Notes about AQL calculation...", "rows": 2}');

-- Page 5: Packaging
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(28, 5, 'packaging_info', 'info_text', 'Verify packaging quality, polybag condition, and packing method.', 0, 0,
  NULL, NULL, NULL),

(29, 5, 'packaging_type', 'select', 'Packaging Type', 1, 1,
  '{"required": true}',
  '[{"label": "Polybag", "value": "polybag"}, {"label": "Master Polybag", "value": "master_polybag"}, {"label": "Box", "value": "box"}, {"label": "Hanger Pack", "value": "hanger_pack"}, {"label": "Flat Pack", "value": "flat_pack"}, {"label": "Other", "value": "other"}]',
  NULL),

(30, 5, 'packaging_condition', 'select', 'Packaging Condition', 1, 2,
  '{"required": true}',
  '[{"label": "Excellent", "value": "excellent"}, {"label": "Good", "value": "good"}, {"label": "Fair", "value": "fair"}, {"label": "Poor", "value": "poor"}]',
  NULL),

(31, 5, 'polybag_thickness', 'number', 'Polybag Thickness (microns)', 0, 3,
  '{"min": 10, "max": 200}',
  NULL, '{"unit": "microns"}'),

(32, 5, 'packaging_defects', 'select', 'Packaging Defects Found?', 1, 4,
  '{"required": true}',
  '[{"label": "None", "value": "none"}, {"label": "Torn polybag", "value": "torn"}, {"label": "Wrong size", "value": "wrong_size"}, {"label": "Missing polybag", "value": "missing"}, {"label": "Damaged box", "value": "damaged_box"}, {"label": "Other", "value": "other"}]',
  NULL),

(33, 5, 'packaging_photo', 'photo', 'Packaging Photo', 0, 5,
  NULL, NULL, '{"require_gps": true, "max_files": 3}'),

(34, 5, 'packaging_notes', 'textarea', 'Packaging Notes', 0, 6,
  NULL, NULL, '{"placeholder": "Additional packaging observations...", "rows": 2}');

-- Page 6: Photos
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(35, 6, 'photos_info', 'info_text', 'Attach photo evidence. Minimum 4 photos required (front view, back view, label, defect sample).', 0, 0,
  NULL, NULL, NULL),

(36, 6, 'front_view_photo', 'photo', 'Front View', 1, 1,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_gps": true, "require_annotation": false}'),

(37, 6, 'back_view_photo', 'photo', 'Back View', 1, 2,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_gps": true, "require_annotation": false}'),

(38, 6, 'label_photo', 'photo', 'Label / Care Label Photo', 1, 3,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_annotation": true}'),

(39, 6, 'defect_sample_photo', 'photo', 'Defect Sample Photo', 1, 4,
  '{"required": true, "max_files": 5}',
  NULL, '{"require_annotation": true, "require_gps": false}'),

(40, 6, 'additional_photos', 'photo', 'Additional Photos', 0, 5,
  '{"max_files": 10}',
  NULL, '{"require_gps": false}');

-- Page 7: Signatures
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(41, 7, 'signatures_info', 'info_text', 'Digital signatures confirm inspection completion and acceptance.', 0, 0,
  NULL, NULL, NULL),

(42, 7, 'inspector_signature', 'signature', 'Inspector Signature', 1, 1,
  '{"required": true}',
  NULL, '{"role": "inspector", "pad_height": 200, "pad_width": 500}'),

(43, 7, 'inspector_name_sig', 'text', 'Inspector Name (printed)', 1, 2,
  '{"required": true}',
  NULL, '{"placeholder": "Full name"}'),

(44, 7, 'inspector_title', 'text', 'Inspector Title', 1, 3,
  '{"required": true}',
  NULL, '{"placeholder": "e.g. Quality Inspector"}'),

(45, 7, 'factory_representative_signature', 'signature', 'Factory Representative Signature', 0, 4,
  NULL, NULL, '{"role": "factory_qa", "pad_height": 200, "pad_width": 500}'),

(46, 7, 'factory_rep_name', 'text', 'Factory Representative Name', 0, 5,
  NULL, NULL, '{"placeholder": "Full name"}');

-- Page 8: Notes
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(47, 8, 'notes_info', 'info_text', 'Record any additional observations, comments, or non-conformance details.', 0, 0,
  NULL, NULL, NULL),

(48, 8, 'inspector_comments', 'textarea', 'Inspector Comments', 1, 1,
  '{"required": true, "min_length": 10}',
  NULL, '{"placeholder": "Detailed comments about the inspection findings...", "rows": 5}'),

(49, 8, 'recommendations', 'textarea', 'Recommendations', 0, 2,
  NULL, NULL, '{"placeholder": "Suggestions for improvement...", "rows": 3}'),

(50, 8, 'divider_notes', 'divider', '', 0, 3, NULL, NULL, NULL),

(51, 8, 'rework_required', 'checkbox', 'Rework Required', 0, 4,
  NULL, NULL, '{"help_text": "Check if rework is needed before shipment"}'),

(52, 8, 'rework_details', 'textarea', 'Rework Details', 0, 5,
  NULL, NULL, '{"placeholder": "Describe rework required...", "rows": 2}');

-- Page 9: Decision
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(53, 9, 'decision_info', 'info_text', 'Final inspection decision based on all collected data and AQL results.', 0, 0,
  NULL, NULL, NULL),

(54, 9, 'final_decision', 'select', 'Final Decision', 1, 1,
  '{"required": true}',
  '[{"label": "PASS", "value": "PASS"}, {"label": "Conditional Pass", "value": "CONDITIONAL_PASS"}, {"label": "FAIL", "value": "FAIL"}, {"label": "Re-Inspection Required", "value": "RE_INSPECTION"}, {"label": "On Hold", "value": "HOLD"}]',
  '{"help_text": "Select the final inspection outcome"}'),

(55, 9, 'decision_reason', 'textarea', 'Decision Reason', 1, 2,
  '{"required": true, "min_length": 10}',
  NULL, '{"placeholder": "Explain the rationale for this decision...", "rows": 4}'),

(56, 9, 'next_steps', 'select', 'Recommended Next Steps', 0, 3,
  NULL,
  '[{"label": "Shipment approved", "value": "ship_approved"}, {"label": "Conditional shipment", "value": "conditional_ship"}, {"label": "Hold for re-inspection", "value": "hold_reinspect"}, {"label": "Return to factory", "value": "return_factory"}, {"label": "Initiate CAPA", "value": "initiate_capa"}, {"label": "Escalate to buyer", "value": "escalate_buyer"}]',
  NULL),

(57, 9, 'divider_decision', 'divider', '', 0, 4, NULL, NULL, NULL),

(58, 9, 'final_decision_notes', 'textarea', 'Final Notes', 0, 5,
  NULL, NULL, '{"placeholder": "Any final notes...", "rows": 2}');

-- Page 10: Carton Verification (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(59, 10, 'carton_info', 'info_text', 'Verify carton markings, dimensions, and contents.', 0, 0,
  NULL, NULL, NULL),

(60, 10, 'carton_count', 'number', 'Total Cartons in Lot', 1, 1,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "cartons"}'),

(61, 10, 'cartons_inspected', 'number', 'Cartons Inspected', 1, 2,
  '{"required": true, "min": 1}',
  NULL, '{"unit": "cartons"}'),

(62, 10, 'carton_marking', 'select', 'Carton Marking Correct?', 1, 3,
  '{"required": true}',
  '[{"label": "Correct", "value": "correct"}, {"label": "Minor issues", "value": "minor_issues"}, {"label": "Incorrect", "value": "incorrect"}]',
  NULL),

(63, 10, 'carton_condition', 'select', 'Carton Condition', 1, 4,
  '{"required": true}',
  '[{"label": "Excellent", "value": "excellent"}, {"label": "Good", "value": "good"}, {"label": "Damaged", "value": "damaged"}]',
  NULL),

(64, 10, 'carton_quantity_accuracy', 'select', 'Quantity per Carton Accurate?', 1, 5,
  '{"required": true}',
  '[{"label": "Yes - all correct", "value": "correct"}, {"label": "Minor discrepancies", "value": "minor"}, {"label": "Major discrepancies", "value": "major"}]',
  NULL),

(65, 10, 'carton_measurements', 'measurement_grid', 'Carton Measurements', 0, 6,
  NULL, NULL, '{"measurement_template_id": 2, "show_tolerances": true}'),

(66, 10, 'carton_photo', 'photo', 'Carton Photo', 0, 7,
  NULL, NULL, '{"max_files": 3, "require_annotation": true}'),

(67, 10, 'carton_notes', 'textarea', 'Carton Notes', 0, 8,
  NULL, NULL, '{"placeholder": "Carton verification notes...", "rows": 2}');

-- Page 11: Labeling Check (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(68, 11, 'labeling_info', 'info_text', 'Verify all labels including hang tags, care labels, price tags, and barcodes.', 0, 0,
  NULL, NULL, NULL),

(69, 11, 'hang_tag_present', 'checkbox', 'Hang Tag Present', 1, 1,
  '{"required": true}',
  NULL, NULL),

(70, 11, 'hang_tag_correct', 'select', 'Hang Tag Information Correct?', 1, 2,
  '{"required": true}',
  '[{"label": "Correct", "value": "correct"}, {"label": "Incorrect", "value": "incorrect"}, {"label": "N/A", "value": "na"}]',
  NULL),

(71, 11, 'care_label_present', 'checkbox', 'Care Label Present', 1, 3,
  '{"required": true}',
  NULL, NULL),

(72, 11, 'care_label_correct', 'select', 'Care Label Information Correct?', 1, 4,
  '{"required": true}',
  '[{"label": "Correct", "value": "correct"}, {"label": "Incorrect", "value": "incorrect"}, {"label": "N/A", "value": "na"}]',
  NULL),

(73, 11, 'price_tag_present', 'checkbox', 'Price Tag Present (if required)', 0, 5,
  NULL, NULL, NULL),

(74, 11, 'barcode_verified', 'select', 'Barcode / QR Verified?', 1, 6,
  '{"required": true}',
  '[{"label": "Verified - correct", "value": "verified"}, {"label": "Not readable", "value": "not_readable"}, {"label": "Wrong code", "value": "wrong_code"}, {"label": "N/A", "value": "na"}]',
  NULL),

(75, 11, 'label_photo', 'photo', 'Label Photo Evidence', 0, 7,
  NULL, NULL, '{"max_files": 3}'),

(76, 11, 'labeling_notes', 'textarea', 'Labeling Notes', 0, 8,
  NULL, NULL, '{"placeholder": "Labeling observations...", "rows": 2}');

-- Page 12: Shipment Readiness (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(77, 12, 'shipment_info', 'info_text', 'Verify overall shipment readiness before release.', 0, 0,
  NULL, NULL, NULL),

(78, 12, 'shipment_ready', 'select', 'Shipment Ready?', 1, 1,
  '{"required": true}',
  '[{"label": "Yes - ready to ship", "value": "ready"}, {"label": "Conditional - minor issues", "value": "conditional"}, {"label": "No - hold shipment", "value": "hold"}]',
  NULL),

(79, 12, 'quantity_confirmed', 'checkbox', 'Total Quantity Confirmed', 1, 2,
  '{"required": true}',
  NULL, '{"help_text": "Confirm total quantity matches PO"}'),

(80, 12, 'ex_factory_date', 'date', 'Ex-Factory Date', 0, 3,
  NULL, NULL, NULL),

(81, 12, 'etd_date', 'date', 'Estimated Departure Date', 0, 4,
  NULL, NULL, NULL),

(82, 12, 'shipping_method', 'select', 'Shipping Method', 0, 5,
  NULL,
  '[{"label": "Sea Freight", "value": "sea"}, {"label": "Air Freight", "value": "air"}, {"label": "Rail", "value": "rail"}, {"label": "Courier", "value": "courier"}]',
  NULL),

(83, 12, 'documents_ready', 'checkbox', 'Export Documents Ready', 0, 6,
  NULL, NULL, NULL),

(84, 12, 'shipment_notes', 'textarea', 'Shipment Notes', 0, 7,
  NULL, NULL, '{"placeholder": "Additional shipment readiness notes...", "rows": 2}');

-- Page 13: Accessory Verification (FRI - conditional)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(85, 13, 'accessory_info', 'info_text', 'Verify all accessories including buttons, zippers, thread, trims, etc.', 0, 0,
  NULL, NULL, NULL),

(86, 13, 'accessory_type', 'multi_select', 'Accessories Verified', 1, 1,
  '{"required": true}',
  '[{"label": "Buttons", "value": "buttons"}, {"label": "Zippers", "value": "zippers"}, {"label": "Thread", "value": "thread"}, {"label": "Elastic", "value": "elastic"}, {"label": "Lining", "value": "lining"}, {"label": "Interlining", "value": "interlining"}, {"label": "Rib/Knit trim", "value": "rib_trim"}, {"label": "Drawstrings", "value": "drawstrings"}, {"label": "Labels (woven)", "value": "woven_labels"}, {"label": "Rivets", "value": "rivets"}, {"label": "Snaps", "value": "snaps"}, {"label": "Other", "value": "other"}]',
  NULL),

(87, 13, 'accessory_quality', 'select', 'Accessory Quality', 1, 2,
  '{"required": true}',
  '[{"label": "All good", "value": "good"}, {"label": "Minor issues", "value": "minor"}, {"label": "Major defects", "value": "major"}]',
  NULL),

(88, 13, 'accessory_photo', 'photo', 'Accessory Photo', 0, 3,
  NULL, NULL, '{"max_files": 3}'),

(89, 13, 'accessory_notes', 'textarea', 'Accessory Notes', 0, 4,
  NULL, NULL, '{"placeholder": "Accessory verification notes...", "rows": 2}');

-- Page 14: Color Assessment (FRI)
INSERT IGNORE INTO `inspection_fields` (`id`, `page_id`, `field_key`, `field_type`, `label`, `is_required`, `sort_order`, `validation_rules`, `options`, `config`) VALUES

(90, 14, 'color_info', 'info_text', 'Assess color against approved standard / swatch.', 0, 0,
  NULL, NULL, NULL),

(91, 14, 'color_standard_ref', 'text', 'Color Standard Reference', 1, 1,
  '{"required": true}',
  NULL, '{"placeholder": "e.g. PMS 19-4023 TPX, or swatch number"}'),

(92, 14, 'color_match_result', 'select', 'Color Match Result', 1, 2,
  '{"required": true}',
  '[{"label": "Match - acceptable", "value": "match"}, {"label": "Slight variation - acceptable", "value": "slight_variation"}, {"label": "Significant variation - reject", "value": "reject"}]',
  NULL),

(93, 14, 'color_dE_value', 'number', 'Delta E (dE) Value', 0, 3,
  '{"min": 0, "max": 10}',
  NULL, '{"help_text": "If spectrophotometer reading available", "unit": "dE"}'),

(94, 14, 'color_consistency', 'select', 'Color Consistency Across Pieces', 0, 4,
  NULL,
  '[{"label": "Consistent", "value": "consistent"}, {"label": "Minor variation", "value": "minor_variation"}, {"label": "Inconsistent", "value": "inconsistent"}]',
  NULL),

(95, 14, 'color_standard_photo', 'photo', 'Color Standard Photo', 1, 5,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_annotation": true, "help_text": "Photo of approved color standard"}'),

(96, 14, 'color_sample_photo', 'photo', 'Production Sample Photo', 1, 6,
  '{"required": true, "max_files": 2}',
  NULL, '{"require_annotation": true, "help_text": "Photo of actual production piece"}'),

(97, 14, 'color_notes', 'textarea', 'Color Assessment Notes', 0, 7,
  NULL, NULL, '{"placeholder": "Color observations...", "rows": 2}');

-- ========================
-- 4. MASTER DEFECT LIBRARY
-- ========================
INSERT IGNORE INTO `master_defect_library` (`id`, `category`, `code`, `description`, `detailed_description`, `defect_type`, `inspection_type`, `photo_required`, `auto_capa`, `capa_template`, `is_active`) VALUES

-- Critical defects
(1, 'critical', 'CRT-01', 'Sharp needle in garment', 'Metal needle fragment found in finished garment - safety hazard', 'sewing', 'FRI', 1, 1, 'Immediate containment of all affected lot. 100% needle inspection required. Root cause analysis for needle breakage protocol.', 1),
(2, 'critical', 'CRT-02', 'Chemical smell / off-gassing', 'Strong chemical odor indicating residual formaldehyde or other chemicals', 'finishing', 'FRI', 0, 1, 'Hold shipment. Conduct chemical testing. Identify source of chemical contamination.', 1),
(3, 'critical', 'CRT-03', 'Wrong fabric composition', 'Fabric composition does not match spec (e.g. 100% cotton instead of 65/35 poly-cotton)', 'fabric', 'FRI', 1, 1, 'Hold all production. Verify fabric rolls. Notify buyer and supplier.', 1),
(4, 'critical', 'CRT-04', 'Missing safety component', 'Missing required safety component (e.g. child safety lock, reflective strip)', 'accessory', 'FRI', 1, 1, 'Stop production. Identify missing components. Implement corrective action.', 1),

-- Major defects
(5, 'major', 'MAJ-01', 'Open seam', 'Seam open more than 1cm exposing raw edge', 'sewing', 'FRI', 0, 0, NULL, 1),
(6, 'major', 'MAJ-02', 'Color shading within lot', 'Significant color variation between pieces in same lot', 'color', 'FRI', 1, 0, NULL, 1),
(7, 'major', 'MAJ-03', 'Broken stitch', 'Stitch breakage longer than 1.5cm', 'sewing', 'FRI', 0, 0, NULL, 1),
(8, 'major', 'MAJ-04', 'Incorrect label', 'Wrong care label, size label, or hang tag attached', 'labeling', 'FRI', 1, 0, NULL, 1),
(9, 'major', 'MAJ-05', 'Zipper malfunction', 'Zipper does not open/close properly or is missing teeth', 'sewing', 'FRI', 0, 0, NULL, 1),
(10, 'major', 'MAJ-06', 'Measurement out of tolerance', 'Key measurement point exceeds allowed tolerance', 'sizing', 'FRI', 0, 0, NULL, 1),
(11, 'major', 'MAJ-07', 'Contamination / stains', 'Visible dirt, oil stains, or foreign material on garment', 'finishing', 'FRI', 1, 0, NULL, 1),
(12, 'major', 'MAJ-08', 'Skipped stitch', 'Stitch skipped over 2cm length', 'sewing', 'FRI', 0, 0, NULL, 1),

-- Minor defects
(13, 'minor', 'MIN-01', 'Loose thread ends', 'Uncut thread ends longer than 2cm', 'sewing', 'FRI', 0, 0, NULL, 1),
(14, 'minor', 'MIN-02', 'Slight color variation', 'Minor color difference within acceptable range', 'color', 'FRI', 0, 0, NULL, 1),
(15, 'minor', 'MIN-03', 'Uneven stitch length', 'Stitch length variation outside spec', 'sewing', 'FRI', 0, 0, NULL, 1),
(16, 'minor', 'MIN-04', 'Slight fabric defect', 'Small slub, nep, or yarn irregularity less than 1cm', 'fabric', 'FRI', 0, 0, NULL, 1),
(17, 'minor', 'MIN-05', 'Crooked button', 'Button sewn slightly off alignment', 'sewing', 'FRI', 0, 0, NULL, 1),
(18, 'minor', 'MIN-06', 'Puckered seam', 'Slight seam puckering not affecting garment appearance significantly', 'sewing', 'FRI', 0, 0, NULL, 1),
(19, 'minor', 'MIN-07', 'Pilling', 'Minor surface pilling affecting less than 5% of area', 'fabric', 'FRI', 0, 0, NULL, 1),
(20, 'minor', 'MIN-08', 'Packaging crease', 'Minor crease from packaging that will resolve', 'packaging', 'FRI', 0, 0, NULL, 1);

-- ========================
-- 5. MEASUREMENT TEMPLATES
-- ========================

-- Template 1: Body Measurements (for apparel)
INSERT IGNORE INTO `measurement_templates` (`id`, `inspection_type`, `point_name`, `tolerance_min`, `tolerance_max`, `unit`, `sort_order`, `category`, `is_active`) VALUES
(1, 'FRI', 'Chest Width (1\" below armhole)', -1.00, 1.00, 'cm', 1, 'body', 1),
(2, 'FRI', 'Waist Width (at narrowest)', -1.00, 1.00, 'cm', 2, 'body', 1),
(3, 'FRI', 'Bottom Opening', -1.00, 1.00, 'cm', 3, 'body', 1),
(4, 'FRI', 'Center Back Length (from HPS)', -1.50, 1.50, 'cm', 4, 'body', 1),
(5, 'FRI', 'Shoulder Width (across)', -0.50, 0.50, 'cm', 5, 'body', 1),
(6, 'FRI', 'Sleeve Length (from shoulder)', -1.00, 1.00, 'cm', 6, 'sleeve', 1),
(7, 'FRI', 'Sleeve Opening', -0.50, 0.50, 'cm', 7, 'sleeve', 1),
(8, 'FRI', 'Armhole Curved', -0.50, 0.50, 'cm', 8, 'sleeve', 1),
(9, 'FRI', 'Neck Width (seam to seam)', -0.50, 0.50, 'cm', 9, 'collar', 1),
(10, 'FRI', 'Neck Depth (from HPS)', -0.50, 0.50, 'cm', 10, 'collar', 1),
(11, 'FRI', 'Collar Height (at CB)', -0.30, 0.30, 'cm', 11, 'collar', 1),
(12, 'FRI', 'Cross Back (15cm from HPS)', -1.00, 1.00, 'cm', 12, 'body', 1);

-- Template 2: Carton Measurements
INSERT IGNORE INTO `measurement_templates` (`id`, `inspection_type`, `point_name`, `tolerance_min`, `tolerance_max`, `unit`, `sort_order`, `category`, `is_active`) VALUES
(13, 'FRI', 'Carton Length (external)', -0.50, 0.50, 'cm', 1, 'carton', 1),
(14, 'FRI', 'Carton Width (external)', -0.50, 0.50, 'cm', 2, 'carton', 1),
(15, 'FRI', 'Carton Height (external)', -0.50, 0.50, 'cm', 3, 'carton', 1),
(16, 'FRI', 'Carton Gross Weight', -0.20, 0.20, 'kg', 4, 'carton', 1);

-- ========================
-- 6. INSPECTION RULES
-- ========================
INSERT IGNORE INTO `inspection_rules` (`id`, `template_id`, `priority`, `condition_expression`, `actions`, `label`, `is_active`) VALUES

-- Auto-FAIL if any critical defects
(1, 1, 1, 'defects.critical_count > 0',
  '["set_status:FAIL", "require_manager_approval", "trigger_CAPA", "set_decision:FAIL"]',
  'Critical defect found - auto fail', 1),

-- Fail if major defects exceed limit (based on AQL)
(2, 1, 2, 'defects.major_count > aql_calculator.major_allowable',
  '["set_status:FAIL", "require_manager_approval", "set_decision:FAIL"]',
  'Major defects exceed AQL limit', 1),

-- Conditional pass if minor defects exceed limit
(3, 1, 3, 'defects.minor_count > aql_calculator.minor_allowable AND defects.total_defects > 0',
  '["set_status:CONDITIONAL_PASS", "require_manager_approval", "set_decision:CONDITIONAL_PASS"]',
  'Minor defects exceed limit - conditional pass', 1),

-- Auto-pass if no defects
(4, 1, 4, 'defects.total_defects = 0 AND aql_calculator.aql_result = "accept"',
  '["set_status:PASS", "set_decision:PASS"]',
  'Clean inspection - auto pass', 1),

-- Trigger CAPA for measurement failures
(5, 1, 5, 'measurements.failure_count > 3',
  '["trigger_CAPA", "set_decision:HOLD", "require_manager_approval"]',
  'Multiple measurement failures', 1),

-- Require re-inspection for major packaging defects
(6, 1, 6, 'packaging.packaging_defects != "none" AND packaging.packaging_defects != "minor"',
  '["set_decision:RE_INSPECTION", "set_status:FAIL"]',
  'Packaging issues require re-inspection', 1),

-- Conditional pass for labeling issues
(7, 1, 7, 'labeling.barcode_verified = "not_readable" OR labeling.barcode_verified = "wrong_code"',
  '["set_decision:CONDITIONAL_PASS", "require_manager_approval", "trigger_CAPA"]',
  'Label issues detected', 1),

-- Color reject triggers CAPA
(8, 1, 8, 'color_check.color_match_result = "reject"',
  '["set_decision:FAIL", "trigger_CAPA", "require_manager_approval"]',
  'Color rejection - auto CAPA', 1),

-- Auto-CAPA for certain critical defects
(9, 1, 9, 'defects.critical_defect_codes CONTAINS "CRT-01" OR defects.critical_defect_codes CONTAINS "CRT-02"',
  '["trigger_CAPA", "set_status:FAIL", "set_decision:FAIL", "require_regional_approval"]',
  'Safety-critical defect requires regional manager approval', 1);

-- ========================
-- 7. QUALITY PLAN
-- ========================
INSERT IGNORE INTO `quality_plans` (`id`, `buyer_id`, `template_id`, `name`, `version`, `aql_level`, `aql_critical`, `aql_major`, `aql_minor`, `critical_defects_limit`, `major_defects_limit`, `minor_defects_limit`, `rules`, `config`, `status`) VALUES
(1, NULL, 1, 'Standard AQL Plan (Level II)', 1, 'II', 0, 2.5, 4.0, 0, NULL, NULL,
  '{"auto_calculate": true, "inspection_level": "normal", "lot_size_table": "standard"}',
  '{"default_sample_size_code": "M", "use_reduced_inspection": false, "use_tightened_inspection": false}',
  'active');

-- ========================
-- 8. SAMPLE INSPECTORS
-- ========================
INSERT IGNORE INTO `quality_inspectors` (`id`, `name`, `employee_code`, `designation`, `certification`, `status`) VALUES
(1, 'Rajesh Kumar', 'QC-001', 'Senior Quality Inspector', 'AQL Level II Certified, Six Sigma Green Belt', 'active'),
(2, 'Priya Sharma', 'QC-002', 'Quality Inspector', 'AQL Level II Certified', 'active'),
(3, 'Ahmed Hassan', 'QC-003', 'Junior Quality Inspector', 'In Training', 'active'),
(4, 'Maria Santos', 'QC-004', 'Quality Supervisor', 'AQL Level III Certified, Six Sigma Black Belt', 'active');



-- ============================================================
-- FILE: migration_capa_advanced.sql
-- ============================================================

-- FashCognitive Quality OS - Advanced CAPA Management Schema
-- Run AFTER migration_qc_inspection.sql
-- Implements closed-loop CAPA with RCA, actions, verification, and knowledge base


-- ========================
-- EXTEND inspection_capa with advanced fields
-- ========================
ALTER TABLE `inspection_capa`
  ADD COLUMN `department` VARCHAR(100) DEFAULT NULL COMMENT 'Department responsible' AFTER `title`,
  ADD COLUMN `severity` ENUM('critical','major','minor') NOT NULL DEFAULT 'major' AFTER `department`,
  ADD COLUMN `source` VARCHAR(100) DEFAULT NULL COMMENT 'CAPA trigger source e.g. inspection, complaint, audit' AFTER `severity`,
  ADD COLUMN `containment_action` TEXT DEFAULT NULL AFTER `preventive_action`,
  ADD COLUMN `containment_responsible` VARCHAR(255) DEFAULT NULL AFTER `containment_action`,
  ADD COLUMN `containment_deadline` DATE DEFAULT NULL AFTER `containment_responsible`,
  ADD COLUMN `containment_completed_at` TIMESTAMP NULL DEFAULT NULL AFTER `containment_deadline`,
  ADD COLUMN `effectiveness_rating` ENUM('effective','partial','ineffective') DEFAULT NULL AFTER `re_inspection_session_id`,
  ADD COLUMN `effectiveness_review_date` DATE DEFAULT NULL AFTER `effectiveness_rating`,
  ADD COLUMN `closure_notes` TEXT DEFAULT NULL AFTER `effectiveness_review_date`,
  ADD COLUMN `lessons_learned` TEXT DEFAULT NULL AFTER `closure_notes`,
  ADD COLUMN `approved_by` INT UNSIGNED DEFAULT NULL AFTER `lessons_learned`,
  ADD COLUMN `approved_at` TIMESTAMP NULL DEFAULT NULL AFTER `approved_by`;

-- ========================
-- CAPA ROOT CAUSES (supports multiple RCA methods per CAPA)
-- ========================
