"""Build the match report workbook + machine crosswalk CSV from final.json."""
import json, csv, collections, datetime
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.comments import Comment

D = '/var/www/html/peta/storage/app/propertylab-catalogue-match'
rc = {r['scheme_id']: r for r in json.load(open(f'{D}/realtycheck_schemes.json'))}
cat = json.load(open(f'{D}/catalogue_my_projects.json'))
months = {m['scheme_id']: m for m in json.load(open(f'{D}/realtycheck_months.json'))}
final = json.load(open(f'{D}/final.json'))
stats = json.load(open(f'{D}/report_stats.json'))

STATUSES = ['SAME - confident', 'SAME - probable', 'PART OF (phase / block / sub-estate)', 'NEEDS HUMAN CHECK',
            'NOT SAME - other property type', 'NOT SAME - other phase', 'NOT IN CATALOGUE']
MEANING = {
    'SAME - confident': 'Same development. Safe to attach realtycheck\'s monthly prices to this catalogue project.',
    'SAME - probable': 'Very likely the same; AI reviewer was fairly but not fully sure. Spot-check before relying on it.',
    'PART OF (phase / block / sub-estate)': 'Related but not 1:1 — one is a phase / precinct / block of the other. Do not attach prices blindly.',
    'NEEDS HUMAN CHECK': 'Could not be settled from the data. A person who knows the area should decide.',
    'NOT SAME - other property type': 'Same neighbourhood name, but realtycheck\'s figures are for a different property class (e.g. shops vs the catalogue\'s houses).',
    'NOT SAME - other phase': 'Same township, different phase / precinct number.',
    'NOT IN CATALOGUE': 'No catalogue project is this development (a same-named estate elsewhere, or nothing similar).',
}

F = 'Arial'
HFONT = Font(name=F, bold=True, color='FFFFFF', size=10)
HFILL = PatternFill('solid', start_color='1F2A44')
BFONT = Font(name=F, size=10)
BOLD = Font(name=F, size=10, bold=True)
TITLE = Font(name=F, size=14, bold=True, color='1F2A44')
SUB = Font(name=F, size=10, italic=True, color='555555')
thin = Side(style='thin', color='D0D5DD')
BOX = Border(top=thin, bottom=thin, left=thin, right=thin)
STATUS_FILL = {'SAME - confident': 'E3F4E8', 'SAME - probable': 'EEF7E4', 'PART OF (phase / block / sub-estate)': 'FFF4D6',
               'NEEDS HUMAN CHECK': 'FDE7D2', 'NOT SAME - other property type': 'F1F2F4', 'NOT SAME - other phase': 'F1F2F4',
               'NOT IN CATALOGUE': 'F7F7F8'}

def fnum(x):
    try:
        v = float(x); return v if v > 0 else None
    except (TypeError, ValueError):
        return None

def row_for(f):
    r = rc[f['scheme_id']]; m = months.get(f['scheme_id'], {})
    linked = f['status'].startswith(('SAME', 'PART OF', 'NEEDS'))
    p = cat[f['cat_i']] if (f['cat_i'] is not None and linked) else None
    b = f['best']
    reason = f['reason'] or ''
    if not linked and f['cat_i'] is not None:
        rp = cat[f['cat_i']]
        reason = f"{reason} — related catalogue row: {rp['project_name']} (id {rp['id']}, {rp['area'] or '?'})"
    if f.get('web'): reason = f'{reason} [web-checked]'
    if p is None and f['best_i'] is not None and f['status'] == 'NOT IN CATALOGUE' and f['method'] == 'rules':
        bp = cat[f['best_i']]
        reason = f"{reason} — closest: {bp['project_name']} ({bp['area'] or '?'}, {bp['state'] or '?'})"
    dist = None
    if p is not None and b and b['i'] == f['cat_i']:
        dist = b['dist_m']
    elif p is not None and f['cat_i'] is not None:
        # AI picked a candidate other than the rules' first choice — find its distance in the stored candidates
        dist = next((c['dist_m'] for c in (f.get('top_all') or []) if c['i'] == f['cat_i']), None)
    twins = '; '.join(f"{cat[i]['project_name']} (id {cat[i]['id']})" for i in f['twins']) if (p is not None and f['cat_i'] == f['best_i']) else ''
    return [f['scheme_id'], r['display_name'], r['category'], r['state'] or '', r['district'] or '',
            f['status'], f['method'], f['confidence'] or '',
            p['id'] if p else None, p['uuid'] if p else '', p['project_name'] if p else '', (p['area'] or '') if p else '',
            (p['state'] or '') if p else '', (p['property_type'] or '') if p else '', dist,
            fnum(r['median_rm']), fnum(p['price_median']) if p else None,
            (round(fnum(r['median_rm']) / fnum(p['price_median']), 2) if (p and fnum(r['median_rm']) and fnum(p['price_median'])) else None),
            fnum(r['reported_psf']), fnum(p['psf_median']) if p else None,
            m.get('months'), m.get('first_m', ''), m.get('last_m', ''), reason, twins, SHARED.get(f['cat_i'], 1) - 1 if (p is not None and f['status'].startswith('SAME')) else None, f['tier']]

