import logging, socket, traceback, requests
from celery import shared_task, current_task
from django.conf import settings
from django.utils import timezone
from django.utils.dateparse import parse_datetime
from django.db import transaction
from django.db.models import Sum

from esi.models import Token
from esi.errors import TokenInvalidError
from eveuniverse.models import EveRegion

from .models import (
    MarketCharacter,
    MarketTrackingConfig,
    MarketOrderSnapshot,
    TrackedItem,
    TrackedStructure,
    Delivery,
    ContractSnapshot,
    ContractDelivery,
    TrackedContract,
    ContractError,
)
from .discord import send_items_alert, send_contracts_alert, items_restocked_alert, contracts_restocked_alert
from .utils import contract_matches, fetch_contract_items, db_log

logger = logging.getLogger(__name__)
ESI_BASE_URL = "https://esi.evetech.net/latest"

HOSTNAME = socket.gethostname()

def _ctx(extra=None):
    data = {
        "host": HOSTNAME,
        "task_id": getattr(getattr(current_task, "request", None), "id", None),
    }
    if extra:
        data.update(extra)
    return data


def _location_name(config: MarketTrackingConfig) -> str:
    if not config:
        return "Unknown"
    if config.scope == "region":
        try:
            return EveRegion.objects.get(id=config.location_id).name
        except EveRegion.DoesNotExist:
            return str(config.location_id)
    else:
        try:
            return TrackedStructure.objects.get(structure_id=config.location_id).name
        except TrackedStructure.DoesNotExist:
            return str(config.location_id)


# ========== MARKET (ITEMS) ==========

@shared_task
def fetch_market_data_auto():
    mc = MarketCharacter.objects.first()
    if not mc:
        logger.warning("[MarketTracker] No MarketCharacter found for auto refresh.")
        return
    fetch_market_data(mc.character.character_id)


