mercadodevida/project/migrations/066_club_core.js

/**
 * CLUB-001 — Club de Clientes core backend.
 *
 * Phase 1 schema:
 * - anonymous members + device tokens
 * - ledger transactions as source of truth
 * - future-ready recovery/campaign/reward tables
 * - club config seeds in store_settings
 */

/** @param {import('node-pg-migrate').MigrationBuilder} pgm */
export const up = (pgm) => {
  pgm.sql(`
    CREATE TABLE IF NOT EXISTS club_members (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      user_id uuid REFERENCES identity_users(id) ON DELETE SET NULL,
      member_code text NOT NULL UNIQUE,
      status text NOT NULL DEFAULT 'active',
      tier_code text NOT NULL DEFAULT 'base',
      current_balance_cents integer NOT NULL DEFAULT 0,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      CONSTRAINT club_members_status_check CHECK (status IN ('active', 'blocked', 'merged')),
      CONSTRAINT club_members_balance_non_negative CHECK (current_balance_cents >= 0),
      CONSTRAINT club_members_member_code_format CHECK (member_code ~ '^MDV-[A-Z0-9]{8}$')
    )
  `);

  pgm.sql(`
    CREATE UNIQUE INDEX IF NOT EXISTS club_members_user_id_unique_idx
      ON club_members (user_id)
      WHERE user_id IS NOT NULL
  `);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS club_devices (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      member_id uuid NOT NULL REFERENCES club_members(id) ON DELETE CASCADE,
      device_token_hash text NOT NULL UNIQUE,
      last_used_at timestamptz NOT NULL DEFAULT now(),
      created_at timestamptz NOT NULL DEFAULT now(),
      revoked_at timestamptz
    )
  `);
  pgm.sql(`CREATE INDEX IF NOT EXISTS club_devices_member_id_idx ON club_devices (member_id)`);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS club_campaigns (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      name text NOT NULL,
      kind text NOT NULL DEFAULT 'generic',
      status text NOT NULL DEFAULT 'draft',
      config jsonb NOT NULL DEFAULT '{}'::jsonb,
      starts_at timestamptz,
      ends_at timestamptz,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      CONSTRAINT club_campaigns_status_check CHECK (status IN ('draft', 'active', 'archived'))
    )
  `);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS club_transactions (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      member_id uuid NOT NULL REFERENCES club_members(id) ON DELETE CASCADE,
      sale_id uuid REFERENCES orders_orders(id) ON DELETE SET NULL,
      store_id uuid REFERENCES pos_stores(id) ON DELETE SET NULL,
      type text NOT NULL,
      amount_cents integer NOT NULL DEFAULT 0,
      balance_delta_cents integer NOT NULL,
      idempotency_key text UNIQUE,
      metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
      created_at timestamptz NOT NULL DEFAULT now(),
      CONSTRAINT club_transactions_type_check CHECK (type IN ('earn', 'redeem', 'refund', 'bonus', 'adjustment')),
      CONSTRAINT club_transactions_amount_non_negative CHECK (amount_cents >= 0),
      CONSTRAINT club_transactions_balance_delta_non_zero CHECK (balance_delta_cents <> 0)
    )
  `);
  pgm.sql(`
    CREATE INDEX IF NOT EXISTS club_transactions_member_id_created_at_idx
      ON club_transactions (member_id, created_at DESC)
  `);
  pgm.sql(`CREATE INDEX IF NOT EXISTS club_transactions_sale_id_idx ON club_transactions (sale_id)`);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS club_recovery_codes (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      member_id uuid NOT NULL REFERENCES club_members(id) ON DELETE CASCADE,
      code_hash text NOT NULL,
      code_fingerprint text NOT NULL UNIQUE,
      used_at timestamptz,
      expires_at timestamptz,
      created_at timestamptz NOT NULL DEFAULT now()
    )
  `);
  pgm.sql(`CREATE INDEX IF NOT EXISTS club_recovery_codes_member_id_idx ON club_recovery_codes (member_id)`);

  pgm.sql(`
    CREATE TABLE IF NOT EXISTS club_rewards (
      id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
      member_id uuid NOT NULL REFERENCES club_members(id) ON DELETE CASCADE,
      campaign_id uuid REFERENCES club_campaigns(id) ON DELETE SET NULL,
      status text NOT NULL DEFAULT 'available',
      label text NOT NULL,
      amount_cents integer,
      metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
      expires_at timestamptz,
      created_at timestamptz NOT NULL DEFAULT now(),
      updated_at timestamptz NOT NULL DEFAULT now(),
      CONSTRAINT club_rewards_status_check CHECK (status IN ('available', 'redeemed', 'expired', 'cancelled'))
    )
  `);
  pgm.sql(`CREATE INDEX IF NOT EXISTS club_rewards_member_id_idx ON club_rewards (member_id)`);

  pgm.sql(`
    INSERT INTO store_settings (key, value) VALUES
      ('club_enabled', 'true'),
      ('club_cashback_bps', '200'),
      ('club_allow_anonymous_members', 'true'),
      ('club_allow_recovery_codes', 'true'),
      ('club_minimum_redeem_cents', '500')
    ON CONFLICT (key) DO NOTHING
  `);
};

/** @param {import('node-pg-migrate').MigrationBuilder} pgm */
export const down = (pgm) => {
  pgm.sql(`DELETE FROM store_settings WHERE key IN (
    'club_enabled',
    'club_cashback_bps',
    'club_allow_anonymous_members',
    'club_allow_recovery_codes',
    'club_minimum_redeem_cents'
  )`);
  pgm.sql('DROP INDEX IF EXISTS club_rewards_member_id_idx');
  pgm.sql('DROP TABLE IF EXISTS club_rewards');
  pgm.sql('DROP INDEX IF EXISTS club_recovery_codes_member_id_idx');
  pgm.sql('DROP TABLE IF EXISTS club_recovery_codes');
  pgm.sql('DROP INDEX IF EXISTS club_transactions_sale_id_idx');
  pgm.sql('DROP INDEX IF EXISTS club_transactions_member_id_created_at_idx');
  pgm.sql('DROP TABLE IF EXISTS club_transactions');
  pgm.sql('DROP TABLE IF EXISTS club_campaigns');
  pgm.sql('DROP INDEX IF EXISTS club_devices_member_id_idx');
  pgm.sql('DROP TABLE IF EXISTS club_devices');
  pgm.sql('DROP INDEX IF EXISTS club_members_user_id_unique_idx');
  pgm.sql('DROP TABLE IF EXISTS club_members');
};