-- Supplier Setup + Part Catalog
-- Suppliers table
CREATE TABLE IF NOT EXISTS suppliers (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    contact_person VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(255) NULL,
    address TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_suppliers_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Part Catalog (predefined part name + number combos)
CREATE TABLE IF NOT EXISTS part_catalog (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    part_number VARCHAR(100) NULL,
    category_id BIGINT UNSIGNED NULL,
    default_unit_cost DECIMAL(12,2) NULL,
    default_reorder_level DECIMAL(10,2) NULL,
    unit_of_measure VARCHAR(30) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_catalog_name (name),
    KEY idx_catalog_category (category_id),
    CONSTRAINT fk_catalog_category FOREIGN KEY (category_id) REFERENCES part_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed common auto parts catalog
INSERT IGNORE INTO part_catalog (name, part_number, category_id, unit_of_measure) VALUES
('Engine Oil Filter', 'EO-FILTER-001', 7, 'Pieces'),
('Fuel Filter', 'FUEL-FILTER-001', 7, 'Pieces'),
('Air Filter', 'AIR-FILTER-001', 7, 'Pieces'),
('AC Filter', 'AC-FILTER-001', 7, 'Pieces'),
('Engine Oil 5W-30 (4L)', 'OIL-5W30-4L', 8, 'Litres'),
('Engine Oil 10W-40 (4L)', 'OIL-10W40-4L', 8, 'Litres'),
('Engine Oil 20W-50 (4L)', 'OIL-20W50-4L', 8, 'Litres'),
('Automatic Transmission Fluid (ATF)', 'ATF-FLUID-001', 8, 'Litres'),
('Brake Fluid DOT 4', 'BRAKE-FLUID-DOT4', 8, 'Litres'),
('Coolant/Antifreeze (4L)', 'COOLANT-4L', 8, 'Litres'),
('Power Steering Fluid', 'PS-FLUID-001', 8, 'Litres'),
('Windscreen Washer Fluid', 'WASHER-FLUID-001', 8, 'Litres'),
('Brake Pad Set (Front)', 'BRAKE-PAD-FR-001', 3, 'Sets'),
('Brake Pad Set (Rear)', 'BRAKE-PAD-RR-001', 3, 'Sets'),
('Brake Disc (Front)', 'BRAKE-DISC-FR-001', 3, 'Pieces'),
('Brake Disc (Rear)', 'BRAKE-DISC-RR-001', 3, 'Pieces'),
('Brake Shoe Set', 'BRAKE-SHOE-001', 3, 'Sets'),
('Clutch Kit', 'CLUTCH-KIT-001', 2, 'Sets'),
('Clutch Plate', 'CLUTCH-PLATE-001', 2, 'Pieces'),
('Transmission Mount', 'TRANS-MOUNT-001', 2, 'Pieces'),
('Shock Absorber (Front)', 'SHOCK-FR-001', 4, 'Pieces'),
('Shock Absorber (Rear)', 'SHOCK-RR-001', 4, 'Pieces'),
('Strut Mount', 'STRUT-MOUNT-001', 4, 'Pieces'),
('Control Arm (Front)', 'CTRL-ARM-FR-001', 4, 'Pieces'),
('Ball Joint', 'BALL-JOINT-001', 4, 'Pieces'),
('Tie Rod End', 'TIE-ROD-001', 4, 'Pieces'),
('Stabiliser Link', 'STAB-LINK-001', 4, 'Pieces'),
('Spark Plug', 'SPARK-PLUG-001', 5, 'Pieces'),
('Ignition Coil', 'IGN-COIL-001', 5, 'Pieces'),
('Battery 12V 60Ah', 'BATTERY-60AH', 5, 'Pieces'),
('Battery 12V 80Ah', 'BATTERY-80AH', 5, 'Pieces'),
('Alternator', 'ALTERNATOR-001', 5, 'Pieces'),
('Starter Motor', 'STARTER-001', 5, 'Pieces'),
('Headlight Bulb (H7)', 'BULB-H7', 5, 'Pieces'),
('Tail Light Bulb', 'BULB-TAIL-001', 5, 'Pieces'),
('Indicator Bulb', 'BULB-IND-001', 5, 'Pieces'),
('Fuse 10A', 'FUSE-10A', 5, 'Pieces'),
('Fuse 15A', 'FUSE-15A', 5, 'Pieces'),
('Fuse 20A', 'FUSE-20A', 5, 'Pieces'),
('Fuse 30A', 'FUSE-30A', 5, 'Pieces'),
('Wiper Blade (Front Pair)', 'WIPER-FR-001', 6, 'Sets'),
('Wiper Blade (Rear)', 'WIPER-RR-001', 6, 'Pieces'),
('Side Mirror (Left)', 'MIRROR-L-001', 6, 'Pieces'),
('Side Mirror (Right)', 'MIRROR-R-001', 6, 'Pieces'),
('Radiator', 'RADIATOR-001', 1, 'Pieces'),
('Water Pump', 'WATER-PUMP-001', 1, 'Pieces'),
('Thermostat', 'THERMOSTAT-001', 1, 'Pieces'),
('Timing Belt Kit', 'TIMING-BELT-001', 1, 'Sets'),
('Fan Belt / Serpentine Belt', 'SERP-BELT-001', 1, 'Pieces'),
('Drive Shaft', 'DRIVE-SHAFT-001', 2, 'Pieces'),
('CV Joint', 'CV-JOINT-001', 2, 'Pieces'),
('Engine Mount', 'ENG-MOUNT-001', 1, 'Pieces'),
('Gasket Set (Engine)', 'GASKET-ENG-001', 1, 'Sets'),
('Oil Seal (Crankshaft)', 'SEAL-CRANK-001', 1, 'Pieces'),
('Oil Seal (Camshaft)', 'SEAL-CAM-001', 1, 'Pieces'),
('Hose (Radiator Upper)', 'HOSE-RAD-UPPER', 1, 'Pieces'),
('Hose (Radiator Lower)', 'HOSE-RAD-LOWER', 1, 'Pieces'),
('Hose (Heater)', 'HOSE-HEATER-001', 1, 'Pieces'),
('Exhaust Gasket', 'GASKET-EXH-001', 1, 'Pieces'),
('Lambda Sensor / O2 Sensor', 'O2-SENSOR-001', 5, 'Pieces'),
('ABS Sensor', 'ABS-SENSOR-001', 5, 'Pieces'),
('Crankshaft Position Sensor', 'CKP-SENSOR-001', 5, 'Pieces'),
('Camshaft Position Sensor', 'CMP-SENSOR-001', 5, 'Pieces'),
('MAF Sensor', 'MAF-SENSOR-001', 5, 'Pieces'),
('Throttle Body', 'THROTTLE-001', 1, 'Pieces'),
('Fuel Pump', 'FUEL-PUMP-001', 1, 'Pieces'),
('Fuel Injector', 'FUEL-INJ-001', 1, 'Pieces'),
('Fuel Tank Cap', 'FUEL-CAP-001', 6, 'Pieces'),
('Wheel Bearing (Front)', 'WHL-BRG-FR-001', 4, 'Pieces'),
('Wheel Bearing (Rear)', 'WHL-BRG-RR-001', 4, 'Pieces'),
('Tyre Pressure Sensor (TPMS)', 'TPMS-001', 5, 'Pieces'),
('A/C Compressor', 'AC-COMP-001', 10, 'Pieces'),
('A/C Condenser', 'AC-COND-001', 10, 'Pieces'),
('A/C Evaporator', 'AC-EVAP-001', 10, 'Pieces'),
('A/C Gas (R134a)', 'AC-GAS-R134A', 10, 'Kg'),
('A/C Gas (R1234yf)', 'AC-GAS-R1234YF', 10, 'Kg'),
('Cabin Filter / Pollen Filter', 'CABIN-FILTER-001', 7, 'Pieces'),
('Radiator Cap', 'RAD-CAP-001', 1, 'Pieces'),
('Expansion Tank', 'EXP-TANK-001', 1, 'Pieces'),
('Turbocharger', 'TURBO-001', 1, 'Pieces'),
('Intercooler', 'INTERCOOLER-001', 1, 'Pieces'),
('EGR Valve', 'EGR-VALVE-001', 1, 'Pieces'),
('Glow Plug', 'GLOW-PLUG-001', 5, 'Pieces'),
('Diesel Particulate Filter (DPF)', 'DPF-001', 7, 'Pieces'),
('AdBlue (10L)', 'ADBLUE-10L', 8, 'Litres'),
('Grease (Multi-purpose)', 'GREASE-MP-001', 8, 'Kg'),
('Brake Cleaner', 'BRAKE-CLEANER-001', 8, 'Spray'),
('Carburettor Cleaner', 'CARB-CLEANER-001', 8, 'Spray'),
('Silicone Spray', 'SILICONE-SPRAY-001', 8, 'Spray'),
('WD-40', 'WD40-001', 8, 'Spray'),
('Gasket Maker / Sealant', 'GASKET-MAKER-001', 8, 'Pieces'),
('Threadlocker (Medium)', 'THREADLOCK-MED', 8, 'Pieces'),
('Threadlocker (High)', 'THREADLOCK-HIGH', 8, 'Pieces'),
('Solder Wire', 'SOLDER-WIRE-001', 5, 'Metres'),
('Electrical Tape', 'ELEC-TAPE-001', 5, 'Pieces'),
('Crimp Connector Kit', 'CRIMP-KIT-001', 5, 'Sets'),
('Heat Shrink Tubing Kit', 'HEAT-SHRINK-001', 5, 'Sets'),
('Relay 12V 30A', 'RELAY-30A', 5, 'Pieces'),
('Relay 12V 40A', 'RELAY-40A', 5, 'Pieces');

-- Add supplier_id to parts
ALTER TABLE parts
ADD COLUMN supplier_id BIGINT UNSIGNED NULL AFTER category_id,
ADD CONSTRAINT fk_part_supplier FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL;

-- Add catalog_id to parts
ALTER TABLE parts
ADD COLUMN catalog_id BIGINT UNSIGNED NULL AFTER supplier_id,
ADD CONSTRAINT fk_part_catalog FOREIGN KEY (catalog_id) REFERENCES part_catalog(id) ON DELETE SET NULL;

-- Seed common suppliers
INSERT IGNORE INTO suppliers (name, contact_person, phone, email, status) VALUES
('Toyota Nigeria Ltd', 'Customer Service', '01-2955000', 'info@toyotanigeria.com', 'active'),
('Mikano International Ltd', 'Sales Dept', '01-4488000', 'parts@mikano.com', 'active'),
('Elizade Nigeria Ltd', 'Spare Parts', '01-2702000', 'parts@elizade.com', 'active'),
('Bewac Auto Services', 'Customer Support', '01-2916333', 'info@bewacautos.com', 'active'),
('Trade Centre Ltd', 'Parts Division', '01-2798877', 'parts@tradecentre.com', 'active'),
('Mandilas Motors', 'Spare Parts', '01-2632010', 'parts@mandilas.com', 'active'),
('Leventis Motors', 'Parts Sales', '01-2645600', 'parts@leventismotors.com', 'active'),
('CFAO Motors Nigeria', 'Customer Service', '01-2714800', 'info@cfaomotors.com', 'active'),
('Kia Nigeria (Kopetic)', 'Parts Dept', '01-2711212', 'parts@kianigeria.com', 'active'),
('Glory Auto Parts Ltd', 'Sales Team', '0803-555-0100', 'info@gloryautoparts.com', 'active'),
('Benson Auto Spares', 'Manager', '0802-222-3344', 'benson@autospares.com', 'active'),
('JAC Automobile Nigeria', 'Parts Support', '01-4540011', 'parts@jacnigeria.com', 'active'),
('Talabi Auto Parts', 'Customer Care', '0805-555-2200', 'info@talabiauto.com', 'active'),
('NAPA Auto Parts', 'Sales', '0806-123-4567', 'napa@autoparts.com', 'active'),
('Aba Auto Spares Market', 'Trade Desk', '0803-777-8899', 'aba@autospares.com', 'active'),
('Ladipo Auto Spares', 'Wholesale', '0802-888-9900', 'info@ladiopoautospares.com', 'active');

-- Add permissions for supplier and part_catalog
INSERT IGNORE INTO permissions (code, description) VALUES
('supplier.view', 'View suppliers'),
('supplier.create', 'Create suppliers'),
('supplier.edit', 'Edit suppliers'),
('supplier.delete', 'Delete suppliers'),
('part_catalog.view', 'View part catalog'),
('part_catalog.create', 'Create catalog items'),
('part_catalog.edit', 'Edit catalog items'),
('part_catalog.delete', 'Delete catalog items');

-- Grant to all roles
INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r CROSS JOIN permissions p
WHERE p.code IN ('supplier.view','supplier.create','supplier.edit','supplier.delete',
                 'part_catalog.view','part_catalog.create','part_catalog.edit','part_catalog.delete');