"""
Regenerate frontend/src/data/tata/normalized.json from the two source xlsx
files at the repo root.

Source files:
  Contract Master Data.xlsx                       (24 contracts)
  Early Warning _ Quotations and NCE Data.xlsx   (884 EWs / 590 CEs / 1151 quotations)

Run:
  python3 scripts/generate_tata_normalized.py

The output schema must match frontend/src/data/tata/types.ts → NormalizedDataset.
Optional fields (severity / likelihood / priority on EW; ceType on CE; carried-to-*
on quotations) populated when the Excel rows have them.
"""

from __future__ import annotations

import json
import re
from datetime import datetime
from pathlib import Path
from typing import Any

import openpyxl

ROOT = Path(__file__).resolve().parent.parent
EUR_TO_GBP = 0.85

CONTRACT_MASTER = ROOT / 'Contract Master Data.xlsx'
EW_NCE_QUOTE = ROOT / 'Early Warning _ Quotations and NCE Data.xlsx'
OUTPUT = ROOT / 'frontend' / 'src' / 'data' / 'tata' / 'normalized.json'

PROJECT_MAP = {
    'Meltshop': 'Meltshop',
    'Pickleline': 'Pickleline',
    'Pickle': 'Pickleline',
    'Programme Cost Management': 'Programme Cost Management',
    'Programme': 'Programme Cost Management',
    'HRP': 'HRP',
}

CONTRACT_FORM_MAP = {
    'NEC ECC Option A': 'NEC ECC A',
    'NEC ECC Option B': 'NEC ECC B',
    'NEC ECC Option E': 'NEC ECC E',
    'NEC PSC Option A': 'NEC PSC A',
    'NEC PSC Option E': 'NEC PSC E',
    'FIDIC Yellow Book': 'FIDIC Yellow',
    'TSUK': 'TSUK',
}

CATEGORY_MAP = {
    'Site Conditions - Weather':              'Site Conditions - Weather',
    'Site Conditions - Hidden':               'Site Conditions - Hidden',
    'Site Conditions - Other':                'Site Conditions - Other',
    'Programme':                              'Programme',
    'Design Maturity':                        'Design Maturity',
    'Error by Contractor':                    'Error by Contractor',
    'Change to Scope of Work':                'Change to Scope of Work',
    'Change to Scope of Work - Legacy Condition': 'Change to Scope of Work',
    'Cost Increase':                          'Cost Increase',
    'Key Information not Supplied':           'Key Information not Supplied',
    'Key Information not Supplied - Non Permit Related': 'Key Information not Supplied',
    'Key Information not Supplied - Permit Related':     'Key Information not Supplied',
    'Work Front Not Available':               'Work Front Not Available',
}

VALID_SEVERITIES = {'Very Low', 'Low', 'Medium', 'High', 'Very High'}


def slug(s: str | None) -> str:
    if not s:
        return ''
    s = re.sub(r'[^a-zA-Z0-9]+', '-', s.strip().lower())
    return s.strip('-')


def parse_date(v: Any) -> str | None:
    if v is None or v == '':
        return None
    if isinstance(v, datetime):
        return v.strftime('%Y-%m-%d')
    s = str(v).strip()
    for fmt in ('%d/%m/%Y', '%Y-%m-%d', '%m/%d/%Y'):
        try:
            return datetime.strptime(s, fmt).strftime('%Y-%m-%d')
        except ValueError:
            continue
    return None


def map_category(raw: str | None) -> str:
    if not raw:
        return 'Undefined'
    if raw in CATEGORY_MAP:
        return CATEGORY_MAP[raw]
    for key, val in CATEGORY_MAP.items():
        if raw.startswith(key):
            return val
    return 'Undefined'


def map_raised_by(raw: str | None) -> str:
    if raw == 'Contractor':
        return 'Contractor'
    if raw == 'Project Manager':
        return 'PM'
    return 'Other'


