import psycopg2
import uuid
import json

conn = psycopg2.connect(
    host="postgresql-genesis-u50607.vm.elestio.app",
    port=25432,
    user="postgres",
    password="etY0eog17tD-dDuj--IRH",
    dbname="postgres",
    connect_timeout=15
)
conn.autocommit = True
cur = conn.cursor()

cur.execute("SELECT schema_name FROM information_schema.schemata WHERE schema_name=%s", ("sunaiva",))
schema_exists = cur.fetchone() is not None
print("sunaiva schema exists:", schema_exists)

if not schema_exists:
    print("Creating sunaiva schema...")
    cur.execute("CREATE SCHEMA IF NOT EXISTS sunaiva")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.users (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), email TEXT UNIQUE NOT NULL, password_hash TEXT NOT NULL DEFAULT '', name TEXT NOT NULL DEFAULT 'User', plan_tier TEXT NOT NULL DEFAULT 'free', created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW())")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.vaults (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID NOT NULL REFERENCES sunaiva.users(id) ON DELETE CASCADE, name TEXT NOT NULL DEFAULT 'My Vault', description TEXT, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW())")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.vault_entities (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), vault_id UUID NOT NULL REFERENCES sunaiva.vaults(id) ON DELETE CASCADE, name TEXT, type TEXT, context JSONB DEFAULT '{}', mentions INT DEFAULT 1, confidence REAL DEFAULT 0.8, created_at TIMESTAMPTZ DEFAULT NOW(), updated_at TIMESTAMPTZ DEFAULT NOW())")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.vault_decisions (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), vault_id UUID NOT NULL REFERENCES sunaiva.vaults(id) ON DELETE CASCADE, decision TEXT NOT NULL, context TEXT, date TEXT, importance REAL DEFAULT 0.5, created_at TIMESTAMPTZ DEFAULT NOW())")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.vault_preferences (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), vault_id UUID NOT NULL REFERENCES sunaiva.vaults(id) ON DELETE CASCADE, category TEXT, preference JSONB DEFAULT '{}')")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.vault_conversations (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), vault_id UUID NOT NULL REFERENCES sunaiva.vaults(id) ON DELETE CASCADE, source_platform TEXT, message_count INT DEFAULT 0, raw_data JSONB DEFAULT '{}', created_at TIMESTAMPTZ DEFAULT NOW())")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.api_keys (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), vault_id UUID NOT NULL REFERENCES sunaiva.vaults(id) ON DELETE CASCADE, key_hash TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW())")
    cur.execute("CREATE TABLE IF NOT EXISTS sunaiva.usage_events (id UUID PRIMARY KEY DEFAULT gen_random_uuid(), user_id UUID REFERENCES sunaiva.users(id) ON DELETE SET NULL, event_type TEXT NOT NULL, metadata JSONB DEFAULT '{}', created_at TIMESTAMPTZ DEFAULT NOW())")
    print("Schema and all tables created\!")

KINAN_VAULT_ID = "41f4785c-e9e0-43ac-b6e8-eedb571fba57"
cur.execute("SELECT id FROM sunaiva.vaults WHERE id = %s", (KINAN_VAULT_ID,))
kinan_vault = cur.fetchone()
print("Kinan vault exists:", kinan_vault is not None)

if not kinan_vault:
    kinan_uid = str(uuid.uuid4())
    cur.execute("INSERT INTO sunaiva.users (id, email, name, plan_tier, password_hash) VALUES (%s, %s, %s, %s, %s) ON CONFLICT (email) DO UPDATE SET name=EXCLUDED.name RETURNING id",
        (kinan_uid, "kinan@agileadapt.com", "Kinan", "enterprise", "placeholder"))
    kinan_uid = cur.fetchone()[0]
    cur.execute("INSERT INTO sunaiva.vaults (id, user_id, name, description) VALUES (%s, %s, %s, %s) ON CONFLICT (id) DO NOTHING",
        (KINAN_VAULT_ID, str(kinan_uid), "Kinan Vault", "Genesis creator primary vault"))
    print("Kinan vault created:", KINAN_VAULT_ID)

