USE engine;

CREATE TABLE IF NOT EXISTS unified_orders (
    id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
    engine_payment_id   BIGINT NULL,
    store_order_id      INT UNSIGNED NULL,
    order_ref           VARCHAR(32) NOT NULL UNIQUE,
    customer_hash       VARCHAR(64) NOT NULL,
    total_amount        DECIMAL(10,2) NOT NULL,
    currency            CHAR(3) NOT NULL DEFAULT 'CAD',
    status              ENUM('pending','confirmed','processing','shipped','delivered','cancelled','refunded') DEFAULT 'pending',
    payment_status      ENUM('pending','completed','failed','refunded') DEFAULT 'pending',
    fulfillment_status  ENUM('pending','processing','shipped','delivered','cancelled') DEFAULT 'pending',
    shipping_address    JSON NULL,
    tracking_number     VARCHAR(64) NULL,
    source_system       ENUM('engine','store','both') DEFAULT 'engine',
    sync_version        INT DEFAULT 1,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_order_ref (order_ref),
    INDEX idx_customer_hash (customer_hash),
    INDEX idx_status (status),
    INDEX idx_engine_payment (engine_payment_id),
    INDEX idx_store_order (store_order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unified_customers (
    id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_hash       VARCHAR(64) NOT NULL UNIQUE,
    email_hash          VARCHAR(64) NOT NULL,
    phone_hash          VARCHAR(64) NULL,
    name_hash           VARCHAR(64) NULL,
    trust_score         DECIMAL(3,2) DEFAULT 0.10,
    purchase_count      INT DEFAULT 0,
    total_spent         DECIMAL(10,2) DEFAULT 0.00,
    currency            CHAR(3) DEFAULT 'CAD',
    first_purchase_at   TIMESTAMP NULL,
    last_purchase_at    TIMESTAMP NULL,
    engine_session_id   VARCHAR(64) NULL,
    store_user_id       INT UNSIGNED NULL,
    risk_level          ENUM('low','medium','high','critical') DEFAULT 'low',
    sync_version        INT DEFAULT 1,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_customer_hash (customer_hash),
    INDEX idx_email_hash (email_hash),
    INDEX idx_trust_score (trust_score),
    INDEX idx_risk_level (risk_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unified_inventory (
    id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
    sku                 VARCHAR(50) NOT NULL UNIQUE,
    product_name        VARCHAR(200) NOT NULL,
    store_product_id    INT UNSIGNED NULL,
    engine_product_id   BIGINT NULL,
    quantity_available  INT DEFAULT 0,
    quantity_reserved   INT DEFAULT 0,
    reorder_threshold   INT DEFAULT 10,
    unit_cost           DECIMAL(10,2) DEFAULT 0.00,
    sale_price          DECIMAL(10,2) DEFAULT 0.00,
    currency            CHAR(3) DEFAULT 'CAD',
    warehouse_location  VARCHAR(100) NULL,
    last_counted_at     TIMESTAMP NULL,
    sync_version        INT DEFAULT 1,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_sku (sku),
    INDEX idx_quantity (quantity_available),
    INDEX idx_reorder (reorder_threshold)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unified_transactions (
    id                  BIGINT AUTO_INCREMENT PRIMARY KEY,
    unified_order_id    BIGINT NOT NULL,
    payment_method      ENUM('stripe','xmr','usdt-trc','usdc-poly','etransfer') NOT NULL,
    amount              DECIMAL(10,2) NOT NULL,
    currency            CHAR(3) NOT NULL DEFAULT 'CAD',
    stripe_intent_id    VARCHAR(64) NULL,
    crypto_tx_hash      VARCHAR(200) NULL,
    etransfer_ref       VARCHAR(100) NULL,
    direction           ENUM('credit','debit','refund','chargeback') DEFAULT 'credit',
    status              ENUM('pending','completed','failed','reversed') DEFAULT 'pending',
    settled_at          TIMESTAMP NULL,
    sync_version        INT DEFAULT 1,
    created_at          TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_order (unified_order_id),
    INDEX idx_method (payment_method),
    INDEX idx_status (status),
    INDEX idx_stripe (stripe_intent_id),
    CONSTRAINT fk_ut_order FOREIGN KEY (unified_order_id) REFERENCES unified_orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unified_audit_log (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    entity_type     VARCHAR(50) NOT NULL,
    entity_id       BIGINT NOT NULL,
    action          VARCHAR(50) NOT NULL,
    actor_hash      VARCHAR(64) NULL,
    old_value       JSON NULL,
    new_value       JSON NULL,
    source_system   ENUM('engine','store','unified') NOT NULL,
    ip_address      VARCHAR(45) NULL,
    session_id      VARCHAR(64) NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_action (action),
    INDEX idx_actor (actor_hash),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS node_store_map (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    node_id         VARCHAR(6) NOT NULL,
    store_id        INT UNSIGNED NOT NULL,
    store_domain    VARCHAR(200) NULL,
    mapping_type    ENUM('primary','failover','mirror') DEFAULT 'primary',
    is_active       BOOLEAN DEFAULT TRUE,
    last_sync_at    TIMESTAMP NULL,
    sync_latency_ms INT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_node_store (node_id, store_id),
    INDEX idx_node (node_id),
    INDEX idx_store (store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sync_queue (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    entity_type     VARCHAR(50) NOT NULL,
    entity_id       VARCHAR(100) NOT NULL,
    action          ENUM('insert','update','delete','sync') NOT NULL,
    source_system   ENUM('engine','store') NOT NULL,
    target_system   ENUM('engine','store') NOT NULL,
    payload         JSON NULL,
    status          ENUM('pending','processing','completed','failed') DEFAULT 'pending',
    retry_count     INT DEFAULT 0,
    max_retries     INT DEFAULT 3,
    error_message   TEXT NULL,
    processed_at    TIMESTAMP NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_entity (entity_type, entity_id),
    INDEX idx_status (status),
    INDEX idx_source_target (source_system, target_system),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS unified_notifications (
    id              BIGINT AUTO_INCREMENT PRIMARY KEY,
    recipient_hash  VARCHAR(64) NOT NULL,
    channel         ENUM('email','sms','webhook','push') NOT NULL,
    template_key    VARCHAR(100) NOT NULL,
    subject         VARCHAR(500) NULL,
    body            TEXT NULL,
    payload         JSON NULL,
    status          ENUM('pending','queued','sent','failed','bounced') DEFAULT 'pending',
    priority        ENUM('low','normal','high','critical') DEFAULT 'normal',
    scheduled_at    TIMESTAMP NULL,
    sent_at         TIMESTAMP NULL,
    error_detail    TEXT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_recipient (recipient_hash),
    INDEX idx_status (status),
    INDEX idx_channel (channel),
    INDEX idx_scheduled (scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