HEAD = ['Realtycheck scheme ID', 'Realtycheck name', 'Category', 'State', 'District', 'Final status', 'Decided by', 'Confidence',
        'Catalogue project ID', 'Catalogue UUID', 'Catalogue project name', 'Catalogue area', 'Catalogue state', 'Catalogue type',
        'Distance (m)', 'Realtycheck median price (RM)', 'Catalogue median price (RM)', 'Price ratio (realtycheck / catalogue)',
        'Realtycheck PSF (RM)', 'Catalogue PSF (RM)', 'Months of realtycheck price data', 'First month', 'Last month',
        'Reason', 'Catalogue duplicate rows (same development)', 'Other realtycheck schemes matched to the same project', 'Rule tier (technical)']
WIDTH = [18, 34, 16, 14, 18, 30, 20, 11, 12, 38, 38, 20, 14, 26, 11, 14, 14, 14, 11, 11, 11, 10, 10, 60, 40, 14, 26]
MONEY, INT, RATIO = '#,##0', '#,##0', '0.00"x"'

def write_table(ws, rows, start_row=1):
    for j, h in enumerate(HEAD, 1):
        c = ws.cell(row=start_row, column=j, value=h); c.font = HFONT; c.fill = HFILL
        c.alignment = Alignment(wrap_text=True, vertical='center'); c.border = BOX
    for i, row in enumerate(rows, start_row + 1):
        for j, v in enumerate(row, 1):
            c = ws.cell(row=i, column=j, value=v); c.font = BFONT
        ws.cell(row=i, column=18).number_format = RATIO
        for col in (15, 21, 26): ws.cell(row=i, column=col).number_format = INT
        for col in (16, 17, 19, 20): ws.cell(row=i, column=col).number_format = MONEY
        ws.cell(row=i, column=9).number_format = '0'
        fill = STATUS_FILL.get(row[5])
        if fill: ws.cell(row=i, column=6).fill = PatternFill('solid', start_color=fill)
    for j, w in enumerate(WIDTH, 1): ws.column_dimensions[get_column_letter(j)].width = w
    ws.row_dimensions[start_row].height = 42
    ws.freeze_panes = ws.cell(row=start_row + 1, column=3)
    last = start_row + max(len(rows), 1)
    ws.auto_filter.ref = f'A{start_row}:{get_column_letter(len(HEAD))}{last}'
    ws.cell(row=start_row, column=18).comment = Comment('Median sale price ratio. For the same development the two sources '
                                                        'typically agree within about ±15% (measured on the confident matches).', 'match report')
    ws.cell(row=start_row, column=26).comment = Comment('How many OTHER realtycheck schemes were also matched SAME to this catalogue project '
                                                        '(e.g. one taman split by land title, or its houses and flats listed separately). Computed when the file was built.', 'match report')
    ws.cell(row=start_row, column=19).comment = Comment('PSF agrees within ±10% for condos/apartments/flats of the same building, '
                                                        'but NOT for landed homes — the two sources use a different area basis.', 'match report')

SHARED = collections.Counter(f['cat_i'] for f in final if f['status'].startswith('SAME'))
ORDER = {s: i for i, s in enumerate(STATUSES)}
STATUS_N = collections.Counter(f['status'] for f in final)
CAT_N = collections.Counter((rc[f['scheme_id']]['category'], f['status']) for f in final)
rows_all = sorted((row_for(f) for f in final), key=lambda r: (ORDER.get(r[5], 99), r[3], r[1]))

