SET FOREIGN_KEY_CHECKS = 0;

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,
  `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;

INSERT IGNORE INTO `master_size_series` (`id`, `company_id`, `name`, `description`, `status`) VALUES
(1, 1, 'S/M/L/XL', 'Standard small to extra large', 'active'),
(2, 1, 'S/M/L', 'Standard small to large', 'active'),
(3, 1, '30/32/34', 'Waist sizes 30 to 34', 'active'),
(4, 1, '38/40/42/44', 'European sizes 38 to 44', 'active'),
(5, 1, 'XS/S/M', '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,
  `display_order` int unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `size_series_id` (`size_series_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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);

DROP TABLE IF EXISTS `master_buyer_departments`;

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;

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 '',
  `quantity` int unsigned NOT NULL DEFAULT 0,
  `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_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 `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',
  `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_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',
  `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;

SET FOREIGN_KEY_CHECKS = 1;
