src/database.py

import os
import asyncio
import asyncpg
import re
from dotenv import load_dotenv

load_dotenv()

DATABASE_URL = os.getenv("DATABASE_URL")

_pool = None

_UUID_RE = re.compile(r'^[0-9a-f]{8}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{4}-[0-9a-f]{12}$', re.IGNORECASE)

def _is_valid_uuid(val: str) -> bool:
    return bool(_UUID_RE.match(str(val) if val else ''))

async def get_db_pool():
    global _pool
    if _pool is None:
        _pool = await asyncpg.create_pool(DATABASE_URL)
    return _pool

async def get_tenant_config(tenant_id: str):
    pool = await get_db_pool()
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            """
            SELECT t.*, ac.system_prompt_gu, ac.system_prompt_hi, ac.system_prompt_en, 
                   ac.greeting_gu, ac.greeting_hi, ac.greeting_en
            FROM tenants t
            JOIN agent_configs ac ON t.id = ac.tenant_id
            WHERE t.id = $1
            """,
            tenant_id
        )
        return dict(row) if row else None

async def save_call(call_data: dict):
    pool = await get_db_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """
            INSERT INTO calls (
                tenant_id, caller_number, duration_seconds, language_used, 
                mode_used, was_intercepted, transcript, summary, status, call_sid
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
            """,
            call_data.get("tenant_id"),
            call_data.get("caller_number"),
            call_data.get("duration_seconds"),
            call_data.get("language_used"),
            call_data.get("mode_used"),
            call_data.get("was_intercepted"),
            call_data.get("transcript"),
            call_data.get("summary"),
            call_data.get("status"),
            call_data.get("call_sid")
        )

async def save_lead(lead_data: dict):
    # Skip if tenant_id is not a valid UUID (e.g. demo-tenant-123)
    if not _is_valid_uuid(lead_data.get("tenant_id")):
        return
    pool = await get_db_pool()
    async with pool.acquire() as conn:
        await conn.execute(
            """
            INSERT INTO leads (tenant_id, call_id, name, phone, note, status)
            VALUES ($1, $2, $3, $4, $5, $6)
            """,
            lead_data.get("tenant_id"),
            lead_data.get("call_id"),
            lead_data.get("name"),
            lead_data.get("phone"),
            lead_data.get("note"),
            lead_data.get("status", "new")
        )

async def get_leads(tenant_id: str):
    # Skip if tenant_id is not a valid UUID (e.g. demo-tenant-123)
    if not _is_valid_uuid(tenant_id):
        return []
    pool = await get_db_pool()
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            """
            SELECT * FROM leads 
            WHERE tenant_id = $1 
            ORDER BY created_at DESC
            """,
            tenant_id
        )
        return [dict(r) for r in rows]