"""FINAL deliverables: FINAL-propertylab-catalogue-match.xlsx + FINAL-crosswalk.csv (from final_links.json)."""
import json, csv, collections, datetime, glob
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'; R = f'{D}/review'
SITE = 'https://wk.propertylab.com.my'
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'))
links = json.load(open(f'{D}/final_links.json'))
final = {f['scheme_id']: f for f in json.load(open(f'{D}/final.json'))}
months = {m['scheme_id']: m for m in json.load(open(f'{D}/realtycheck_months.json'))}
urls = json.load(open(f'{D}/realtycheck_urls.json'))
stats = json.load(open(f'{D}/report_stats.json'))
key3 = json.load(open(f'{R}/_key3.json'))
v_web = 0
for fn in glob.glob(f'{R}/v_*_out.jsonl'):
    v_web += sum(1 for l in open(fn) if l.strip() and json.loads(l).get('web'))

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

LINKS = ['LINK', 'HOLD', 'RELATED', 'NONE']
LABEL = {'LINK': 'LINK (auto, ≥90%)', 'HOLD': 'HOLD (same, <90%)', 'RELATED': 'RELATED (part of)', 'NONE': 'NO LINK'}
MEANING = {
    'LINK': 'Same development, verified to at least 90% confidence. Attach realtycheck\'s transaction data to this catalogue project automatically.',
    'HOLD': 'Probably the same development, but the evidence stops short of 90% (no price to compare, a price gap, or an ambiguous name). Do not attach automatically.',
    'RELATED': 'Related but not one-to-one: a phase / block of the project, or the flats of a mixed estate / township row. Keep the relation; do not attach prices as if it were the same project.',
    'NONE': 'No catalogue project is this development (not in the catalogue, a same-named place elsewhere, or a different property class / phase).',
}
SEGS = ['high-rise', 'landed', 'commercial']
FILL = {'LINK': 'E3F4E8', 'HOLD': 'FFF4D6', 'RELATED': 'EAF1FB', 'NONE': 'F4F5F7'}
F = 'Arial'
HF, HFILL = Font(name=F, bold=True, color='FFFFFF', size=10), PatternFill('solid', start_color='1F2A44')
BF, BOLD, LINK = Font(name=F, size=10), Font(name=F, size=10, bold=True), Font(name=F, size=10, color='0563C1', underline='single')
TITLE, SUB = Font(name=F, size=14, bold=True, color='1F2A44'), 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)

def phase(o):
    if o['link'] != 'LINK': return ''
    return 'Phase 1 (high-rise)' if o['segment'] == 'high-rise' else 'Phase 2 (landed / commercial, backend first)'

def row(o):
    r = rc[o['scheme_id']]; m = months.get(o['scheme_id'], {}); f = final[o['scheme_id']]
    p = cat[o['cat_i']] if o['cat_i'] is not None and o['link'] != 'NONE' else None
    dist = next((t['dist_m'] for t in f['top_all'] if p is not None and t['i'] == o['cat_i']), None)
    a, b = fnum(r['median_rm']), fnum(p['price_median']) if p else None
    return [o['scheme_id'], r['display_name'], r['category'], o['segment'], r['state'] or '', r['district'] or '',
            LABEL[o['link']], o['confidence'] if o['link'] in ('LINK', 'HOLD') else None, phase(o),
            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,
            a, b, round(a / b, 2) if (a and b) 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', ''), o['path'], o['note'] or '',
            ('realtycheck', urls.get(o['scheme_id'])), ('open', f"{SITE}/manage/property/catalog/{p['uuid']}") if p else None]

HEAD = ['Realtycheck scheme ID', 'Realtycheck name', 'Category', 'Segment', 'State', 'District', 'Decision', 'Confidence %', 'Import phase',
        '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 data', 'First month', 'Last month', 'How it was decided', 'Reason', 'Realtycheck page', 'Catalogue page']
WIDTH = [17, 34, 15, 11, 14, 18, 20, 11, 22, 11, 37, 36, 20, 13, 24, 10, 13, 13, 12, 10, 10, 10, 9, 9, 34, 60, 11, 9]

