-- ══════════════════════════════════════════════════════════════
-- ChainPay Store — Full Database Schema
-- Domain: chainpay.store  |  API: api.chainpay.store
-- Run: mysql -u root -p < db/schema.sql
-- ══════════════════════════════════════════════════════════════

CREATE DATABASE IF NOT EXISTS chainpay_store
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE chainpay_store;

-- ── USERS ─────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id            BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
  uuid          VARCHAR(36)         NOT NULL,
  name          VARCHAR(120)        NOT NULL,
  email         VARCHAR(255)        NOT NULL,
  password_hash VARCHAR(255)                 DEFAULT NULL,
  avatar        VARCHAR(10)         NOT NULL DEFAULT 'U',
  role          ENUM('client','admin','superadmin') NOT NULL DEFAULT 'client',
  plan          ENUM('free','pro','enterprise') NOT NULL DEFAULT 'free',
  status        ENUM('active','suspended','pending') NOT NULL DEFAULT 'active',
  google_id     VARCHAR(128)                 DEFAULT NULL,
  google_email  VARCHAR(255)                 DEFAULT NULL,
  email_verified TINYINT(1)         NOT NULL DEFAULT 0,
  email_verify_token VARCHAR(128)           DEFAULT NULL,
  reset_token   VARCHAR(128)                 DEFAULT NULL,
  reset_expires DATETIME                     DEFAULT NULL,
  last_login_at DATETIME                     DEFAULT NULL,
  last_login_ip VARCHAR(45)                  DEFAULT NULL,
  created_at    DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  UNIQUE KEY   uk_uuid  (uuid),
  UNIQUE KEY   uk_email (email),
  UNIQUE KEY   uk_gid   (google_id),
  KEY          idx_role  (role),
  KEY          idx_plan  (plan),
  KEY          idx_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── REFRESH TOKENS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS refresh_tokens (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED NOT NULL,
  token_hash VARCHAR(255)    NOT NULL,
  ip         VARCHAR(45)              DEFAULT NULL,
  user_agent TEXT                     DEFAULT NULL,
  expires_at DATETIME        NOT NULL,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY  uk_token (token_hash),
  KEY         idx_user  (user_id),
  CONSTRAINT  fk_rt_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── STORES ────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS stores (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid       VARCHAR(36)     NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  name       VARCHAR(200)    NOT NULL,
  url        VARCHAR(500)    NOT NULL,
  platform   ENUM('woocommerce','shopify','magento','custom','other') NOT NULL DEFAULT 'woocommerce',
  status     ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  plugin_version VARCHAR(20)          DEFAULT NULL,
  last_seen  DATETIME                 DEFAULT NULL,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY  uk_uuid    (uuid),
  KEY         idx_user   (user_id),
  CONSTRAINT  fk_store_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── API KEYS ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS api_keys (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid          VARCHAR(36)     NOT NULL,
  user_id       BIGINT UNSIGNED NOT NULL,
  store_id      BIGINT UNSIGNED          DEFAULT NULL,
  name          VARCHAR(120)    NOT NULL,
  key_hash      VARCHAR(255)    NOT NULL,
  key_prefix    VARCHAR(20)     NOT NULL,
  key_preview   VARCHAR(30)     NOT NULL,
  scope         SET('invoices','wallets','webhooks','read','full') NOT NULL DEFAULT 'full',
  environment   ENUM('live','test') NOT NULL DEFAULT 'live',
  last_used_at  DATETIME                 DEFAULT NULL,
  last_used_ip  VARCHAR(45)              DEFAULT NULL,
  requests_count BIGINT UNSIGNED NOT NULL DEFAULT 0,
  status        ENUM('active','revoked') NOT NULL DEFAULT 'active',
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY   (id),
  UNIQUE KEY    uk_uuid     (uuid),
  UNIQUE KEY    uk_key_hash (key_hash),
  KEY           idx_user    (user_id),
  KEY           idx_prefix  (key_prefix),
  CONSTRAINT    fk_ak_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
  CONSTRAINT    fk_ak_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── LICENSES ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS licenses (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid          VARCHAR(36)     NOT NULL,
  user_id       BIGINT UNSIGNED          DEFAULT NULL,
  license_key   VARCHAR(80)     NOT NULL,
  key_hash      VARCHAR(255)    NOT NULL,
  plan          ENUM('pro','enterprise') NOT NULL DEFAULT 'pro',
  status        ENUM('active','expired','revoked','pending') NOT NULL DEFAULT 'pending',
  domain        VARCHAR(500)             DEFAULT NULL,
  store_id      BIGINT UNSIGNED          DEFAULT NULL,
  activated_at  DATETIME                 DEFAULT NULL,
  expires_at    DATETIME                 DEFAULT NULL,
  last_check    DATETIME                 DEFAULT NULL,
  check_count   INT UNSIGNED    NOT NULL DEFAULT 0,
  plugin_ver    VARCHAR(20)              DEFAULT NULL,
  stripe_sub_id VARCHAR(255)             DEFAULT NULL,
  notes         TEXT                     DEFAULT NULL,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY   (id),
  UNIQUE KEY    uk_uuid     (uuid),
  UNIQUE KEY    uk_lkey     (license_key),
  UNIQUE KEY    uk_key_hash (key_hash),
  KEY           idx_user    (user_id),
  KEY           idx_status  (status),
  KEY           idx_domain  (domain(255)),
  CONSTRAINT    fk_lic_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE SET NULL,
  CONSTRAINT    fk_lic_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── XPUB WALLET KEYS ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS xpub_keys (
  id            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id       BIGINT UNSIGNED NOT NULL,
  store_id      BIGINT UNSIGNED          DEFAULT NULL,
  coin          VARCHAR(20)     NOT NULL,
  xpub          TEXT            NOT NULL,
  derivation    VARCHAR(80)     NOT NULL DEFAULT "m/84'/0'/0'",
  gap_limit     INT UNSIGNED    NOT NULL DEFAULT 20,
  current_index INT UNSIGNED    NOT NULL DEFAULT 0,
  created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY   (id),
  UNIQUE KEY    uk_user_coin (user_id, coin),
  KEY           idx_user     (user_id),
  CONSTRAINT    fk_xpub_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
  CONSTRAINT    fk_xpub_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── DERIVED ADDRESSES ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS derived_addresses (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED NOT NULL,
  store_id   BIGINT UNSIGNED          DEFAULT NULL,
  coin       VARCHAR(20)     NOT NULL,
  address    VARCHAR(255)    NOT NULL,
  idx        INT UNSIGNED    NOT NULL DEFAULT 0,
  invoice_id BIGINT UNSIGNED          DEFAULT NULL,
  used       TINYINT(1)      NOT NULL DEFAULT 0,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY  uk_address  (address),
  KEY         idx_user    (user_id),
  KEY         idx_coin_used (coin, used)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INVOICES ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS invoices (
  id            BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
  uuid          VARCHAR(36)         NOT NULL,
  user_id       BIGINT UNSIGNED              DEFAULT NULL,
  store_id      BIGINT UNSIGNED              DEFAULT NULL,
  external_order_id VARCHAR(120)             DEFAULT NULL,
  coin          VARCHAR(20)         NOT NULL,
  network       VARCHAR(80)         NOT NULL DEFAULT '',
  address       VARCHAR(255)        NOT NULL DEFAULT '',
  amount_crypto DECIMAL(28,10)      NOT NULL DEFAULT '0',
  amount_fiat   DECIMAL(14,2)       NOT NULL DEFAULT '0',
  fiat_currency VARCHAR(10)         NOT NULL DEFAULT 'USD',
  exchange_rate DECIMAL(24,8)       NOT NULL DEFAULT '0',
  fee_pct       DECIMAL(5,4)        NOT NULL DEFAULT '0.0100',
  fee_fiat      DECIMAL(14,4)       NOT NULL DEFAULT '0',
  status        ENUM('pending','detected','confirmed','underpaid','overpaid','expired') NOT NULL DEFAULT 'pending',
  tx_hash       VARCHAR(255)                 DEFAULT NULL,
  confirmations INT UNSIGNED                 DEFAULT 0,
  metadata      JSON                         DEFAULT NULL,
  expires_at    DATETIME            NOT NULL,
  detected_at   DATETIME                     DEFAULT NULL,
  confirmed_at  DATETIME                     DEFAULT NULL,
  created_at    DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY   (id),
  UNIQUE KEY    uk_uuid         (uuid),
  KEY           idx_user        (user_id),
  KEY           idx_store       (store_id),
  KEY           idx_status      (status),
  KEY           idx_order       (external_order_id),
  KEY           idx_address     (address),
  CONSTRAINT    fk_inv_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE SET NULL,
  CONSTRAINT    fk_inv_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── WEBHOOKS ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS webhooks (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  uuid       VARCHAR(36)     NOT NULL,
  user_id    BIGINT UNSIGNED NOT NULL,
  store_id   BIGINT UNSIGNED          DEFAULT NULL,
  url        VARCHAR(500)    NOT NULL,
  secret     VARCHAR(255)    NOT NULL,
  events     JSON            NOT NULL,
  status     ENUM('active','disabled') NOT NULL DEFAULT 'active',
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY  uk_uuid  (uuid),
  KEY         idx_user (user_id),
  CONSTRAINT  fk_wh_user  FOREIGN KEY (user_id)  REFERENCES users(id)  ON DELETE CASCADE,
  CONSTRAINT  fk_wh_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── WEBHOOK DELIVERY LOGS ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS webhook_logs (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  webhook_id   BIGINT UNSIGNED NOT NULL,
  invoice_id   BIGINT UNSIGNED          DEFAULT NULL,
  event        VARCHAR(60)     NOT NULL,
  payload      JSON            NOT NULL,
  response_status INT                   DEFAULT NULL,
  response_body TEXT                    DEFAULT NULL,
  duration_ms  INT UNSIGNED             DEFAULT NULL,
  attempt      TINYINT UNSIGNED NOT NULL DEFAULT 1,
  status       ENUM('delivered','failed','pending') NOT NULL DEFAULT 'pending',
  created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  KEY          idx_webhook  (webhook_id),
  KEY          idx_invoice  (invoice_id),
  KEY          idx_status   (status),
  CONSTRAINT   fk_whl_wh  FOREIGN KEY (webhook_id) REFERENCES webhooks(id) ON DELETE CASCADE,
  CONSTRAINT   fk_whl_inv FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── PRICE CACHE ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS price_cache (
  id         INT UNSIGNED    NOT NULL AUTO_INCREMENT,
  coin       VARCHAR(20)     NOT NULL,
  currency   VARCHAR(10)     NOT NULL DEFAULT 'USD',
  price      DECIMAL(24,8)   NOT NULL,
  source     VARCHAR(40)     NOT NULL DEFAULT 'coingecko',
  updated_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY  uk_coin_cur (coin, currency),
  KEY         idx_updated (updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── AUDIT LOG ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_log (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED          DEFAULT NULL,
  action     VARCHAR(80)     NOT NULL,
  target     VARCHAR(80)              DEFAULT NULL,
  target_id  VARCHAR(80)              DEFAULT NULL,
  details    JSON                     DEFAULT NULL,
  ip         VARCHAR(45)              DEFAULT NULL,
  user_agent TEXT                     DEFAULT NULL,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY         idx_user   (user_id),
  KEY         idx_action (action),
  KEY         idx_ts     (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── NOTIFICATIONS ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS notifications (
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id    BIGINT UNSIGNED NOT NULL,
  type       VARCHAR(60)     NOT NULL,
  title      VARCHAR(200)    NOT NULL,
  message    TEXT            NOT NULL,
  read_at    DATETIME                 DEFAULT NULL,
  data       JSON                     DEFAULT NULL,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY         idx_user_read (user_id, read_at),
  CONSTRAINT  fk_notif_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── SETTINGS (per user) ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS user_settings (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id         BIGINT UNSIGNED NOT NULL,
  invoice_timeout INT UNSIGNED    NOT NULL DEFAULT 15,
  underpay_pct    DECIMAL(5,2)    NOT NULL DEFAULT '1.00',
  confirmed_status VARCHAR(30)    NOT NULL DEFAULT 'processing',
  expired_status   VARCHAR(30)    NOT NULL DEFAULT 'cancelled',
  pending_status   VARCHAR(30)    NOT NULL DEFAULT 'on-hold',
  smart_routing    TINYINT(1)     NOT NULL DEFAULT 1,
  eth_fee_threshold DECIMAL(10,2) NOT NULL DEFAULT '30.00',
  show_qr          TINYINT(1)    NOT NULL DEFAULT 1,
  show_timer       TINYINT(1)    NOT NULL DEFAULT 1,
  show_rate        TINYINT(1)    NOT NULL DEFAULT 1,
  show_fee         TINYINT(1)    NOT NULL DEFAULT 1,
  notify_confirmed TINYINT(1)    NOT NULL DEFAULT 1,
  notify_detected  TINYINT(1)    NOT NULL DEFAULT 1,
  notify_underpaid TINYINT(1)    NOT NULL DEFAULT 1,
  notify_expired   TINYINT(1)    NOT NULL DEFAULT 0,
  enabled_coins    JSON                    DEFAULT NULL,
  created_at       DATETIME      NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME      NOT NULL  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (id),
  UNIQUE KEY   uk_user (user_id),
  CONSTRAINT   fk_us_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── INDEXES for analytics queries ────────────────────────────────
CREATE INDEX idx_inv_created      ON invoices(created_at);
CREATE INDEX idx_inv_user_status  ON invoices(user_id, status);
CREATE INDEX idx_inv_confirmed_at ON invoices(confirmed_at);
