-- ShopiDate + Dynamic Discounts — Database Schema
-- Run this once against your MySQL database (via cPanel phpMyAdmin or CLI)

CREATE TABLE IF NOT EXISTS shops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_domain VARCHAR(255) NOT NULL UNIQUE,
    access_token VARCHAR(255) NOT NULL,
    scope VARCHAR(500) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    plan VARCHAR(50) DEFAULT 'free',
    installed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    uninstalled_at DATETIME DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value TEXT,
    UNIQUE KEY uniq_shop_key (shop_id, setting_key),
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_slots (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    day_of_week TINYINT NOT NULL,          -- 0=Sunday ... 6=Saturday
    start_time TIME NOT NULL,
    end_time TIME NOT NULL,
    max_orders INT DEFAULT 0,              -- 0 = unlimited
    label VARCHAR(100) DEFAULT NULL,       -- e.g. "Morning", "Evening"
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS blackout_dates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    blackout_date DATE NOT NULL,
    reason VARCHAR(255) DEFAULT NULL,
    is_recurring_yearly TINYINT(1) DEFAULT 0,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS delivery_zones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    zip_codes TEXT,                        -- comma separated, supports wildcards e.g. 900*
    countries VARCHAR(255) DEFAULT NULL,
    extra_fee DECIMAL(10,2) DEFAULT 0.00,
    is_active TINYINT(1) DEFAULT 1,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders_delivery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    order_id BIGINT NOT NULL,
    order_name VARCHAR(50) DEFAULT NULL,
    customer_name VARCHAR(255) DEFAULT NULL,
    customer_email VARCHAR(255) DEFAULT NULL,
    delivery_date DATE DEFAULT NULL,
    delivery_slot VARCHAR(100) DEFAULT NULL,
    delivery_zone VARCHAR(150) DEFAULT NULL,
    status ENUM('pending','confirmed','fulfilled','cancelled') DEFAULT 'pending',
    order_total DECIMAL(10,2) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_order (shop_id, order_id),
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS discount_rules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    name VARCHAR(200) NOT NULL,
    rule_type ENUM('cart_value','product','collection','customer_tag','flash_sale','bogo') NOT NULL,
    discount_method ENUM('percentage','fixed_amount') DEFAULT 'percentage',
    discount_value DECIMAL(10,2) NOT NULL DEFAULT 0,
    conditions JSON DEFAULT NULL,          -- flexible rule conditions (thresholds, ids, tags)
    starts_at DATETIME DEFAULT NULL,
    ends_at DATETIME DEFAULT NULL,
    priority INT DEFAULT 10,
    is_active TINYINT(1) DEFAULT 1,
    shopify_discount_id VARCHAR(100) DEFAULT NULL,  -- GraphQL GID of the Automatic Discount in Shopify
    sync_status ENUM('pending','synced','error') DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS discount_usage_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    discount_rule_id INT DEFAULT NULL,
    order_id BIGINT NOT NULL,
    order_name VARCHAR(50) DEFAULT NULL,
    amount_saved DECIMAL(10,2) DEFAULT 0.00,
    order_total DECIMAL(10,2) DEFAULT 0.00,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT NOT NULL,
    action VARCHAR(150) NOT NULL,
    description TEXT,
    meta JSON DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shop_id) REFERENCES shops(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS webhook_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT DEFAULT NULL,
    topic VARCHAR(100) NOT NULL,
    payload JSON DEFAULT NULL,
    status ENUM('received','processed','failed') DEFAULT 'received',
    error_message TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS error_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    shop_id INT DEFAULT NULL,
    message TEXT NOT NULL,
    context TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;