def table(ws, rows):
    for j, h in enumerate(HEAD, 1):
        c = ws.cell(row=1, column=j, value=h); c.font = HF; c.fill = HFILL; c.border = BOX
        c.alignment = Alignment(wrap_text=True, vertical='center')
    for i, rw in enumerate(rows, 2):
        for j, v in enumerate(rw, 1):
            c = ws.cell(row=i, column=j)
            if isinstance(v, tuple):
                if v[1]: c.value = v[0]; c.hyperlink = v[1]; c.font = LINK
            else:
                c.value = v; c.font = BF
        dec = rw[6].split(' ')[0]
        ws.cell(row=i, column=7).fill = PatternFill('solid', start_color=FILL.get(dec, 'FFFFFF'))
        for col in (16, 22): ws.cell(row=i, column=col).number_format = '#,##0'
        for col in (17, 18, 20, 21): ws.cell(row=i, column=col).number_format = '#,##0'
        ws.cell(row=i, column=19).number_format = '0.00"x"'
        ws.cell(row=i, column=10).number_format = '0'
    for j, w in enumerate(WIDTH, 1): ws.column_dimensions[get_column_letter(j)].width = w
    ws.row_dimensions[1].height = 42
    ws.freeze_panes = 'C2'
    ws.auto_filter.ref = f'A1:{get_column_letter(len(HEAD))}{max(len(rows), 1) + 1}'
    ws.cell(row=1, column=19).comment = Comment('Median sale price ratio — for the same development usually within ±15%.', 'report')
    ws.cell(row=1, column=20).comment = Comment('PSF is comparable for condos/apartments/flats only; the two sources measure landed PSF on a different area basis.', 'report')

ORDER = {k: n for n, k in enumerate(LINKS)}
SORD = {k: n for n, k in enumerate(SEGS)}
all_rows = sorted(links, key=lambda o: (ORDER[o['link']], SORD[o['segment']], rc[o['scheme_id']]['state'] or '~', rc[o['scheme_id']]['display_name']))
N = collections.Counter((o['segment'], o['link']) for o in links)

wb = Workbook(); ws = wb.active; ws.title = 'Summary'; ws.sheet_view.showGridLines = False
ws['A1'] = 'Realtycheck ↔ Master catalogue — FINAL verified links'; ws['A1'].font = TITLE
ws['A2'] = (f'Built {datetime.date.today().isoformat()}. {len(links):,} Malaysian realtycheck schemes (snapshot collected 2026-09-07) against '
            f'{len(cat):,} Malaysian catalogue projects. Target: only matches at ≥90% confidence are linked automatically. Nothing was written to either database.')
ws['A2'].font = SUB
ws['A4'] = 'Decision by segment'; ws['A4'].font = BOLD
hdr = ['Decision'] + [s.title() for s in SEGS] + ['Total', 'What it means']
for j, h in enumerate(hdr, 1):
    c = ws.cell(row=5, column=j, value=h); c.font = HF; c.fill = HFILL; c.border = BOX
for i, L in enumerate(LINKS, 6):
    ws.cell(row=i, column=1, value=LABEL[L]).fill = PatternFill('solid', start_color=FILL[L])
    for j, s in enumerate(SEGS, 2): ws.cell(row=i, column=j, value=N[(s, L)]).number_format = '#,##0'
    ws.cell(row=i, column=5, value=sum(N[(s, L)] for s in SEGS)).number_format = '#,##0'
    ws.cell(row=i, column=6, value=MEANING[L]).alignment = Alignment(wrap_text=True, vertical='top')
    for j in range(1, 7): ws.cell(row=i, column=j).font = BF; ws.cell(row=i, column=j).border = BOX
