import io
import json
import os
import re
import subprocess
from collections import defaultdict
import httpx
import openpyxl

from common.protocol import (
    AnnouncementItem,
    AttachmentItem,
    CollectorMeta,
    IngestPayload,
    PositionItem,
)

CORE_INGEST_URL = os.getenv("CORE_INGEST_URL", "http://127.0.0.1:8080/api/v1/ingest")
INGEST_TOKEN = os.getenv("INGEST_TOKEN", "")

def find_header_row(sheet):
    for r_idx in range(1, 15):
        row_vals = [
            str(sheet.cell(r_idx, c).value or "").replace("\n", "").strip()
            for c in range(1, min(sheet.max_column + 1, 40))
        ]
        row_text = " ".join(row_vals)
        if any(k in row_text for k in ["岗位名称", "招聘岗位", "岗位代码", "职位"]) and any(
            k in row_text for k in ["人数", "学历", "专业", "单位"]
        ):
            return r_idx, {v: idx for idx, v in enumerate(row_vals, 1) if v}
    return None, {}

def parse_excel_positions(content_bytes: bytes) -> list[PositionItem]:
    wb = openpyxl.load_workbook(io.BytesIO(content_bytes), data_only=True)
    sheet = wb.active
    h_row, headers = find_header_row(sheet)
    if not h_row:
        return []

    name_col = headers.get("岗位名称") or headers.get("招聘岗位") or headers.get("职位")
    code_col = headers.get("岗位代码") or headers.get("编号") or headers.get("序号")
    edu_col = headers.get("学历要求") or headers.get("学历")
    deg_col = headers.get("学位要求") or headers.get("学位")
    hc_col = headers.get("招聘人数") or headers.get("选调人数") or headers.get("人数")
    age_col = headers.get("年龄要求") or headers.get("年龄")
    maj_col = headers.get("专业要求") or headers.get("专业")
    req_col = headers.get("其他招聘条件") or headers.get("其他报考资格条件") or headers.get("备注")

    items = []
    for r_idx in range(h_row + 1, sheet.max_row + 1):
        post_name = (
            str(sheet.cell(r_idx, name_col).value or "").strip()
            if name_col
            else ""
        )
        if not post_name or post_name.lower() in ["none", ""]:
            continue

        post_code = (
            str(sheet.cell(r_idx, code_col).value or "").strip()
            if code_col
            else None
        )

        dept_parts = []
        for d_key in ["一级单位名称", "二级单位名称", "三级单位名称", "招聘单位名称", "用人单位"]:
            if d_key in headers:
                v = str(sheet.cell(r_idx, headers[d_key]).value or "").strip()
                if v and v not in dept_parts:
                    dept_parts.append(v)
        dept = " ".join(dept_parts) if dept_parts else None

        headcount = None
        if hc_col:
            try:
                headcount = int(sheet.cell(r_idx, hc_col).value or 0)
            except Exception:
                pass

        age_max = None
        if age_col:
            m = re.search(r"(\d+)", str(sheet.cell(r_idx, age_col).value or ""))
            if m:
                age_max = int(m.group(1))

        edu_min = (
            str(sheet.cell(r_idx, edu_col).value or "").strip()
            if edu_col
            else "不限"
        )

        degree_required = False
        if deg_col:
            deg_val = str(sheet.cell(r_idx, deg_col).value or "").strip()
            if any(k in deg_val for k in ["学士", "硕士", "博士", "学位"]):
                degree_required = True

        majors = []
        if maj_col:
            maj_str = str(sheet.cell(r_idx, maj_col).value or "").strip()
            majors = [
                m.strip()
                for m in re.split(r"[,，、;\s\n]+", maj_str)
                if m.strip() and not m.startswith("专业：")
            ]
            if not majors and maj_str:
                majors = [maj_str.replace("专业：", "").strip()]

        other_req = (
            str(sheet.cell(r_idx, req_col).value or "").strip()
            if req_col
            else None
        )

        items.append(
            PositionItem(
                post_code=post_code,
                dept=dept,
                post_name=post_name,
                headcount=headcount,
                education_min=edu_min,
                degree_required=degree_required,
                majors=majors,
                major_unlimited=(len(majors) == 0 or "不限" in "".join(majors)),
                age_max=age_max,
                bianzhi="事业编制",
                work_city="玉溪市",
                other_req=other_req,
            )
        )
    return items

