-- =====================================================================
--  FAUCET — esquema (MariaDB 10.11 / InnoDB / utf8mb4). TODO en UTC.
--  Adaptado de database-portable + timers-portable a lo que hoy guardamos en JSON.
--  Patrones: dinero en DECIMAL propio (CHECK >= 0); historial/estado en JSON (state);
--  version = bloqueo optimista; next_due_at = fin de cooldown indexado (timers).
-- =====================================================================

-- ---------------------------------------------------------------------
--  users — una fila por (coin_type, email). id = 'coin_type:email'.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`                    VARCHAR(191)  NOT NULL,
  `coin_type`             VARCHAR(16)   NOT NULL,
  `email`                 VARCHAR(191)  NOT NULL,
  `created_ip`            VARCHAR(45)   NULL,

  -- Dinero: columnas DECIMAL propias (no dentro del JSON) -> SUM/índices/CHECK.
  `available_balance`     DECIMAL(30,8) NOT NULL DEFAULT 0,
  `lifetime_earned`       DECIMAL(30,8) NOT NULL DEFAULT 0,
  `lifetime_withdrawn`    DECIMAL(30,8) NOT NULL DEFAULT 0,
  `referral_earned_total` DECIMAL(30,8) NOT NULL DEFAULT 0,

  `referral_code`         VARCHAR(16)   NULL,
  `referred_by_code`      VARCHAR(16)   NULL,

  `claims_today`          INT           NOT NULL DEFAULT 0,
  `last_claim_at`         DATETIME(3)   NULL,
  `streak_count`          INT           NOT NULL DEFAULT 0,
  `last_streak_date`      VARCHAR(10)   NULL,

  -- Historial/estado que se lee/escribe entero (actividad reciente, etc.), recortado por la app.
  `state`                 LONGTEXT      NOT NULL DEFAULT '{}' CHECK (JSON_VALID(`state`)),
  -- Bloqueo optimista: el UPDATE exige la versión leída; si otro escribió en medio, 0 filas -> reintenta.
  `version`               BIGINT        NOT NULL DEFAULT 0,
  -- Fin del cooldown (u otro vencimiento) indexado: "no me mires hasta entonces".
  `next_due_at`           DATETIME(3)   NULL,

  `created_at`            DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updated_at`            DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_coin_email`   (`coin_type`, `email`),
  UNIQUE KEY `uq_users_coin_refcode` (`coin_type`, `referral_code`),
  KEY `idx_users_referred_by` (`coin_type`, `referred_by_code`),
  KEY `idx_users_created_ip`  (`coin_type`, `created_ip`),
  KEY `idx_users_next_due`    (`next_due_at`),
  KEY `idx_users_balance`     (`available_balance`),

  CONSTRAINT `chk_users_balance_non_negative` CHECK (`available_balance` >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ---------------------------------------------------------------------
--  withdrawals — dinero que SALE (payouts). Fuente del "Live Payouts".
--  Se inserta 'pending' ANTES de pagar; un pago enviado nunca queda invisible.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `withdrawals` (
  `id`               VARCHAR(191)  NOT NULL,
  `user_id`          VARCHAR(191)  NOT NULL,
  `coin_type`        VARCHAR(16)   NOT NULL,
  `email_masked`     VARCHAR(191)  NULL,          -- p. ej. 'c***@gmail.com' para el listado público
  `destination`      VARCHAR(191)  NOT NULL,      -- email FaucetPay destino (congelado al pedir)
  `amount`           DECIMAL(30,8) NOT NULL,
  `currency`         VARCHAR(16)   NOT NULL,
  `status`           VARCHAR(20)   NOT NULL DEFAULT 'pending',
  `provider_message` TEXT          NULL,
  `created_at`       DATETIME(3)   NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `settled_at`       DATETIME(3)   NULL,

  PRIMARY KEY (`id`),
  KEY `idx_withdrawals_user_created`   (`user_id`, `created_at`),
  KEY `idx_withdrawals_status_created` (`coin_type`, `status`, `created_at`),

  CONSTRAINT `fk_withdrawals_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `chk_withdrawals_status` CHECK (`status` IN ('pending','paid','failed'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ---------------------------------------------------------------------
--  json_store — blobs JSON pequeños por clave (se leen/escriben enteros).
--  Aquí viven: estadísticas globales, y el estado del shortlink (tickets/pool),
--  y opcionalmente la config runtime. Lo que se consulte entre filas va en su tabla.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `json_store` (
  `store_key`  VARCHAR(64) NOT NULL,
  `value`      LONGTEXT    NOT NULL CHECK (JSON_VALID(`value`)),
  `updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),

  PRIMARY KEY (`store_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;