-- ============================================================
-- MEDICUS POC - Database Schema + Seed Data
-- Plain PHP + MySQL
-- Import this file via phpMyAdmin in cPanel, or:
--   mysql -u USERNAME -p DATABASE_NAME < schema.sql
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS import_rows;
DROP TABLE IF EXISTS imports;
DROP TABLE IF EXISTS suppliers;
DROP TABLE IF EXISTS stock;
DROP TABLE IF EXISTS costings;
DROP TABLE IF EXISTS styles;
DROP TABLE IF EXISTS product_groups;
DROP TABLE IF EXISTS users;

SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------
-- Users (simple auth)
-- ------------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(100) NOT NULL,
    role VARCHAR(30) NOT NULL DEFAULT 'user',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- NOTE: demo users (admin / user1, password: Medicus2026) are created
-- automatically the first time login.php runs against an empty users
-- table, using PHP's own password_hash() so the hash is guaranteed to
-- match this PHP install. No need to insert a hash here by hand.

-- ------------------------------------------------------------
-- Product Groups
-- ------------------------------------------------------------
CREATE TABLE product_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    brand VARCHAR(100),
    status ENUM('Active','Inactive') DEFAULT 'Active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO product_groups (code, name, brand, status) VALUES
('PG-001', 'Product Group 1', 'Brand 1', 'Active'),
('PG-002', 'Product Group 2', 'Brand 1', 'Active'),
('PG-003', 'Product Group 3', 'Brand 2', 'Active'),
('PG-004', 'Product Group 4', 'Brand 2', 'Inactive');

-- ------------------------------------------------------------
-- Suppliers
-- ------------------------------------------------------------
CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL
) ENGINE=InnoDB;

INSERT INTO suppliers (name) VALUES ('Supplier 1'), ('Supplier 2'), ('Supplier 3');

-- ------------------------------------------------------------
-- Styles
-- ------------------------------------------------------------
CREATE TABLE styles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    style_code VARCHAR(50) NOT NULL,
    style_name VARCHAR(150) NOT NULL,
    product_group_id INT NOT NULL,
    supplier_id INT,
    supplier_style_code VARCHAR(50),
    colour VARCHAR(50),
    gender ENUM('Mens','Womens','Unisex') DEFAULT 'Unisex',
    size_curve VARCHAR(50) DEFAULT 'SC - Size Curve',
    fabric VARCHAR(100),
    season VARCHAR(20),
    status ENUM('Active','Non-Replenishable','Pending','Clearance','Discontinued','Inactive') DEFAULT 'Active',
    discontinued_date DATE NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_group_id) REFERENCES product_groups(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
) ENGINE=InnoDB;

INSERT INTO styles (style_code, style_name, product_group_id, supplier_id, supplier_style_code, colour, gender, season, status) VALUES
('STYLE001','Style Name 1',1,1,'Supplier Code1','BLACK','Mens','1H26','Active'),
('STYLE002','Style Name 2',1,1,'Supplier Code2','BROWN','Mens','1H26','Non-Replenishable'),
('STYLE003','Style Name 3',1,2,'Supplier Code3','NAVY','Womens','1H26','Pending'),
('STYLE004','Style Name 4',2,2,'Supplier Code4','GREY','Womens','2H26','Clearance'),
('STYLE005','Style Name 5',2,1,'Supplier Code5','OLIVE','Mens','2H26','Active'),
('STYLE006','Style Name 6',2,3,'Supplier Code6','WHITE','Unisex','2H26','Non-Replenishable'),
('STYLE007','Style Name 7',3,1,'Supplier Code7','TAN','Mens','2H27','Pending'),
('STYLE008','Style Name 8',3,2,'Supplier Code8','RED','Womens','1H27','Discontinued');

-- ------------------------------------------------------------
-- Costings / Pricing
-- ------------------------------------------------------------
CREATE TABLE costings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    style_id INT NOT NULL,
    effective_date DATE NOT NULL,
    delivery_from DATE,
    delivery_to DATE,
    std_price DECIMAL(10,2) NOT NULL,
    group_price DECIMAL(10,2) NOT NULL,
    rrp DECIMAL(10,2) NOT NULL,
    base_cost DECIMAL(10,2) NOT NULL,
    duty DECIMAL(10,2) DEFAULT 0,
    landing DECIMAL(10,2) DEFAULT 0,
    freight DECIMAL(10,2) DEFAULT 0,
    overhead_package DECIMAL(10,2) DEFAULT 0,
    overhead_insurance DECIMAL(10,2) DEFAULT 0,
    rejection_cost DECIMAL(10,2) DEFAULT 0,
    selling_cost DECIMAL(10,2) DEFAULT 0,
    total_cost DECIMAL(10,2) DEFAULT 0,
    status ENUM('Draft','Pending Approval','Active','Rejected') DEFAULT 'Active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (style_id) REFERENCES styles(id)
) ENGINE=InnoDB;

