-- =============================================================
-- PrintPro Sublimation Shop — Database Schema
-- MySQL 8 | Charset: utf8mb4 | Collation: utf8mb4_unicode_ci
-- =============================================================


-- -------------------------------------------------------------
-- DATABASE
-- -------------------------------------------------------------
CREATE DATABASE IF NOT EXISTS `printpro_db`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `printpro_db`;

-- =============================================================
-- TABLE 2: customers
-- =============================================================
CREATE TABLE `customers` (
  `id`             INT           NOT NULL AUTO_INCREMENT,
  `account_number` VARCHAR(20)   NULL     DEFAULT NULL,
  `name`           VARCHAR(150)  NOT NULL,
  `contact`        VARCHAR(20)   NULL,
  `email`          VARCHAR(150)  NULL,
  `address`        TEXT          NULL,
  `business_name`  VARCHAR(150)  NULL,
  `type`           ENUM('walk_in','regular','corporate','reseller') NOT NULL DEFAULT 'walk_in',
  `notes`          TEXT          NULL,
  `created_at`     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_customers_account_number` (`account_number`),
  KEY `idx_customers_type`    (`type`),
  KEY `idx_customers_contact` (`contact`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 3: job_orders
-- =============================================================
CREATE TABLE `job_orders` (
  `id`                  INT            NOT NULL AUTO_INCREMENT,
  `jo_number`           VARCHAR(20)    NOT NULL,
  `customer_id`         INT            NOT NULL,
  `encoder_id`          INT            NOT NULL,
  `product_type`        VARCHAR(100)   NOT NULL,
  `quantity`            INT            NOT NULL,
  `size`                VARCHAR(100)   NULL,
  `design_instructions` TEXT           NULL,
  `deadline`            DATE           NULL,
  `status`              ENUM('pending','processing','finished','materials_check','releasing','completed')
                        NOT NULL DEFAULT 'pending',
  `total_amount`        DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `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_job_orders_jo_number` (`jo_number`),
  KEY `idx_jo_status`      (`status`),
  KEY `idx_jo_customer_id` (`customer_id`),
  KEY `idx_jo_encoder_id`  (`encoder_id`),
  KEY `idx_jo_created_at`  (`created_at`),
  KEY `idx_jo_deadline`    (`deadline`),
  CONSTRAINT `fk_jo_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
  CONSTRAINT `fk_jo_encoder`  FOREIGN KEY (`encoder_id`)  REFERENCES `users`     (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 4: payments
-- =============================================================
CREATE TABLE `payments` (
  `id`             INT            NOT NULL AUTO_INCREMENT,
  `jo_id`          INT            NOT NULL,
  `cashier_id`     INT            NOT NULL,
  `amount`         DECIMAL(10,2)  NOT NULL,
  `payment_type`   ENUM('partial','balance','full') NOT NULL,
  `or_number`      VARCHAR(20)    NULL,
  `payment_method` ENUM('cash','gcash','maya') NOT NULL DEFAULT 'cash',
  `paid_at`        TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_payments_jo_id`      (`jo_id`),
  KEY `idx_payments_cashier_id` (`cashier_id`),
  KEY `idx_payments_paid_at`    (`paid_at`),
  KEY `idx_payments_or_number`  (`or_number`),
  CONSTRAINT `fk_payments_jo`      FOREIGN KEY (`jo_id`)      REFERENCES `job_orders` (`id`),
  CONSTRAINT `fk_payments_cashier` FOREIGN KEY (`cashier_id`) REFERENCES `users`      (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 5: inventory
-- =============================================================
CREATE TABLE `inventory` (
  `id`            INT            NOT NULL AUTO_INCREMENT,
  `item_name`     VARCHAR(150)   NOT NULL,
  `category`      VARCHAR(100)   NOT NULL,
  `quantity`      DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `unit`          VARCHAR(50)    NOT NULL,
  `reorder_level` DECIMAL(10,2)  NOT NULL DEFAULT 0.00,
  `is_active`     TINYINT(1)     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 `idx_inventory_category`  (`category`),
  KEY `idx_inventory_is_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 6: product_bom (Bill of Materials per product type)
-- =============================================================
CREATE TABLE `product_bom` (
  `id`            INT            NOT NULL AUTO_INCREMENT,
  `product_type`  VARCHAR(100)   NOT NULL,
  `inventory_id`  INT            NOT NULL,
  `suggested_qty` DECIMAL(10,2)  NOT NULL,
  `per_unit`      TINYINT(1)     NOT NULL DEFAULT 1,
  `notes`         VARCHAR(255)   NULL,
  PRIMARY KEY (`id`),
  KEY `idx_bom_product_type`  (`product_type`),
  KEY `idx_bom_inventory_id`  (`inventory_id`),
  CONSTRAINT `fk_bom_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 7: jo_materials_used
-- =============================================================
CREATE TABLE `jo_materials_used` (
  `id`           INT            NOT NULL AUTO_INCREMENT,
  `jo_id`        INT            NOT NULL,
  `inventory_id` INT            NOT NULL,
  `qty_used`     DECIMAL(10,2)  NOT NULL,
  `submitted_by` INT            NOT NULL,
  `submitted_at` TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes`        TEXT           NULL,
  PRIMARY KEY (`id`),
  KEY `idx_jmu_jo_id`        (`jo_id`),
  KEY `idx_jmu_inventory_id` (`inventory_id`),
  KEY `idx_jmu_submitted_by` (`submitted_by`),
  CONSTRAINT `fk_jmu_jo`        FOREIGN KEY (`jo_id`)        REFERENCES `job_orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_jmu_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory`  (`id`),
  CONSTRAINT `fk_jmu_submitted` FOREIGN KEY (`submitted_by`) REFERENCES `users`      (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 8: jo_materials_verification
-- =============================================================
CREATE TABLE `jo_materials_verification` (
  `id`               INT       NOT NULL AUTO_INCREMENT,
  `jo_id`            INT       NOT NULL,
  `submitted_by`     INT       NOT NULL,
  `submitted_at`     TIMESTAMP NULL DEFAULT NULL,
  `reviewed_by`      INT       NULL DEFAULT NULL,
  `reviewed_at`      TIMESTAMP NULL DEFAULT NULL,
  `status`           ENUM('draft','pending','approved','rejected') NOT NULL DEFAULT 'draft',
  `rejection_reason` TEXT      NULL,
  `overall_notes`    TEXT      NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_jmv_jo_id` (`jo_id`),
  KEY `idx_jmv_status`       (`status`),
  KEY `idx_jmv_submitted_by` (`submitted_by`),
  KEY `idx_jmv_reviewed_by`  (`reviewed_by`),
  CONSTRAINT `fk_jmv_jo`        FOREIGN KEY (`jo_id`)        REFERENCES `job_orders` (`id`),
  CONSTRAINT `fk_jmv_submitted` FOREIGN KEY (`submitted_by`) REFERENCES `users`      (`id`),
  CONSTRAINT `fk_jmv_reviewed`  FOREIGN KEY (`reviewed_by`)  REFERENCES `users`      (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 9: inventory_log
-- =============================================================
CREATE TABLE `inventory_log` (
  `id`           INT            NOT NULL AUTO_INCREMENT,
  `inventory_id` INT            NOT NULL,
  `jo_id`        INT            NULL DEFAULT NULL,
  `action`       ENUM('deduct','restock','adjustment','over_deducted') NOT NULL,
  `qty_changed`  DECIMAL(10,2)  NOT NULL,
  `qty_before`   DECIMAL(10,2)  NOT NULL,
  `qty_after`    DECIMAL(10,2)  NOT NULL,
  `performed_by` INT            NOT NULL,
  `performed_at` TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notes`        TEXT           NULL,
  PRIMARY KEY (`id`),
  KEY `idx_invlog_inventory_id` (`inventory_id`),
  KEY `idx_invlog_jo_id`        (`jo_id`),
  KEY `idx_invlog_action`       (`action`),
  KEY `idx_invlog_performed_at` (`performed_at`),
  CONSTRAINT `fk_invlog_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory`  (`id`),
  CONSTRAINT `fk_invlog_jo`        FOREIGN KEY (`jo_id`)        REFERENCES `job_orders` (`id`),
  CONSTRAINT `fk_invlog_performed` FOREIGN KEY (`performed_by`) REFERENCES `users`      (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- TABLE 10: activity_log
-- =============================================================
CREATE TABLE `activity_log` (
  `id`           INT          NOT NULL AUTO_INCREMENT,
  `user_id`      INT          NOT NULL,
  `action`       VARCHAR(255) NOT NULL,
  `module`       VARCHAR(100) NOT NULL,
  `reference_id` INT          NULL DEFAULT NULL,
  `details`      TEXT         NULL,
  `created_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_actlog_user_id`    (`user_id`),
  KEY `idx_actlog_module`     (`module`),
  KEY `idx_actlog_created_at` (`created_at`),
  CONSTRAINT `fk_actlog_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================
-- SAMPLE DATA — SECTION B: Customers (10 records)
-- Mix of walk-in and registered types
-- =============================================================
INSERT INTO `customers` (`account_number`, `name`, `contact`, `email`, `address`, `business_name`, `type`, `notes`) VALUES
-- Walk-in customers (no account number)
(NULL, 'Maria Santos',        '09171234567', NULL,                       'Poblacion, General Santos City', NULL,                        'walk_in',   NULL),
(NULL, 'Juan dela Cruz',      '09281234567', NULL,                       'Lagao, General Santos City',     NULL,                        'walk_in',   NULL),
(NULL, 'Liza Macapagal',      '09391234567', 'liza@email.com',           'Dadiangas North, GenSan',        NULL,                        'walk_in',   'Prefers matte finish'),
-- Registered regular
('ACC-2024-00001', 'Rolando Aquino',      '09451234567', 'rolando@email.com',        'Bula, General Santos City',      NULL,                        'regular',   'Repeat customer for shirts'),
('ACC-2024-00002', 'Sunshine Florido',    '09561234567', 'sunshine@florido.com',     'Fatima, General Santos City',    NULL,                        'regular',   NULL),
-- Corporate
('ACC-2024-00003', 'GenSan Trading Corp', '09671234567', 'orders@gensantrading.com', 'Makar, General Santos City',     'GenSan Trading Corporation','corporate', 'Net 30 terms. Invoice required.'),
('ACC-2024-00004', 'Kalibo Events PH',    '09781234567', 'events@kalibo.ph',         'Buayan, General Santos City',    'Kalibo Events Philippines', 'corporate', 'Bulk orders. Requires claim slip copy.'),
-- Reseller
('ACC-2024-00005', 'Jomar Dela Peña',     '09891234567', 'jomar.reseller@gmail.com', 'Apopong, General Santos City',   'Jomar Prints & Promos',     'reseller',  '15% reseller discount applied'),
('ACC-2024-00006', 'Cherry Tagle',        '09121234567', 'cherry@tagleprints.com',   'San Isidro, General Santos City','Tagle Prints & Gifts',      'reseller',  'Handles Sarangani area orders'),
-- Walk-in with email
(NULL, 'Pedro Alvarado',      '09231234567', 'pedro@alvarado.com',       'Calumpang, General Santos City', NULL,                        'walk_in',   NULL);

-- =============================================================
-- SAMPLE DATA — SECTION C: Inventory (12 items)
-- =============================================================
INSERT INTO `inventory` (`item_name`, `category`, `quantity`, `unit`, `reorder_level`) VALUES
-- Blank Substrates
('Blank Sublimation Shirt (White)',  'Blank Items', 150.00, 'pcs',    20.00),  -- id 1
('Blank Ceramic Mug (11oz)',         'Blank Items',  80.00, 'pcs',    15.00),  -- id 2
('Blank Tote Bag (Canvas)',          'Blank Items',  60.00, 'pcs',    10.00),  -- id 3
('Blank ID Lace (plain)',            'Blank Items', 200.00, 'pcs',    30.00),  -- id 4
('Tarpaulin Material',              'Substrate',   500.00, 'sqft',   50.00),  -- id 5
('Vinyl Sticker Sheet A4',          'Substrate',   120.00, 'sheets',  20.00), -- id 6
-- Ink (per ml)
('Sublimation Ink — Cyan',          'Ink',         800.00, 'ml',    100.00),  -- id 7
('Sublimation Ink — Magenta',       'Ink',         800.00, 'ml',    100.00),  -- id 8
('Sublimation Ink — Yellow',        'Ink',         800.00, 'ml',    100.00),  -- id 9
('Sublimation Ink — Black',         'Ink',         800.00, 'ml',    100.00),  -- id 10
-- Supplies / Consumables
('Sublimation Paper A3',            'Supplies',    300.00, 'sheets',  30.00), -- id 11
('Heat Tape',                       'Supplies',   5000.00, 'cm',    200.00),  -- id 12
('Sublimation Paper A4',            'Supplies',    250.00, 'sheets',  25.00), -- id 13
('Laminate Sheet A4',               'Supplies',     80.00, 'sheets',  15.00), -- id 14
('Grommet',                         'Supplies',    400.00, 'pcs',    50.00),  -- id 15
('Buckle Clip (ID Lace)',           'Supplies',    300.00, 'pcs',    40.00);  -- id 16

-- =============================================================
-- SAMPLE DATA — SECTION D: Product BOM
-- product_type values must match job_orders.product_type options
-- per_unit=1 means qty is multiplied by job order quantity
-- =============================================================

-- ---- Sublimation Shirt ----
INSERT INTO `product_bom` (`product_type`, `inventory_id`, `suggested_qty`, `per_unit`, `notes`) VALUES
('Sublimation Shirt', 1,  1.00, 1, '1 blank shirt per unit'),
('Sublimation Shirt', 11, 2.00, 1, '2 sheets A3 paper per unit'),
('Sublimation Shirt', 7,  3.00, 1, '3ml Cyan ink per unit'),
('Sublimation Shirt', 8,  3.00, 1, '3ml Magenta ink per unit'),
('Sublimation Shirt', 9,  3.00, 1, '3ml Yellow ink per unit'),
('Sublimation Shirt', 10, 3.00, 1, '3ml Black ink per unit'),
('Sublimation Shirt', 12,20.00, 1, '20cm heat tape per unit');

-- ---- Mug Printing ----
INSERT INTO `product_bom` (`product_type`, `inventory_id`, `suggested_qty`, `per_unit`, `notes`) VALUES
('Mug Printing', 2,  1.00, 1, '1 blank mug per unit'),
('Mug Printing', 13, 1.00, 1, '1 sheet A4 paper per unit'),
('Mug Printing', 7,  2.00, 1, '2ml Cyan ink per unit'),
('Mug Printing', 8,  2.00, 1, '2ml Magenta ink per unit'),
('Mug Printing', 9,  2.00, 1, '2ml Yellow ink per unit'),
('Mug Printing', 10, 2.00, 1, '2ml Black ink per unit'),
('Mug Printing', 12,30.00, 1, '30cm heat tape per unit');

-- ---- Tarpaulin ----
-- per_unit=0 means qty is flat (total per job, not multiplied by qty ordered)
-- For tarp: quantity represents per sqft; the order qty is sqft
INSERT INTO `product_bom` (`product_type`, `inventory_id`, `suggested_qty`, `per_unit`, `notes`) VALUES
('Tarpaulin', 5,  1.00, 1, '1 sqft tarp material per sqft ordered'),
('Tarpaulin', 7,  5.00, 1, '5ml Cyan ink per sqft'),
('Tarpaulin', 8,  5.00, 1, '5ml Magenta ink per sqft'),
('Tarpaulin', 9,  5.00, 1, '5ml Yellow ink per sqft'),
('Tarpaulin', 10, 5.00, 1, '5ml Black ink per sqft'),
('Tarpaulin', 15, 4.00, 0, '4 grommets per piece (flat)');

-- ---- Sticker ----
INSERT INTO `product_bom` (`product_type`, `inventory_id`, `suggested_qty`, `per_unit`, `notes`) VALUES
('Sticker', 6,  1.00, 1, '1 vinyl sheet A4 per sheet'),
('Sticker', 7,  3.00, 1, '3ml Cyan ink per sheet'),
('Sticker', 8,  3.00, 1, '3ml Magenta ink per sheet'),
('Sticker', 9,  3.00, 1, '3ml Yellow ink per sheet'),
('Sticker', 10, 3.00, 1, '3ml Black ink per sheet'),
('Sticker', 14, 1.00, 1, '1 laminate sheet per unit');

-- ---- ID Lace ----
INSERT INTO `product_bom` (`product_type`, `inventory_id`, `suggested_qty`, `per_unit`, `notes`) VALUES
('ID Lace', 4,  1.00,  1, '1 blank lace per unit'),
('ID Lace', 13, 0.20,  1, '1 sheet per 5 units = 0.20 sheets/unit'),
('ID Lace', 7,  0.50,  1, '0.5ml Cyan ink per unit'),
('ID Lace', 8,  0.50,  1, '0.5ml Magenta ink per unit'),
('ID Lace', 9,  0.50,  1, '0.5ml Yellow ink per unit'),
('ID Lace', 10, 0.50,  1, '0.5ml Black ink per unit'),
('ID Lace', 16, 1.00,  1, '1 buckle clip per unit');

-- ---- Tote Bag ----
INSERT INTO `product_bom` (`product_type`, `inventory_id`, `suggested_qty`, `per_unit`, `notes`) VALUES
('Tote Bag', 3,  1.00, 1, '1 blank tote bag per unit'),
('Tote Bag', 11, 2.00, 1, '2 sheets A3 paper per unit'),
('Tote Bag', 7,  3.00, 1, '3ml Cyan ink per unit'),
('Tote Bag', 8,  3.00, 1, '3ml Magenta ink per unit'),
('Tote Bag', 9,  3.00, 1, '3ml Yellow ink per unit'),
('Tote Bag', 10, 3.00, 1, '3ml Black ink per unit'),
('Tote Bag', 12,20.00, 1, '20cm heat tape per unit');

-- =============================================================
-- SAMPLE DATA — SECTION E: Job Orders (15 records)
-- Various statuses to populate all workflow stages
-- =============================================================
INSERT INTO `job_orders`
  (`jo_number`, `customer_id`, `encoder_id`, `product_type`, `quantity`, `size`, `design_instructions`, `deadline`, `status`, `total_amount`, `created_at`, `updated_at`)
VALUES
-- pending (2)
('JO-2024-00001', 1,  5, 'Sublimation Shirt', 10, 'Medium',   'Front: Company logo. Back: employee name.',         DATE_ADD(CURDATE(), INTERVAL 5  DAY), 'pending',         1500.00, NOW() - INTERVAL 1  DAY, NOW() - INTERVAL 1  DAY),
('JO-2024-00002', 6,  6, 'Tarpaulin',          1, '4ft x 6ft','Full color banner for grand opening event.',        DATE_ADD(CURDATE(), INTERVAL 3  DAY), 'pending',         800.00,  NOW() - INTERVAL 2  DAY, NOW() - INTERVAL 2  DAY),
-- processing (2)
('JO-2024-00003', 4,  5, 'Mug Printing',       5, '11oz',     'Name + photo on each mug. Different photos.',       DATE_ADD(CURDATE(), INTERVAL 2  DAY), 'processing',      750.00,  NOW() - INTERVAL 3  DAY, NOW() - INTERVAL 1  DAY),
('JO-2024-00004', 7,  6, 'ID Lace',           50, 'Standard', 'School logo + student names. Blue/white color.',    DATE_ADD(CURDATE(), INTERVAL 4  DAY), 'processing',      1250.00, NOW() - INTERVAL 4  DAY, NOW() - INTERVAL 2  DAY),
-- finished (2)
('JO-2024-00005', 8,  5, 'Sticker',           20, 'A4 sheets','Store price tags, 6-up layout per sheet.',          DATE_ADD(CURDATE(), INTERVAL 1  DAY), 'finished',        400.00,  NOW() - INTERVAL 5  DAY, NOW() - INTERVAL 1  DAY),
('JO-2024-00006', 5,  6, 'Tote Bag',          15, 'Standard', 'Eco bag with brand logo. Green color.',             DATE_ADD(CURDATE(), INTERVAL 6  DAY), 'finished',        2250.00, NOW() - INTERVAL 6  DAY, NOW() - INTERVAL 2  DAY),
-- materials_check (2)
('JO-2024-00007', 9,  5, 'Sublimation Shirt',  5, 'Large',    'Polo style. Company uniform design.',               DATE_ADD(CURDATE(), INTERVAL 3  DAY), 'materials_check', 850.00,  NOW() - INTERVAL 7  DAY, NOW() - INTERVAL 1  DAY),
('JO-2024-00008', 3,  6, 'Mug Printing',       3, '11oz',     'Anniversary souvenir mugs. Script font.',           DATE_ADD(CURDATE(), INTERVAL 2  DAY), 'materials_check', 450.00,  NOW() - INTERVAL 8  DAY, NOW() - INTERVAL 1  DAY),
-- releasing (3)
('JO-2024-00009', 4,  5, 'ID Lace',           30, 'Standard', 'Event laces for company outing.',                   DATE_ADD(CURDATE(), INTERVAL 1  DAY), 'releasing',       900.00,  NOW() - INTERVAL 9  DAY, NOW() - INTERVAL 1  DAY),
('JO-2024-00010', 6,  6, 'Tarpaulin',          1, '3ft x 5ft','Tarp for retail store promo.',                     DATE_ADD(CURDATE(), INTERVAL 2  DAY), 'releasing',       600.00,  NOW() - INTERVAL 10 DAY, NOW() - INTERVAL 1  DAY),
('JO-2024-00011', 7,  5, 'Sticker',           10, 'A4',       'Product label stickers. Glossy laminate.',          DATE_ADD(CURDATE(), INTERVAL 3  DAY), 'releasing',       300.00,  NOW() - INTERVAL 11 DAY, NOW() - INTERVAL 1  DAY),
-- completed (4)
('JO-2024-00012', 2,  6, 'Sublimation Shirt', 20, 'Various',  'Team shirts. Two designs alternating.',             DATE_ADD(CURDATE(), INTERVAL -5 DAY), 'completed',       3000.00, NOW() - INTERVAL 15 DAY, NOW() - INTERVAL 5  DAY),
('JO-2024-00013', 10, 5, 'Mug Printing',       8, '11oz',     'Wedding souvenir mugs. Couple photo.',              DATE_ADD(CURDATE(), INTERVAL -3 DAY), 'completed',       1200.00, NOW() - INTERVAL 12 DAY, NOW() - INTERVAL 3  DAY),
('JO-2024-00014', 9,  6, 'Tote Bag',           6, 'Standard', 'Gift bags for giveaway.',                           DATE_ADD(CURDATE(), INTERVAL -2 DAY), 'completed',       900.00,  NOW() - INTERVAL 10 DAY, NOW() - INTERVAL 2  DAY),
('JO-2024-00015', 8,  5, 'ID Lace',           25, 'Standard', 'PTA laces with school seal.',                       DATE_ADD(CURDATE(), INTERVAL -1 DAY), 'completed',       625.00,  NOW() - INTERVAL 8  DAY, NOW() - INTERVAL 1  DAY);

-- =============================================================
-- SAMPLE DATA — SECTION F: Payments (20 records)
-- =============================================================
INSERT INTO `payments` (`jo_id`, `cashier_id`, `amount`, `payment_type`, `or_number`, `payment_method`, `paid_at`) VALUES
-- JO-00001 (pending) — partial only
(1,  9, 500.00,  'partial', 'OR-2024-00001', 'cash',  NOW() - INTERVAL 1  DAY),

-- JO-00002 (pending) — partial
(2,  9, 300.00,  'partial', 'OR-2024-00002', 'gcash', NOW() - INTERVAL 2  DAY),

-- JO-00003 (processing) — partial
(3,  9, 300.00,  'partial', 'OR-2024-00003', 'cash',  NOW() - INTERVAL 3  DAY),

-- JO-00004 (processing) — partial
(4,  10,500.00,  'partial', 'OR-2024-00004', 'gcash', NOW() - INTERVAL 4  DAY),

-- JO-00005 (finished) — partial
(5,  9, 200.00,  'partial', 'OR-2024-00005', 'cash',  NOW() - INTERVAL 5  DAY),

-- JO-00006 (finished) — partial
(6,  10,1000.00, 'partial', 'OR-2024-00006', 'maya',  NOW() - INTERVAL 6  DAY),

-- JO-00007 (materials_check) — partial
(7,  9, 500.00,  'partial', 'OR-2024-00007', 'cash',  NOW() - INTERVAL 7  DAY),

-- JO-00008 (materials_check) — partial
(8,  10,200.00,  'partial', 'OR-2024-00008', 'gcash', NOW() - INTERVAL 8  DAY),

-- JO-00009 (releasing) — partial only, balance still owed
(9,  9, 500.00,  'partial', 'OR-2024-00009', 'cash',  NOW() - INTERVAL 9  DAY),

-- JO-00010 (releasing) — partial
(10, 10,300.00,  'partial', 'OR-2024-00010', 'gcash', NOW() - INTERVAL 10 DAY),

-- JO-00011 (releasing) — full paid upfront
(11, 9, 300.00,  'full',    'OR-2024-00011', 'cash',  NOW() - INTERVAL 11 DAY),

-- JO-00012 (completed) — partial + balance
(12, 9,  1500.00,'partial', 'OR-2024-00012', 'gcash', NOW() - INTERVAL 15 DAY),
(12, 10, 1500.00,'balance', 'OR-2024-00013', 'cash',  NOW() - INTERVAL 5  DAY),

-- JO-00013 (completed) — partial + balance
(13, 9,  600.00, 'partial', 'OR-2024-00014', 'cash',  NOW() - INTERVAL 12 DAY),
(13, 10, 600.00, 'balance', 'OR-2024-00015', 'gcash', NOW() - INTERVAL 3  DAY),

-- JO-00014 (completed) — full upfront
(14, 9,  900.00, 'full',    'OR-2024-00016', 'maya',  NOW() - INTERVAL 10 DAY),

-- JO-00015 (completed) — partial + balance
(15, 10, 300.00, 'partial', 'OR-2024-00017', 'cash',  NOW() - INTERVAL 8  DAY),
(15, 9,  325.00, 'balance', 'OR-2024-00018', 'gcash', NOW() - INTERVAL 1  DAY),

-- Extra partial entries for reporting variety
(1,  9,  200.00, 'partial', 'OR-2024-00019', 'gcash', NOW() - INTERVAL 1  DAY),
(2,  10, 100.00, 'partial', 'OR-2024-00020', 'maya',  NOW() - INTERVAL 1  DAY);

-- =============================================================
-- SAMPLE DATA — SECTION G: Materials verification for
-- JO-00007 and JO-00008 (both in materials_check status)
-- =============================================================

-- Materials used for JO-00007 (5 Sublimation Shirts)
INSERT INTO `jo_materials_used` (`jo_id`, `inventory_id`, `qty_used`, `submitted_by`, `notes`) VALUES
(7, 1,  5.00, 5, NULL),  -- 5 shirts
(7, 11,10.00, 5, NULL),  -- 10 sheets A3
(7, 7,  15.00,5, NULL),  -- 15ml Cyan
(7, 8,  15.00,5, NULL),  -- 15ml Magenta
(7, 9,  15.00,5, NULL),  -- 15ml Yellow
(7, 10, 15.00,5, NULL),  -- 15ml Black
(7, 12,100.00,5, NULL);  -- 100cm heat tape

-- Materials used for JO-00008 (3 Mugs)
INSERT INTO `jo_materials_used` (`jo_id`, `inventory_id`, `qty_used`, `submitted_by`, `notes`) VALUES
(8, 2,  3.00, 6, NULL),  -- 3 mugs
(8, 13, 3.00, 6, NULL),  -- 3 sheets A4
(8, 7,  6.00, 6, NULL),  -- 6ml Cyan
(8, 8,  6.00, 6, NULL),  -- 6ml Magenta
(8, 9,  6.00, 6, NULL),  -- 6ml Yellow
(8, 10, 6.00, 6, NULL);  -- 6ml Black

-- Verification records (status = pending for manager review)
INSERT INTO `jo_materials_verification` (`jo_id`, `submitted_by`, `submitted_at`, `status`) VALUES
(7, 5, NOW() - INTERVAL 12 HOUR, 'pending'),
(8, 6, NOW() - INTERVAL 6  HOUR, 'pending');

-- =============================================================
-- SAMPLE DATA — SECTION H: Activity log samples
-- =============================================================
INSERT INTO `activity_log` (`user_id`, `action`, `module`, `reference_id`, `details`) VALUES
(5, 'Created Job Order JO-2024-00001', 'job_orders', 1,  'Walk-in customer: Maria Santos. Product: Sublimation Shirt x10'),
(6, 'Created Job Order JO-2024-00002', 'job_orders', 2,  'Customer: GenSan Trading Corp. Product: Tarpaulin 4x6ft'),
(5, 'Status changed to processing',    'job_orders', 3,  'JO-2024-00003 moved from pending to processing'),
(3, 'Status changed to finished',      'job_orders', 5,  'JO-2024-00005 QC passed, marked as finished'),
(5, 'Submitted materials for approval','materials',  7,  'JO-2024-00007: 7 materials listed'),
(6, 'Submitted materials for approval','materials',  8,  'JO-2024-00008: 6 materials listed'),
(9, 'Payment collected',               'payments',   1,  'OR-2024-00001: PHP 500.00 Cash partial payment'),
(9, 'Payment collected',               'payments',   12, 'OR-2024-00012: PHP 1500.00 GCash partial payment');

-- =============================================================
-- RE-ENABLE FK CHECKS
-- =============================================================

-- =============================================================
-- END OF database.sql
-- All tables: users, customers, job_orders, payments, inventory,
--             product_bom, jo_materials_used, jo_materials_verification,
--             inventory_log, activity_log
-- View: v_jo_summary
-- Indexes: on all FK columns, status, created_at, paid_at
-- Sample data: 10 users, 10 customers, 15 job orders, 20 payments,
--              16 inventory items, 39 BOM rows, 2 material verifications
-- =============================================================
