/**
 * Pricing convoy schema: price timestamps, history snapshots, home zip, geo caches.
 *
 * @type {import('node-pg-migrate').ColumnDefinitions | undefined}
 */
export const shorthands = undefined;

/**
 * @param {import('node-pg-migrate').MigrationBuilder} pgm
 */
export const up = (pgm) => {
  pgm.sql(`
    ALTER TABLE cards
      ADD COLUMN IF NOT EXISTS prices_updated_at TIMESTAMPTZ;

    CREATE TABLE IF NOT EXISTS card_price_history (
      id SERIAL PRIMARY KEY,
      card_id INTEGER NOT NULL REFERENCES cards(id) ON DELETE CASCADE,
      source VARCHAR(32) NOT NULL,
      price_type VARCHAR(32) NOT NULL DEFAULT 'market',
      currency VARCHAR(3) NOT NULL DEFAULT 'USD',
      amount DECIMAL(10,2) NOT NULL,
      recorded_at DATE NOT NULL DEFAULT CURRENT_DATE,
      created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
      UNIQUE (card_id, source, price_type, currency, recorded_at)
    );

    CREATE INDEX IF NOT EXISTS idx_card_price_history_card_date
      ON card_price_history (card_id, recorded_at DESC);

    ALTER TABLE users
      ADD COLUMN IF NOT EXISTS home_zip VARCHAR(10);

    CREATE TABLE IF NOT EXISTS geo_zip_cache (
      zip VARCHAR(10) PRIMARY KEY,
      lat DOUBLE PRECISION NOT NULL,
      lng DOUBLE PRECISION NOT NULL,
      display_name TEXT,
      geocoded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE TABLE IF NOT EXISTS geo_stores_cache (
      cache_key VARCHAR(64) PRIMARY KEY,
      lat DOUBLE PRECISION NOT NULL,
      lng DOUBLE PRECISION NOT NULL,
      radius_meters INTEGER NOT NULL,
      stores JSONB NOT NULL,
      fetched_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
    );

    CREATE INDEX IF NOT EXISTS idx_geo_stores_cache_fetched
      ON geo_stores_cache (fetched_at);
  `);
};

/**
 * @param {import('node-pg-migrate').MigrationBuilder} pgm
 */
export const down = (pgm) => {
  throw new Error(
    '[migration:1783000000001_add-pricing-schema] Refusing to drop pricing schema. ' +
      'Rolling back would erase card_price_history snapshots and geo caches.'
  );
};