def severity_or_null(v: Any) -> str | None:
    return v if v in VALID_SEVERITIES else None


# ─── Load Contract Master ───────────────────────────────────────────────────

def load_contracts() -> tuple[list[dict], dict[str, str], dict[str, str]]:
    """
    Returns (contracts, contract_number_to_id, contract_number_to_currency).
    """
    wb = openpyxl.load_workbook(CONTRACT_MASTER, data_only=True)
    ws = wb['Sheet1']

    contracts: list[dict] = []
    cn_to_id: dict[str, str] = {}
    cn_to_currency: dict[str, str] = {}

    for r in range(2, ws.max_row + 1):
        project_raw = ws.cell(r, 1).value
        if not project_raw:
            continue
        cnum = ws.cell(r, 2).value
        ctype = ws.cell(r, 3).value
        manager = ws.cell(r, 4).value
        supplier = ws.cell(r, 5).value
        type2 = ws.cell(r, 6).value
        title = ws.cell(r, 7).value
        start = ws.cell(r, 8).value
        end = ws.cell(r, 9).value
        currency = ws.cell(r, 10).value or 'GBP'
        initial = ws.cell(r, 11).value or 0
        current = ws.cell(r, 12).value or 0
        done = ws.cell(r, 13).value or 0

        vendor = supplier.split('/')[0].strip() if supplier else ''
        cid = f'cm-{r - 1:03d}-{slug(vendor)[:20]}'
        cn_to_id[cnum] = cid
        cn_to_currency[cnum] = currency

        contract_type: str | None = None
        if type2:
            t = type2.strip().lower()
            if 'supplies' in t and 'service' in t:
                contract_type = 'Both'
            elif 'service' in t:
                contract_type = 'Services'
            elif 'supplies' in t:
                contract_type = 'Supplies'

        contracts.append({
            'id': cid,
            'contractNumber': cnum,
            'vendor': vendor,
            'project': PROJECT_MAP.get(project_raw, project_raw),
            'area': None,
            'form': CONTRACT_FORM_MAP.get(ctype, 'Other'),
            'type': contract_type,
            'pmId': slug(manager),
            'pmName': manager or '',
            'startDate': parse_date(start),
            'endDate': parse_date(end),
            'initialValue': {'amount': float(initial), 'currency': currency},
            'currentValue': {'amount': float(current), 'currency': currency},
            'workDoneValue': {'amount': float(done), 'currency': currency},
            'title': title or '',
        })

    return contracts, cn_to_id, cn_to_currency


# ─── Load EW data ───────────────────────────────────────────────────────────

