#!/usr/bin/env python3
# -*- coding: utf-8 -*-

from __future__ import annotations

from datetime import datetime
from pathlib import Path

import openpyxl


def read_unique_codes(xlsx_path: Path, header_name: str = "کد دیدار مشتری") -> list[str]:
    wb = openpyxl.load_workbook(xlsx_path, data_only=True)
    ws = wb.active
    header = [str((ws.cell(1, c).value or "")).strip() for c in range(1, ws.max_column + 1)]
    idx = {h: i + 1 for i, h in enumerate(header) if h}
    col = idx.get(header_name)
    if not col:
        raise SystemExit(f"Missing header '{header_name}' in {xlsx_path.name}; header={header}")

    raw: list[str] = []
    for r in range(2, ws.max_row + 1):
        v = ws.cell(r, col).value
        if v in (None, ""):
            continue
        s = str(v).strip()
        if s.endswith(".0"):
            s = s[:-2]
        if s:
            raw.append(s)

    seen: set[str] = set()
    uniq: list[str] = []
    for x in raw:
        if x not in seen:
            seen.add(x)
            uniq.append(x)
    return uniq


def sql_quote(v: str) -> str:
    return "'" + v.replace("\\", "\\\\").replace("'", "''") + "'"


def main() -> int:
    root = Path(__file__).resolve().parent.parent
    xlsx_path = root / "معاملات بلا تکلیف 23 آذر 1404.xlsx"
    out_dir = root / "output"
    out_dir.mkdir(parents=True, exist_ok=True)

    codes = read_unique_codes(xlsx_path)
    generated_at = datetime.now().isoformat(sep=" ", timespec="seconds")

    # 1) Mark persons.is_blataklif=1 based on code list, and exclude them from normal leads.
    mark_sql = []
    mark_sql.append("-- Generated by scripts/generate_blataklif_bucket_sql_23azar1404.py")
    mark_sql.append(f"-- Source Excel: {xlsx_path.name}")
    mark_sql.append(f"-- Generated at: {generated_at}")
    mark_sql.append("SET NAMES utf8mb4;")
    mark_sql.append("START TRANSACTION;")
    mark_sql.append("")
    mark_sql.append("ALTER TABLE `persons` ADD COLUMN IF NOT EXISTS `is_blataklif` tinyint(1) NOT NULL DEFAULT 0;")
    mark_sql.append("")
    mark_sql.append("-- Persist the code list in a helper table (so you can query it later)")
    mark_sql.append("CREATE TABLE IF NOT EXISTS `blataklif_codes_23azar1404` (`code` varchar(255) PRIMARY KEY) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;")
    mark_sql.append("TRUNCATE TABLE `blataklif_codes_23azar1404`;")

    batch = 800
    for i in range(0, len(codes), batch):
        chunk = codes[i : i + batch]
        values = ", ".join(f"({sql_quote(c)})" for c in chunk)
        mark_sql.append(f"INSERT IGNORE INTO `blataklif_codes_23azar1404` (`code`) VALUES {values};")
    mark_sql.append("")
    mark_sql.append("-- Reset then set flag based on code list")
    mark_sql.append("UPDATE `persons` SET `is_blataklif` = 0;")
    mark_sql.append("UPDATE `persons` p JOIN `blataklif_codes_23azar1404` b ON b.`code` = p.`code` SET p.`is_blataklif` = 1;")
    mark_sql.append("")
    mark_sql.append("-- Diagnostics")
    mark_sql.append("SELECT COUNT(*) AS blataklif_leads FROM persons WHERE is_blataklif = 1;")
    mark_sql.append("COMMIT;")
    (out_dir / "mark_blataklif_leads_23azar1404.sql").write_text("\n".join(mark_sql) + "\n", encoding="utf-8")

    # 2) Cleanup wrong deals created earlier from this blataklif import.
    cleanup_sql = []
    cleanup_sql.append("-- Generated by scripts/generate_blataklif_bucket_sql_23azar1404.py")
    cleanup_sql.append("-- Cleanup deals mistakenly created for blataklif leads")
    cleanup_sql.append(f"-- Generated at: {generated_at}")
    cleanup_sql.append("SET NAMES utf8mb4;")
    cleanup_sql.append("START TRANSACTION;")
    cleanup_sql.append("")
    cleanup_sql.append("-- Remove linked deal_products first")
    cleanup_sql.append(
        "DELETE dp FROM deal_products dp "
        "JOIN deals d ON d.id = dp.deal_id "
        "WHERE d.didar_deal_id LIKE 'excel_blataklif_23azar1404_%';"
    )
    cleanup_sql.append("-- Remove activities linked to those deals (if any)")
    cleanup_sql.append("DELETE FROM activities WHERE deal_didar_id LIKE 'excel_blataklif_23azar1404_%';")
    cleanup_sql.append("-- Remove the deals themselves")
    cleanup_sql.append("DELETE FROM deals WHERE didar_deal_id LIKE 'excel_blataklif_23azar1404_%';")
    cleanup_sql.append("")
    cleanup_sql.append("-- Diagnostics")
    cleanup_sql.append("SELECT COUNT(*) AS remaining_excel_blataklif_deals FROM deals WHERE didar_deal_id LIKE 'excel_blataklif_23azar1404_%';")
    cleanup_sql.append("COMMIT;")
    (out_dir / "cleanup_blataklif_deals_23azar1404.sql").write_text("\n".join(cleanup_sql) + "\n", encoding="utf-8")

    print("Wrote:")
    print(" -", out_dir / "mark_blataklif_leads_23azar1404.sql")
    print(" -", out_dir / "cleanup_blataklif_deals_23azar1404.sql")
    print("codes:", len(codes))
    return 0


if __name__ == "__main__":
    raise SystemExit(main())