INSERT INTO costings (style_id, effective_date, delivery_from, delivery_to, std_price, group_price, rrp, base_cost, duty, landing, freight, overhead_package, overhead_insurance, rejection_cost, selling_cost, total_cost, status) VALUES
(1,'2025-01-01','2025-01-01','2025-06-30',1095.00,1055.00,2399.00,24.00,4.00,218.44,128.44,110.00,45.00,29.80,82.16,184.80,'Active'),
(2,'2025-01-01','2025-01-01','2025-06-30',1150.00,1108.00,2499.00,25.50,4.50,225.15,132.15,112.00,46.00,31.60,85.10,189.60,'Active'),
(3,'2025-02-01','2025-01-01','2025-06-30',1030.00,992.00,2299.00,23.00,4.50,212.40,122.40,105.00,43.00,30.20,79.90,178.20,'Pending Approval'),
(4,'2025-07-01','2025-07-01','2025-12-31',1120.00,1078.00,2399.00,24.00,4.50,220.10,128.10,109.00,45.00,32.00,83.20,186.00,'Active'),
(5,'2025-07-01','2025-07-01','2025-12-31',1175.00,1130.00,2599.00,26.00,5.00,231.75,134.75,115.00,47.00,33.60,87.35,195.60,'Active'),
(6,'2025-08-01','2025-08-01','2026-01-31',1060.00,1020.00,2349.00,24.00,4.00,216.30,126.30,107.00,44.00,31.40,81.60,182.40,'Draft'),
(7,'2026-08-01','2026-08-01','2027-01-31',990.00,952.00,2199.00,22.00,4.00,204.75,118.75,101.00,42.00,29.20,76.80,172.20,'Active'),
(8,'2026-01-01','2026-01-01','2026-06-30',1155.00,1112.00,2549.00,25.00,5.00,226.80,132.80,112.00,46.00,32.80,85.60,190.80,'Rejected');

-- ------------------------------------------------------------
-- Imports (Import Management / Style Onboarding)
-- ------------------------------------------------------------
CREATE TABLE imports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    import_number VARCHAR(50) NOT NULL,
    supplier_id INT,
    product_group_id INT,
    season VARCHAR(20),
    effective_from DATE,
    effective_to DATE,
    delivery_from DATE,
    delivery_to DATE,
    status ENUM('Onboarding','Pending Approval','Integrated','Unintegrated') DEFAULT 'Onboarding',
    imported_by VARCHAR(100),
    imported_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (product_group_id) REFERENCES product_groups(id)
) ENGINE=InnoDB;

INSERT INTO imports (import_number, supplier_id, product_group_id, season, effective_from, effective_to, delivery_from, delivery_to, status, imported_by, imported_at) VALUES
('Import File 1', 1, 1, 'AW26', '2026-09-01', '2027-03-30', '2026-09-01', '2027-03-30', 'Onboarding', 'User1', '2025-03-22 15:37:00'),
('Import File 2', 2, 2, 'SS26', '2026-01-01', '2026-06-30', '2026-01-01', '2026-06-30', 'Pending Approval', 'User1', '2026-04-09 15:62:00'),
('Import File 3', 1, 3, 'AW25', '2025-09-01', '2026-03-30', '2025-09-01', '2026-03-30', 'Integrated', 'Admin', '2025-01-10 09:12:00');

-- ------------------------------------------------------------
-- Import Rows (classification-driven onboarding wizard)
-- classification: sample | existing | non_continuable | inactive | new
-- row_status: pending | onboarded | discarded
-- ------------------------------------------------------------
CREATE TABLE import_rows (
    id INT AUTO_INCREMENT PRIMARY KEY,
    import_id INT NOT NULL,
    supplier_code VARCHAR(50),
    style_code VARCHAR(50) NOT NULL,
    style_name VARCHAR(150),
    colour VARCHAR(50),
    gender ENUM('Mens','Womens','Unisex') DEFAULT 'Unisex',
    factory VARCHAR(50),
    origin VARCHAR(50),
    fob_price DECIMAL(10,2),
    fob_effective DATE,
    order_moq INT,
    prod_moq INT,
    ship_moq INT,
    buy_window VARCHAR(20),
    prev_cost DECIMAL(10,2),
    new_cost DECIMAL(10,2),
    classification ENUM('sample','existing','non_continuable','inactive','new') NOT NULL,
    row_status ENUM('pending','onboarded','discarded') DEFAULT 'pending',
    sample_match VARCHAR(50),
    FOREIGN KEY (import_id) REFERENCES imports(id)
) ENGINE=InnoDB;