def load_ews(cn_to_id: dict[str, str]) -> list[dict]:
    """
    EW Record Numbers (EW-000001 etc.) are per-contractor sequences and
    repeat across contractors in the source data. Build a globally unique
    id by prefixing with the contract id (or contractor slug fallback).
    The same scoping applies to nceId and quotationId references.
    """
    wb = openpyxl.load_workbook(EW_NCE_QUOTE, data_only=True)
    ws = wb['EW all Data']

    ews: list[dict] = []
    for r in range(6, ws.max_row + 1):
        project = ws.cell(r, 1).value
        if not project:
            continue
        contractor = ws.cell(r, 2).value or ''
        area = ws.cell(r, 3).value
        cnum = ws.cell(r, 4).value
        rec = ws.cell(r, 5).value
        nce_num = ws.cell(r, 6).value
        quote_num = ws.cell(r, 7).value
        # Scope record-numbers by contractor (not contract_id) — the source
        # sheets reuse EW-/NCE-/QUOTE- sequences per contractor and the
        # contract numbers don't always agree across sheets, so contractor
        # slug is the more stable join key for cross-sheet linkage.
        scope_prefix = slug(contractor) or 'unscoped'
        scoped_id = f'{scope_prefix}::{rec}' if rec else None
        scoped_nce = f'{scope_prefix}::{nce_num}' if nce_num else None
        scoped_quote = f'{scope_prefix}::{quote_num}' if quote_num else None
        title = ws.cell(r, 9).value or ''
        category_raw = ws.cell(r, 10).value
        notified_by = ws.cell(r, 12).value or ''
        notif_date = ws.cell(r, 13).value
        ew_type = ws.cell(r, 14).value  # raisedBy field
        severity = ws.cell(r, 15).value
        likelihood = ws.cell(r, 16).value
        priority = ws.cell(r, 17).value
        status = ws.cell(r, 18).value
        closed_date = ws.cell(r, 20).value
        mitigation = ws.cell(r, 21).value
        accepted = ws.cell(r, 23).value == 'Yes'
        rejected = ws.cell(r, 24).value == 'Yes'
        under_disc = ws.cell(r, 25).value == 'Yes'

        if status not in ('Open', 'Closed', 'Submitted'):
            status = 'Open'

        decision: str | None = None
        if accepted:
            decision = 'Accepted'
        elif rejected:
            decision = 'Rejected'
        elif under_disc:
            decision = 'Under Discussion'

        # Compute daysOpen at generation time. For Open/Submitted, use today
        # as the snapshot date; for Closed, span notify → close. Stored on
        # the record so all helpers can sort/filter without reparsing dates.
        notif_iso = parse_date(notif_date)
        closed_iso = parse_date(closed_date)
        days_open: int | None = None
        if notif_iso:
            try:
                d_notif = datetime.strptime(notif_iso, '%Y-%m-%d')
                if status == 'Closed' and closed_iso:
                    d_close = datetime.strptime(closed_iso, '%Y-%m-%d')
                    days_open = (d_close - d_notif).days
                else:
                    days_open = (datetime.utcnow() - d_notif).days
            except ValueError:
                pass

        ews.append({
            'id': scoped_id or f'{scope_prefix}::row-{r}',
            'contractId': cn_to_id.get(cnum),
            'contractor': contractor,
            'area': area,
            'title': title,
            'category': map_category(category_raw),
            'notifiedBy': notified_by,
            'notificationDate': notif_iso,
            'raisedBy': map_raised_by(ew_type),
            'severity': severity_or_null(severity),
            'likelihood': severity_or_null(likelihood),  # 'Occurred' folds to null
            'priority': severity_or_null(priority),
            'status': status,
            'daysOpen': days_open,
            'closedDate': closed_iso,
            'description': None,
            'nceId': scoped_nce,
            'quotationId': scoped_quote,
            # — extensions for new vizes —
            'mitigation': mitigation if isinstance(mitigation, str) else None,
            'decision': decision,
        })

    return ews


# ─── Load CE / NCE data ─────────────────────────────────────────────────────

def load_ces(
    cn_to_id: dict[str, str],
    ew_by_nce: dict[str, str],
) -> list[dict]:
    wb = openpyxl.load_workbook(EW_NCE_QUOTE, data_only=True)
    ws = wb['NCE']

    ces: list[dict] = []
    for r in range(6, ws.max_row + 1):
        project = ws.cell(r, 1).value
        if not project:
            continue
        contractor = ws.cell(r, 2).value or ''
        area = ws.cell(r, 3).value
        cnum = ws.cell(r, 4).value
        rec = ws.cell(r, 5).value
        notified_by = ws.cell(r, 6).value or ''
        notif_date = ws.cell(r, 7).value
        title = ws.cell(r, 8).value or ''
        ce_type = ws.cell(r, 9).value
        reply_due = ws.cell(r, 10).value  # noqa: F841 — kept for documentation
        reply_date = ws.cell(r, 11).value
        reply_by = ws.cell(r, 12).value
        is_ce_raw = ws.cell(r, 13).value
        quotation = ws.cell(r, 14).value

        is_ce = bool(is_ce_raw and 'compensation event' in str(is_ce_raw).lower())

        # Scope record-numbers by contractor (not contract_id) — the source
        # sheets reuse EW-/NCE-/QUOTE- sequences per contractor and the
        # contract numbers don't always agree across sheets, so contractor
        # slug is the more stable join key for cross-sheet linkage.
        scope_prefix = slug(contractor) or 'unscoped'
        scoped_id = f'{scope_prefix}::{rec}' if rec else f'{scope_prefix}::row-{r}'
        scoped_quote = f'{scope_prefix}::{quotation}' if quotation else None

        ces.append({
            'id': scoped_id,
            'contractId': cn_to_id.get(cnum),
            'contractor': contractor,
            'area': area,
            'title': title,
            'ceType': ce_type,
            'notifiedBy': notified_by,
            'notificationDate': parse_date(notif_date),
            'replyDate': parse_date(reply_date),
            'replyBy': reply_by,
            'decision': is_ce_raw,
            'isCE': is_ce,
            'ewId': ew_by_nce.get(scoped_id),
            'quotationId': scoped_quote,
        })

    return ces


