-- ============================================================
-- Stiles Energy LPG App - MySQL Schema  (run this to set up the DB)
-- ============================================================

CREATE DATABASE IF NOT EXISTS stiles_lpg CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE stiles_lpg;

-- ============================================================
-- users
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id          VARCHAR(36)  NOT NULL PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    email       VARCHAR(150) NOT NULL UNIQUE,
    phone       VARCHAR(30)  DEFAULT NULL,
    password    VARCHAR(255) NOT NULL,
    role        ENUM('customer','admin') NOT NULL DEFAULT 'customer',
    address     TEXT         DEFAULT NULL,
    is_active   TINYINT(1)  NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- products
-- image_url used by UI pages (accessories.php, admin/products.php)
-- ============================================================
CREATE TABLE IF NOT EXISTS products (
    id          VARCHAR(36)   NOT NULL PRIMARY KEY,
    name        VARCHAR(200)  NOT NULL,
    description TEXT          DEFAULT NULL,
    price       DECIMAL(12,2) NOT NULL,
    category    VARCHAR(60)   NOT NULL,   -- 'lpg' | 'accessory'
    image_url   VARCHAR(500)  DEFAULT NULL,
    stock       INT           NOT NULL DEFAULT 0,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- orders
-- ============================================================
CREATE TABLE IF NOT EXISTS orders (
    id             VARCHAR(36)   NOT NULL PRIMARY KEY,
    user_id        VARCHAR(36)   NOT NULL,
    status         VARCHAR(40)   NOT NULL DEFAULT 'pending',
    -- pending | confirmed | out_for_delivery | delivered | cancelled
    total          DECIMAL(12,2) NOT NULL,
    fulfillment    VARCHAR(20)   NOT NULL DEFAULT 'delivery',  -- 'delivery' | 'pickup'
    payment_method VARCHAR(20)   NOT NULL DEFAULT 'cod',       -- always 'cod'
    address        TEXT          DEFAULT NULL,
    phone          VARCHAR(30)   DEFAULT NULL,
    notes          TEXT          DEFAULT NULL,
    created_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

-- ============================================================
-- order_items
-- ============================================================
CREATE TABLE IF NOT EXISTS order_items (
    id           VARCHAR(36)   NOT NULL PRIMARY KEY,
    order_id     VARCHAR(36)   NOT NULL,
    product_id   VARCHAR(36)   DEFAULT NULL,
    product_name VARCHAR(200)  DEFAULT NULL,
    category     VARCHAR(60)   DEFAULT NULL,
    quantity     INT           NOT NULL,
    price        DECIMAL(12,2) NOT NULL,
    FOREIGN KEY (order_id)   REFERENCES orders(id)   ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
);

-- ============================================================
-- gallery  (used by gallery.php and admin/gallery.php)
-- ============================================================
CREATE TABLE IF NOT EXISTS gallery (
    id          VARCHAR(36)  NOT NULL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    description TEXT         DEFAULT NULL,
    image_url   VARCHAR(500) NOT NULL,
    media_type  VARCHAR(10)  DEFAULT 'image',
    category    VARCHAR(80)  DEFAULT 'General',
    is_active   TINYINT(1)  NOT NULL DEFAULT 1,
    created_at  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- messages  (used by contact.php and admin/messages.php)
-- ============================================================
CREATE TABLE IF NOT EXISTS messages (
    id         VARCHAR(36)  NOT NULL PRIMARY KEY,
    name       VARCHAR(150) NOT NULL,
    email      VARCHAR(150) DEFAULT NULL,
    phone      VARCHAR(30)  DEFAULT NULL,
    subject    VARCHAR(200) DEFAULT NULL,
    message    TEXT         NOT NULL,
    is_read    TINYINT(1)  NOT NULL DEFAULT 0,
    created_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- site_settings  (global key/value config managed by admin)
-- ============================================================
CREATE TABLE IF NOT EXISTS site_settings (
    setting_key   VARCHAR(100) NOT NULL PRIMARY KEY,
    setting_value TEXT         NOT NULL,
    updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ============================================================
-- Seed data
-- ============================================================

-- Default admin account  (password: admin123)
INSERT IGNORE INTO users (id, name, email, password, role, is_active)
VALUES (
    'admin-001',
    'Admin',
    'admin@stileenergy.com',
    '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
    'admin',
    1
);

-- Sample LPG products
INSERT IGNORE INTO products (id, name, description, price, category, stock, is_active)
VALUES
    ('prod-lpg-001', '3kg Cylinder', 'Perfect for small households and camping.', 3500.00, 'lpg', 50, 1),
    ('prod-lpg-002', '6kg Cylinder', 'Ideal for small families.', 6800.00, 'lpg', 40, 1),
    ('prod-lpg-003', '12.5kg Cylinder', 'Most popular size for medium families.', 13500.00, 'lpg', 30, 1),
    ('prod-lpg-004', '25kg Cylinder', 'For large households and businesses.', 26000.00, 'lpg', 15, 1),
    ('prod-lpg-005', '50kg Cylinder', 'Industrial and commercial use.', 51000.00, 'lpg', 10, 1);

-- Sample accessories
INSERT IGNORE INTO products (id, name, description, price, category, stock, is_active)
VALUES
    ('prod-acc-001', 'Gas Regulator', 'High-pressure safety regulator, universal fit.', 2500.00, 'accessory', 100, 1),
    ('prod-acc-002', 'Gas Hose (1.5m)', 'Reinforced rubber hose, 1.5 metres.', 1200.00, 'accessory', 80, 1),
    ('prod-acc-003', '2-Burner Gas Cooker', 'Stainless steel top, auto-ignition.', 18000.00, 'accessory', 20, 1);

-- Default website contact details (editable from admin settings)
INSERT IGNORE INTO site_settings (setting_key, setting_value) VALUES
    ('contact_phone', '+234 800 000 0000'),
    ('contact_email', 'info@stileenergylpg.com'),
    ('contact_location', 'Lagos, Nigeria'),
    ('contact_hours', 'Mon - Sat, 7:00 AM - 8:00 PM');
