"""
Standalone script: sync local MySQL mkt_day_end_data -> remote FastAPI /project_data_init_mde

Reads from local MySQL in id-based batches (efficient for ~1.3M rows) and POSTs each
batch to the remote API. Skipped rows: un_close / un_volume / ad_close / ad_volume == 0
(also enforced on the API side).

Usage:
  python sync.py
  python sync.py --batch-size 2000 --start-id 0
  python sync.py --resume   # continue from .sync_mde_checkpoint

Before running, set MYSQL_* and API_BASE_URL below (or via environment variables).
"""

from __future__ import annotations

import argparse
import json
import os
import sys
import time
from datetime import date, datetime
from decimal import Decimal
from typing import Any

import requests

try:
    import pymysql
    from pymysql.cursors import DictCursor
except ImportError:
    print("Missing dependency: pip install pymysql")
    sys.exit(1)


# ---------------------------------------------------------------------------
# Config (override with env vars)
# ---------------------------------------------------------------------------
MYSQL_HOST = os.getenv("MYSQL_HOST", "127.0.0.1")
MYSQL_PORT = int(os.getenv("MYSQL_PORT", "3306"))
MYSQL_USER = os.getenv("MYSQL_USER", "root")
MYSQL_PASSWORD = os.getenv("MYSQL_PASSWORD", "")
MYSQL_DATABASE = os.getenv("MYSQL_DATABASE", "fi_api")
MYSQL_TABLE = os.getenv("MYSQL_TABLE", "mkt_day_end_data")

API_BASE_URL = os.getenv("API_BASE_URL", "https://fintralvar-252844350293.asia-southeast1.run.app").rstrip("/")
API_ENDPOINT = f"{API_BASE_URL}/project_data_init_mde"

# Keep batches modest: large writes hold SQLite lock and cause "database is locked"
# on concurrent /get-lvar-data MSI writes.
DEFAULT_BATCH_SIZE = int(os.getenv("SYNC_BATCH_SIZE", "20000"))
REQUEST_TIMEOUT = int(os.getenv("SYNC_TIMEOUT", "120"))
MAX_RETRIES = int(os.getenv("SYNC_MAX_RETRIES", "3"))
RETRY_DELAY_SEC = float(os.getenv("SYNC_RETRY_DELAY", "5"))

CHECKPOINT_FILE = os.path.join(os.path.dirname(os.path.abspath(__file__)), ".sync_mde_checkpoint")

SELECT_COLUMNS = (
    "id, date, security_code, "
    "un_open, un_high, un_low, un_close, un_volume, total_value, "
    "ad_open, ad_high, ad_low, ad_close, ad_volume, adst_ratio_value, "
    "pct_change, pct_change_with_dsex, create_at, update_at"
)

PAYLOAD_KEYS = (
    "date",
    "security_code",
    "un_open",
    "un_high",
    "un_low",
    "un_close",
    "un_volume",
    "total_value",
    "ad_open",
    "ad_high",
    "ad_low",
    "ad_close",
    "ad_volume",
    "adst_ratio_value",
    "pct_change",
    "pct_change_with_dsex",
    "create_at",
    "update_at",
)


def json_safe(value: Any) -> Any:
    if value is None:
        return None
    if isinstance(value, datetime):
        return value.strftime("%Y-%m-%d %H:%M:%S")
    if isinstance(value, date):
        return value.strftime("%Y-%m-%d")
    if isinstance(value, Decimal):
        return float(value)
    return value


def row_to_payload(row: dict) -> dict | None:
    if (
        row.get("un_close") == 0
        or row.get("un_volume") == 0
        or row.get("ad_close") == 0
        or row.get("ad_volume") == 0
    ):
        return None
    return {key: json_safe(row.get(key)) for key in PAYLOAD_KEYS}


def get_mysql_connection():
    return pymysql.connect(
        host=MYSQL_HOST,
        port=MYSQL_PORT,
        user=MYSQL_USER,
        password=MYSQL_PASSWORD,
        database=MYSQL_DATABASE,
        charset="utf8mb4",
        cursorclass=DictCursor,
        connect_timeout=30,
        read_timeout=120,
    )


def get_total_eligible_count(conn) -> int:
    sql = f"""
        SELECT COUNT(*) AS cnt
        FROM `{MYSQL_TABLE}`
        WHERE un_close != 0
          AND un_volume != 0
          AND ad_close != 0
          AND ad_volume != 0
    """
    with conn.cursor() as cursor:
        cursor.execute(sql)
        return int(cursor.fetchone()["cnt"])


def fetch_batch(conn, after_id: int, batch_size: int) -> list[dict]:
    sql = f"""
        SELECT {SELECT_COLUMNS}
        FROM `{MYSQL_TABLE}`
        WHERE id > %s
          AND un_close != 0
          AND un_volume != 0
          AND ad_close != 0
          AND ad_volume != 0
        ORDER BY id ASC
        LIMIT %s
    """
    with conn.cursor() as cursor:
        cursor.execute(sql, (after_id, batch_size))
        return list(cursor.fetchall())


