-- ================================================
-- FRESH SEED DATA - Matching UI Screenshots
-- Run AFTER setup_db.sql
-- ================================================

-- Countries
INSERT INTO country (country_id, name, phone_code) VALUES 
(1, 'Denmark', '+45'),
(2, 'Norway', '+47'),
(3, 'Sweden', '+46'),
(4, 'Netherlands', '+31'),
(5, 'United Kingdom', '+44'),
(6, 'Ireland', '+353');

-- Territories
INSERT INTO territory (name, country_id) VALUES 
('Copenhagen', 1),
('Oslo', 2),
('Stockholm', 3),
('Amsterdam', 4),
('London', 5),
('Dublin', 6);

-- Cities
INSERT INTO city (territory_id, country_id, name) VALUES 
(1, 1, 'Copenhagen'),
(2, 2, 'Oslo'),
(3, 3, 'Stockholm'),
(4, 4, 'Amsterdam'),
(5, 5, 'London'),
(6, 6, 'Dublin');

-- Addresses
INSERT INTO address (address_1, address_2, city_id, territory_id, country_id) VALUES 
('Kastrup Lufthavn', 'Terminal 3, Gate B', 1, 1, 1),
('Oslo Gardermoen', 'Terminal 1', 2, 2, 2),
('Arlanda Airport', 'Terminal 5', 3, 3, 3),
('Schiphol Airport', 'Departure Hall 2', 4, 4, 4),
('Heathrow Airport', 'Terminal 5', 5, 5, 5),
('Dublin Airport', 'Terminal 1', 6, 6, 6);

-- Tax Agencies
INSERT INTO tax_agency (country_id, name, link) VALUES
(1, 'SKAT Denmark', 'https://skat.dk'),
(4, 'Dutch Tax Auth', 'https://belastingdienst.nl'),
(5, 'HMRC', 'https://hmrc.gov.uk');

-- Companies (matching screenshots: SAS, KLM, EasyJet, Ryanair)
INSERT INTO company (name, email, website, country_id, billing_address_id, shipping_address_id, tax_agency_id) VALUES 
('Scandinavian Airlines Systems', 'ops@flysas.com', 'www.flysas.com', 1, 1, 1, 1),
('KLM Royal Dutch Airlines', 'ops@klm.com', 'www.klm.com', 4, 4, 4, 2),
('EasyJet', 'ops@easyjet.com', 'www.easyjet.com', 5, 5, 5, 3),
('Ryanair', 'ops@ryanair.com', 'www.ryanair.com', 6, 6, 6, NULL);

-- Roles
INSERT INTO roles (initials, name, description) VALUES 
('SA', 'SuperAdmin', 'Full system access'),
('AM', 'Approving Manager', 'Approve and manage orders'),
('SM', 'Station Manager', 'Manage station operations');

-- Users
INSERT INTO users (company_id, role_id, first_name, last_name, email, password) VALUES 
(1, 1, 'Admin', 'System', 'admin@weborder.com', 'hashed_pass'),
(1, 2, 'Denise', 'Approving Manager', 'denise@flysas.com', 'hashed_pass'),
(1, 3, 'Henry', 'Station Manager', 'henry@flysas.com', 'hashed_pass'),
(2, 3, 'Jan', 'de Vries', 'jan@klm.com', 'hashed_pass'),
(3, 3, 'Emily', 'Wilson', 'emily@easyjet.com', 'hashed_pass');

-- Stations (matching screenshots: SAS-North-1, etc.)
INSERT INTO station (company_id, name) VALUES 
(1, 'SAS-North-1'),
(1, 'SAS-South-2'),
(2, 'KLM-Schiphol-1'),
(3, 'EasyJet-LHR-1'),
(4, 'Ryanair-DUB-1');

-- ================================
-- PRODUCT SYSTEM
-- ================================

-- Categories (matching screenshots)
INSERT INTO category (name, description) VALUES 
('Bag Tags', 'Airport baggage identification tags'),
('Boarding Pass', 'Passenger boarding documentation'),
('Overhead Storage', 'Overhead bin labels and tags');

