-- Products + Click & Collect orders (seed ≤6)
USE BarberShop;

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(160) NOT NULL,
    short_description VARCHAR(255) NULL,
    description TEXT NULL,
    price DECIMAL(10,2) NOT NULL DEFAULT 0,
    stock INT NOT NULL DEFAULT 0,
    image_path VARCHAR(255) NULL,
    category VARCHAR(80) DEFAULT 'Grooming',
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    UNIQUE KEY uq_products_tenant_slug (tenant_id, slug),
    INDEX idx_products_tenant_active (tenant_id, is_active, display_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shop_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    user_id INT NULL,
    customer_name VARCHAR(150) NOT NULL,
    customer_phone VARCHAR(50) NULL,
    customer_email VARCHAR(150) NULL,
    status VARCHAR(30) DEFAULT 'pending_pickup',
    fulfillment VARCHAR(30) DEFAULT 'pickup',
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_orders_tenant (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS shop_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NULL,
    product_name VARCHAR(150) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    qty INT NOT NULL DEFAULT 1,
    line_total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES shop_orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

DELETE FROM products WHERE tenant_id = 1 AND is_active = 1;

INSERT INTO products (tenant_id, name, slug, short_description, description, price, stock, category, is_active, display_order) VALUES
(1, 'Matte Clay Pomade', 'matte-clay-pomade', 'Medium hold, natural finish.', 'Shop favorite clay for texture and control without shine. Water-based wash-out.', 18.00, 24, 'Styling', 1, 1),
(1, 'Beard Oil - Cedar', 'beard-oil-cedar', 'Softens and conditions.', 'Lightweight cedarwood blend to tame itch and add healthy sheen.', 16.00, 30, 'Beard', 1, 2),
(1, 'Sea Salt Spray', 'sea-salt-spray', 'Beach texture, light hold.', 'Lift and grit for casual styles. Mist on damp or dry hair.', 14.00, 20, 'Styling', 1, 3),
(1, 'Daily Shampoo', 'daily-shampoo', 'Clean without stripping.', 'Gentle daily wash for all hair types. Barbershop scent.', 12.00, 40, 'Wash', 1, 4),
(1, 'Aftershave Balm', 'aftershave-balm', 'Calms post-shave skin.', 'Alcohol-free balm with aloe. Use after hot towel shaves.', 15.00, 18, 'Skin', 1, 5),
(1, 'Beard Brush', 'beard-brush', 'Boar bristle grooming brush.', 'Distributes oils evenly and shapes growth. Fits travel kit.', 22.00, 12, 'Tools', 1, 6);
