"""Build the human review workbook: SAME-probable / PART OF / NEEDS CHECK, with map + page links and a decision column."""
import json
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.datavalidation import DataValidation

D = '/var/www/html/peta/storage/app/propertylab-catalogue-match'
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'))
final = 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'))
RES = {'Landed', 'Condo/Apartment', 'Serviced Apartment', 'Flat'}

def fnum(x):
    try:
        v = float(x); return v if v > 0 else None
    except (TypeError, ValueError):
        return None
def gmap(lat, lng):
    return f'https://www.google.com/maps?q={lat},{lng}' if fnum(lat) and fnum(lng) else None

F = 'Arial'
HF, HFILL = Font(name=F, bold=True, color='FFFFFF', size=10), PatternFill('solid', start_color='1F2A44')
BF, LINK = Font(name=F, size=10), Font(name=F, size=10, color='0563C1', underline='single')
INPUT = PatternFill('solid', start_color='FFF2A8')
thin = Side(style='thin', color='D0D5DD'); BOX = Border(top=thin, bottom=thin, left=thin, right=thin)
HEAD = ['#', 'Priority', 'Realtycheck scheme ID', 'Realtycheck name', 'Category', 'Where (district / state / mukim)',
        'RC median price (RM)', 'RC PSF (RM)', 'Months of RC data', 'RC map', 'RC page',
        'AI suggestion', 'Suggested catalogue project', 'Catalogue area, state', 'Catalogue type',
        'Catalogue median price (RM)', 'Catalogue PSF (RM)', 'Distance (m)', 'Catalogue map', 'Catalogue page',
        'Suggested catalogue UUID', 'AI reason', 'Other candidates (id · name · area · distance · median)',
        'YOUR DECISION', 'Correct catalogue ID / UUID (if another project)', 'Your note']
WIDTH = [5, 8, 17, 34, 15, 30, 12, 9, 9, 7, 9, 20, 34, 26, 22, 12, 9, 9, 9, 10, 37, 46, 70, 16, 24, 30]
DECISIONS = '"SAME,PART OF,DIFFERENT,SKIP"'

def rows_for(status):
    out = []
    for f in final:
        if f['status'] != status: continue
        r = rc[f['scheme_id']]; m = months.get(f['scheme_id'], {})
        p = cat[f['cat_i']] if f['cat_i'] is not None else None
        dist = next((c['dist_m'] for c in f['top_all'] if p is not None and c['i'] == f['cat_i']), None)
        others = []
        for c in f['top_all']:
            if f['cat_i'] is not None and c['i'] == f['cat_i']: continue
            q = cat[c['i']]
            others.append(f"{q['id']} · {q['project_name']} · {q['area'] or '?'} · {c['dist_m'] if c['dist_m'] is not None else '?'} m · {int(fnum(q['price_median'])) if fnum(q['price_median']) else '-'}")
            if len(others) == 3: break
        prio = 1 if (r['category'] in RES and p is not None and 'edgeprop' in p['providers']) else 2
        where = ' / '.join(x for x in [r['district'], r['state'], ('mukim ' + r['mukim']) if r['mukim'] else None] if x) or '(no location given)'
        out.append({
            'prio': prio,
            'vals': [None, prio, f['scheme_id'], r['display_name'], r['category'], where,
                     fnum(r['median_rm']), fnum(r['reported_psf']), m.get('months'),
                     ('map', gmap(r['latitude'], r['longitude'])), ('page', urls.get(f['scheme_id'])),
                     f"{status} ({f['confidence'] or '-'})" if status != 'NEEDS HUMAN CHECK' else f"unsure ({f['confidence'] or '-'})",
                     p['project_name'] if p else '(no single suggestion)', f"{p['area'] or '?'}, {p['state'] or '?'}" if p else '',
                     (p['property_type'] or '-') if p else '', fnum(p['price_median']) if p else None, fnum(p['psf_median']) if p else None, dist,
                     ('map', gmap(p['latitude'], p['longitude'])) if p else None, ('open', f"{SITE}/manage/property/catalog/{p['uuid']}") if p else None,
                     p['uuid'] if p else '', f['reason'] or '', '\n'.join(others), None, None, None]})
    out.sort(key=lambda x: (x['prio'], x['vals'][5] == '(no location given)', x['vals'][5], x['vals'][3]))
    return out