-- Presentations (matching screenshot details: per Roll, per Box)
INSERT INTO presentation (name, units_per_presentation) VALUES 
('Roll', 400),
('Box', 2400),
('Sheet', 100),
('Pack', 1000),
('Bundle', 500);

-- Products (matching screenshot names exactly)
INSERT INTO product (name, category_id) VALUES 
('Non EU Tag Without Green Border', 1),
('Non EU Tag With Green Border', 1),
('EU Domestic Bag Tag', 1),
('Overhead Storage OK', 3),
('Underseat Storage OK', 3),
('Cabin Friendly', 3),
('Cabin Baggage Approved', 1),
('Standard Boarding Pass', 2),
('Priority Boarding Pass', 2),
('Self-Service Boarding Pass', 2);

-- Product Presentations (linking products to their presentations)
-- Tags come in Roll and Box
INSERT INTO product_presentation (product_id, presentation_id) VALUES 
(1, 1), (1, 2),
(2, 1), (2, 2),
(3, 1), (3, 2),
(4, 4), (4, 2),
(5, 4), (5, 2),
(6, 4), (6, 2),
(7, 1), (7, 2),
(8, 3), (8, 5),
(9, 3), (9, 5),
(10, 3), (10, 5);

-- Packaging
INSERT INTO packaging (presentation_id, name, quantity) VALUES 
(1, 'Standard Roll Pack', 10),
(2, 'Carton Box', 4),
(3, 'Sheet Pack', 50),
(4, 'Bulk Pack', 3),
(5, 'Bundle Wrap', 6);

-- Attributes
INSERT INTO attribute (name, unit, data_type) VALUES 
('Width', 'mm', 'numeric'),
('Height', 'mm', 'numeric'),
('Weight', 'g', 'numeric'),
('Material', NULL, 'text'),
('Color', NULL, 'text'),
('Adhesive Type', NULL, 'text');

-- Product Attribute Values
INSERT INTO product_attribute_value (product_id, attribute_id, value_text, value_numeric) VALUES 
(1, 1, NULL, 76.2),
(1, 2, NULL, 203.2),
(1, 4, 'Thermal Paper', NULL),
(1, 5, 'White/Blue', NULL),
(2, 1, NULL, 76.2),
(2, 2, NULL, 203.2),
(2, 4, 'Thermal Paper', NULL),
(2, 5, 'White/Green', NULL),
(4, 1, NULL, 120.0),
(4, 2, NULL, 60.0),
(4, 4, 'PVC Sticker', NULL),
(8, 1, NULL, 210.0),
(8, 2, NULL, 99.0),
(8, 4, 'A5 Cardstock', NULL);

-- Product Station assignments
INSERT INTO product_station (product_id, station_id, price, max_qty) VALUES 
(1, 1, 0.12, 5000),
(2, 1, 0.14, 5000),
(3, 1, 0.10, 3000),
(4, 1, 0.08, 2000),
(5, 1, 0.08, 2000),
(6, 1, 0.09, 2500),
(7, 1, 0.11, 3000),
(1, 3, 0.13, 4000),
(4, 3, 0.09, 3000),
(8, 4, 0.15, 1000);

-- ================================
-- ORDER SYSTEM
-- ================================

-- Priorities
INSERT INTO priority (name, color) VALUES 
('High', '#E53935'),
('Medium', '#FB8C00'),
('Low', '#FDD835');

-- Order Statuses (matching screenshot dropdown)
INSERT INTO order_status (name, description) VALUES 
('Created', 'Initial order placement'),
('Approved', 'Order confirmed by approving manager'),
('Not Approved', 'Order rejected or needs revision'),
('Received', 'Order received at station'),
('Invoice', 'Invoice generated for order');