wb = Workbook()
ws = wb.active; ws.title = 'Summary'
ws.sheet_view.showGridLines = False
ws['A1'] = 'Realtycheck (propertylab_*) ↔ Master catalogue — match report'; ws['A1'].font = TITLE
ws['A2'] = (f"Built {datetime.date.today().isoformat()}. Realtycheck: {len(final):,} Malaysian schemes from the imported snapshot "
            f"(collected {stats['snapshot_collected']}). Master catalogue: {stats['catalogue_my']:,} Malaysian projects (read-only). Nothing was written to either database.")
ws['A2'].font = SUB
ws['A4'] = 'Result'; ws['A4'].font = BOLD
hdr = ['Final status', 'Schemes', '% of all', 'What it means']
for j, h in enumerate(hdr, 1):
    c = ws.cell(row=5, column=j, value=h); c.font = HFONT; c.fill = HFILL; c.border = BOX
N_ALL = len(final) + 1
rng = f"'All schemes'!$F$2:$F${N_ALL}"
for i, s in enumerate(STATUSES, 6):
    ws.cell(row=i, column=1, value=s).font = BFONT
    ws.cell(row=i, column=1).fill = PatternFill('solid', start_color=STATUS_FILL[s])
    ws.cell(row=i, column=2, value=STATUS_N[s]).number_format = INT
    ws.cell(row=i, column=3, value=STATUS_N[s] / len(final)).number_format = '0.0%'
    ws.cell(row=i, column=4, value=MEANING[s]).alignment = Alignment(wrap_text=True, vertical='top')
    for j in range(1, 5): ws.cell(row=i, column=j).border = BOX; ws.cell(row=i, column=j).font = BFONT
tr = 6 + len(STATUSES)
ws.cell(row=tr, column=1, value='Total').font = BOLD
ws.cell(row=tr, column=2, value=sum(STATUS_N.values())).number_format = INT
ws.cell(row=tr, column=2).font = BOLD
ws.cell(row=tr, column=3, value=1).number_format = '0.0%'
ws.cell(row=tr, column=4, value=f'Every realtycheck scheme appears exactly once. Counts were computed when the file was built — filter the "All schemes" tab by Final status to reproduce any of them.').font = SUB

r0 = tr + 2
ws.cell(row=r0, column=1, value='Catalogue side').font = BOLD
facts = [
    ('Distinct catalogue projects that receive a SAME match', stats['distinct_cat_same'],
     'Counted at build time from the SAME rows (confident + probable). Some catalogue projects are matched by two realtycheck schemes, e.g. a township whose houses and flats realtycheck lists separately.'),
    ('Malaysian catalogue projects in total', stats['catalogue_my'], 'catalog_projects where country = Malaysia, read 2026-09-23.'),
    ('Catalogue duplicate groups found on the way', stats['dup_groups'],
     'The same development listed twice or more in the catalogue (e.g. "Dedaun" and "Dedaun Condominium"). See the "Catalogue duplicates" tab.'),
]
for j, h in enumerate(['Measure', 'Value', 'Note'], 1):
    c = ws.cell(row=r0 + 1, column=j, value=h); c.font = HFONT; c.fill = HFILL
for k, (a, b, note) in enumerate(facts, r0 + 2):
    ws.cell(row=k, column=1, value=a).font = BFONT
    ws.cell(row=k, column=2, value=b).number_format = INT; ws.cell(row=k, column=2).font = BFONT
    ws.cell(row=k, column=3, value=note).font = SUB
    ws.cell(row=k, column=3).alignment = Alignment(wrap_text=True, vertical='top')

r1 = r0 + 2 + len(facts) + 1
ws.cell(row=r1, column=1, value='By realtycheck category').font = BOLD
cats = ['Landed', 'Condo/Apartment', 'Serviced Apartment', 'Flat', 'Office/SOHO', 'Shop', 'Industrial']
short = ['Same (conf.)', 'Same (prob.)', 'Part of', 'Check', 'Other type', 'Other phase', 'Not in cat.']
for j, h in enumerate(['Category'] + short + ['Total'], 1):
    c = ws.cell(row=r1 + 1, column=j, value=h); c.font = HFONT; c.fill = HFILL