-- Sample styles
INSERT INTO import_rows (import_id, supplier_code, style_code, style_name, colour, gender, factory, origin, fob_price, fob_effective, order_moq, prod_moq, ship_moq, classification, row_status, sample_match) VALUES
(1,'Supplier Code1','Style Code 1','Style Name 1','Black','Mens','Factory 1','Origin 1',30.00,'2025-03-22',200,600,300,'sample','pending','SMP-1042'),
(1,'Supplier Code2','Style Code 2','Style Name 2','Black','Mens','Factory 2','Origin 2',35.00,'2025-03-22',100,600,300,'sample','pending','SMP-1178');

-- Existing styles (price updates)
INSERT INTO import_rows (import_id, supplier_code, style_code, style_name, colour, gender, factory, origin, fob_price, fob_effective, prev_cost, new_cost, buy_window, classification, row_status) VALUES
(1,'Supplier Code3','Style Code 3','Style Name 3','Grey','Womens','Factory 1','Origin 1',28.50,'2025-03-22',28.50,30.00,'AW25','existing','pending'),
(1,'Supplier Code4','Style Code 4','Style Name 4','Navy','Mens','Factory 2','Origin 2',35.00,'2025-03-22',35.00,35.00,'SS26','existing','pending');

-- Non-continuable / discontinued
INSERT INTO import_rows (import_id, supplier_code, style_code, style_name, colour, gender, factory, origin, prev_cost, new_cost, classification, row_status) VALUES
(1,'Supplier Code5','Style Code 5','Style Name 5','Olive','Mens','Factory 1','Vietnam',30.00,33.00,'non_continuable','pending'),
(1,'Supplier Code6','Style Code 6','Style Name 6','White','Womens','Factory 1','Cambodia',35.00,37.50,'non_continuable','pending');

-- Inactive (no price supplied)
INSERT INTO import_rows (import_id, supplier_code, style_code, style_name, colour, gender, factory, origin, prev_cost, classification, row_status) VALUES
(1,'Supplier Code7','Style Code 7','Style Name 7','Tan','Mens','Factory 2','Bangladesh',25.00,'inactive','pending'),
(1,'Supplier Code8','Style Code 8','Style Name 8','Red','Unisex','Factory 2','India',28.50,'inactive','pending');

-- New styles to onboard
INSERT INTO import_rows (import_id, supplier_code, style_code, style_name, colour, gender, factory, origin, fob_price, fob_effective, order_moq, prod_moq, ship_moq, buy_window, classification, row_status) VALUES
(1,'Supplier Code9','Style Code 9','Style Name 9','Black','Mens','Factory 3','Vietnam',18.00,'2025-03-22',550,350,250,'AW25','new','pending'),
(1,'Supplier Code10','Style Code 10','Style Name 10','Navy','Womens','Factory 1','Vietnam',34.90,'2025-03-22',400,300,200,'AW25','new','pending'),
(1,'Supplier Code11','Style Code 11','Style Name 11','Grey','Unisex','Factory 2','India',26.40,'2025-03-22',600,400,300,'SS26','new','pending');

-- ------------------------------------------------------------
-- Stock
-- ------------------------------------------------------------
CREATE TABLE stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    style_id INT NOT NULL,
    warehouse VARCHAR(50) DEFAULT 'Main Warehouse',
    bin_location VARCHAR(30),
    quantity_on_hand INT DEFAULT 0,
    quantity_allocated INT DEFAULT 0,
    quantity_available INT GENERATED ALWAYS AS (quantity_on_hand - quantity_allocated) STORED,
    last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (style_id) REFERENCES styles(id)
) ENGINE=InnoDB;

INSERT INTO stock (style_id, warehouse, bin_location, quantity_on_hand, quantity_allocated) VALUES
(1,'Main Warehouse','A1-01',450,120),
(2,'Main Warehouse','A1-02',300,80),
(3,'Main Warehouse','A2-01',210,50),
(4,'Main Warehouse','A2-02',0,0),
(5,'Main Warehouse','B1-01',600,200),
(6,'Main Warehouse','B1-02',150,150),
(7,'Main Warehouse','B2-01',80,0),
(8,'Main Warehouse','B2-02',0,0);