#!/usr/bin/env python3
"""CEE & Russia OA bulk-update pipeline — Step 1.

Reads the sheet attached to PLATFORM-4913 (`CEE & Russia Bulk.csv`) and resolves
each Account ID to its vendor_uuid. Every non-empty row in the sheet uses the
same column-C-through-H values, so the per-vendor payload is constant — only
vendor_uuid varies. We just need a single output CSV that can drive all three
config runs (service_tier, internal-staff, metadata).

A divergence guard aborts the prepare step if any row's non-empty value
disagrees with the constants below; that protects us from silently bulk-setting
unintended fields if the sheet is updated later.

Usage:
    uv run python src/pipelines/cee_russia_oa_update/1_prepare.py \\
        --input "data/prepare/CEE & Russia Bulk.csv"

DB creds are read from .env (DB_URL). Requires `awsume prod`.
"""

import argparse
import csv
import os
import sys
from pathlib import Path
from urllib.parse import unquote

import pymysql  # type: ignore[import-untyped]
from dotenv import load_dotenv

ACCOUNT_ID_HEADER = 'Account ID'
BATCH_SIZE = 1000

# Expected column values per the sheet — non-empty cells must match exactly.
EXPECTED: dict[str, str] = {
    'Relationship Manager': 'Client Services',
    'Secondary Relationship Manager': 'Blank',
    'Tier': 'Basic',
    'Product Manager': 'Client Services',
    'Support Contact Email': 'orchardsupport@theorchard.com',
    'Assigned Reviewer': 'Content Review',
}


def db_connect() -> pymysql.connections.Connection:
    load_dotenv()
    url = os.environ['DB_URL']
    rest = url.split('://', 1)[1]
    creds, hostdb = rest.split('@', 1)
    user, pw = creds.split(':', 1)
    host, db = hostdb.split('/', 1)
    return pymysql.connect(host=host, user=user, password=unquote(pw), database=db)


def load_account_ids(csv_path: Path) -> list[int]:
    with open(csv_path, encoding='utf-8-sig', newline='') as f:
        reader = csv.DictReader(f)
        if reader.fieldnames is None:
            raise KeyError('CSV has no header row')
        # Source sheet has stray whitespace in some headers — normalize once.
        normalized = [h.strip() for h in reader.fieldnames]
        if ACCOUNT_ID_HEADER not in normalized:
            raise KeyError(f'Column {ACCOUNT_ID_HEADER!r} not found; header={normalized}')
        for col in EXPECTED:
            if col not in normalized:
                raise KeyError(f'Column {col!r} not found; header={normalized}')

        def field(row: dict[str, str], key: str) -> str:
            for k, v in row.items():
                if k.strip() == key:
                    return (v or '').strip()
            return ''

        ids: list[int] = []
        divergences: list[tuple[int, str, str]] = []
        for row_num, row in enumerate(reader, start=2):
            account_id = field(row, ACCOUNT_ID_HEADER)
            if not account_id:
                continue

            for col, expected_val in EXPECTED.items():
                cell = field(row, col)
                if cell and cell != expected_val:
                    divergences.append((row_num, col, cell))

            ids.append(int(account_id))

        if divergences:
            print('Sheet diverges from expected constants:', file=sys.stderr)
            for row_num, col, cell in divergences[:20]:
                print(f'  row {row_num} {col!r}: {cell!r}', file=sys.stderr)
            sys.exit(1)

    return ids


def lookup_uuids(account_ids: list[int]) -> dict[int, str]:
    conn = db_connect()
    try:
        cur = conn.cursor()
        result: dict[int, str] = {}
        for i in range(0, len(account_ids), BATCH_SIZE):
            batch = account_ids[i : i + BATCH_SIZE]
            placeholders = ','.join(['%s'] * len(batch))
            cur.execute(
                f'SELECT vendor_id, vendor_uuid FROM vendor WHERE vendor_id IN ({placeholders})',
                batch,
            )
            for vid, vuuid in cur.fetchall():
                if vuuid:
                    result[vid] = vuuid
        cur.close()
        return result
    finally:
        conn.close()


def write_csv(path: Path, uuids: list[str]) -> None:
    with open(path, 'w', newline='', encoding='utf-8') as f:
        w = csv.writer(f)
        w.writerow(['vendor_uuid'])
        for u in uuids:
            w.writerow([u])


def main() -> None:
    parser = argparse.ArgumentParser(description='CEE & Russia OA bulk update prep.')
    parser.add_argument('--input', required=True, help='Path to CEE & Russia Bulk.csv')
    parser.add_argument(
        '--output-dir',
        default='data/input',
        help='Directory for output CSV (default: data/input)',
    )
    args = parser.parse_args()

    src = Path(args.input)
    if not src.exists():
        print(f'Input not found: {src}', file=sys.stderr)
        sys.exit(1)

    account_ids = load_account_ids(src)
    print(f'Read {len(account_ids)} account IDs from {src.name}', file=sys.stderr)

    uuid_map = lookup_uuids(account_ids)
    resolved = [uuid_map[aid] for aid in account_ids if aid in uuid_map]
    missing = [aid for aid in account_ids if aid not in uuid_map]

    print(f'Resolved {len(resolved)}/{len(account_ids)} vendor UUIDs', file=sys.stderr)
    if missing:
        print(
            f'WARNING: {len(missing)} account IDs had no vendor_uuid in art_relations.vendor',
            file=sys.stderr,
        )
        print(f'  sample: {missing[:10]}', file=sys.stderr)

    out_dir = Path(args.output_dir)
    out_dir.mkdir(parents=True, exist_ok=True)
    out_csv = out_dir / 'cee_russia_vendors.csv'
    write_csv(out_csv, resolved)
    print(f'Wrote {out_csv} ({len(resolved)} rows)', file=sys.stderr)


if __name__ == '__main__':
    main()