"""SQLite 資料層"""
import sqlite3
import os
from config import DB_PATH


def get_connection() -> sqlite3.Connection:
    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA journal_mode=WAL")
    return conn


def init_db():
    conn = get_connection()
    conn.executescript("""
        CREATE TABLE IF NOT EXISTS projects (
            id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            region TEXT,
            district TEXT,
            address TEXT,
            unit_price_min REAL,
            unit_price_max REAL,
            total_price_min REAL,
            total_price_max REAL,
            build_type TEXT,
            house_type TEXT,
            developer TEXT,
            sales_company TEXT,
            total_units INTEGER,
            area_min REAL,
            area_max REAL,
            rooms TEXT,
            completion_date TEXT,
            status TEXT,
            url TEXT,
            first_seen DATE,
            last_updated DATETIME,
            raw_json TEXT
        );

        CREATE TABLE IF NOT EXISTS crawl_log (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            crawl_date DATETIME,
            region TEXT,
            total_found INTEGER,
            new_projects INTEGER,
            updated_projects INTEGER,
            duration_seconds REAL
        );

        CREATE INDEX IF NOT EXISTS idx_projects_region ON projects(region);
        CREATE INDEX IF NOT EXISTS idx_projects_sales ON projects(sales_company);
        CREATE INDEX IF NOT EXISTS idx_projects_status ON projects(status);
    """)
    # 擴充欄位（v2：建築規劃 + 建商資料 + 列表 API 額外欄位）
    _migrate_v2(conn)
    conn.close()


# 需要新增的欄位（ALTER TABLE ADD COLUMN 不報錯如果已存在）
_V2_COLUMNS = [
    # 建築規劃
    ("common_ratio", "TEXT"),        # 公設比
    ("site_area", "TEXT"),           # 基地面積
    ("coverage_ratio", "TEXT"),      # 建蔽率
    ("mgmt_fee", "TEXT"),            # 管理費
    ("parking_ratio", "TEXT"),       # 車位配比
    ("parking_plan", "TEXT"),        # 車位規劃
    ("floor_plan", "TEXT"),          # 樓層規劃
    ("ev_charging", "TEXT"),         # 充電設備
    # 建商資料
    ("constructor", "TEXT"),         # 營造公司
    ("architect", "TEXT"),           # 建築設計
    ("building_permit", "TEXT"),     # 建造執照
    ("usage_permit", "TEXT"),        # 使用執照
    # 聯絡 & 熱度
    ("phone", "TEXT"),              # 電話
    ("life_circle", "TEXT"),        # 生活圈
    ("tags", "TEXT"),               # 標籤（JSON array）
    ("cover_image", "TEXT"),        # 封面圖 URL
    ("browse_count", "INTEGER"),    # 瀏覽數
    ("call_count", "INTEGER"),      # 電話諮詢數
    # 詳情補完標記
    ("detail_scraped", "INTEGER DEFAULT 0"),  # 是否已爬詳情頁
]


def _migrate_v2(conn: sqlite3.Connection):
    """安全地新增 v2 欄位（已存在則跳過）"""
    existing = {
        row[1] for row in conn.execute("PRAGMA table_info(projects)").fetchall()
    }
    for col_name, col_type in _V2_COLUMNS:
        if col_name not in existing:
            conn.execute(f"ALTER TABLE projects ADD COLUMN {col_name} {col_type}")
    conn.commit()


def upsert_project(conn: sqlite3.Connection, data: dict) -> bool:
    """插入或更新建案。回傳 True 表示新增，False 表示更新。"""
    existing = conn.execute(
        "SELECT id FROM projects WHERE id = ?", (data["id"],)
    ).fetchone()

    if existing:
        conn.execute("""
            UPDATE projects SET
                name=?, region=?, district=?, address=?,
                unit_price_min=?, unit_price_max=?,
                total_price_min=?, total_price_max=?,
                build_type=?, house_type=?,
                developer=?, sales_company=?,
                total_units=?, area_min=?, area_max=?,
                rooms=?, completion_date=?, status=?, url=?,
                last_updated=datetime('now'),
                raw_json=?
            WHERE id=?
        """, (
            data.get("name"), data.get("region"), data.get("district"),
            data.get("address"),
            data.get("unit_price_min"), data.get("unit_price_max"),
            data.get("total_price_min"), data.get("total_price_max"),
            data.get("build_type"), data.get("house_type"),
            data.get("developer"), data.get("sales_company"),
            data.get("total_units"), data.get("area_min"), data.get("area_max"),
            data.get("rooms"), data.get("completion_date"),
            data.get("status"), data.get("url"),
            data.get("raw_json"),
            data["id"],
        ))
        return False
    else:
        conn.execute("""
            INSERT INTO projects (
                id, name, region, district, address,
                unit_price_min, unit_price_max,
                total_price_min, total_price_max,
                build_type, house_type,
                developer, sales_company,
                total_units, area_min, area_max,
                rooms, completion_date, status, url,
                first_seen, last_updated, raw_json
            ) VALUES (
                ?, ?, ?, ?, ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?, ?,
                ?, ?, ?,
                ?, ?, ?, ?,
                date('now'), datetime('now'), ?
            )
        """, (
            data["id"], data.get("name"), data.get("region"),
            data.get("district"), data.get("address"),
            data.get("unit_price_min"), data.get("unit_price_max"),
            data.get("total_price_min"), data.get("total_price_max"),
            data.get("build_type"), data.get("house_type"),
            data.get("developer"), data.get("sales_company"),
            data.get("total_units"), data.get("area_min"), data.get("area_max"),
            data.get("rooms"), data.get("completion_date"),
            data.get("status"), data.get("url"),
            data.get("raw_json"),
        ))
        return True


def log_crawl(conn: sqlite3.Connection, region: str, total: int,
              new: int, updated: int, duration: float):
    conn.execute("""
        INSERT INTO crawl_log (crawl_date, region, total_found,
                               new_projects, updated_projects, duration_seconds)
        VALUES (datetime('now'), ?, ?, ?, ?, ?)
    """, (region, total, new, updated, duration))


def get_existing_ids(conn: sqlite3.Connection) -> set:
    rows = conn.execute("SELECT id FROM projects").fetchall()
    return {r["id"] for r in rows}


def get_projects_without_detail(conn: sqlite3.Connection, limit: int = 0) -> list:
    """取得尚未爬過詳情頁的建案 ID。"""
    sql = "SELECT id FROM projects WHERE detail_scraped = 0 OR detail_scraped IS NULL"
    if limit > 0:
        sql += f" LIMIT {limit}"
    return [r["id"] for r in conn.execute(sql).fetchall()]