import asyncio
import asyncpg
import os
from dotenv import load_dotenv

load_dotenv()

SQL = """
-- Phase E4: Caller Memory Tables for Neon

-- ── TABLE 1: caller_profiles ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS caller_profiles (
  id                  UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id           UUID        NOT NULL,
  phone_number        TEXT        NOT NULL,
  name                TEXT,                     
  language_preference TEXT        DEFAULT 'gujarati',
  call_count          INTEGER     DEFAULT 0,
  first_call_at       TIMESTAMPTZ DEFAULT NOW(),
  last_call_at        TIMESTAMPTZ DEFAULT NOW(),
  last_service        TEXT,                     
  is_vip              BOOLEAN     DEFAULT FALSE, 
  notes               TEXT,                     
  created_at          TIMESTAMPTZ DEFAULT NOW(),
  updated_at          TIMESTAMPTZ DEFAULT NOW(),

  CONSTRAINT uq_caller_per_tenant UNIQUE (tenant_id, phone_number)
);

CREATE INDEX IF NOT EXISTS idx_caller_profiles_lookup ON caller_profiles (tenant_id, phone_number);

-- ── TABLE 2: caller_history ───────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS caller_history (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  caller_id       UUID        NOT NULL REFERENCES caller_profiles(id) ON DELETE CASCADE,
  tenant_id       UUID        NOT NULL,
  call_sid        TEXT,                     
  called_at       TIMESTAMPTZ DEFAULT NOW(),
  duration_secs   INTEGER     DEFAULT 0,
  language_used   TEXT,
  summary         TEXT,                     
  emotion         TEXT,                     
  appointment_booked BOOLEAN  DEFAULT FALSE
);

CREATE INDEX IF NOT EXISTS idx_caller_history_caller ON caller_history (caller_id, called_at DESC);
"""

async def migrate():
    url = os.getenv("DATABASE_URL")
    print(f"Connecting to {url.split('@')[-1]}...")
    conn = await asyncpg.connect(url)
    try:
        await conn.execute(SQL)
        print("OK: Migration successful: caller_profiles and caller_history tables created.")
    except Exception as e:
        print(f"ERROR: Migration failed: {e}")
    finally:
        await conn.close()

if __name__ == "__main__":
    asyncio.run(migrate())