#!/usr/bin/env python3
"""
Foundation Accounts pipeline — Step 1: transform source CSV → bulk_runner input.

Reads the business-supplied "Foundation Accounts - Filled in Orchard
Spreadsheet.csv" (columns A–T; Currency is the trailing column T), resolves
staff names → orchadmin_users.id via
MySQL, maps enums (tier, genre, country, company brand) to the values ows-account
`V2CreateVendorSchema` accepts, and writes a runner-ready CSV for Step 2
(bulk_runner.py + configs/foundation_create_vendor.json).

Usage:
    uv run python src/pipelines/foundation_accounts/1_prepare.py \\
        --input "data/prepare/Foundation Accounts - Filled in Orchard Spreadsheet.csv" \\
        --output data/input/foundation_vendors.csv

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

PLATFORM-4980. Columns M–S (Main Client Contact block) have no ows-account
endpoint — they are not sent here; Step 3 carries them into the output CSV for
manual follow-up.
"""

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

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

# ── Enum maps (mirror ows-account account/constants/constants.py) ──────────────

# Tier column is "Disregard" for every row; map to the 'untiered' service tier.
TIER_UUIDS: dict[str, str] = {
    'Disregard': 'c069ab15-9370-4bd6-8ad1-895a64ae2ad8',  # untiered
}

# Genre column is "Hip Hop" for every row; `genre` (=Hip-hop/Rap) in the genre table.
GENRE_IDS: dict[str, int] = {
    'Hip Hop': 6,
}

# Company column is "Foundation"; ows-account brand is 'foundation'.
COMPANY_BRAND_MAP: dict[str, str] = {
    'Foundation': 'foundation',
}

# Fallback when the source Currency column is blank — ows-account requires
# payment_currency but does not persist it to the vendor (it only rides the
# post-create event payload).
DEFAULT_PAYMENT_CURRENCY = 'USD'

# Country name → ISO alpha-3. Covers every distinct value in the source,
# including its misspellings (Columbia, Russoa, Venezuala, United Kindgom).
COUNTRY_CODES: dict[str, str] = {
    'Australia': 'AUS',
    'Brazil': 'BRA',
    'Canada': 'CAN',
    'Cayman Islands': 'CYM',
    'Chile': 'CHL',
    'Colombia': 'COL',
    'Columbia': 'COL',
    'Croatia': 'HRV',
    'Denmark': 'DNK',
    'Dominican Republic': 'DOM',
    'Ecuador': 'ECU',
    'Egypt': 'EGY',
    'France': 'FRA',
    'Germany': 'DEU',
    'Guatemala': 'GTM',
    'India': 'IND',
    'Kazakhstan': 'KAZ',
    'Mexico': 'MEX',
    'Monaco': 'MCO',
    'Netherlands': 'NLD',
    'New Zealand': 'NZL',
    'Nigeria': 'NGA',
    'Norway': 'NOR',
    'Peru': 'PER',
    'Portugal': 'PRT',
    'Puerto Rico': 'PRI',
    'Russia': 'RUS',
    'Russoa': 'RUS',
    'Serbia': 'SRB',
    'Sierra Leone': 'SLE',
    'South Africa': 'ZAF',
    'South Korea': 'KOR',
    'Spain': 'ESP',
    'Sweden': 'SWE',
    'Switzerland': 'CHE',
    'Turks and Caicos': 'TCA',
    'Ukraine': 'UKR',
    'United Arab Emirates': 'ARE',
    'United Kindgom': 'GBR',
    'United Kingdom': 'GBR',
    'United States': 'USA',
    'Venezuela': 'VEN',
    'Venezuala': 'VEN',
}

# Source column indices (A–S). The header row carries embedded newlines, so we
# pin by position and sanity-check a few anchors rather than match on name.
COL_NAME = 0
COL_COMPANY = 1
COL_OWNER = 2
COL_TIER = 3
COL_DAY_TO_DAY = 4  # Relationship Manager → assigned_to
COL_CONTENT_OPS = 5  # Product Manager → product_manager
COL_ASSIGNED_REVIEWER = 6
COL_D3 = 7  # D3 Y/N → is_distributor
COL_COUNTRY = 8
COL_GENRE = 9
COL_DEALMAKER = 10  # Closer → closers
COL_LABEL_SUMMARY = 11
COL_CURRENCY = 19  # appended after the Main Client Contact block (M–S) → payment_currency


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 split_name(full: str) -> tuple[str, str]:
    parts = full.strip().split(None, 1)
    if len(parts) == 1:
        return parts[0], ''
    return parts[0], parts[1]


def lookup_user_id(cur: Any, full_name: str, cache: dict[str, int | None]) -> int | None:
    full_name = full_name.strip()
    if full_name in cache:
        return cache[full_name]
    if not full_name:
        cache[full_name] = None
        return None
    first, last = split_name(full_name)
    cur.execute(
        "SELECT id FROM orchadmin_users WHERE f_name=%s AND l_name=%s AND active='Y' ORDER BY id LIMIT 2",
        (first, last),
    )
    rows = cur.fetchall()
    if len(rows) == 1:
        cache[full_name] = rows[0][0]
    elif len(rows) > 1:
        print(f'WARN: multiple matches for {full_name!r}: {rows}', file=sys.stderr)
        cache[full_name] = rows[0][0]
    else:
        print(f'WARN: no match for {full_name!r}', file=sys.stderr)
        cache[full_name] = None
    return cache[full_name]


