-- Dha-Mi Atelier Database Schema
-- Designed for Hostinger PHP/MySQL Deployment

-- 1. Admin Users Auth
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Note: Insert default admin. Password "admin" will be hashed during setup, but for now we could insert a raw string and handle hashing in PHP, or just insert the PHP password_hash equivalent.
-- MD5 is unsafe, PHP uses BCRYPT. For simplicity, we can have the login script compare plain text initially or require a setup script.

-- 2. Inventory 
CREATE TABLE IF NOT EXISTS inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category ENUM('fashion', 'jewelry', 'lingerie', 'cosmetics') NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT DEFAULT 0,
    image_url VARCHAR(500),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Customers / CRM
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(20),
    loyalty_points INT DEFAULT 0,
    lead_source VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 4. WhatsApp Automations
CREATE TABLE IF NOT EXISTS whatsapp_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rule_key VARCHAR(50) NOT NULL UNIQUE, -- e.g., 'wa_cart', 'wa_festive', 'wa_general'
    template_text TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Initialize default rules
INSERT IGNORE INTO whatsapp_rules (rule_key, template_text) VALUES 
('wa_cart', 'Hi {{name}}! You left something exquisite in your cart. Complete your purchase now and enjoy an exclusive Festive Offer of 10% off using code DHAMI10.'),
('wa_festive', 'Thank you for reaching out! In celebration of the ongoing festival season, we invite you to browse our latest Volume IV collection.'),
('wa_general', 'Welcome to Dha-Mi Atelier. Your query has been received by our digital assistant. An artisan guide will be with you shortly.');

-- 5. Site CMS Content
CREATE TABLE IF NOT EXISTS site_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    page_id VARCHAR(100) NOT NULL UNIQUE, -- e.g. 'index.html', 'story.html'
    html_content LONGTEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);