def sheet(wb, title, status, blurb):
    ws = wb.create_sheet(title)
    ws['A1'] = blurb; ws['A1'].font = Font(name=F, size=10, italic=True, color='555555')
    for j, h in enumerate(HEAD, 1):
        c = ws.cell(row=2, column=j, value=h); c.font = HF; c.fill = HFILL; c.border = BOX
        c.alignment = Alignment(wrap_text=True, vertical='center')
    for col in (24, 25, 26): ws.cell(row=2, column=col).fill = PatternFill('solid', start_color='B7791F')
    rows = rows_for(status)
    for n, row in enumerate(rows, 1):
        i = n + 2
        vals = row['vals']; vals[0] = n
        for j, v in enumerate(vals, 1):
            c = ws.cell(row=i, column=j)
            if isinstance(v, tuple):
                label, url = v
                if url: c.value = label; c.hyperlink = url; c.font = LINK
            else:
                c.value = v; c.font = BF
            c.border = BOX
            c.alignment = Alignment(vertical='top', wrap_text=j in (4, 6, 13, 22, 23))
        for col in (7, 16): ws.cell(row=i, column=col).number_format = '#,##0'
        for col in (24, 25, 26): ws.cell(row=i, column=col).fill = INPUT
    dv = DataValidation(type='list', formula1=DECISIONS, allow_blank=True, showDropDown=False)
    dv.error = 'Choose SAME, PART OF, DIFFERENT or SKIP'; dv.errorTitle = 'Decision'
    ws.add_data_validation(dv); dv.add(f'X3:X{len(rows) + 2}')
    for j, w in enumerate(WIDTH, 1): ws.column_dimensions[get_column_letter(j)].width = w
    ws.row_dimensions[2].height = 44
    ws.freeze_panes = 'E3'
    ws.auto_filter.ref = f'A2:{get_column_letter(len(HEAD))}{len(rows) + 2}'
    return len(rows), sum(1 for r in rows if r['prio'] == 1)

wb = Workbook()
ws = wb.active; ws.title = 'How to review'
ws.sheet_view.showGridLines = False
lines = [
    ('Realtycheck ↔ catalogue — human review', Font(name=F, size=14, bold=True, color='1F2A44')),
    ('Each row is one realtycheck scheme the matcher could not settle on its own. Please fill the three yellow columns (X, Y, Z) on the three list tabs.', BF),
    ('', BF),
    ('Where to start', Font(name=F, size=11, bold=True)),
    ('• Priority 1 rows come first on every tab: residential schemes whose suggested project is a subsale / completed project (EdgeProp source). They are the ones that matter for the first import.', BF),
    ('• Priority 2 rows (commercial, new-launch or legacy catalogue rows) can wait.', BF),
    ('• Partial work is fine — only rows with a decision are used. Rows you leave blank keep the machine\'s answer.', BF),
    ('', BF),
    ('What to put in column X — YOUR DECISION', Font(name=F, size=11, bold=True)),
    ('SAME — the realtycheck scheme IS the suggested catalogue project (or the project whose ID you put in column Y).', BF),
    ('PART OF — one is a phase / precinct / block of the other (e.g. "TMN X FASA 2" vs "Taman X"). Put the related project in Y if it is not the suggested one.', BF),
    ('DIFFERENT — not this project, and none of the other candidates either.', BF),
    ('SKIP — you cannot tell; it stays for someone who knows the area.', BF),
    ('', BF),
    ('Column Y — only when the right project is NOT the suggested one: paste its catalogue ID (the number in "Other candidates") or its UUID.', BF),
    ('Column Z — anything worth recording, e.g. "Seri Penaga is the new phase across the road".', BF),
    ('', BF),
    ('How to check a row quickly', Font(name=F, size=11, bold=True)),
    ('1) Open both maps (columns J and S): the same estate usually sits within ~300 m (condo) or ~1.5 km (landed). Realtycheck points with no district are guessed from the name and can be far off.', BF),
    ('2) Compare median prices (G vs P): the same development is usually within ±15%. PSF (H vs Q) only for condos/flats — landed PSF is measured differently by the two sources.', BF),
    ('3) Extra estate words (Indah, Jaya, Baru, Permai…) or Kampung vs Taman usually mean a different estate.', BF),
    ('4) "RC page" opens the scheme on realtycheck.my; "Catalogue page" opens our admin page for the project.', BF),
    ('', BF),
    ('Example of a filled row', Font(name=F, size=11, bold=True)),
    ('X = SAME · Y = (blank) · Z = "checked map, same taman, price 520k vs 510k"', BF),
    ('X = SAME · Y = 23914 · Z = "the Lot 3705 row is the right one"', BF),
    ('', BF),
    ('When you are done', Font(name=F, size=11, bold=True)),
    ('Save the file and put it back in the same folder on the server (storage/app/propertylab-catalogue-match/) as review-for-humans-DONE.xlsx, then tell Claude. Your decisions override the machine\'s, and the crosswalk is rebuilt.', BF),
]
for k, (text, font) in enumerate(lines, 1):
    c = ws.cell(row=k, column=1, value=text); c.font = font; c.alignment = Alignment(wrap_text=True, vertical='top')
ws.column_dimensions['A'].width = 150

counts = {}
counts['probable'] = sheet(wb, 'Same (probable)', 'SAME - probable', 'Likely the same development; the AI reviewer was fairly but not fully sure.')
counts['part'] = sheet(wb, 'Part of', 'PART OF (phase / block / sub-estate)', 'Related but not one-to-one: one side is a phase / precinct / block of the other.')
counts['check'] = sheet(wb, 'Needs check', 'NEEDS HUMAN CHECK', 'The data could not settle these — a person who knows the area decides.')
wb.save(f'{D}/review-for-humans.xlsx')
print({k: f'{v[0]} rows ({v[1]} priority 1)' for k, v in counts.items()})