#!/usr/bin/env python3
import json
import os
import ssl
import urllib.parse
import urllib.request
from datetime import datetime, time, timedelta, timezone
from zoneinfo import ZoneInfo

import pymysql


def env(name, default=None, required=False):
    value = os.environ.get(name, default)
    if required and (value is None or str(value).strip() == ""):
        raise RuntimeError(f"{name} is required")
    return value


def mysql_connect():
    database_url = env("TARGET_DATABASE_URL") or env("DATABASE_URL")
    if database_url:
        return mysql_connect_url(database_url)

    return pymysql.connect(
        host=env("TARGET_DB_HOST", required=True),
        port=int(env("TARGET_DB_PORT", "3306")),
        user=env("TARGET_DB_USER", required=True),
        password=env("TARGET_DB_PASSWORD", required=True),
        database=env("TARGET_DB_NAME", required=True),
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True,
        connect_timeout=10,
        read_timeout=60,
        write_timeout=60,
    )


def mysql_connect_url(database_url):
    parsed = urllib.parse.urlparse(database_url)
    if not parsed.hostname:
        raise RuntimeError("TARGET_DATABASE_URL must include a host")
    database = parsed.path.lstrip("/")
    if not database:
        raise RuntimeError("TARGET_DATABASE_URL must include a database name")

    ssl_ctx = ssl.create_default_context()
    ssl_ctx.minimum_version = ssl.TLSVersion.TLSv1_2
    ca_path = env("TARGET_TIDB_SSL_CA_PATH") or env("TIDB_SSL_CA_PATH")
    if ca_path:
        ssl_ctx.load_verify_locations(cafile=ca_path)

    return pymysql.connect(
        host=parsed.hostname,
        port=parsed.port or 4000,
        user=urllib.parse.unquote(parsed.username or ""),
        password=urllib.parse.unquote(parsed.password or ""),
        database=urllib.parse.unquote(database),
        charset="utf8mb4",
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=True,
        connect_timeout=10,
        read_timeout=60,
        write_timeout=60,
        ssl=ssl_ctx,
    )


def fetch_one(conn, sql, params=()):
    conn.ping(reconnect=True)
    with conn.cursor() as cur:
        cur.execute(sql, params)
        return cur.fetchone()


def fetch_weekly_metrics(conn):
    week_start_utc, week_end_utc = previous_complete_cst_week()
    week_start_cst = week_start_utc.replace(tzinfo=timezone.utc).astimezone(
        ZoneInfo("Asia/Shanghai")
    ).date()
    week_end_cst = week_end_utc.replace(tzinfo=timezone.utc).astimezone(
        ZoneInfo("Asia/Shanghai")
    ).date()
    return fetch_one(
        conn,
        """
        WITH scoped_tenants AS (
          SELECT
            a.*,
            CASE
              WHEN a.source_type = 'active_candidate'
               AND a.status = 'active'
               AND a.kind = 'external'
               AND a.sales_force_region = 'APAC'
               AND a.country NOT IN ('JP')
               AND a.plan NOT IN ('free_trial', 'new_user', 'closing')
              THEN 1 ELSE 0
            END AS is_apac_stock
          FROM apac_active_tenants a
        )
        SELECT
          %s AS week_start_cst,
          %s AS week_end_cst,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
            THEN 1 END) AS apac_stock_total,
          COUNT(CASE
            WHEN a.country_region = 'APAC'
             AND a.country NOT IN ('JP')
             AND a.created_at >= %s
             AND a.created_at < %s
            THEN 1 END) AS weekly_signup_total,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
             AND a.priority = 'P0'
            THEN 1 END) AS active_p0_total,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
             AND a.priority = 'P1'
            THEN 1 END) AS active_p1_total,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
             AND a.priority IN ('P0', 'P1')
            THEN 1 END) AS active_p0_p1_total,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
             AND a.track_step = 2
            THEN 1 END) AS connected_total,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
             AND a.has_credit_card = 1
            THEN 1 END) AS paid_total,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
             AND a.priority IN ('P0', 'P1')
             AND a.track_step = 2
            THEN 1 END) AS active_p0_p1_connected_total,
          COUNT(CASE
            WHEN a.is_apac_stock = 1
             AND a.priority IN ('P0', 'P1')
             AND a.has_credit_card = 1
            THEN 1 END) AS active_p0_p1_paid_total
        FROM scoped_tenants a
        """,
        (week_start_cst, week_end_cst, week_start_utc, week_end_utc),
    )