tr = 6 + len(LINKS)
ws.cell(row=tr, column=1, value='Total').font = BOLD
for j, s in enumerate(SEGS, 2): ws.cell(row=tr, column=j, value=sum(N[(s, L)] for L in LINKS)).number_format = '#,##0'
ws.cell(row=tr, column=5, value=len(links)).number_format = '#,##0'
link_rows = [o for o in links if o['link'] == 'LINK']
facts = [
    ('Phase 1 — high-rise links (condo / apartment / serviced / flat)', N[('high-rise', 'LINK')]),
    ('Phase 2 — landed + commercial links (backend first, frontend later)', N[('landed', 'LINK')] + N[('commercial', 'LINK')]),
    ('Distinct catalogue projects that receive a link', len({o['cat_i'] for o in link_rows})),
    ('Monthly price rows those links carry (all phases)', sum(months.get(o['scheme_id'], {}).get('months', 0) for o in link_rows)),
    ('Catalogue duplicate groups found on the way (see its own tab)', stats['dup_groups']),
]
r0 = tr + 2
ws.cell(row=r0, column=1, value='Import scope').font = BOLD
for k, (a, b) in enumerate(facts, r0 + 1):
    ws.cell(row=k, column=1, value=a).font = BF
    ws.cell(row=k, column=2, value=b).number_format = '#,##0'; ws.cell(row=k, column=2).font = BOLD
r1 = r0 + 2 + len(facts)
ws.cell(row=r1, column=1, value='How every link was verified').font = BOLD
checks = [
    'Rules: names normalised (JPPH abbreviations, land-title numbers, building-type words, phase/section numbers) + distance allowance by type + property class + median price / high-rise PSF.',
    f'AI review round 1 & 2: {sum(1 for f in final.values() if f["method"].startswith("AI review")):,} grey-zone schemes judged one by one; blind audit of 100 rule matches — 100 agreed.',
    f'Final verification: {len(key3):,} schemes (every probable / part-of / needs-check, plus 426 high-rise rule matches with a price, PSF or distance doubt) re-judged by a second, independent AI reviewer who did not see the first verdict ({v_web} with a web search).',
    'Where the two reviews disagreed, high-rise cases (241) were adjudicated one by one by Claude; landed / commercial cases are linked only when both reviews agree AND the median price is within ±15%, otherwise held.',
    'Estate-level check: a high-rise scheme is not linked to a mixed estate / township row unless that row\'s own median (and PSF) match the high-rise — 78 such links were moved to RELATED.',
    'Hand checks by Claude: 170 rule matches, 70 AI "confident" verdicts, 15 price-tie-breaker links — 0 wrong. The final verification also caught 10 earlier "confident" high-rise matches that were wrong (e.g. Akasia Apartment Seksyen 32 is in Berjaya Park, not Setia Alam; Sri Ara Kayu Ara is not Ara Damansara\'s Sri Ara) — all removed.',
    'Price corroboration: for condos / apartments / flats the two sources\' PSF agree almost exactly for the same building; landed PSF is on a different area basis and is never compared.',
    'HOLD re-check (2026-09-24): every scheme that had been held under 90% was researched again on the web (its realtycheck page, the catalogue project\'s EdgeProp page, listing and transaction prices) and given a final decision — '
    + ', '.join(f'{sum(1 for o in links if o["path"].startswith("HOLD re-check") and o["link"] == k):,} {LABEL[k]}' for k in ('LINK', 'RELATED', 'NONE'))
    + '. A link below 90% after research counts as not linked; high-rise verdicts were adjudicated by Claude.',
]
NONE_TAG = 'NONE re-check 2026-09-25'
none_rc = [o for o in links if NONE_TAG + ': web research' in o['path']]
if none_rc:
    none_nc = sum(1 for o in links if NONE_TAG + ': no catalogue candidate' in o['path'])
    checks.append(
        f'High-rise NONE re-check (2026-09-25): every high-rise scheme left unlinked was checked again. {len(none_rc):,} that had a catalogue '
        'candidate (a shared distinctive name word in the same state, or any high-rise within 1.2 km) were researched on the web the same way — '
        + ', '.join(f'{sum(1 for o in none_rc if o["link"] == k):,} {LABEL[k]}' for k in ('LINK', 'RELATED', 'NONE'))
        + f'; the other {none_nc:,} have no such candidate and stay NO LINK. Claude read every verdict before it was applied.')
    corrected = sum(1 for o in links if 'correction 2026-09-25' in o['path'])
    if corrected:
        checks.append(f'The same research corrected {corrected} earlier links (a source-less duplicate row swapped for the row with EdgeProp data, '
                      'or a scheme moved to the building it really is) — each is marked "correction 2026-09-25" with its reason.')