@shared_task
def fetch_market_data(character_id):
    db_log(source="items", event="start", message="fetch_market_data start", data=_ctx({"character_id": character_id,}))

    import re
    from django.db import connection

    config = MarketTrackingConfig.objects.first()
    if not config:
        db_log(level="WARN", source="items", event="no_config", message="No MarketTrackingConfig found", data=_ctx())
        return

    yellow_threshold = config.yellow_threshold
    red_threshold = config.red_threshold
    changed_statuses = []

    try:
        character = MarketCharacter.objects.get(character__character_id=character_id)
        admin_token = character.token
        admin_access_token = admin_token.valid_access_token()
    except MarketCharacter.DoesNotExist:
        db_log(level="WARN", source="items", event="no_market_character", message=f"No MarketCharacter found for character_id={character_id}", data=_ctx({"character_id": character_id}))
        return
    except Exception as e:
        db_log(level="ERROR", source="items", event="token_refresh_failed", message=str(e), data=_ctx({"character_id": character_id, "traceback": traceback.format_exc()}))
        return

    orig_table = MarketOrderSnapshot._meta.db_table            # np. "markettracker_marketordersnapshot"
    tmp_table = f"{orig_table}_tmp"                            # "markettracker_marketordersnapshot_tmp"
    old_table = f"{orig_table}_old"                            # "markettracker_marketordersnapshot_old"

    with connection.cursor() as cursor:
        try:
            cursor.execute(f"DROP TABLE IF EXISTS `{tmp_table}`;")
            cursor.execute(f"SHOW CREATE TABLE `{orig_table}`;")
            row = cursor.fetchone()
            if not row or len(row) < 2:
                logger.error("[MarketTracker] SHOW CREATE TABLE returned no result for %s", orig_table)
                return

            create_sql = row[1]

            create_tmp_sql = re.sub(
                r"^CREATE TABLE `[^`]+`",
                f"CREATE TABLE `{tmp_table}`",
                create_sql,
                count=1,
                flags=re.IGNORECASE | re.MULTILINE,
            )


            lines = create_tmp_sql.splitlines()
            lines_wo_fk = [ln for ln in lines if "FOREIGN KEY" not in ln]
            create_tmp_sql = "\n".join(lines_wo_fk)

   
            create_tmp_sql = re.sub(r",\s*\n\)", "\n)", create_tmp_sql)


            cursor.execute(create_tmp_sql)
            db_log(source="items", event="tmp_ready", data=_ctx({"tmp_table": tmp_table, "orig_table": orig_table,}))

        except Exception as e:
            logger.exception("[MarketTracker] Failed to prepare temporary table %s: %s", tmp_table, e)
            try:
                cursor.execute(f"DROP TABLE IF EXISTS `{tmp_table}`;")
            except Exception:
                pass
            return

    original_db_table = MarketOrderSnapshot._meta.db_table
    MarketOrderSnapshot._meta.db_table = tmp_table

    try:
        MarketOrderSnapshot.objects.all().delete()

        if config.scope == "region":
            seen_orders = _fetch_region_orders(config.location_id)
        else:
            seen_orders = _fetch_structure_orders(config.location_id, admin_access_token)

        db_log(source="items", event="orders_imported", data=_ctx({
            "tmp_table": tmp_table,
            "seen_orders": len(seen_orders),
            "scope": config.scope,
            "location_id": config.location_id,
        }))

        tracked_count = TrackedItem.objects.count()
        db_log(source="items", event="status_calc_start", data=_ctx({
            "tracked_items": tracked_count,
            "yellow_threshold": yellow_threshold,
            "red_threshold": red_threshold,
        }))

        for item in TrackedItem.objects.all():
            orders = MarketOrderSnapshot.objects.filter(tracked_item=item)
            total_volume = orders.aggregate(Sum("volume_remain"))["volume_remain__sum"] or 0
            desired = item.desired_quantity or 1
            percentage = int((total_volume / desired) * 100) if desired > 0 else 100

            if percentage <= red_threshold:
                new_status = "RED"
            elif percentage <= yellow_threshold:
                new_status = "YELLOW"
            else:
                new_status = "OK"

            old_status = item.last_status

            if new_status != old_status:
                changed_statuses.append(
                    (item, old_status, new_status, percentage, total_volume, desired)
                )
                item.last_status = new_status
                item.save(update_fields=["last_status"])

        db_log(source="items", event="status_calc_done", data=_ctx({
            "tracked_items": tracked_count,
            "changed": len(changed_statuses),
        }))


        _update_deliveries(config)

        if changed_statuses:
            db_log(source="items", event="alerts", data=_ctx({
                "changed": len(changed_statuses),
                "location": _location_name(config),
            }))
            send_items_alert(changed_statuses, _location_name(config))
            items_restocked_alert(changed_statuses, _location_name(config))
        else:
            db_log(source="items", event="no_changes", data=_ctx({
                "location": _location_name(config),
            }))

        with connection.cursor() as cursor:
            cursor.execute(f"DROP TABLE IF EXISTS `{old_table}`;")

            rename_sql = (
                f"RENAME TABLE "
                f"`{orig_table}` TO `{old_table}`, "
                f"`{tmp_table}` TO `{orig_table}`;"
            )
            cursor.execute(rename_sql)
            db_log(source="items", event="swap_done", message="Atomic swap done", data=_ctx({
                "orig_table": orig_table,
                "tmp_table": tmp_table,
                "old_table": old_table,
            }))


        with connection.cursor() as cursor:
            cursor.execute(f"DROP TABLE IF EXISTS `{old_table}`;")
            logger.info("[MarketTracker] Dropped old snapshot table: %s", old_table)

    except Exception as e:
        db_log(level="ERROR", source="items", event="exception", message=str(e), data=_ctx({
           "traceback": traceback.format_exc(),
           "tmp_table": tmp_table,
           "orig_table": orig_table,
       }))

        try:
            with connection.cursor() as cursor:
                cursor.execute(f"DROP TABLE IF EXISTS `{tmp_table}`;")
        except Exception:
            pass
        return

    finally:
        db_log(source="items", event="end", message="fetch_market_data end", data=_ctx())
        MarketOrderSnapshot._meta.db_table = original_db_table



