from __future__ import annotations

import json
import sqlite3
from pathlib import Path
from project_catalog import load_all_projects

DB_PATH = Path(__file__).parent / 'email_agent.db'

SCHEMA = '''
CREATE TABLE IF NOT EXISTS product_catalog (
  project_id TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  status TEXT,
  target_industries TEXT NOT NULL,
  customer_problem TEXT,
  potential_score REAL,
  confidence TEXT,
  source_file TEXT NOT NULL,
  synced_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS lead_project_matches (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  contact_id INTEGER NOT NULL,
  project_id TEXT NOT NULL,
  industry_fit REAL NOT NULL DEFAULT 0,
  problem_pressure REAL NOT NULL DEFAULT 0,
  company_fit REAL NOT NULL DEFAULT 0,
  technical_fit REAL NOT NULL DEFAULT 0,
  economic_value REAL NOT NULL DEFAULT 0,
  reachability REAL NOT NULL DEFAULT 0,
  evidence_quality REAL NOT NULL DEFAULT 0,
  sale_probability REAL NOT NULL DEFAULT 0,
  lead_score REAL NOT NULL DEFAULT 0,
  rationale TEXT,
  evidence_json TEXT,
  status TEXT NOT NULL DEFAULT 'researched',
  human_gate TEXT NOT NULL DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(contact_id, project_id)
);
CREATE INDEX IF NOT EXISTS idx_lpm_score ON lead_project_matches(lead_score DESC);
CREATE INDEX IF NOT EXISTS idx_lpm_gate ON lead_project_matches(human_gate, status);
'''


def init_schema(db_path: Path = DB_PATH) -> None:
    with sqlite3.connect(db_path) as conn:
        conn.executescript(SCHEMA)
        conn.commit()


def sync_projects(db_path: Path = DB_PATH) -> int:
    projects = load_all_projects()
    with sqlite3.connect(db_path) as conn:
        for p in projects:
            values = (
                p.project_id, p.title, p.status,
                json.dumps(p.target_industries, ensure_ascii=False),
                p.customer_problem, p.potential_score,
                p.confidence, p.source_file,
            )
            conn.execute(
                'INSERT OR REPLACE INTO product_catalog '
                '(project_id,title,status,target_industries,customer_problem,'
                'potential_score,confidence,source_file,synced_at) '
                'VALUES (?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP)', values
            )
        conn.commit()
    return len(projects)


if __name__ == '__main__':
    init_schema()
    print(f'synced_projects={sync_projects()}')