from __future__ import annotations

import json
import sqlite3
from pathlib import Path
from typing import Any

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

SCHEMA = '''
CREATE TABLE IF NOT EXISTS prospects_v2 (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  project_id TEXT NOT NULL,
  company TEXT NOT NULL,
  industry TEXT,
  location TEXT,
  website TEXT,
  email TEXT,
  phone TEXT,
  contact_page TEXT,
  company_size TEXT,
  need_signals TEXT,
  fit_rationale TEXT,
  sources_json TEXT NOT NULL,
  research_method TEXT NOT NULL,
  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
);
'''
def init(db_path: Path = DB_PATH) -> None:
    with sqlite3.connect(db_path) as conn:
        conn.executescript(SCHEMA)
        conn.commit()


def validate(item: dict[str, Any]) -> None:
    required = ['project_id', 'company', 'sources', 'research_method']
    missing = [k for k in required if not item.get(k)]
    if missing:
        raise ValueError(f'missing required fields: {missing}')
    if not isinstance(item.get('sources'), list) or not item['sources']:
        raise ValueError('sources must be a non-empty list')
    if not any(item.get(k) for k in ('email', 'phone', 'contact_page', 'website')):
        raise ValueError('at least one public business contact path is required')


def upsert(item: dict[str, Any], db_path: Path = DB_PATH) -> int:
    validate(item)
    init(db_path)
    with sqlite3.connect(db_path) as conn:
        existing = None
        if item.get('website'):
            existing = conn.execute(
                'SELECT id FROM prospects_v2 WHERE project_id=? AND website=?',
                (item['project_id'], item['website']),
            ).fetchone()
        if existing:
            prospect_id = existing[0]
            conn.execute(
                '''UPDATE prospects_v2 SET industry=?, location=?, email=?, phone=?,
                   contact_page=?, company_size=?, need_signals=?, fit_rationale=?,
                   sources_json=?, research_method=?, updated_at=CURRENT_TIMESTAMP
                   WHERE id=?''',
                (
                    item.get('industry'), item.get('location'), item.get('email'),
                    item.get('phone'), item.get('contact_page'), item.get('company_size'),
                    json.dumps(item.get('need_signals', []), ensure_ascii=False),
                    item.get('fit_rationale'), json.dumps(item['sources'], ensure_ascii=False),
                    item['research_method'], prospect_id,
                ),
            )
        else:
            cur = conn.execute(
                '''INSERT INTO prospects_v2 (
                   project_id,company,industry,location,website,email,phone,contact_page,
                   company_size,need_signals,fit_rationale,sources_json,research_method
                   ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)''',
                (
                    item['project_id'], item['company'], item.get('industry'), item.get('location'),
                    item.get('website'), item.get('email'), item.get('phone'), item.get('contact_page'),
                    item.get('company_size'), json.dumps(item.get('need_signals', []), ensure_ascii=False),
                    item.get('fit_rationale'), json.dumps(item['sources'], ensure_ascii=False),
                    item['research_method'],
                ),
            )
            prospect_id = cur.lastrowid
        conn.commit()
    return prospect_id


def import_json(path: str, db_path: Path = DB_PATH) -> int:
    data = json.loads(Path(path).read_text(encoding='utf-8'))
    items = data if isinstance(data, list) else data.get('prospects', [])
    count = 0
    for item in items:
        upsert(item, db_path)
        count += 1
    return count


if __name__ == '__main__':
    import argparse
    parser = argparse.ArgumentParser(description='Import scraper/browser-use prospects')
    parser.add_argument('--import-json', required=True)
    args = parser.parse_args()
    print(f'imported={import_json(args.import_json)}')