import psycopg
import uuid
import os
import re
from pathlib import Path
from psycopg.rows import dict_row

def load_dotenv(path: Path) -> dict[str, str]:
    values: dict[str, str] = {}
    if not path.exists():
        return values
    for raw_line in path.read_text(encoding="utf-8").splitlines():
        line = raw_line.strip()
        if not line or line.startswith("#") or "=" not in line:
            continue
        key, value = line.split("=", 1)
        values[key.strip()] = value
    return values

def get_database_url():
    repo_root = Path(__file__).resolve().parents[1]
    env_path = repo_root / ".env.local"
    loaded = load_dotenv(env_path)
    value = loaded.get("DATABASE_URL", "").strip()
    if value.startswith(":postgresql://"):
        value = value[1:]
    # Clean up brackets if any
    value = re.sub(
        r"(postgres(?:ql)?://[^:/?#]+:)\[([^\]]+)\](@)",
        r"\1\2\3",
        value,
        flags=re.IGNORECASE,
    )
    return value

def main():
    db_url = get_database_url()
    if not db_url:
        print("Error: DATABASE_URL not found in .env.local")
        return

    pdf_path = r"c:\Users\Angelah\Documents\TenderHubKenya\17899-PURCHASE OF MOBILE PHONES.pdf"
    if not os.path.exists(pdf_path):
        print(f"Error: PDF not found at {pdf_path}")
        return

    print("Connecting to database...")
    try:
        with psycopg.connect(db_url, row_factory=dict_row) as conn:
            with conn.cursor() as cur:
                # 1. Find the org and user
                cur.execute("""
                    SELECT o.id as org_id, u.id as user_id
                    FROM public.organizations o 
                    JOIN public.members m ON o.id = m.organization_id 
                    JOIN auth.users u ON m.user_id = u.id 
                    WHERE u.email = 'tester@tenderhub.co.ke'
                """)
                row = cur.fetchone()
                if not row:
                    print("Org/User not found for tester@tenderhub.co.ke")
                    return
                
                org_id = row['org_id']
                user_id = row['user_id']
                print(f"Found Org ID: {org_id}, User ID: {user_id}")
                
                # 2. Elevate Org
                cur.execute("""
                    UPDATE public.organizations 
                    SET plan_type = 'WHITE_LABEL',
                        white_label_branding = '{"logoText": "ContractorElite", "logoIcon": "🏗️"}'
                    WHERE id = %s
                """, (org_id,))
                
                # 3. Insert Tender (Simulating Upload)
                tender_id = str(uuid.uuid4())
                cur.execute("""
                    INSERT INTO public.tenders (id, organization_id, uploaded_by, source_filename, status)
                    VALUES (%s, %s, %s, %s, 'PROCESSING')
                """, (tender_id, org_id, user_id, "17899-PURCHASE OF MOBILE PHONES.pdf"))
                
                # 4. Insert Job
                cur.execute("""
                    INSERT INTO public.processing_jobs (tender_id, status, idempotency_key)
                    VALUES (%s, 'QUEUED', %s)
                """, (tender_id, f"test-{tender_id}"))
                
                conn.commit()
                print(f"SUCCESS: Created tender {tender_id} and queued job.")
                print(f"Check the worker logs! Visit http://localhost:3000/dashboard/tenders/{tender_id}")
    except Exception as e:
        print(f"Database error: {e}")

if __name__ == "__main__":
    main()