def main():
    print("Querying announcements with excel attachments...")
    cmd = [
        "docker", "compose", "exec", "-T", "postgres",
        "psql", "-U", "bangwen", "-d", "bangwen", "-t", "-A", "-F|", "-c",
        """
        SELECT DISTINCT
            a.id, a.source_id, a.url, a.title, a.kind, a.employer, a.employer_type,
            COALESCE(a.published_at::text, ''),
            att.id as att_id, att.filename, att.url as att_url
        FROM announcement a
        JOIN attachment att ON att.announcement_id = a.id
        WHERE att.url ILIKE '%.xlsx%' AND (att.filename ILIKE '%岗位表%' OR att.filename ILIKE '%岗位%')
        ORDER BY a.id;
        """
    ]
    res = subprocess.run(cmd, capture_output=True, text=True)
    if res.returncode != 0:
        print(f"Error querying Postgres: {res.stderr}")
        return

    lines = res.stdout.strip().split("\n")
    print(f"Found {len(lines)} attachment rows in database.")

    ann_map = defaultdict(lambda: {"info": None, "atts": [], "positions": [], "seen_att_urls": set(), "has_error": False})
    client = httpx.Client(verify=True, timeout=15)

    for line in lines:
        parts = line.strip().split("|")
        if len(parts) < 11:
            continue
        (
            ann_id, src_id, url, title, kind, employer, employer_type,
            pub_date, att_id, fn, att_url
        ) = parts[:11]

        if not ann_map[url]["info"]:
            ann_map[url]["info"] = {
                "source_id": src_id,
                "url": url,
                "title": title,
                "kind": kind,
                "employer": employer,
                "employer_type": employer_type,
                "published_at": pub_date if pub_date else None,
            }
        
        if att_url not in ann_map[url]["seen_att_urls"]:
            ann_map[url]["seen_att_urls"].add(att_url)
            ann_map[url]["atts"].append(AttachmentItem(name=fn, url=att_url))

            try:
                print(f"Downloading {fn} from {att_url} ...")
                r = client.get(att_url)
                posts = parse_excel_positions(r.content)
                print(f"  -> Extracted {len(posts)} positions")
                ann_map[url]["positions"].extend(posts)
            except Exception as e:
                ann_map[url]["has_error"] = True
                print(f"  -> Failed to parse {att_url}: {e}")

    items_to_ingest = []
    for url, data in ann_map.items():
        info = data["info"]
        if not data["positions"]:
            continue
        items_to_ingest.append(
            AnnouncementItem(
                url=info["url"],
                title=info["title"],
                kind=info["kind"],
                employer=info["employer"],
                employer_type=info["employer_type"],
                published_at=info["published_at"],
                region_hint="云南省玉溪市",
                attachments=data["atts"],
                positions=data["positions"],
                reconcile_positions=not data["has_error"],
            )
        )

    print(f"Submitting {len(items_to_ingest)} announcements with {sum(len(it.positions) for it in items_to_ingest)} positions...")
    target_source_id = os.getenv("SOURCE_ID", "yn.yuxi.rszk")
    payload = IngestPayload(
        protocol_version="bangwen/v1",
        source_id=target_source_id,
        batch_id=f"batch-excel-positions-{int(time.time())}",
        collector=CollectorMeta(
            name="excel_position_extractor",
            runtime="python/3.12",
            auth_status="authenticated",
        ),
        items=items_to_ingest,
    )

    headers = {
        "Content-Type": "application/json",
        "Authorization": f"Bearer {INGEST_TOKEN}",
    }
    resp = client.post(
        CORE_INGEST_URL,
        headers=headers,
        content=payload.model_dump_json(),
        timeout=30,
    )
    print(f"Ingest response status: {resp.status_code}")
    print(f"Response: {resp.text}")
    assert resp.status_code == 200, f"Ingestion failed: {resp.text}"
    resp_data = resp.json()
    assert resp_data.get("announcements", 0) > 0, "No announcements updated: verify source_id matches owner"

if __name__ == "__main__":
    main()