#!/usr/bin/env python3
from __future__ import annotations

import argparse
import hashlib
import os
import re
import subprocess
import tempfile
from collections import defaultdict
from dataclasses import dataclass, field
from datetime import datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any

from openpyxl import load_workbook


ROOT = Path(__file__).resolve().parents[1]
DEFAULT_XLSX = Path("/Users/turgay/Downloads/Dergi Gazete Satışlarrr.xlsx")
TARGET_SHEETS = ("2018 ORDERS", "2019 ORDERS", "2020 ORDERS")
IMPORT_TAG = "excel-last3"

MONTH_ALIASES = [
    ("JANUARY", 1),
    ("JAN", 1),
    ("FEBRUARY", 2),
    ("FEB", 2),
    ("MARCH", 3),
    ("MAR", 3),
    ("APRIL", 4),
    ("APR", 4),
    ("MAY", 5),
    ("JUNE", 6),
    ("JUN", 6),
    ("JULY", 7),
    ("JUL", 7),
    ("AUGUST", 8),
    ("AUG", 8),
    ("SEPTEMBER", 9),
    ("SEP", 9),
    ("OCTOBER", 10),
    ("OCT", 10),
    ("NOVEMBER", 11),
    ("NOV", 11),
    ("DECEMBER", 12),
    ("DEC", 12),
]


@dataclass
class Item:
    sheet: str
    row_no: int
    year: int
    media: str
    customer: str
    source_order_no: str
    issue_original: str
    issue_month: str
    issue_fallback: bool
    position: str
    size_page: str
    gross_price: Decimal
    net_price: Decimal
    notes: str


@dataclass
class OrderGroup:
    key: str
    year: int
    customer: str
    source_order_no: str
    sales_rep: str
    items: list[Item] = field(default_factory=list)

    @property
    def import_id(self) -> str:
        digest = hashlib.sha1(self.key.encode("utf-8")).hexdigest()[:16]
        return f"{IMPORT_TAG}:{digest}"


def clean(value: Any) -> str:
    if value is None:
        return ""
    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d")
    text = str(value).replace("\xa0", " ")
    return re.sub(r"\s+", " ", text).strip()


def upper_asciiish(value: str) -> str:
    return (
        value.upper()
        .replace("İ", "I")
        .replace("İ", "I")
        .replace("Ş", "S")
        .replace("Ğ", "G")
        .replace("Ü", "U")
        .replace("Ö", "O")
        .replace("Ç", "C")
    )


def sql(value: Any) -> str:
    if value is None:
        return "NULL"
    if isinstance(value, Decimal):
        return str(value)
    return "'" + str(value).replace("\\", "\\\\").replace("'", "''") + "'"


def decimalize(value: Any, default: Decimal = Decimal("0")) -> Decimal:
    if value is None:
        return default
    if isinstance(value, (int, float, Decimal)):
        return Decimal(str(value)).quantize(Decimal("0.01"))
    text = clean(value).replace(",", ".")
    try:
        return Decimal(text).quantize(Decimal("0.01"))
    except (InvalidOperation, ValueError):
        return default


def parse_year(sheet_name: str) -> int:
    match = re.search(r"20\d{2}", sheet_name)
    if not match:
        raise ValueError(f"Sheet year not found: {sheet_name}")
    return int(match.group(0))


def parse_issue_month(value: Any, year: int) -> tuple[str, bool]:
    if isinstance(value, datetime):
        return f"{value.year:04d}-{value.month:02d}", False

    text = upper_asciiish(clean(value))
    for alias, month in MONTH_ALIASES:
        if re.search(rf"(^|[^A-Z]){re.escape(alias)}([^A-Z]|$)", text):
            return f"{year:04d}-{month:02d}", False

    return f"{year:04d}-01", True


def infer_position(source_position: str, page: str) -> str:
    src = clean(source_position)
    page_clean = clean(page)
    probe = upper_asciiish(f"{src} {page_clean}")

    if "CENTER BANNER" in probe:
        return "Center Banner"
    if "FRONT COVER" in probe or "F.C" in probe:
        return "Front Cover"
    if "BACK COVER" in probe:
        return "Back Cover"
    if "RIGHT" in probe:
        return "Right hand side"
    if "LEFT" in probe:
        return "Left hand side"
    if re.search(r"\b1/1\b", probe):
        return "Full Page (1/1)"
    if re.search(r"\b1/2\b", probe):
        return "Half Page (1/2)"
    if re.search(r"\b1/3\b", probe):
        return "1/3 Page"
    if re.search(r"\b1/4\b", probe):
        return "1/4 Page"
    if src:
        return src
    if page_clean:
        return page_clean
    return "Full Page (1/1)"


