-- Migración: depósitos/upgrades (FaucetPay). TODO en UTC.

-- Total depositado por el usuario (USD). > 0 => upgrade activo. El multiplicador y los
-- reclamos/día se DERIVAN de este valor con la fórmula (no se guardan por separado).
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `total_deposited` DECIMAL(30,8) NOT NULL DEFAULT 0;

-- Órdenes de depósito (dinero que ENTRA). Se crea 'pending' antes de mandar al checkout.
CREATE TABLE IF NOT EXISTS `deposit_orders` (
  `payment_id`     VARCHAR(191)  NOT NULL,     -- TU id de orden (= `custom` del webhook)
  `user_id`        VARCHAR(191)  NOT NULL,
  `coin_type`      VARCHAR(16)   NOT NULL,
  `email`          VARCHAR(191)  NOT NULL,
  `amount_usd`     DECIMAL(30,8) NOT NULL,      -- monto esperado (USD)
  `status`         VARCHAR(20)   NOT NULL DEFAULT 'pending',
  `transaction_id` VARCHAR(191)  NULL,
  `metadata`       LONGTEXT      NOT NULL DEFAULT '{}' CHECK (JSON_VALID(`metadata`)),
  `created_at`     DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `credited_at`    DATETIME(3)   NULL,

  PRIMARY KEY (`payment_id`),
  UNIQUE KEY `uq_deposit_txid` (`transaction_id`),
  KEY `idx_deposit_user_status` (`user_id`, `status`),
  KEY `idx_deposit_status_created` (`status`, `created_at`),
  CONSTRAINT `chk_deposit_status` CHECK (`status` IN ('pending','credited','amount-mismatch','cancelled'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Candado de idempotencia: PRIMARY KEY sobre transaction_id -> un IPN repetido choca (1062).
CREATE TABLE IF NOT EXISTS `credited_transactions` (
  `transaction_id` VARCHAR(191)  NOT NULL,
  `payment_id`     VARCHAR(191)  NOT NULL,
  `user_id`        VARCHAR(191)  NOT NULL,
  `amount`         DECIMAL(30,8) NOT NULL,
  `created_at`     DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`transaction_id`),
  KEY `idx_credited_payment` (`payment_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Buzón de webhooks crudos (durabilidad): se guarda ANTES de procesar; reintentos con backoff.
CREATE TABLE IF NOT EXISTS `ipn_events` (
  `id`            BIGINT       NOT NULL AUTO_INCREMENT,
  `provider`      VARCHAR(32)  NOT NULL DEFAULT 'faucetpay',
  `raw_body`      LONGTEXT     NOT NULL,
  `received_at`   DATETIME(3)  NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `processed_at`  DATETIME(3)  NULL,
  `attempts`      INT          NOT NULL DEFAULT 0,
  `next_retry_at` DATETIME(3)  NULL,
  `last_error`    TEXT         NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ipn_pending` (`processed_at`, `next_retry_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;