cur.execute("SELECT u.id, v.id FROM sunaiva.users u JOIN sunaiva.vaults v ON v.user_id=u.id WHERE u.email=%s", ("george@receptionistai.com.au",))
george = cur.fetchone()
print("George vault exists:", george is not None)

if not george:
    george_uid = str(uuid.uuid4())
    george_vid = str(uuid.uuid4())
    cur.execute("INSERT INTO sunaiva.users (id, email, name, plan_tier, password_hash) VALUES (%s, %s, %s, %s, %s) ON CONFLICT (email) DO UPDATE SET name=EXCLUDED.name RETURNING id",
        (george_uid, "george@receptionistai.com.au", "George", "starter", "placeholder"))
    george_uid = cur.fetchone()[0]
    cur.execute("INSERT INTO sunaiva.vaults (id, user_id, name, description) VALUES (%s, %s, %s, %s) RETURNING id",
        (george_vid, str(george_uid), "George Vault", "George - Plumber Cairns QLD"))
    george_vid = cur.fetchone()[0]
    print("George vault created:", george_vid)

    entities = [
        ("George Plumbing Services", "business", {"trade": "plumber", "location": "Cairns", "state": "QLD", "australia": True}),
        ("George", "person", {"role": "owner", "trade": "plumber", "phone_area": "07", "city": "Cairns"}),
        ("Cairns QLD", "location", {"city": "Cairns", "state": "Queensland", "area_code": "07", "country": "Australia"}),
        ("Telnyx AI Receptionist", "product", {"provider": "Telnyx", "purpose": "after-hours call handling", "status": "active"}),
    ]
    for name, etype, ctx in entities:
        cur.execute("INSERT INTO sunaiva.vault_entities (vault_id, name, type, context, mentions, confidence) VALUES (%s, %s, %s, %s, %s, %s)",
            (str(george_vid), name, etype, json.dumps(ctx), 1, 0.9))

    decisions = [
        ("Use Telnyx AI to answer all after-hours calls", "Lead capture and booking", "2026-02-24", 0.9),
        ("Target residential plumbing in Cairns and surrounds", "Business focus area", "2026-02-24", 0.8),
        ("Phone number: Cairns 07-area-code needed", "Telnyx provisioning", "2026-02-24", 0.8),
    ]
    for dec, ctx, date, imp in decisions:
        cur.execute("INSERT INTO sunaiva.vault_decisions (vault_id, decision, context, date, importance) VALUES (%s, %s, %s, %s, %s)",
            (str(george_vid), dec, ctx, date, imp))

    print("George seeded:", len(entities), "entities +", len(decisions), "decisions")
    print("")
    print("=== GEORGE MCP URL ===")
    print("https://memory.sunaiva.ai/mcp/?vault_id=" + str(george_vid))
    print("=====================")
else:
    george_uid, george_vid = george
    print("George vault ID:", george_vid)
    print("")
    print("=== GEORGE MCP URL (EXISTING) ===")
    print("https://memory.sunaiva.ai/mcp/?vault_id=" + str(george_vid))
    print("=================================")

print("")
print("=== VAULT SUMMARY ===")
cur.execute("SELECT u.name, v.id, v.name FROM sunaiva.users u JOIN sunaiva.vaults v ON v.user_id=u.id ORDER BY u.name")
for row in cur.fetchall():
    print("  " + str(row[0]) + ": vault=" + str(row[1]) + " (" + str(row[2]) + ")")

cur.execute("SELECT COUNT(*) FROM sunaiva.vault_entities")
print("Total vault entities:", cur.fetchone()[0])
cur.execute("SELECT COUNT(*) FROM sunaiva.vault_decisions")
print("Total vault decisions:", cur.fetchone()[0])
conn.close()
print("")
print("DONE - PostgreSQL provisioning complete")