def sales_rep_from_source(source_order_no: str) -> str:
    probe = upper_asciiish(source_order_no)
    if "DILARA" in probe:
        return "dilara"
    if "ENES" in probe:
        return "enes"
    return "enes"


def load_items(path: Path) -> list[Item]:
    wb = load_workbook(path, data_only=True, read_only=True)
    items: list[Item] = []

    for sheet_name in TARGET_SHEETS:
        ws = wb[sheet_name]
        year = parse_year(sheet_name)

        for row_no, row in enumerate(ws.iter_rows(min_row=2, max_col=10, values_only=True), start=2):
            media, customer, order_no, issue, position, page, notes, gross, _commission, net = row
            media_text = clean(media)
            customer_text = clean(customer)
            issue_text = clean(issue)

            if not any(clean(value) for value in row):
                continue
            if not media_text or not customer_text:
                continue

            issue_month, fallback = parse_issue_month(issue, year)
            gross_price = decimalize(gross)
            net_price = decimalize(net, gross_price)
            position_text = infer_position(clean(position), clean(page))

            item_notes = []
            if clean(notes):
                item_notes.append(clean(notes))
            item_notes.append(f"Source: {sheet_name} row {row_no}")
            item_notes.append(f"Original issue: {issue_text or '-'}")
            item_notes.append(f"Original order no: {clean(order_no) or '-'}")
            if fallback:
                item_notes.append("Issue month fallback: no month name found, imported as January.")

            items.append(
                Item(
                    sheet=sheet_name,
                    row_no=row_no,
                    year=year,
                    media=media_text,
                    customer=customer_text,
                    source_order_no=clean(order_no),
                    issue_original=issue_text,
                    issue_month=issue_month,
                    issue_fallback=fallback,
                    position=position_text,
                    size_page=clean(page),
                    gross_price=gross_price,
                    net_price=net_price,
                    notes=" | ".join(item_notes),
                )
            )

    return items


def group_items(items: list[Item]) -> list[OrderGroup]:
    groups: dict[str, OrderGroup] = {}

    for item in items:
        order_token = item.source_order_no or f"row-{item.row_no}"
        key = f"{item.sheet}|{item.customer}|{order_token}"
        if key not in groups:
            groups[key] = OrderGroup(
                key=key,
                year=item.year,
                customer=item.customer,
                source_order_no=item.source_order_no,
                sales_rep=sales_rep_from_source(item.source_order_no),
            )
        groups[key].items.append(item)

    return sorted(groups.values(), key=lambda g: (min(i.issue_month for i in g.items), g.customer, g.source_order_no))


def unique_order_numbers(groups: list[OrderGroup]) -> dict[str, str]:
    counters: dict[str, int] = defaultdict(lambda: 9000)
    output: dict[str, str] = {}

    for group in groups:
        first_issue = min(item.issue_month for item in group.items)
        prefix = first_issue.replace("-", "")
        counters[prefix] += 1
        output[group.key] = f"{prefix}{counters[prefix]:04d}"

    return output


def select_id_sql(table: str, field: str, value: str) -> str:
    return f"(SELECT id FROM {table} WHERE {field} = {sql(value)} LIMIT 1)"