def previous_complete_cst_week(now=None):
    now = now or datetime.now(timezone.utc)
    if now.tzinfo is None:
        now = now.replace(tzinfo=timezone.utc)
    cst = ZoneInfo("Asia/Shanghai")
    today = now.astimezone(cst).date()
    days_since_sunday = (today.weekday() + 1) % 7
    current_week_sunday = today - timedelta(days=days_since_sunday)
    previous_week_sunday = current_week_sunday - timedelta(days=7)
    return (
        datetime.combine(previous_week_sunday, time.min, cst)
        .astimezone(timezone.utc)
        .replace(tzinfo=None),
        datetime.combine(current_week_sunday, time.min, cst)
        .astimezone(timezone.utc)
        .replace(tzinfo=None),
    )


def int_value(value):
    try:
        return int(value or 0)
    except (TypeError, ValueError):
        return 0


def date_value(value):
    if value is None:
        return "-"
    if hasattr(value, "strftime"):
        return value.strftime("%Y-%m-%d")
    text = str(value)
    if len(text) >= 10 and text[4:5] == "-" and text[7:8] == "-":
        return text[:10]
    return text


def feishu_text(text):
    return {"tag": "plain_text", "content": text}


def feishu_md(text):
    return {"tag": "lark_md", "content": text}


def build_card(metrics):
    shanghai_today = datetime.now(ZoneInfo("Asia/Shanghai")).strftime("%Y-%m-%d")
    week_start = date_value(metrics.get("week_start_cst"))
    week_end = date_value(metrics.get("week_end_cst"))

    return {
        "msg_type": "interactive",
        "card": {
            "config": {"wide_screen_mode": True},
            "header": {
                "template": "blue",
                "title": feishu_text(f"APAC Weekly Tenant Metrics - {shanghai_today}"),
            },
            "elements": [
                {
                    "tag": "div",
                    "text": feishu_md(
                        f"**APAC Stock Tenants**\n"
                        f"- active_candidate: **{int_value(metrics.get('apac_stock_total'))}**\n\n"
                        f"**Active P0 + P1 Tenants**\n"
                        f"- Total: **{int_value(metrics.get('active_p0_p1_total'))}** "
                        f"(P0: **{int_value(metrics.get('active_p0_total'))}**, "
                        f"P1: **{int_value(metrics.get('active_p1_total'))}**)\n"
                        f"- Connected: **{int_value(metrics.get('active_p0_p1_connected_total'))}**\n"
                        f"- Paid: **{int_value(metrics.get('active_p0_p1_paid_total'))}**\n\n"
                        f"**Connected Tenants**\n"
                        f"- track_step=2: **{int_value(metrics.get('connected_total'))}**\n\n"
                        f"**Paid Tenants**\n"
                        f"- has_credit_card=1: **{int_value(metrics.get('paid_total'))}**\n\n"
                        f"**Previous Week New Signups**\n"
                        f"- New signups: **{int_value(metrics.get('weekly_signup_total'))}**\n"
                        f"- Signup window: CST **{week_start} 00:00** to **{week_end} 00:00** "
                        f"(previous complete Sunday-to-Saturday week)"
                    ),
                }
            ],
        },
    }


def send_feishu_card(card):
    webhook_url = env("FEISHU_WEBHOOK_URL", required=True)
    req = urllib.request.Request(
        webhook_url,
        data=json.dumps(card, ensure_ascii=False).encode("utf-8"),
        headers={"Content-Type": "application/json"},
        method="POST",
    )
    with urllib.request.urlopen(req, timeout=20) as resp:
        body = resp.read().decode("utf-8")
    result = json.loads(body)
    if result.get("code") not in (0, None):
        raise RuntimeError(f"Feishu webhook failed: {body}")
    return result


def lambda_handler(event, context):
    started = datetime.now(timezone.utc)
    conn = mysql_connect()
    try:
        metrics = fetch_weekly_metrics(conn)
    finally:
        conn.close()

    send_feishu_card(build_card(metrics))
    return {
        "metrics": {key: str(value) for key, value in metrics.items()},
        "elapsed_seconds": round((datetime.now(timezone.utc) - started).total_seconds(), 3),
    }