CREATE TABLE IF NOT EXISTS buyers (
    id SERIAL PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL,
    contact_name VARCHAR(100) NOT NULL,
    email VARCHAR(200) UNIQUE NOT NULL,
    password_hash VARCHAR(64) NOT NULL,
    country VARCHAR(100) NOT NULL,
    pricing_tier VARCHAR(20) NOT NULL
);

CREATE TABLE IF NOT EXISTS products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    variety VARCHAR(100) NOT NULL,
    harvest_year INTEGER NOT NULL,
    price_per_liter DECIMAL(10,2) NOT NULL,
    stock_liters INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    buyer_id INTEGER REFERENCES buyers(id),
    product_id INTEGER REFERENCES products(id),
    quantity_liters INTEGER NOT NULL,
    price_per_liter DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS shipments (
    id SERIAL PRIMARY KEY,
    order_id INTEGER REFERENCES orders(id),
    tracking_number VARCHAR(50),
    carrier VARCHAR(100),
    status VARCHAR(20) DEFAULT 'preparing',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Products: Kabylie olive oil varieties
INSERT INTO products (name, variety, harvest_year, price_per_liter, stock_liters) VALUES
('Thazarth Chemlal', 'Chemlal', 2025, 850.00, 12000),
('Thazarth Chemlal Reserve', 'Chemlal', 2024, 1100.00, 3500),
('Azeradj Premium', 'Azeradj', 2025, 950.00, 8000),
('Azeradj First Press', 'Azeradj', 2024, 1250.00, 2000),
('Sigoise Blend', 'Sigoise', 2025, 780.00, 15000),
('Kabylie Terroir Mix', 'Chemlal/Azeradj', 2025, 900.00, 6000),
('Bejaia Gold Label', 'Azeradj', 2024, 1400.00, 1500),
('Export Standard', 'Sigoise/Chemlal', 2025, 720.00, 20000);

-- Buyers across export markets (password is sha256 of the plaintext shown in comments)
INSERT INTO buyers (company_name, contact_name, email, password_hash, country, pricing_tier) VALUES
-- password: lyon2024
('Maison Olivier', 'Philippe Durand', 'philippe@maison-olivier.fr', 'a3f2b8c1d4e5f6a7b8c9d0e1f2a3b4c5d6e7f8a9b0c1d2e3f4a5b6c7d8e9f0a1', 'France', 'premium'),
-- password: hamburg24
('Nordol GmbH', 'Katrin Schreiber', 'katrin@nordol.de', 'b4c3d2e1f0a9b8c7d6e5f4a3b2c1d0e9f8a7b6c5d4e3f2a1b0c9d8e7f6a5b4c3', 'Germany', 'standard'),
-- password: montreal24
('Huiles du Monde', 'Jean-Pierre Tremblay', 'jp@huilesdumonde.ca', 'c5d4e3f2a1b0c9d8e7f6a5b4c3d2e1f0a9b8c7d6e5f4a3b2c1d0e9f8a7b6c5d4', 'Canada', 'premium'),
-- password: newyork24
('Mediterranean Imports LLC', 'Sarah Chen', 'sarah@medimports.com', 'd6e5f4a3b2c1d0e9f8a7b6c5d4e3f2a1b0c9d8e7f6a5b4c3d2e1f0a9b8c7d6e5', 'United States', 'standard'),
-- password: paris2024
('Comptoir des Huiles', 'Marie Lefebvre', 'marie@comptoirdeshuiles.fr', 'e7f6a5b4c3d2e1f0a9b8c7d6e5f4a3b2c1d0e9f8a7b6c5d4e3f2a1b0c9d8e7f6', 'France', 'bulk'),
-- password: berlin24
('Bio Olivenhaus', 'Thomas Mueller', 'thomas@bio-olivenhaus.de', 'f8a7b6c5d4e3f2a1b0c9d8e7f6a5b4c3d2e1f0a9b8c7d6e5f4a3b2c1d0e9f8a7', 'Germany', 'premium');

-- Recent orders
INSERT INTO orders (buyer_id, product_id, quantity_liters, price_per_liter, status) VALUES
(1, 1, 500, 850.00, 'shipped'),
(1, 3, 200, 950.00, 'confirmed'),
(2, 5, 1000, 780.00, 'delivered'),
(3, 2, 150, 1100.00, 'shipped'),
(4, 8, 2000, 720.00, 'pending'),
(5, 6, 800, 900.00, 'confirmed'),
(1, 7, 100, 1400.00, 'pending'),
(6, 1, 300, 850.00, 'shipped'),
(2, 4, 75, 1250.00, 'delivered'),
(3, 5, 500, 780.00, 'confirmed'),
(4, 3, 400, 950.00, 'shipped'),
(5, 8, 1500, 720.00, 'delivered');

-- Shipments for shipped/delivered orders
INSERT INTO shipments (order_id, tracking_number, carrier, status) VALUES
(1, 'DZ-FR-2025-0412', 'CMA CGM', 'in_transit'),
(3, 'DZ-DE-2025-0398', 'Hapag-Lloyd', 'delivered'),
(4, 'DZ-CA-2025-0405', 'MSC', 'in_transit'),
(8, 'DZ-DE-2025-0418', 'Hapag-Lloyd', 'in_transit'),
(9, 'DZ-DE-2025-0387', 'DHL Express', 'delivered'),
(11, 'DZ-US-2025-0421', 'Maersk', 'in_transit'),
(12, 'DZ-FR-2025-0390', 'CMA CGM', 'delivered');