def build_sql(items: list[Item], groups: list[OrderGroup]) -> str:
    media = sorted({item.media for item in items})
    customers = sorted({item.customer for item in items})
    positions = sorted({item.position for item in items})
    order_numbers = unique_order_numbers(groups)
    lines = ["SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;", "START TRANSACTION;", ""]

    for name in customers:
        lines.append(
            "INSERT IGNORE INTO customers (name, contact_person, email, phone, fax, mobile, address, notes) "
            f"VALUES ({sql(name)}, '', '', '', '', '', '', 'Imported from Excel sales history');"
        )

    lines.append("")
    for name in media:
        lines.append(f"INSERT IGNORE INTO media (name, active) VALUES ({sql(name)}, 1);")

    lines.append("")
    for name in positions:
        lines.append(f"INSERT IGNORE INTO positions (name, active) VALUES ({sql(name)}, 1);")

    lines.append("")
    for group in groups:
        first_issue = min(item.issue_month for item in group.items)
        order_date = f"{first_issue}-01"
        import_id = group.import_id
        order_notes = (
            f"Imported from Dergi Gazete Satışlarrr.xlsx. Import ID: {import_id}. "
            f"Source sheets/items: {', '.join(sorted({item.sheet for item in group.items}))}. "
            f"Source order no: {group.source_order_no or '-'}."
        )
        lines.extend(
            [
                f"SET @import_id = {sql(import_id)};",
                "SET @existing_order_id = (SELECT id FROM orders WHERE notes LIKE CONCAT('%Import ID: ', @import_id, '%') LIMIT 1);",
                "INSERT INTO orders (order_no, customer_id, order_date, payment_status_id, sales_rep_id, notes, created_by)",
                "SELECT "
                f"{sql(order_numbers[group.key])}, "
                f"{select_id_sql('customers', 'name', group.customer)}, "
                f"{sql(order_date)}, "
                "(SELECT id FROM payment_statuses WHERE name = 'Paid' LIMIT 1), "
                f"(SELECT id FROM users WHERE username = {sql(group.sales_rep)} LIMIT 1), "
                f"{sql(order_notes)}, "
                "(SELECT id FROM users WHERE username = 'enes' LIMIT 1) "
                "WHERE @existing_order_id IS NULL;",
                "SET @order_id = IF(@existing_order_id IS NULL, LAST_INSERT_ID(), @existing_order_id);",
            ]
        )

        for item in group.items:
            lines.append(
                "INSERT INTO order_items (order_id, media_id, issue_month, position_id, size_page, currency_id, gross_price, net_price, notes) "
                "SELECT @order_id, "
                f"{select_id_sql('media', 'name', item.media)}, "
                f"{sql(item.issue_month)}, "
                f"{select_id_sql('positions', 'name', item.position)}, "
                f"{sql(item.size_page)}, "
                "(SELECT id FROM currencies WHERE code = 'EUR' LIMIT 1), "
                f"{item.gross_price}, {item.net_price}, {sql(item.notes)} "
                "WHERE @existing_order_id IS NULL;"
            )
        lines.append("")

    lines.append("COMMIT;")
    lines.append("")
    return "\n".join(lines)


def run_mysql(sql_path: Path, args: argparse.Namespace) -> None:
    env = os.environ.copy()
    env["MYSQL_PWD"] = args.db_pass
    command = [
        "mysql",
        f"-u{args.db_user}",
        f"-h{args.db_host}",
        f"-P{args.db_port}",
        args.db_name,
    ]
    with sql_path.open("rb") as handle:
        subprocess.run(command, stdin=handle, env=env, check=True)


def main() -> None:
    parser = argparse.ArgumentParser(description="Import the latest 3 years of Excel ad sales into MySQL.")
    parser.add_argument("--xlsx", type=Path, default=DEFAULT_XLSX)
    parser.add_argument("--apply", action="store_true", help="Apply generated SQL to MySQL.")
    parser.add_argument("--out", type=Path, default=ROOT / "database" / "import_last3_sales.sql")
    parser.add_argument("--db-host", default=os.getenv("DB_HOST", "127.0.0.1"))
    parser.add_argument("--db-port", default=os.getenv("DB_PORT", "3306"))
    parser.add_argument("--db-name", default=os.getenv("DB_NAME", "ad_sales"))
    parser.add_argument("--db-user", default=os.getenv("DB_USER", "root"))
    parser.add_argument("--db-pass", default=os.getenv("DB_PASS", "123456"))
    args = parser.parse_args()

    items = load_items(args.xlsx)
    groups = group_items(items)
    sql_text = build_sql(items, groups)
    args.out.write_text(sql_text, encoding="utf-8")

    fallback_count = sum(1 for item in items if item.issue_fallback)
    media_count = len({item.media for item in items})
    customer_count = len({item.customer for item in items})
    position_count = len({item.position for item in items})

    print(f"Workbook: {args.xlsx}")
    print(f"Sheets: {', '.join(TARGET_SHEETS)}")
    print(f"Items parsed: {len(items)}")
    print(f"Order groups: {len(groups)}")
    print(f"Customers: {customer_count}, media: {media_count}, positions: {position_count}")
    print(f"Issue fallbacks to January: {fallback_count}")
    print(f"SQL written: {args.out}")

    if args.apply:
        run_mysql(args.out, args)
        print("Import applied.")
    else:
        print("Dry run only. Re-run with --apply to write to MySQL.")


if __name__ == "__main__":
    main()