-- Orders (realistic data matching screenshots)
INSERT INTO orders (ord_number, company_id, station_id, priority_id, order_status_id, note, reference_number) VALUES 
(100001, 1, 1, 1, 1, 'Urgent restock needed for summer season', 'SAS-2025-REF-001'),
(100002, 1, 2, 2, 2, 'Regular monthly order', 'SAS-2025-REF-002'),
(100003, 2, 3, 1, 1, 'KLM Schiphol restock', 'KLM-2025-REF-001'),
(100004, 3, 4, 3, 3, 'Seasonal order review pending', 'EJ-2025-REF-001'),
(100005, 1, 1, 2, 4, 'Received at warehouse', 'SAS-2025-REF-003'),
(100006, 2, 3, 1, 5, 'Invoice pending payment', 'KLM-2025-REF-002'),
(100007, 1, 1, 1, 2, 'Priority boarding pass restock', 'SAS-2025-REF-004');

-- Order Products (matching screenshot: item descriptions with quantities)
-- Order 100001 - SAS North-1 (matches screenshot exactly)
INSERT INTO order_products (ord_id, product_id, price, qty, fulfilled_qty) VALUES 
(1, 1, 0.12, 250, 0),
(1, 4, 0.08, 1300, 0),
(1, 5, 0.08, 800, 0),
(1, 6, 0.09, 1000, 0),
(1, 7, 0.11, 250, 0);

-- Order 100002
INSERT INTO order_products (ord_id, product_id, price, qty, fulfilled_qty) VALUES 
(2, 1, 0.12, 500, 500),
(2, 2, 0.14, 300, 300),
(2, 8, 0.15, 200, 0);

-- Order 100003
INSERT INTO order_products (ord_id, product_id, price, qty, fulfilled_qty) VALUES 
(3, 1, 0.13, 400, 0),
(3, 4, 0.09, 600, 0);

-- Order 100004
INSERT INTO order_products (ord_id, product_id, price, qty, fulfilled_qty) VALUES 
(4, 8, 0.15, 150, 0);

-- Order 100005
INSERT INTO order_products (ord_id, product_id, price, qty, fulfilled_qty) VALUES 
(5, 3, 0.10, 1000, 800),
(5, 6, 0.09, 500, 500);

-- Order 100006
INSERT INTO order_products (ord_id, product_id, price, qty, fulfilled_qty) VALUES 
(6, 4, 0.09, 700, 700),
(6, 5, 0.08, 400, 400);

-- Order 100007
INSERT INTO order_products (ord_id, product_id, price, qty, fulfilled_qty) VALUES 
(7, 9, 0.15, 300, 0),
(7, 10, 0.15, 200, 0);

-- Order Event Logs (matching Event History tab)
INSERT INTO order_event_log (ord_id, ord_prod_id, order_status_id, user_id, event_note, internal_note) VALUES
(1, NULL, 1, 3, 'Order Created', 'Summer restock order placed by station manager'),
(2, NULL, 1, 3, 'Order Created', 'Monthly regular order'),
(2, NULL, 2, 2, 'Order Approved', 'Approved by Denise'),
(5, NULL, 1, 3, 'Order Created', 'Restock request'),
(5, NULL, 2, 2, 'Order Approved', NULL),
(5, NULL, 4, 2, 'Order Received', 'Partial delivery confirmed at warehouse'),
(6, NULL, 1, 4, 'Order Created', 'KLM Schiphol monthly order'),
(6, NULL, 2, 2, 'Order Approved', NULL),
(6, NULL, 4, 2, 'Order Received', 'Full delivery'),
(6, NULL, 5, 1, 'Invoice Generated', 'Invoice INV-2025-001 created');

-- Invoices
INSERT INTO invoices (ord_id, inv_number, is_partial) VALUES 
(6, 'INV-2025-001', 0),
(5, 'INV-2025-002', 1);

-- Invoice Items  
INSERT INTO invoice_items (inv_id, ord_prod_id, qty) VALUES 
(1, 14, 700),
(1, 15, 400),
(2, 12, 800);