def _fetch_region_orders(region_id):
    seen_orders = set()
    for tracked in TrackedItem.objects.all():
        type_id = tracked.item.id
        page = 1
        while True:
            url = f"{ESI_BASE_URL}/markets/{region_id}/orders/"
            params = {"order_type": "sell", "type_id": type_id, "page": page}
            headers = {"User-Agent": getattr(settings, "ESI_USER_AGENT", "MarketTracker/1.0")}
            try:
                resp = requests.get(url, params=params, headers=headers, timeout=10)
                resp.raise_for_status()
            except Exception as e:
                db_log(level="ERROR", source="items", event="esi_region_error", message=str(e), data=_ctx({
                    "region_id": region_id,
                    "type_id": type_id,
                    "item_name": tracked.item.name,
                    "page": page,
                    "url": url,
                }))
                break
            data = resp.json()
            pages = int(resp.headers.get("X-Pages", 1))
            seen_orders.update(_save_orders(data, tracked, region_id))
            if page >= pages:
                break
            page += 1
    return seen_orders


def _fetch_structure_orders(structure_id, access_token):
    seen_orders = set()
    for tracked in TrackedItem.objects.all():
        type_id = tracked.item.id
        page = 1
        while True:
            url = f"{ESI_BASE_URL}/markets/structures/{structure_id}/"
            params = {"page": page}
            headers = {
                "User-Agent": getattr(settings, "ESI_USER_AGENT", "MarketTracker/1.0"),
                "Authorization": f"Bearer {access_token}",
            }
            try:
                resp = requests.get(url, params=params, headers=headers, timeout=10)
                resp.raise_for_status()
            except Exception as e:
                db_log(level="ERROR", source="items", event="esi_structure_error", message=str(e), data=_ctx({
                    "structure_id": structure_id,
                    "type_id": type_id,
                    "item_name": tracked.item.name,
                    "page": page,
                    "url": url,
                }))
                break

            data = resp.json()
            pages = int(resp.headers.get("X-Pages", 1))
            filtered = [o for o in data if o.get("type_id") == type_id and not o.get("is_buy_order", False)]
            seen_orders.update(_save_orders(filtered, tracked, structure_id))
            if page >= pages:
                break
            page += 1
    return seen_orders


def _save_orders(orders, tracked_item, location_id):
    from django.utils import timezone as _tz
    seen_ids = set()
    for order in orders:
        if not isinstance(order, dict) or "type_id" not in order or "order_id" not in order:
            continue
        MarketOrderSnapshot.objects.update_or_create(
            order_id=order["order_id"],
            defaults={
                "tracked_item": tracked_item,
                "structure_id": location_id,
                "price": order.get("price", 0),
                "volume_remain": order.get("volume_remain", 0),
                "is_buy_order": order.get("is_buy_order", False),
                "issued": order.get("issued", _tz.now().isoformat()),
            },
        )
        seen_ids.add(order["order_id"])
    return seen_ids


def _update_deliveries(config):
    deliveries = Delivery.objects.filter(status="PENDING")

    raw_tokens = [
        t for t in Token.objects.select_related("user").all()
        if t.scopes.filter(name="esi-markets.read_character_orders.v1").exists()
    ]

    valid_tokens = []
    for token in raw_tokens:
        try:
            access_token = token.valid_access_token()
            valid_tokens.append((token, access_token))
        except TokenInvalidError:
            logger.warning(
                "[MarketTracker] Skipping invalid token for char %s (id=%s)",
                token.character_id,
                token.id,
            )
        except Exception:
            logger.exception(
                "[MarketTracker] Token refresh failed for char %s (id=%s)",
                token.character_id,
                token.id,
            )

    if not valid_tokens:
        logger.warning("[MarketTracker] No valid tokens available for deliveries update.")
        return

    for delivery in deliveries:
        total_delivered = 0
        for token, access_token in valid_tokens:
            try:
                orders = _fetch_character_orders(token.character_id, access_token, config)
                filtered = []
                for o in orders:
                    if "issued" not in o or "type_id" not in o:
                        continue
                    issued_dt = parse_datetime(o["issued"])
                    if issued_dt:
                        issued_dt = issued_dt.astimezone(timezone.utc)
                    if (
                        issued_dt
                        and issued_dt >= delivery.created_at
                        and o["type_id"] == delivery.item.id
                        and not o.get("is_buy_order", False)
                    ):
                        filtered.append(o)
                delivered_from_orders = sum(o["volume_remain"] for o in filtered)
                total_delivered += delivered_from_orders
            except Exception:
                logger.exception(
                    "[MarketTracker] Orders fetch failed for char %s", token.character_id
                )

        delivery.delivered_quantity = min(total_delivered, delivery.declared_quantity)
        if delivery.delivered_quantity >= delivery.declared_quantity:
            delivery.status = "FINISHED"
        delivery.save(update_fields=["delivered_quantity", "status"])