# ─── Load Quotation data ────────────────────────────────────────────────────

def load_quotations(
    cn_to_id: dict[str, str],
    cn_to_currency: dict[str, str],
    nce_by_quote: dict[str, str],
) -> list[dict]:
    wb = openpyxl.load_workbook(EW_NCE_QUOTE, data_only=True)
    ws = wb['Q and NCE Data']

    quotes: list[dict] = []
    for r in range(6, ws.max_row + 1):
        area = ws.cell(r, 1).value
        if area is None:
            continue
        contractor = ws.cell(r, 2).value or ''
        cnum = ws.cell(r, 3).value
        rec = ws.cell(r, 4).value
        status = ws.cell(r, 7).value
        due_date = ws.cell(r, 8).value
        quote_date = ws.cell(r, 9).value
        quote_by = ws.cell(r, 10).value or ''
        title = ws.cell(r, 11).value or ''
        change_prices = ws.cell(r, 12).value
        change_days = ws.cell(r, 13).value
        reply_by = ws.cell(r, 14).value
        decision = ws.cell(r, 15).value
        carried_cost = ws.cell(r, 16).value
        carried_sched = ws.cell(r, 17).value

        # Scope record-numbers by contractor (not contract_id) — the source
        # sheets reuse EW-/NCE-/QUOTE- sequences per contractor and the
        # contract numbers don't always agree across sheets, so contractor
        # slug is the more stable join key for cross-sheet linkage.
        scope_prefix = slug(contractor) or 'unscoped'
        scoped_id = f'{scope_prefix}::{rec}' if rec else f'{scope_prefix}::row-{r}'

        amount: float | None = None
        if isinstance(change_prices, (int, float)):
            amount = float(change_prices)

        days: float | None = None
        if isinstance(change_days, (int, float)):
            days = float(change_days)

        currency = cn_to_currency.get(cnum, 'GBP')

        quotes.append({
            'id': scoped_id,
            'contractId': cn_to_id.get(cnum),
            'contractor': contractor,
            'area': area if isinstance(area, str) else None,
            'title': title,
            'status': status or '',
            'quotationDueDate': parse_date(due_date),
            'quotationDate': parse_date(quote_date),
            'quotationBy': quote_by,
            'changeToPrices': {'amount': amount, 'currency': currency} if amount is not None else None,
            'changeToDays': days,
            'replyBy': reply_by,
            'decision': decision,
            'carriedToCostSheet': bool(carried_cost) and str(carried_cost).strip().lower() in ('yes', 'true', 'y', '1'),
            'impactCarriedToSchedule': bool(carried_sched) and str(carried_sched).strip().lower() in ('yes', 'true', 'y', '1'),
            'nceId': nce_by_quote.get(scoped_id),
        })

    return quotes


# ─── Derive PM records ──────────────────────────────────────────────────────

