import { Pool } from 'pg';
import 'dotenv/config';

if (!process.env.DB_URL) throw new Error('DB_URL is required');

const pool = new Pool({
  connectionString: process.env.DB_URL,
  ssl: { rejectUnauthorized: false },
});

const sql = `
ALTER TABLE simo_customers ADD COLUMN IF NOT EXISTS password_hash VARCHAR(255);

CREATE TABLE IF NOT EXISTS simo_otps (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  customer_id UUID NOT NULL REFERENCES simo_customers(id) ON DELETE CASCADE,
  otp_code    VARCHAR(6) NOT NULL,
  expires_at  TIMESTAMPTZ NOT NULL,
  used        BOOLEAN NOT NULL DEFAULT FALSE,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_simo_otps_customer ON simo_otps(customer_id);
`;

try {
  await pool.query(sql);
  console.log('✅ Migration v2: password_hash column + simo_otps table ready');
} catch (e) {
  console.error('❌ Migration v2 failed:', e);
  process.exit(1);
} finally {
  await pool.end();
}