def _fetch_character_orders(character_id, access_token, config):
    url = f"{ESI_BASE_URL}/characters/{character_id}/orders/"
    headers = {
        "Authorization": f"Bearer {access_token}",
        "User-Agent": getattr(settings, "ESI_USER_AGENT", "MarketTracker/1.0"),
    }
    resp = requests.get(url, headers=headers, timeout=10)
    resp.raise_for_status()
    orders = resp.json()
    if config.scope == "region":
        return [o for o in orders if o.get("region_id") == config.location_id]
    return [o for o in orders if o.get("location_id") == config.location_id]


# ========== CONTRACTS ==========

@shared_task
def refresh_contracts():
    """
    Atomic snapshot refresh for contracts.
    """
    db_log(source="contracts", event="start", message="refresh_contracts start", data=_ctx())

    from django.db import connection

    orig = ContractSnapshot._meta.db_table
    tmp = f"{orig}_tmp"
    old = f"{orig}_old"

    with connection.cursor() as cursor:
        cursor.execute(f"DROP TABLE IF EXISTS `{tmp}`;")
        cursor.execute(f"CREATE TABLE `{tmp}` LIKE `{orig}`;")
        logger.info("[Contracts] Temporary snapshot table created: %s", tmp)

    original_name = ContractSnapshot._meta.db_table
    ContractSnapshot._meta.db_table = tmp
    try:
        ContractSnapshot.objects.all().delete()
        logger.info("[Contracts] Purged old snapshots in tmp table")


        raw_tokens = [
            t for t in Token.objects.select_related("user").all()
            if t.scopes.filter(name="esi-contracts.read_character_contracts.v1").exists()
        ]

        valid_tokens = []
        for token in raw_tokens:
            try:
                token.valid_access_token()
                valid_tokens.append(token)
            except TokenInvalidError:
                logger.warning(
                    "[MarketTracker] Skipping invalid contracts token for char %s (id=%s)",
                    token.character_id,
                    token.id,
                )
            except Exception:
                logger.exception(
                    "[MarketTracker] Token refresh failed for char %s (id=%s)",
                    token.character_id,
                    token.id,
                )
        if not valid_tokens:
            logger.warning("[MarketTracker] No valid tokens available for contracts refresh.")
            return


        seen = fetch_contracts_snapshots(ContractSnapshot, valid_tokens)
        db_log(source="contracts", event="fetched", data=_ctx({"contracts_seen": seen}))



        all_contracts = list(ContractSnapshot.objects.all())

        cfg = MarketTrackingConfig.objects.first()
        yellow = cfg.yellow_threshold if cfg else 50
        red = cfg.red_threshold if cfg else 25


        summary = _recalculate_contract_statuses_and_alert(all_contracts, yellow, red)
        db_log(source="contracts", event="recalc_done", data=_ctx(summary or {}))

        _update_contract_deliveries(all_contracts)

        # Atomic swap
        with connection.cursor() as cursor:
            cursor.execute(f"DROP TABLE IF EXISTS `{old}`;")
            cursor.execute(
                f"RENAME TABLE `{orig}` TO `{old}`, `{tmp}` TO `{orig}`;"
            )
            logger.info("[Contracts] Atomic snapshot swap done")
            cursor.execute(f"DROP TABLE IF EXISTS `{old}`;")
        db_log(source="contracts", event="swap_done", message="Atomic snapshot swap done", data=_ctx())



    except Exception as e:
        logger.exception("[Contracts] Error during contracts refresh: %s", e)
        db_log(
            level="ERROR",
            source="contracts",
            event="exception",
            message=str(e),
            data=_ctx({"traceback": traceback.format_exc()}),
        )
        raise

    finally:
        ContractSnapshot._meta.db_table = original_name