def derive_pms(contracts: list[dict], ces: list[dict]) -> list[dict]:
    pm_to_contracts: dict[str, list[str]] = {}
    pm_to_name: dict[str, str] = {}
    for c in contracts:
        pid = c['pmId']
        if not pid:
            continue
        pm_to_contracts.setdefault(pid, []).append(c['id'])
        pm_to_name[pid] = c['pmName']

    pm_to_ce_replied = {pid: 0 for pid in pm_to_contracts}
    pm_to_ce_total = {pid: 0 for pid in pm_to_contracts}
    pm_to_response_days: dict[str, list[float]] = {pid: [] for pid in pm_to_contracts}

    contract_to_pm = {c['id']: c['pmId'] for c in contracts}

    for ce in ces:
        cid = ce['contractId']
        if cid not in contract_to_pm:
            continue
        pid = contract_to_pm[cid]
        pm_to_ce_total[pid] += 1
        if ce.get('replyDate') and ce.get('notificationDate'):
            try:
                d1 = datetime.strptime(ce['notificationDate'], '%Y-%m-%d')
                d2 = datetime.strptime(ce['replyDate'], '%Y-%m-%d')
                lag = (d2 - d1).days
                pm_to_response_days[pid].append(float(lag))
                if lag <= 7:
                    pm_to_ce_replied[pid] += 1
            except ValueError:
                pass

    pms: list[dict] = []
    for pid, cids in pm_to_contracts.items():
        total = pm_to_ce_total[pid]
        replied = pm_to_ce_replied[pid]
        days = pm_to_response_days[pid]
        pms.append({
            'id': pid,
            'name': pm_to_name[pid],
            'contractIds': cids,
            'ewResponseRate': 0.0,  # not derivable from current data
            'ceResponseRate': replied / total if total else 0.0,
            'avgResponseDays': sum(days) / len(days) if days else 0.0,
            'contractsCount': len(cids),
        })
    return pms


# ─── Compute KPIs ───────────────────────────────────────────────────────────

def to_gbp(money: dict | None) -> float:
    if not money or money.get('amount') is None:
        return 0.0
    rate = EUR_TO_GBP if money['currency'] == 'EUR' else 1.0
    return money['amount'] * rate


def days_since(iso: str | None) -> int | None:
    if not iso:
        return None
    try:
        d = datetime.strptime(iso, '%Y-%m-%d')
    except ValueError:
        return None
    return (datetime.utcnow() - d).days