def map_country(raw: str) -> str:
    raw = raw.strip()
    if not raw:
        return ''
    return COUNTRY_CODES.get(raw, '')


def convert(input_path: Path, output_path: Path) -> None:
    with open(input_path, newline='', encoding='utf-8-sig') as f:
        rows = list(csv.reader(f))

    header = rows[0]
    data = rows[1:]

    if len(header) < 12 or not header[COL_NAME].strip().startswith('Account Name') or header[COL_TIER].strip() != 'Tier':
        print(f'ERROR: unexpected header layout: {header[:12]}', file=sys.stderr)
        sys.exit(1)

    conn = db_connect()
    cur = conn.cursor()
    staff_cache: dict[str, int | None] = {}

    out_rows: list[dict[str, Any]] = []
    skipped = 0
    unmapped_tiers: set[str] = set()
    unmapped_genres: set[str] = set()
    unmapped_brands: set[str] = set()
    unmapped_countries: set[str] = set()

    for row in data:
        if len(row) <= COL_LABEL_SUMMARY or not row[COL_NAME].strip():
            skipped += 1
            continue

        name = row[COL_NAME].strip()

        tier_text = row[COL_TIER].strip()
        tier_uuid = TIER_UUIDS.get(tier_text, '')
        if not tier_uuid:
            unmapped_tiers.add(tier_text)

        genre_text = row[COL_GENRE].strip()
        genre_id: int | str = GENRE_IDS.get(genre_text, '')
        if genre_id == '':
            unmapped_genres.add(genre_text)

        brand = COMPANY_BRAND_MAP.get(row[COL_COMPANY].strip(), '')
        if not brand:
            unmapped_brands.add(row[COL_COMPANY].strip())

        country_text = row[COL_COUNTRY].strip()
        country = map_country(country_text)
        if country_text and not country:
            unmapped_countries.add(country_text)

        is_distributor = 'true' if row[COL_D3].strip().upper() == 'Y' else 'false'

        currency = row[COL_CURRENCY].strip() if len(row) > COL_CURRENCY else ''

        assigned_to = lookup_user_id(cur, row[COL_DAY_TO_DAY], staff_cache)
        product_manager = lookup_user_id(cur, row[COL_CONTENT_OPS], staff_cache)
        assigned_reviewer = lookup_user_id(cur, row[COL_ASSIGNED_REVIEWER], staff_cache)
        closer = lookup_user_id(cur, row[COL_DEALMAKER], staff_cache)

        out_rows.append(
            {
                'name': name,
                'owner': row[COL_OWNER].strip(),
                'company_brand': brand,
                'service_tier_uuid': tier_uuid,
                'payment_currency': currency or DEFAULT_PAYMENT_CURRENCY,
                'is_distributor': is_distributor,
                'country': country,
                'genre': genre_id,
                'label_summary': row[COL_LABEL_SUMMARY].strip(),
                'assigned_to': '' if assigned_to is None else assigned_to,
                'assigned_reviewer': '' if assigned_reviewer is None else assigned_reviewer,
                'product_manager': '' if product_manager is None else product_manager,
                'closer': '' if closer is None else closer,
            }
        )

    cur.close()
    conn.close()

    if not out_rows:
        print('ERROR: no rows produced', file=sys.stderr)
        sys.exit(1)

    with open(output_path, 'w', newline='', encoding='utf-8') as f:
        writer = csv.DictWriter(f, fieldnames=list(out_rows[0].keys()))
        writer.writeheader()
        writer.writerows(out_rows)

    print(f'Wrote {len(out_rows)} rows to {output_path}', file=sys.stderr)
    if skipped:
        print(f'Skipped {skipped} empty rows', file=sys.stderr)
    if unmapped_tiers:
        print(f'WARNING: unmapped Tier values — add to TIER_UUIDS: {unmapped_tiers}', file=sys.stderr)
    if unmapped_genres:
        print(f'WARNING: unmapped Genre values — add to GENRE_IDS: {unmapped_genres}', file=sys.stderr)
    if unmapped_brands:
        print(f'WARNING: unmapped Company values — add to COMPANY_BRAND_MAP: {unmapped_brands}', file=sys.stderr)
    if unmapped_countries:
        print(f'WARNING: unmapped Country values (sent without country) — add to COUNTRY_CODES: {unmapped_countries}', file=sys.stderr)
    missing_staff = sum(1 for r in out_rows if '' in (r['assigned_to'], r['assigned_reviewer'], r['product_manager'], r['closer']))
    if missing_staff:
        print(f'WARNING: {missing_staff} rows with unresolved staff IDs', file=sys.stderr)


def main() -> None:
    parser = argparse.ArgumentParser(description='Foundation Accounts Step 1: source CSV → runner-ready CSV')
    parser.add_argument('--input', required=True, help='Source Foundation Accounts CSV')
    parser.add_argument('--output', required=True, help='Output CSV path')
    args = parser.parse_args()

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

    convert(input_path, Path(args.output))


if __name__ == '__main__':
    main()