-- ============================================================
-- xLoyalty – Clover Connector · Complete DB Schema
-- Run via cPanel phpMyAdmin on your new xloyalty_clover DB
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ─── clover_merchants ────────────────────────────────────────────────────────
-- One row per connected Clover merchant.
-- access_token is AES-256-CBC encrypted (see config.php / token_encrypt).
CREATE TABLE IF NOT EXISTS `clover_merchants` (
    `id`                   INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    `merchant_id`          VARCHAR(25)     NOT NULL  COMMENT 'Clover merchant id',
    `access_token`         TEXT            NOT NULL  COMMENT 'AES-256-CBC encrypted Clover access token',
    `employee_id`          VARCHAR(25)     DEFAULT NULL,
    `xloyalty_account_id`  VARCHAR(60)     DEFAULT NULL COMMENT 'xLoyalty account slug, e.g. client_45',
    `created_at`           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_merchant` (`merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── clover_customers_sync ───────────────────────────────────────────────────
-- Maps Clover customer IDs → phone numbers (for order sync lookup).
CREATE TABLE IF NOT EXISTS `clover_customers_sync` (
    `id`                  INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    `merchant_id`         VARCHAR(25)     NOT NULL,
    `clover_customer_id`  VARCHAR(50)     NOT NULL,
    `phone`               VARCHAR(20)     NOT NULL,
    `synced_at`           DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_customer` (`merchant_id`, `clover_customer_id`),
    KEY `idx_phone` (`merchant_id`, `phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── clover_orders_sync ──────────────────────────────────────────────────────
-- Tracks which Clover orders have already been sent to xLoyalty (idempotency).
CREATE TABLE IF NOT EXISTS `clover_orders_sync` (
    `id`               INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    `merchant_id`      VARCHAR(25)     NOT NULL,
    `clover_order_id`  VARCHAR(150)    NOT NULL COMMENT 'order_id:payment_id',
    `amount`           DECIMAL(10,2)   NOT NULL,
    `synced_at`        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_order` (`merchant_id`, `clover_order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── clover_redemptions ──────────────────────────────────────────────────────
-- Audit log + idempotency guard for point redemptions.
-- UNIQUE KEY prevents double-redeem for the same order.
CREATE TABLE IF NOT EXISTS `clover_redemptions` (
    `id`            INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    `merchant_id`   VARCHAR(25)     NOT NULL,
    `order_id`      VARCHAR(150)    NOT NULL,
    `phone`         VARCHAR(20)     NOT NULL,
    `discount_eur`  DECIMAL(10,2)   NOT NULL,
    `points_spent`  INT UNSIGNED    NOT NULL DEFAULT 0,
    `client_ip`     VARCHAR(45)     DEFAULT NULL,
    `redeemed_at`   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_redemption` (`merchant_id`, `order_id`),
    KEY `idx_phone`  (`merchant_id`, `phone`),
    KEY `idx_date`   (`redeemed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── clover_customer_map ─────────────────────────────────────────────────────
-- Maps xLoyalty member IDs → Clover customer IDs (for push script).
CREATE TABLE IF NOT EXISTS `clover_customer_map` (
    `id`                   INT UNSIGNED    AUTO_INCREMENT PRIMARY KEY,
    `merchant_id`          VARCHAR(25)     NOT NULL,
    `clover_customer_id`   VARCHAR(50)     NOT NULL,
    `xloyalty_member_id`   VARCHAR(50)     NOT NULL,
    `phone`                VARCHAR(20)     NOT NULL,
    `synced_at`            DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uq_map` (`merchant_id`, `phone`),
    KEY `idx_clover_cid`   (`merchant_id`, `clover_customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;