def derive_kpis(
    contracts: list[dict],
    ews: list[dict],
    ces: list[dict],
    quotes: list[dict],
) -> dict:
    total_committed = sum(to_gbp(c['currentValue']) for c in contracts)
    contract_count = len(contracts)
    projects = {c['project'] for c in contracts}

    total_ews = len(ews)
    open_ews = sum(1 for e in ews if e['status'] == 'Open')
    closed_ews = sum(1 for e in ews if e['status'] == 'Closed')
    submitted_ews = sum(1 for e in ews if e['status'] == 'Submitted')
    close_rate = closed_ews / total_ews if total_ews else 0.0

    open_ages = [days_since(e['notificationDate']) for e in ews if e['status'] == 'Open']
    open_ages = [a for a in open_ages if a is not None]
    avg_age = sum(open_ages) / len(open_ages) if open_ages else 0.0

    total_ces = len(ces)
    ew_backed = sum(1 for c in ces if c['ewId'])
    direct = total_ces - ew_backed
    linkage = ew_backed / total_ces if total_ces else 0.0

    total_quotes = len(quotes)

    # PM response rate — share of CEs replied within 7 days
    replied_in_7 = 0
    eligible = 0
    for c in ces:
        if not c['notificationDate']:
            continue
        eligible += 1
        if c['replyDate']:
            try:
                d1 = datetime.strptime(c['notificationDate'], '%Y-%m-%d')
                d2 = datetime.strptime(c['replyDate'], '%Y-%m-%d')
                if (d2 - d1).days <= 7:
                    replied_in_7 += 1
            except ValueError:
                pass
    pm_response_rate = replied_in_7 / eligible if eligible else 0.0

    # Deemed acceptance: pending quotations past 28d since quotation_date
    pending_states = {'Submitted', 'Assessment', 'In_Review', 'Internal_Review', 'Revised_Quotation'}
    deemed_count = 0
    deemed_value = 0.0
    approaching_count = 0
    approaching_value = 0.0
    for q in quotes:
        if q['status'] not in pending_states:
            continue
        d = days_since(q['quotationDate'])
        if d is None:
            continue
        v = to_gbp(q['changeToPrices'])
        if d > 28:
            deemed_count += 1
            deemed_value += v
        elif d > 14:
            approaching_count += 1
            approaching_value += v

    return {
        'totalEWs': total_ews,
        'openEWs': open_ews,
        'closedEWs': closed_ews,
        'submittedEWs': submitted_ews,
        'closeRate': close_rate,
        'avgEWAge': avg_age,
        'totalCEs': total_ces,
        'directTriggerCEs': direct,
        'ewBackedCEs': ew_backed,
        'linkageRate': linkage,
        'totalQuotations': total_quotes,
        'pmResponseRate': pm_response_rate,
        'deemedAcceptanceExposureGBP': deemed_value,
        'deemedAcceptanceCount': deemed_count,
        'approachingDeemedCount': approaching_count,
        'approachingDeemedExposureGBP': approaching_value,
        'totalCommittedGBP': total_committed,
        'contractCount': contract_count,
        'projectsCount': len(projects),
    }


def derive_categories(ews: list[dict]) -> list[dict]:
    counts: dict[str, int] = {}
    for e in ews:
        counts[e['category']] = counts.get(e['category'], 0) + 1
    total = sum(counts.values()) or 1
    rows = sorted(counts.items(), key=lambda x: -x[1])
    return [{'category': c, 'count': n, 'share': n / total} for c, n in rows]


# ─── Main ───────────────────────────────────────────────────────────────────

def main() -> None:
    contracts, cn_to_id, cn_to_currency = load_contracts()
    ews = load_ews(cn_to_id)
    ew_by_nce: dict[str, str] = {e['nceId']: e['id'] for e in ews if e.get('nceId')}
    nce_by_quote: dict[str, str] = {}  # placeholder — requires NCE → quote map

    ces = load_ces(cn_to_id, ew_by_nce)
    for ce in ces:
        if ce.get('quotationId'):
            nce_by_quote[ce['quotationId']] = ce['id']

    quotes = load_quotations(cn_to_id, cn_to_currency, nce_by_quote)

    pms = derive_pms(contracts, ces)
    categories = derive_categories(ews)
    kpis = derive_kpis(contracts, ews, ces, quotes)

    anomalies: list[dict] = []
    unmatched_ews = sum(1 for e in ews if not e['contractId'])
    if unmatched_ews:
        anomalies.append({
            'kind': 'EW_unmatched_contract',
            'id': '—',
            'reason': f'{unmatched_ews} EWs reference contract numbers not in Contract Master',
        })

    output = {
        'generatedAt': datetime.utcnow().strftime('%Y-%m-%dT%H:%M:%SZ'),
        'contracts': contracts,
        'ews': ews,
        'ces': ces,
        'quotations': quotes,
        'pms': pms,
        'categories': categories,
        'kpis': kpis,
        'anomalies': anomalies,
    }

    OUTPUT.write_text(json.dumps(output, indent=2, default=str))
    print(f'Wrote {OUTPUT}')
    print(f'  contracts={len(contracts)} ews={len(ews)} ces={len(ces)} quotes={len(quotes)} pms={len(pms)}')
    print(f'  kpis={json.dumps(kpis, indent=2)}')


if __name__ == '__main__':
    main()