-- =====================================================================
-- SUPER PHARMA - Phase 1 Initial Schema
-- MySQL / MariaDB, InnoDB, utf8mb4
-- India market: pincode/state/GSTIN/PAN fields, INR amounts as DECIMAL(10,2)
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. GEOGRAPHY (India: state > city > zone > pincode) - not hardcoded
-- ---------------------------------------------------------------------

CREATE TABLE states (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    code VARCHAR(10) NULL, -- e.g. MH, DL, KA
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cities (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    state_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (state_id) REFERENCES states(id),
    INDEX idx_city_state (state_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Delivery zones: polygons/radii scoped to a city, each with its own charge rules
CREATE TABLE zones (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    city_id INT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    pincode_list TEXT NULL, -- comma-separated pincodes covered, or NULL if polygon-based
    boundary_geojson LONGTEXT NULL, -- optional polygon for map-based zoning
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES cities(id),
    INDEX idx_zone_city (city_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 2. RBAC - Super Admin (full project + backup) + module-based admins
-- ---------------------------------------------------------------------

CREATE TABLE roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE, -- e.g. super_admin, ops_admin, finance_admin, pharmacy_manager
    name VARCHAR(100) NOT NULL,
    is_super_admin TINYINT(1) NOT NULL DEFAULT 0, -- true = sees/backs up everything, bypasses module checks
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Modules mirror the API structure in section 18 of the spec
CREATE TABLE modules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(50) NOT NULL UNIQUE, -- customers, pharmacies, orders, delivery, finance, crm, offers, reports, backup, settings...
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE role_module_permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    module_id INT UNSIGNED NOT NULL,
    can_view TINYINT(1) NOT NULL DEFAULT 0,
    can_create TINYINT(1) NOT NULL DEFAULT 0,
    can_edit TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,
    can_export TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_role_module (role_id, module_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (module_id) REFERENCES modules(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin / internal staff users (Super Admin, module admins, finance, CRM, ops, pharmacy staff logins live in pharmacy_users)
CREATE TABLE admin_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NULL UNIQUE,
    mobile VARCHAR(10) NOT NULL UNIQUE, -- Indian 10-digit
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id),
    INDEX idx_admin_mobile (mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Full-project backup log - Super Admin only
CREATE TABLE backup_logs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    initiated_by INT UNSIGNED NOT NULL, -- admin_users.id, must be super_admin
    backup_type ENUM('full', 'database_only', 'files_only') NOT NULL DEFAULT 'full',
    file_path VARCHAR(500) NULL,
    file_size_bytes BIGINT UNSIGNED NULL,
    status ENUM('pending', 'in_progress', 'completed', 'failed') NOT NULL DEFAULT 'pending',
    error_message TEXT NULL,
    started_at DATETIME NULL,
    completed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (initiated_by) REFERENCES admin_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Audit trail across all sensitive actions (security section 21)
CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_type ENUM('admin', 'pharmacy', 'delivery_partner', 'customer', 'system') NOT NULL,
    actor_id INT UNSIGNED NULL,
    action VARCHAR(100) NOT NULL, -- e.g. order.status_change, pharmacy.verify, backup.create
    entity_type VARCHAR(50) NULL,
    entity_id INT UNSIGNED NULL,
    meta_json TEXT NULL,
    ip_address VARCHAR(45) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_audit_entity (entity_type, entity_id),
    INDEX idx_audit_actor (actor_type, actor_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 3. AUTH - OTP (India mobile-first)
-- ---------------------------------------------------------------------

CREATE TABLE otp_verifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mobile VARCHAR(10) NOT NULL,
    otp_hash VARCHAR(255) NOT NULL,
    purpose ENUM('login', 'registration', 'delivery_pickup', 'delivery_confirm') NOT NULL DEFAULT 'login',
    context_id INT UNSIGNED NULL, -- e.g. order_id for delivery OTPs
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    is_verified TINYINT(1) NOT NULL DEFAULT 0,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_otp_mobile (mobile, purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE auth_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    token VARCHAR(128) NOT NULL UNIQUE,
    owner_type ENUM('customer', 'delivery_partner', 'admin', 'pharmacy_user') NOT NULL,
    owner_id INT UNSIGNED NOT NULL,
    device_info VARCHAR(255) NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_token_owner (owner_type, owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 4. CUSTOMERS
-- ---------------------------------------------------------------------

CREATE TABLE customers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NULL,
    mobile VARCHAR(10) NOT NULL UNIQUE,
    email VARCHAR(150) NULL,
    gender ENUM('male', 'female', 'other') NULL,
    date_of_birth DATE NULL,
    referral_code VARCHAR(20) NULL UNIQUE,
    referred_by INT UNSIGNED NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (referred_by) REFERENCES customers(id),
    INDEX idx_customer_mobile (mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE addresses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    label VARCHAR(30) NULL, -- Home, Work, Other
    contact_name VARCHAR(150) NULL,
    contact_mobile VARCHAR(10) NULL,
    line1 VARCHAR(255) NOT NULL,
    line2 VARCHAR(255) NULL,
    landmark VARCHAR(150) NULL,
    area VARCHAR(100) NULL,
    city_id INT UNSIGNED NULL,
    state_id INT UNSIGNED NULL,
    pincode VARCHAR(6) NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (city_id) REFERENCES cities(id),
    FOREIGN KEY (state_id) REFERENCES states(id),
    INDEX idx_address_customer (customer_id),
    INDEX idx_address_pincode (pincode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Family accounts (future, stubbed now so schema doesn't need rework later)
CREATE TABLE customer_family_members (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    relation VARCHAR(50) NULL,
    date_of_birth DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 5. PHARMACIES / SHOPS
-- ---------------------------------------------------------------------

CREATE TABLE pharmacies (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_name VARCHAR(200) NOT NULL,
    owner_name VARCHAR(150) NOT NULL,
    mobile VARCHAR(10) NOT NULL UNIQUE,
    email VARCHAR(150) NULL,
    password_hash VARCHAR(255) NOT NULL,

    -- Address
    line1 VARCHAR(255) NOT NULL,
    line2 VARCHAR(255) NULL,
    area VARCHAR(100) NULL,
    city_id INT UNSIGNED NOT NULL,
    state_id INT UNSIGNED NOT NULL,
    pincode VARCHAR(6) NOT NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    service_radius_km DECIMAL(5,2) NOT NULL DEFAULT 5.00,
    zone_id INT UNSIGNED NULL,

    -- Verification / compliance (spec section 4)
    drug_license_number VARCHAR(50) NULL,
    drug_license_validity DATE NULL,
    pharmacist_name VARCHAR(150) NULL,
    pharmacist_registration_number VARCHAR(50) NULL,
    gstin VARCHAR(15) NULL, -- optional, India GST number
    pan_number VARCHAR(10) NULL,

    -- Bank details for settlement
    bank_account_name VARCHAR(150) NULL,
    bank_account_number VARCHAR(30) NULL,
    bank_ifsc VARCHAR(11) NULL,
    bank_name VARCHAR(100) NULL,

    verification_status ENUM('pending', 'under_review', 'verified', 'rejected', 'suspended') NOT NULL DEFAULT 'pending',
    verification_notes TEXT NULL,
    verified_by INT UNSIGNED NULL, -- admin_users.id
    verified_at DATETIME NULL,

    is_active TINYINT(1) NOT NULL DEFAULT 1,
    is_accepting_orders TINYINT(1) NOT NULL DEFAULT 1,
    avg_rating DECIMAL(3,2) NOT NULL DEFAULT 0.00,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (city_id) REFERENCES cities(id),
    FOREIGN KEY (state_id) REFERENCES states(id),
    FOREIGN KEY (zone_id) REFERENCES zones(id),
    FOREIGN KEY (verified_by) REFERENCES admin_users(id),
    INDEX idx_pharmacy_pincode (pincode),
    INDEX idx_pharmacy_status (verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pharmacy_documents (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pharmacy_id INT UNSIGNED NOT NULL,
    doc_type ENUM('drug_license', 'gst_certificate', 'pan_card', 'pharmacist_registration', 'shop_establishment', 'other') NOT NULL,
    file_path VARCHAR(500) NOT NULL, -- private storage, served via signed URL only
    status ENUM('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
    reviewed_by INT UNSIGNED NULL,
    reviewed_at DATETIME NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES admin_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pharmacy-side staff logins (owner + staff), scoped to that pharmacy only
CREATE TABLE pharmacy_users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pharmacy_id INT UNSIGNED NOT NULL,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(10) NOT NULL,
    role ENUM('owner', 'manager', 'staff') NOT NULL DEFAULT 'staff',
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_pharmacy_mobile (pharmacy_id, mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 6. CATALOGUE
-- ---------------------------------------------------------------------

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    parent_id INT UNSIGNED NULL,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(120) NOT NULL UNIQUE,
    icon_path VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Master product catalogue (global reference, e.g. from a medicine database)
CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NULL,
    name VARCHAR(255) NOT NULL,
    generic_name VARCHAR(255) NULL,
    manufacturer VARCHAR(150) NULL,
    composition VARCHAR(500) NULL,
    pack_size VARCHAR(50) NULL, -- e.g. "10 tablets", "100ml"
    hsn_code VARCHAR(10) NULL, -- India GST HSN code
    gst_rate DECIMAL(5,2) NOT NULL DEFAULT 12.00,
    requires_prescription TINYINT(1) NOT NULL DEFAULT 0,
    is_scheduled_drug TINYINT(1) NOT NULL DEFAULT 0, -- Schedule H/H1/X - India regulatory flag
    image_path VARCHAR(255) NULL,
    mrp DECIMAL(10,2) NULL, -- printed MRP, reference only - actual selling price is per-pharmacy
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FULLTEXT INDEX ft_product_search (name, generic_name, composition)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-pharmacy price/availability/stock for a product (marketplace model - pharmacy owns inventory)
CREATE TABLE pharmacy_products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pharmacy_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    selling_price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    is_available TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_pharmacy_product (pharmacy_id, product_id),
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_pp_available (is_available)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 7. PRESCRIPTIONS (private storage only)
-- ---------------------------------------------------------------------

CREATE TABLE prescriptions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    file_path VARCHAR(500) NOT NULL, -- image/PDF, private storage, signed-URL access only
    file_type ENUM('image', 'pdf') NOT NULL,
    notes VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_prescription_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-order, per-pharmacy verification workflow for a prescription
CREATE TABLE prescription_reviews (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    prescription_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED NOT NULL,
    pharmacy_id INT UNSIGNED NOT NULL,
    status ENUM('pending', 'accepted', 'rejected', 'clarification_requested') NOT NULL DEFAULT 'pending',
    clarification_note VARCHAR(500) NULL,
    reviewed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id),
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 8. CART
-- ---------------------------------------------------------------------

CREATE TABLE carts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    pharmacy_id INT UNSIGNED NOT NULL, -- one active cart per pharmacy per customer (single-pharmacy checkout)
    status ENUM('active', 'converted', 'abandoned') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
    INDEX idx_cart_customer_status (customer_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cart_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cart_id INT UNSIGNED NOT NULL,
    pharmacy_product_id INT UNSIGNED NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    price_at_add DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (pharmacy_product_id) REFERENCES pharmacy_products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 9. ORDERS (15-state machine, section 6)
-- ---------------------------------------------------------------------

CREATE TABLE orders (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_number VARCHAR(20) NOT NULL UNIQUE, -- human-friendly, e.g. SP-20260920-000123
    customer_id INT UNSIGNED NOT NULL,
    pharmacy_id INT UNSIGNED NOT NULL,
    address_id INT UNSIGNED NOT NULL,
    prescription_id INT UNSIGNED NULL,

    status ENUM(
        'order_placed', 'payment_pending', 'payment_confirmed', 'prescription_verification',
        'pharmacy_confirmation', 'preparing', 'ready_for_pickup', 'delivery_partner_assigned',
        'picked_up', 'out_for_delivery', 'delivered', 'cancelled', 'rejected',
        'failed_delivery', 'refunded'
    ) NOT NULL DEFAULT 'order_placed',

    items_subtotal DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    delivery_charge DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    gst_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    grand_total DECIMAL(10,2) NOT NULL DEFAULT 0.00,

    coupon_id INT UNSIGNED NULL,
    payment_method ENUM('online', 'cod') NOT NULL DEFAULT 'online',
    payment_status ENUM('pending', 'paid', 'failed', 'refunded', 'partial_refund') NOT NULL DEFAULT 'pending',

    delivery_partner_id INT UNSIGNED NULL,
    delivery_zone_id INT UNSIGNED NULL,
    distance_km DECIMAL(6,2) NULL,

    cancel_reason VARCHAR(255) NULL,
    rejection_reason VARCHAR(255) NULL,

    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delivered_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
    FOREIGN KEY (address_id) REFERENCES addresses(id),
    FOREIGN KEY (prescription_id) REFERENCES prescriptions(id),
    FOREIGN KEY (delivery_zone_id) REFERENCES zones(id),
    INDEX idx_order_customer (customer_id),
    INDEX idx_order_pharmacy (pharmacy_id),
    INDEX idx_order_status (status),
    INDEX idx_order_placed_at (placed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE order_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    product_name_snapshot VARCHAR(255) NOT NULL, -- preserved even if product edited later
    quantity INT UNSIGNED NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    gst_rate DECIMAL(5,2) NOT NULL DEFAULT 12.00,
    line_total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Full audit trail of every status transition (spec: "complete order history/audit trail")
CREATE TABLE order_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    from_status VARCHAR(40) NULL,
    to_status VARCHAR(40) NOT NULL,
    changed_by_type ENUM('customer', 'pharmacy', 'delivery_partner', 'admin', 'system') NOT NULL,
    changed_by_id INT UNSIGNED NULL,
    note VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_history_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 10. PAYMENTS
-- ---------------------------------------------------------------------

CREATE TABLE payments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    provider VARCHAR(30) NOT NULL DEFAULT 'razorpay',
    provider_payment_id VARCHAR(100) NULL,
    provider_order_id VARCHAR(100) NULL,
    method VARCHAR(30) NULL, -- upi, card, netbanking, wallet, cod
    amount DECIMAL(10,2) NOT NULL,
    gateway_fee DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    status ENUM('created', 'success', 'failed', 'refunded', 'partial_refund') NOT NULL DEFAULT 'created',
    failure_reason VARCHAR(255) NULL,
    raw_response TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    INDEX idx_payment_order (order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE refunds (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    payment_id INT UNSIGNED NULL,
    amount DECIMAL(10,2) NOT NULL,
    reason VARCHAR(255) NULL,
    status ENUM('initiated', 'processed', 'failed') NOT NULL DEFAULT 'initiated',
    initiated_by INT UNSIGNED NULL, -- admin_users.id
    processed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (payment_id) REFERENCES payments(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 11. DELIVERY PARTNERS + TRACKING
-- ---------------------------------------------------------------------

CREATE TABLE delivery_partners (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(10) NOT NULL UNIQUE,
    email VARCHAR(150) NULL,
    password_hash VARCHAR(255) NOT NULL,
    vehicle_type ENUM('bike', 'scooter', 'bicycle', 'on_foot', 'car') NOT NULL DEFAULT 'bike',
    vehicle_number VARCHAR(20) NULL,
    driving_license_number VARCHAR(30) NULL,
    aadhaar_verified TINYINT(1) NOT NULL DEFAULT 0, -- boolean flag only; no ID number stored here
    bank_account_number VARCHAR(30) NULL,
    bank_ifsc VARCHAR(11) NULL,
    bank_account_name VARCHAR(150) NULL,
    city_id INT UNSIGNED NULL,
    zone_id INT UNSIGNED NULL,
    online_status ENUM('online', 'offline') NOT NULL DEFAULT 'offline',
    verification_status ENUM('pending', 'verified', 'rejected', 'suspended') NOT NULL DEFAULT 'pending',
    avg_rating DECIMAL(3,2) NOT NULL DEFAULT 0.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES cities(id),
    FOREIGN KEY (zone_id) REFERENCES zones(id),
    INDEX idx_dp_mobile (mobile),
    INDEX idx_dp_status (online_status, verification_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE delivery_assignments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    delivery_partner_id INT UNSIGNED NOT NULL,
    status ENUM('assigned', 'accepted', 'rejected', 'picked_up', 'delivered', 'failed', 'reassigned') NOT NULL DEFAULT 'assigned',
    pickup_otp VARCHAR(6) NULL,
    delivery_otp VARCHAR(6) NULL,
    pickup_confirmed_at DATETIME NULL,
    delivered_at DATETIME NULL,
    proof_of_delivery_path VARCHAR(500) NULL, -- photo signature etc, private storage
    cod_collected_amount DECIMAL(10,2) NULL,
    failure_reason VARCHAR(255) NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(id),
    INDEX idx_da_order (order_id),
    INDEX idx_da_partner (delivery_partner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Historical tracking only - CURRENT location lives in Redis per spec section 14, not written here per-ping.
-- This table stores periodic snapshots (e.g. every 30-60s or on status change) for route replay/reports.
CREATE TABLE delivery_tracking_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    delivery_partner_id INT UNSIGNED NOT NULL,
    latitude DECIMAL(10,7) NOT NULL,
    longitude DECIMAL(10,7) NOT NULL,
    speed_kmph DECIMAL(5,2) NULL,
    heading DECIMAL(5,2) NULL,
    accuracy_meters DECIMAL(6,2) NULL,
    recorded_at DATETIME NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(id),
    INDEX idx_tracking_order (order_id, recorded_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 12. COMMISSION ENGINE (section 8)
-- ---------------------------------------------------------------------

-- Priority: product-specific > category-specific > pharmacy-specific > campaign > platform default
CREATE TABLE commission_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope ENUM('platform_default', 'pharmacy', 'category', 'product', 'campaign') NOT NULL,
    pharmacy_id INT UNSIGNED NULL,
    category_id INT UNSIGNED NULL,
    product_id INT UNSIGNED NULL,
    campaign_name VARCHAR(100) NULL,
    commission_type ENUM('percentage', 'flat') NOT NULL DEFAULT 'percentage',
    commission_value DECIMAL(10,2) NOT NULL, -- % or flat INR depending on type
    valid_from DATE NULL,
    valid_to DATE NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_commission_scope (scope, pharmacy_id, category_id, product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One record per completed order - auto-calculated, immutable once written
CREATE TABLE order_commissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL UNIQUE,
    pharmacy_id INT UNSIGNED NOT NULL,
    order_amount DECIMAL(10,2) NOT NULL,
    commission_rule_id INT UNSIGNED NULL,
    commission_rate DECIMAL(10,2) NOT NULL, -- % applied
    commission_amount DECIMAL(10,2) NOT NULL,
    pharmacy_payable_amount DECIMAL(10,2) NOT NULL, -- order_amount - commission_amount
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
    FOREIGN KEY (commission_rule_id) REFERENCES commission_rules(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 13. DELIVERY CHARGE ENGINE (section 9)
-- ---------------------------------------------------------------------

CREATE TABLE delivery_charge_configs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zone_id INT UNSIGNED NULL, -- NULL = platform-wide default
    base_charge DECIMAL(10,2) NOT NULL DEFAULT 20.00,
    per_km_charge DECIMAL(10,2) NOT NULL DEFAULT 5.00,
    free_km_radius DECIMAL(5,2) NOT NULL DEFAULT 2.00, -- distance covered by base charge
    min_order_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    free_delivery_threshold DECIMAL(10,2) NULL, -- order value above which delivery is free
    surge_multiplier DECIMAL(4,2) NOT NULL DEFAULT 1.00, -- rain/night/festival surge
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (zone_id) REFERENCES zones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE delivery_partner_payout_rules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    zone_id INT UNSIGNED NULL,
    base_payout DECIMAL(10,2) NOT NULL DEFAULT 15.00,
    per_km_payout DECIMAL(10,2) NOT NULL DEFAULT 4.00,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (zone_id) REFERENCES zones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 14. FINANCE & SETTLEMENT (section 10)
-- ---------------------------------------------------------------------

CREATE TABLE pharmacy_settlements (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    pharmacy_id INT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_commission DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    total_refunds DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    adjustments DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount_payable DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft', 'approved', 'paid') NOT NULL DEFAULT 'draft',
    payment_reference VARCHAR(100) NULL,
    paid_at DATETIME NULL,
    created_by INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
    INDEX idx_psettle_pharmacy (pharmacy_id, period_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE delivery_settlements (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    delivery_partner_id INT UNSIGNED NOT NULL,
    period_start DATE NOT NULL,
    period_end DATE NOT NULL,
    total_earnings DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    incentives DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    bonuses DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    cod_collected DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    cod_remitted DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    adjustments DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    amount_payable DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    status ENUM('draft', 'approved', 'paid') NOT NULL DEFAULT 'draft',
    payment_reference VARCHAR(100) NULL,
    paid_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (delivery_partner_id) REFERENCES delivery_partners(id),
    INDEX idx_dsettle_partner (delivery_partner_id, period_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Platform-level ledger: GMV, commission income, delivery income, payouts, refunds, gateway charges, expenses
CREATE TABLE platform_ledger (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entry_type ENUM(
        'gmv', 'commission_income', 'delivery_income', 'delivery_payout',
        'refund', 'gateway_charge', 'expense', 'adjustment'
    ) NOT NULL,
    order_id INT UNSIGNED NULL,
    amount DECIMAL(12,2) NOT NULL, -- positive = inflow, negative = outflow
    description VARCHAR(255) NULL,
    entry_date DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    INDEX idx_ledger_type_date (entry_type, entry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 15. SALES / CRM (section 11)
-- ---------------------------------------------------------------------

CREATE TABLE crm_leads (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_type ENUM('pharmacy', 'doctor', 'lab', 'corporate') NOT NULL,
    name VARCHAR(150) NOT NULL,
    mobile VARCHAR(10) NULL,
    email VARCHAR(150) NULL,
    city_id INT UNSIGNED NULL,
    source VARCHAR(50) NULL, -- referral, cold_call, walk_in, campaign, website
    status ENUM('new', 'contacted', 'follow_up', 'negotiation', 'converted', 'lost') NOT NULL DEFAULT 'new',
    assigned_to INT UNSIGNED NULL, -- admin_users.id (salesperson)
    converted_entity_id INT UNSIGNED NULL, -- pharmacies.id once converted
    lost_reason VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (city_id) REFERENCES cities(id),
    FOREIGN KEY (assigned_to) REFERENCES admin_users(id),
    INDEX idx_lead_status (status),
    INDEX idx_lead_assigned (assigned_to)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE crm_lead_notes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id INT UNSIGNED NOT NULL,
    note TEXT NOT NULL,
    next_follow_up_at DATETIME NULL,
    created_by INT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_id) REFERENCES crm_leads(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES admin_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE crm_sales_targets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_user_id INT UNSIGNED NOT NULL,
    period_month DATE NOT NULL, -- first day of month
    target_leads INT UNSIGNED NOT NULL DEFAULT 0,
    target_conversions INT UNSIGNED NOT NULL DEFAULT 0,
    achieved_conversions INT UNSIGNED NOT NULL DEFAULT 0,
    incentive_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_user_id) REFERENCES admin_users(id),
    UNIQUE KEY uniq_target_month (admin_user_id, period_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 16. OFFERS & COUPONS (section 12)
-- ---------------------------------------------------------------------

CREATE TABLE coupons (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(30) NOT NULL UNIQUE,
    title VARCHAR(150) NOT NULL,
    type ENUM('flat', 'percentage', 'first_order', 'referral', 'festival', 'health_package', 'membership') NOT NULL,
    discount_value DECIMAL(10,2) NOT NULL,
    max_discount_amount DECIMAL(10,2) NULL,
    min_order_value DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    usage_limit_total INT UNSIGNED NULL,
    usage_limit_per_customer INT UNSIGNED NULL DEFAULT 1,
    used_count INT UNSIGNED NOT NULL DEFAULT 0,
    pharmacy_id INT UNSIGNED NULL, -- NULL = platform-wide
    category_id INT UNSIGNED NULL,
    eligible_customer_segment ENUM('all', 'new_customers', 'existing_customers') NOT NULL DEFAULT 'all',
    starts_at DATETIME NOT NULL,
    ends_at DATETIME NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id),
    FOREIGN KEY (category_id) REFERENCES categories(id),
    INDEX idx_coupon_active_dates (is_active, starts_at, ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE coupon_usages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    coupon_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED NOT NULL,
    discount_applied DECIMAL(10,2) NOT NULL,
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 17. NOTIFICATIONS (section 13)
-- ---------------------------------------------------------------------

CREATE TABLE notifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    recipient_type ENUM('customer', 'pharmacy', 'delivery_partner', 'admin') NOT NULL,
    recipient_id INT UNSIGNED NOT NULL,
    channel ENUM('push', 'sms', 'email', 'whatsapp', 'in_app') NOT NULL,
    event_type VARCHAR(50) NOT NULL, -- order_placed, pharmacy_accepted, out_for_delivery, delivered, etc.
    title VARCHAR(150) NULL,
    message TEXT NOT NULL,
    related_order_id INT UNSIGNED NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    status ENUM('queued', 'sent', 'failed') NOT NULL DEFAULT 'queued',
    sent_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (related_order_id) REFERENCES orders(id),
    INDEX idx_notif_recipient (recipient_type, recipient_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- 18. RATINGS / REVIEWS / SUPPORT
-- ---------------------------------------------------------------------

CREATE TABLE reviews (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    order_id INT UNSIGNED NOT NULL,
    customer_id INT UNSIGNED NOT NULL,
    pharmacy_rating TINYINT UNSIGNED NULL, -- 1-5
    pharmacy_review TEXT NULL,
    delivery_rating TINYINT UNSIGNED NULL,
    delivery_review TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE support_tickets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    order_id INT UNSIGNED NULL,
    subject VARCHAR(200) NOT NULL,
    description TEXT NOT NULL,
    status ENUM('open', 'in_progress', 'resolved', 'closed') NOT NULL DEFAULT 'open',
    assigned_to INT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (assigned_to) REFERENCES admin_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;