crng = f"'All schemes'!$C$2:$C${N_ALL}"
for k, cname in enumerate(cats, r1 + 2):
    ws.cell(row=k, column=1, value=cname).font = BFONT
    for j, s in enumerate(STATUSES, 2):
        ws.cell(row=k, column=j, value=CAT_N[(cname, s)]).number_format = INT
    ws.cell(row=k, column=len(STATUSES) + 2, value=sum(CAT_N[(cname, s2)] for s2 in STATUSES)).number_format = INT

r2 = r1 + 2 + len(cats) + 1
ws.cell(row=r2, column=1, value='How much can we trust this?').font = BOLD
trust = stats['trust_lines']
for k, line in enumerate(trust, r2 + 1):
    ws.cell(row=k, column=1, value='• ' + line).font = BFONT
    ws.merge_cells(start_row=k, start_column=1, end_row=k, end_column=4)
    ws.cell(row=k, column=1).alignment = Alignment(wrap_text=True, vertical='top')
    ws.row_dimensions[k].height = 30
r3 = r2 + 1 + len(trust) + 1
ws.cell(row=r3, column=1, value='How it was matched').font = BOLD
for k, line in enumerate(stats['method_lines'], r3 + 1):
    ws.cell(row=k, column=1, value='• ' + line).font = BFONT
    ws.merge_cells(start_row=k, start_column=1, end_row=k, end_column=4)
    ws.cell(row=k, column=1).alignment = Alignment(wrap_text=True, vertical='top')
    ws.row_dimensions[k].height = 30
ws.column_dimensions['A'].width = 44; ws.column_dimensions['B'].width = 13; ws.column_dimensions['C'].width = 11
ws.column_dimensions['D'].width = 95
for col in 'EFGHI': ws.column_dimensions[col].width = 12

for title, keep in [('Same (confident)', {'SAME - confident'}), ('Same (probable)', {'SAME - probable'}),
                    ('Part of', {'PART OF (phase / block / sub-estate)'}), ('Needs check', {'NEEDS HUMAN CHECK'})]:
    w = wb.create_sheet(title)
    write_table(w, [r for r in rows_all if r[5] in keep])
w = wb.create_sheet('All schemes')
write_table(w, rows_all)

# catalogue duplicates
w = wb.create_sheet('Catalogue duplicates')
dh = ['Group', 'Catalogue project ID', 'Catalogue UUID', 'Catalogue project name', 'Area', 'State', 'Type', 'Has EdgeProp source', 'Median price (RM)', 'Check']
for j, h in enumerate(dh, 1):
    c = w.cell(row=1, column=j, value=h); c.font = HFONT; c.fill = HFILL
rr = 2
for g, grp in enumerate(stats['dup_list'], 1):
    areas = {(cat[i]['area'] or '').strip().lower() for i in grp} - {''}
    flag = 'Area labels differ — confirm it really is one development' if len(areas) > 1 else ''
    for i in grp:
        p = cat[i]
        vals = [g, p['id'], p['uuid'], p['project_name'], p['area'] or '', p['state'] or '', p['property_type'] or '',
                'yes' if 'edgeprop' in p['providers'] else 'no', fnum(p['price_median']), flag]
        for j, v in enumerate(vals, 1):
            c = w.cell(row=rr, column=j, value=v); c.font = BFONT
        w.cell(row=rr, column=9).number_format = MONEY
        rr += 1
for j, wd in enumerate([8, 12, 38, 44, 22, 14, 30, 12, 14, 48], 1): w.column_dimensions[get_column_letter(j)].width = wd
w.freeze_panes = 'A2'; w.auto_filter.ref = f'A1:J{max(rr-1,1)}'

wb.save(f'{D}/propertylab-catalogue-match.xlsx')

with open(f'{D}/crosswalk.csv', 'w', newline='', encoding='utf-8') as fh:
    wr = csv.writer(fh)
    wr.writerow(['scheme_id', 'scheme_name', 'category', 'status', 'catalog_project_id', 'catalog_project_uuid', 'catalog_project_name',
                 'decided_by', 'confidence', 'distance_m', 'reason'])
    for r in rows_all:
        wr.writerow([r[0], r[1], r[2], r[5], r[8] or '', r[9], r[10], r[6], r[7], '' if r[14] is None else r[14], r[23]])
print('saved', len(rows_all), 'rows')