for k, line in enumerate(checks, r1 + 1):
    c = ws.cell(row=k, column=1, value='• ' + line); c.font = BF; c.alignment = Alignment(wrap_text=True, vertical='top')
    ws.merge_cells(start_row=k, start_column=1, end_row=k, end_column=6); ws.row_dimensions[k].height = 32
r2 = r1 + 2 + len(checks)
ws.cell(row=r2, column=1, value='Known data issues (for the catalogue team)').font = BOLD
issues = [
    f'{stats["dup_groups"]:,} catalogue duplicate groups — the same development listed 2+ times (548 are EdgeProp listing it twice, 502 pair a sourced row with a source-less legacy row). The links point at the row with EdgeProp data.',
    'Misplaced catalogue coordinates (5 to hundreds of km), wrong area labels (e.g. Tangkak estates filed under Muar, Ulu Bernam under Sabak Bernam), ~1,800 junk property types ("0"/"1"/"2"/"3"), some landed estates typed as condo, some stale or shared price medians.',
    'realtycheck rows with no district were placed on the map from the name alone and can sit next to a same-named place elsewhere.',
    'Outside the Klang Valley, Penang and Johor Bahru the catalogue holds almost no completed buildings: Melaka and Perak have no EdgeProp resale rows at all, Pahang 1, Negeri Sembilan 4, Sabah 2 rows in total — so completed condos with years of recorded sales there (Port Dickson, Genting, Cameron Highlands, Ipoh, Melaka, Kota Kinabalu) have no project to attach to.',
    'Some catalogue rows carry another building\'s numbers or address (e.g. Seri Nilam Penang has Ampang\'s address and a "Johor" state; Pangsapuri Cantik Butterworth pools a neighbouring condo\'s sales and a KL namesake\'s source; Greenlane Heights Block G holds Block F\'s sale); links follow the building, so those rows need fixing on the Hub.',
]
for k, line in enumerate(issues, r2 + 1):
    c = ws.cell(row=k, column=1, value='• ' + line); c.font = BF; c.alignment = Alignment(wrap_text=True, vertical='top')
    ws.merge_cells(start_row=k, start_column=1, end_row=k, end_column=6); ws.row_dimensions[k].height = 32
ws.column_dimensions['A'].width = 62
for col in 'BCDE': ws.column_dimensions[col].width = 12
ws.column_dimensions['F'].width = 90

for title, keep in [('Phase 1 - high-rise links', lambda o: o['link'] == 'LINK' and o['segment'] == 'high-rise'),
                    ('Phase 2 - landed + commercial', lambda o: o['link'] == 'LINK' and o['segment'] != 'high-rise'),
                    ('Hold (under 90%)', lambda o: o['link'] == 'HOLD'),
                    ('Related (part of)', lambda o: o['link'] == 'RELATED'),
                    ('All schemes', lambda o: True)]:
    rows_ = [row(o) for o in all_rows if keep(o)]
    if rows_:
        table(wb.create_sheet(title), rows_)

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 = HF; 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]
        for j, val in enumerate([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], 1):
            c = w.cell(row=rr, column=j, value=val); c.font = BF
        w.cell(row=rr, column=9).number_format = '#,##0'
        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}/FINAL-propertylab-catalogue-match.xlsx')

with open(f'{D}/FINAL-crosswalk.csv', 'w', newline='', encoding='utf-8') as fh:
    wr = csv.writer(fh)
    wr.writerow(['scheme_id', 'scheme_name', 'category', 'segment', 'decision', 'confidence_pct', 'import_phase',
                 'catalog_project_id', 'catalog_project_uuid', 'catalog_project_name', 'decided_by', 'reason'])
    for o in all_rows:
        r = rc[o['scheme_id']]; p = cat[o['cat_i']] if o['cat_i'] is not None and o['link'] != 'NONE' else None
        wr.writerow([o['scheme_id'], r['display_name'], r['category'], o['segment'], o['link'],
                     o['confidence'] if o['link'] in ('LINK', 'HOLD') else '', phase(o),
                     p['id'] if p else '', p['uuid'] if p else '', p['project_name'] if p else '', o['path'], o['note'] or ''])
print('saved', len(all_rows), '| LINK', len(link_rows), '| web in verification', v_web)