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