def fetch_contracts_snapshots(tmp_model, mc):
    """
    Download all contracts for all available tokens
    """
    import requests
    from django.utils import timezone

    seen = 0

    for char in mc:
        # --- token / character_id ---
        # char może być MarketCharacter ALBO Token
        token = getattr(char, "token", char)

        try:
            access = token.valid_access_token()
            char_id = token.character_id
            if not isinstance(char_id, int) or char_id < 10:
                logger.warning(
                    "[Contracts] Skipped invalid character_id=%s for token %s",
                    char_id,
                    token.pk,
                )
                continue
        except Exception as e:
            logger.warning("[Contracts] Token refresh failed for %s: %s", char, e)
            continue

        page = 1
        while True:
            url = f"{ESI_BASE_URL}/characters/{char_id}/contracts/"
            params = {"page": page}
            headers = {
                "User-Agent": getattr(settings, "ESI_USER_AGENT", "MarketTracker/1.0"),
                "Authorization": f"Bearer {access}",
            }

            try:
                resp = requests.get(url, params=params, headers=headers, timeout=10)
                resp.raise_for_status()
            except Exception as e:
                logger.error(
                    "[Contracts] Fetch failed for char %s page %s: %s",
                    char_id,
                    page,
                    e,
                )
                break

            data = resp.json() or []
            pages = int(resp.headers.get("X-Pages", 1) or 1)

            for c in data:
                status = (c.get("status") or "").lower()
                if status != "outstanding":
                    continue

                contract_id = c.get("contract_id")
                if not contract_id:
                    continue

                try:
                    tmp_model.objects.update_or_create(
                        contract_id=contract_id,
                        defaults={
                            "issuer_id": c.get("issuer_id"),
                            "acceptor_id": c.get("acceptor_id"),
                            "title": c.get("title") or "",
                            "price": c.get("price") or 0,
                            "reward": c.get("reward") or 0,
                            "volume": c.get("volume") or 0,
                            "type": c.get("type"),
                            "availability": c.get("availability"),
                            "status": c.get("status"),
                            "date_issued": c.get("date_issued") or timezone.now(),
                            "date_expired": c.get("date_expired"),
                            "date_completed": c.get("date_completed"),
                            "start_location_id": c.get("start_location_id"),
                            "end_location_id": c.get("end_location_id"),
                            "collateral": c.get("collateral") or 0,
                            "for_corporation": c.get("for_corporation") or False,
                            "assignee_id": c.get("assignee_id"),
                            "issuer_corporation_id": c.get("issuer_corporation_id"),
                            "owner_character_id": char_id,
                        },
                    )
                    seen += 1
                except Exception as e:
                    logger.error(
                        "[Contracts] Could not save contract %s: %s",
                        contract_id,
                        e,
                    )

            if page >= pages:
                break
            page += 1

    logger.info("[MarketTracker] Contracts fetched/updated: %s", seen)
    return seen



