-- Migration to add 5 new features: Visual Specs, Shipping, Multiple Photos, and WhatsApp Integration

-- 1. Multiple Product Photos
CREATE TABLE IF NOT EXISTS product_images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    image_path TEXT NOT NULL,
    is_primary INTEGER DEFAULT 0,
    sort_order INTEGER DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- 2. Visual Specifications System
CREATE TABLE IF NOT EXISTS product_specs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    name TEXT NOT NULL, -- e.g., "Paper Type", "Finish"
    type TEXT DEFAULT 'text', -- 'text' or 'image'
    required INTEGER DEFAULT 1,
    sort_order INTEGER DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS product_spec_options (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    spec_id INTEGER NOT NULL,
    label TEXT NOT NULL, -- e.g., "Matte", "Gloss"
    image_path TEXT, -- Optional image for visual specs
    price_adjustment REAL DEFAULT 0,
    sort_order INTEGER DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (spec_id) REFERENCES product_specs(id) ON DELETE CASCADE
);

-- 3. Shipping & Delivery Information Settings
CREATE TABLE IF NOT EXISTS delivery_options (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL, -- e.g., "Standard Delivery", "Lagos Mainland Pickup"
    city TEXT,
    state TEXT,
    is_pickup INTEGER DEFAULT 0,
    timeline TEXT, -- e.g., "2-3 working days"
    cost REAL DEFAULT 0,
    active INTEGER DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);

-- 4. WhatsApp & Other Settings
INSERT OR IGNORE INTO settings (key, value) VALUES ('whatsapp_number', '+2348000000000');
INSERT OR IGNORE INTO settings (key, value) VALUES ('whatsapp_checkout_enabled', '1');
INSERT OR IGNORE INTO settings (key, value) VALUES ('whatsapp_support_enabled', '1');
INSERT OR IGNORE INTO settings (key, value) VALUES ('whatsapp_default_message', 'Hello, I would like to inquire about an order.');

-- 5. Add a column to products for "estimated delivery" if needed, though delivery_options is better.
-- Let's add a "delivery_info" field to products for product-specific delivery notes.
ALTER TABLE products ADD COLUMN delivery_info TEXT;