def post_batch(payload_data: list[dict]) -> dict:
    body = {
        "status": 200,
        "message": "mkt day end sync batch",
        "data": payload_data,
    }
    last_error = None
    for attempt in range(1, MAX_RETRIES + 1):
        try:
            response = requests.post(
                API_ENDPOINT,
                json=body,
                timeout=REQUEST_TIMEOUT,
                headers={"Content-Type": "application/json"},
            )
            response.raise_for_status()
            try:
                return response.json()
            except ValueError:
                return {"message": response.text, "http_status": response.status_code}
        except requests.RequestException as exc:
            last_error = exc
            print(f"  Attempt {attempt}/{MAX_RETRIES} failed: {exc}")
            if attempt < MAX_RETRIES:
                time.sleep(RETRY_DELAY_SEC * attempt)
    raise RuntimeError(f"Failed to POST batch after {MAX_RETRIES} retries: {last_error}")


def load_checkpoint() -> int:
    if not os.path.exists(CHECKPOINT_FILE):
        return 0
    try:
        with open(CHECKPOINT_FILE, "r", encoding="utf-8") as f:
            data = json.load(f)
        return int(data.get("last_id", 0))
    except (OSError, ValueError, json.JSONDecodeError):
        return 0


def save_checkpoint(last_id: int, synced_count: int) -> None:
    with open(CHECKPOINT_FILE, "w", encoding="utf-8") as f:
        json.dump(
            {
                "last_id": last_id,
                "synced_count": synced_count,
                "updated_at": datetime.now().isoformat(timespec="seconds"),
            },
            f,
            indent=2,
        )


def parse_args():
    parser = argparse.ArgumentParser(description="Sync MySQL mkt_day_end_data to remote API")
    parser.add_argument(
        "--batch-size",
        type=int,
        default=DEFAULT_BATCH_SIZE,
        help=f"Rows per API request (default: {DEFAULT_BATCH_SIZE})",
    )
    parser.add_argument(
        "--start-id",
        type=int,
        default=0,
        help="Start after this MySQL id (default: 0)",
    )
    parser.add_argument(
        "--resume",
        action="store_true",
        help=f"Resume from checkpoint file ({CHECKPOINT_FILE})",
    )
    parser.add_argument(
        "--dry-run",
        action="store_true",
        help="Fetch and print batch sizes only; do not POST",
    )
    return parser.parse_args()


def main():
    args = parse_args()

    if "YOUR_REMOTE_SERVER" in API_BASE_URL and not args.dry_run:
        print("Set API_BASE_URL to your remote FastAPI host before running.")
        print("Example: set API_BASE_URL=http://203.x.x.x:8000")
        sys.exit(1)

    after_id = load_checkpoint() if args.resume else args.start_id
    batch_size = max(1, args.batch_size)

    print(f"MySQL: {MYSQL_USER}@{MYSQL_HOST}:{MYSQL_PORT}/{MYSQL_DATABASE}.{MYSQL_TABLE}")
    print(f"API:   {API_ENDPOINT}")
    print(f"Batch: {batch_size} | start after id: {after_id} | dry_run={args.dry_run}")
    print("-" * 60)

    conn = get_mysql_connection()
    try:
        total_eligible = get_total_eligible_count(conn)
        print(f"Eligible rows (non-zero OHLC volumes): {total_eligible:,}")

        synced = 0
        batch_no = 0
        started = time.time()

        while True:
            rows = fetch_batch(conn, after_id, batch_size)
            if not rows:
                break

            batch_no += 1
            last_id = int(rows[-1]["id"])
            payload = []
            for row in rows:
                item = row_to_payload(row)
                if item is not None:
                    payload.append(item)

            print(
                f"Batch {batch_no}: ids {rows[0]['id']}..{last_id} "
                f"({len(rows)} fetched, {len(payload)} to send)"
            )

            if payload and not args.dry_run:
                result = post_batch(payload)
                print(f"  -> {result}")

            synced += len(payload)
            after_id = last_id
            save_checkpoint(after_id, synced)

            elapsed = time.time() - started
            rate = synced / elapsed if elapsed > 0 else 0
            print(f"  Progress: {synced:,} sent | {rate:,.0f} rows/sec | last_id={after_id}")

        elapsed = time.time() - started
        print("-" * 60)
        print(f"Done. Sent {synced:,} rows in {batch_no} batches ({elapsed:.1f}s).")
        print(f"Checkpoint saved: last_id={after_id} -> {CHECKPOINT_FILE}")
    finally:
        conn.close()


if __name__ == "__main__":
    main()