def _recalculate_contract_statuses_and_alert(all_contracts, yellow, red):
    """
    Recalculate status of contracts
    """
    db_log(source="contracts", event="recalc_start", data=_ctx({
        "tracked_count": TrackedContract.objects.count(),
        "snapshot_count": ContractSnapshot.objects.count(),
    }))


    changed = []
    characters = list(MarketCharacter.objects.all())

    def get_access_token():
        for c in characters:
            try:
                return c.token.valid_access_token(), c.character.character_id
            except Exception:
                continue
        return None, None

    access_token, _ = get_access_token()

    for tc in TrackedContract.objects.select_related("fitting").all():
        matched = []

        base_contracts = all_contracts

        if tc.mode == TrackedContract.Mode.CUSTOM:
            tf = (tc.title_filter or "").lower()
            if tf:
                base_contracts = [
                    c for c in all_contracts
                    if tf in (c.title or "").lower()
                ]

        elif tc.mode == TrackedContract.Mode.DOCTRINE:
            base_contracts = [
                c for c in all_contracts
                if (c.type or "").lower() == "item_exchange"
                and (c.status or "").lower() == "outstanding"
            ]

        for c in base_contracts:
            if tc.mode == TrackedContract.Mode.DOCTRINE and access_token:
                owner_id = c.owner_character_id or 0
                fetch_contract_items(c, access_token, owner_id)

            ok, _ = contract_matches(tc, c)
            if ok:
                matched.append(c)

        current = len(matched)
        desired = tc.desired_quantity or 0

        if desired <= 0:
            percent = 100
            new = "OK"
        else:
            percent = int((current / desired) * 100)
            if percent <= red:
                new = "RED"
            elif percent <= yellow:
                new = "YELLOW"
            else:
                new = "OK"

       



        old = tc.last_status

        if old != new:
            tc.last_status = new
            tc.save(update_fields=["last_status"])
            if tc.mode == TrackedContract.Mode.DOCTRINE and tc.fitting:
                name = tc.fitting.name
            else:
                name = tc.title_filter or "—"

            prices = [float(m.price) for m in matched if getattr(m, "price", None)]
            min_price = min(prices) if prices else None

            changed.append({
                "name": name,
                "status": new,
                "old_status": old,
                "current": current,
                "desired": desired,
                "percent": percent,
                "min_price": min_price,
            })

        db_log(source="contracts", event="alerts_summary", data=_ctx({
            "changed": len(changed),
        }))

    if changed:
        suspicious_all_zero = all(
            c["desired"] > 0
            and c["current"] == 0
            and c["status"] == "RED"
            and c["old_status"] != "RED"
            for c in changed
        )

        if suspicious_all_zero:
            db_log(level="WARN", source="contracts", event="suspicious_all_zero", message="Suspicious all-zero snapshot detected; skipping alerts", data=_ctx({"changed": len(changed)}))

            for c in changed:
                TrackedContract.objects.filter(pk=c["tc_id"]).update(
                    last_status=c["old_status"] or "OK"
                )
            return
        send_contracts_alert(changed)
        contracts_restocked_alert(changed)

    return {"changed": len(changed)}





def _update_contract_deliveries(all_contracts):
    """
    Delivery auto completion
    """
    deliveries = ContractDelivery.objects.select_related("tracked_contract").filter(
        status="PENDING"
    )

    characters = list(MarketCharacter.objects.all())

    def get_access_token():
        for c in characters:
            try:
                return c.token.valid_access_token(), c.character.character_id
            except Exception:
                continue
        return None, None

    access_token, _ = get_access_token()

    for d in deliveries:
        tc = d.tracked_contract
        matched = 0

        base = all_contracts

        if tc.mode == TrackedContract.Mode.CUSTOM:
            tf = (tc.title_filter or "").lower()
            if tf:
                base = [c for c in all_contracts if tf in (c.title or "").lower()]

        elif tc.mode == TrackedContract.Mode.DOCTRINE:
            base = [
                c for c in all_contracts
                if (c.type or "").lower() == "item_exchange"
                and (c.status or "").lower() == "outstanding"
            ]

        for c in base:
            if c.date_issued and c.date_issued < d.created_at:
                continue

            if tc.mode == TrackedContract.Mode.DOCTRINE and access_token:
                owner_id = c.owner_character_id or 0
                fetch_contract_items(c, access_token, owner_id)

            ok, _ = contract_matches(tc, c)
            if ok:
                matched += 1

        d.delivered_quantity = min(matched, d.declared_quantity)
        if d.delivered_quantity >= d.declared_quantity:
            d.status = "FINISHED"
        d.save(update_fields=["delivered_quantity", "status"])


