src/database.py

"""SPV 都更專案資料庫模組 - Database Module for Urban Renewal SPV Management.

This module handles all database operations including table creation,
data loading, and CRUD operations for the urban renewal project management system.
"""

import datetime
import os
import shutil
import sqlite3
from typing import Any, Optional

import pandas as pd

DB_PATH = "spv_management.db"


def get_connection() -> sqlite3.Connection:
    """Get a database connection with row factory enabled.

    Returns:
        sqlite3.Connection: Database connection object.
    """
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn


def init_db() -> None:
    """Initialize the database with all required tables.

    Creates tables for:
    - Finance transactions (財務交易)
    - Project parameters (專案參數)
    - Project stages (工程階段)
    - Loan contracts (融資合約)
    - Inventory (銷售庫存)
    - Landowners (地主管理) - NEW
    - Milestones (里程碑) - NEW
    - Risk events (風險事件) - NEW
    - Scenarios (情境模擬) - NEW
    """
    conn = get_connection()
    c = conn.cursor()

    # 核心財務表：交易紀錄制，支援無限擴充自訂科目
    c.execute("""
        CREATE TABLE IF NOT EXISTS finance_transactions (
            月份 INTEGER,
            類型 TEXT,
            科目名稱 TEXT,
            金額 REAL,
            科目性質 TEXT,
            PRIMARY KEY (月份, 類型, 科目名稱)
        )
    """)

    # 專案參數
    c.execute("""
        CREATE TABLE IF NOT EXISTS project_params (
            參數名稱 TEXT PRIMARY KEY,
            數值 REAL
        )
    """)

    # 工程階段
    c.execute("""
        CREATE TABLE IF NOT EXISTS project_stages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            工項名稱 TEXT,
            開始月份 INTEGER,
            持續月份 INTEGER,
            成本佔比 REAL,
            撥款佔比 REAL,
            預算金額 REAL DEFAULT 0,
            實際支出 REAL DEFAULT 0
        )
    """)

    # 融資合約
    c.execute("""
        CREATE TABLE IF NOT EXISTS loan_contracts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            貸款名稱 TEXT,
            授信額度 REAL,
            年利率 REAL,
            狀態 TEXT,
            動撥金額 REAL DEFAULT 0,
            到期日 DATE,
            備註 TEXT
        )
    """)

    # 銷售庫存
    c.execute("""
        CREATE TABLE IF NOT EXISTS inventory (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            物件名稱 TEXT,
            預計售價 REAL,
            總數量 INTEGER,
            地主分回數量 INTEGER,
            已售數量 INTEGER DEFAULT 0,
            銷售狀態 TEXT
        )
    """)

    # 地主管理 (NEW)
    c.execute("""
        CREATE TABLE IF NOT EXISTS landowners (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            姓名 TEXT NOT NULL,
            持分比例 REAL,
            分回坪數 REAL,
            分回戶型 TEXT,
            同意書狀態 TEXT DEFAULT '待聯繫',
            聯絡電話 TEXT,
            地址 TEXT,
            備註 TEXT,
            最後聯繫日期 DATE
        )
    """)

    # 里程碑 (NEW)
    c.execute("""
        CREATE TABLE IF NOT EXISTS milestones (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            里程碑名稱 TEXT NOT NULL,
            類型 TEXT,
            預計日期 DATE,
            實際日期 DATE,
            狀態 TEXT DEFAULT '待進行',
            負責單位 TEXT,
            備註 TEXT
        )
    """)

    # 風險事件 (NEW)
    c.execute("""
        CREATE TABLE IF NOT EXISTS risk_events (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            風險類型 TEXT,
            描述 TEXT,
            影響金額 REAL,
            發生機率 TEXT,
            影響程度 TEXT,
            緩解措施 TEXT,
            狀態 TEXT DEFAULT '監控中',
            建立日期 DATE DEFAULT CURRENT_DATE
        )
    """)

    # 情境模擬 (NEW)
    c.execute("""
        CREATE TABLE IF NOT EXISTS scenarios (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            情境名稱 TEXT NOT NULL,
            銷售延遲月數 INTEGER DEFAULT 0,
            成本增幅比例 REAL DEFAULT 0,
            利率調整 REAL DEFAULT 0,
            市場價格調整 REAL DEFAULT 0,
            備註 TEXT
        )
    """)

    # 土地取得成本 (NEW - 含合作與收購)
    c.execute("""
        CREATE TABLE IF NOT EXISTS land_costs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            地主姓名 TEXT,
            取得方式 TEXT,
            土地面積坪 REAL,
            權利價值萬 REAL,
            收購價格萬 REAL DEFAULT 0,
            付款狀態 TEXT DEFAULT '待付款',
            備註 TEXT
        )
    """)

    # 專案元數據 (NEW - 儲存日期等非數值設定)
    c.execute("""
        CREATE TABLE IF NOT EXISTS project_metadata (
            key TEXT PRIMARY KEY,
            value TEXT
        )
    """)

    conn.commit()
    conn.close()


def get_metadata(key: str, default: str = "") -> str:
    """Get a metadata value by key.

    Args:
        key: Metadata key.
        default: Default value if key not found.

    Returns:
        str: Metadata value.
    """
    conn = get_connection()
    c = conn.cursor()
    c.execute("SELECT value FROM project_metadata WHERE key = ?", (key,))
    row = c.fetchone()
    conn.close()
    return row["value"] if row else default


def set_metadata(key: str, value: str) -> None:
    """Set a metadata value.

    Args:
        key: Metadata key.
        value: Metadata value.
    """
    conn = get_connection()
    c = conn.cursor()
    c.execute("INSERT OR REPLACE INTO project_metadata VALUES (?, ?)", (key, str(value)))
    conn.commit()
    conn.close()


def clear_to_actual() -> None:
    """Clear all data and reset to blank project state."""
    conn = get_connection()
    c = conn.cursor()

    tables = [
        "finance_transactions",
        "project_params",
        "project_stages",
        "loan_contracts",
        "inventory",
        "landowners",
        "milestones",
        "risk_events",
        "scenarios",
        "land_costs",
        "project_metadata",
    ]

    for tbl in tables:
        c.execute(f"DELETE FROM {tbl}")  # noqa: S608

    # 設定預設參數
    c.execute("INSERT INTO project_params VALUES (?, ?)", ("地主分回比例", 0.60))
    c.execute("INSERT INTO project_params VALUES (?, ?)", ("總工程預算", 100000))

    conn.commit()
    conn.close()


def load_full_project_example(total_budget: float) -> None:
    """Load comprehensive example data for demonstration.

    Args:
        total_budget: Total project budget in 萬 (10,000 TWD).
    """
    conn = get_connection()
    c = conn.cursor()
    clear_to_actual()

    # 更新預算參數
    c.execute(
        "INSERT OR REPLACE INTO project_params VALUES (?, ?)",
        ("總工程預算", total_budget),
    )

    # ========== 1. 工程階段 (含設計成本) ==========
    design_cost = total_budget * 0.03  # 設計費約 3%
    stages = [
        ("規劃設計", -6, 6, 3.0, design_cost),  # 專案啟動前 6 個月開始
        ("拆遷補償", 0, 4, 10.0, total_budget * 0.10),
        ("結構工程", 12, 18, 70.0, total_budget * 0.70),
        ("機電工程", 24, 8, 8.0, total_budget * 0.08),
        ("裝修交屋", 30, 6, 9.0, total_budget * 0.09),
    ]
    for s in stages:
        c.execute(
            """INSERT INTO project_stages
            (工項名稱, 開始月份, 持續月份, 成本佔比, 撥款佔比, 預算金額, 實際支出)
            VALUES (?,?,?,?,?,?,?)""",
            (s[0], s[1], s[2], s[3], s[3], s[4], s[4] * 0.95),
        )

    # ========== 2. 融資合約 (含土地融資) ==========
    # 土地融資 - 專案啟動前就發生
    land_loan = 5000.0  # 土地收購融資 5000 萬
    c.execute(
        """INSERT INTO loan_contracts
        (貸款名稱, 授信額度, 年利率, 狀態, 動撥金額, 到期日, 備註)
        VALUES (?,?,?,?,?,?,?)""",
        ("土地融資", land_loan, 2.8, "已動撥", land_loan, "2027-12-31", "收購土地用"),
    )

    # 土建融資
    construction_loan = total_budget * 0.7
    c.execute(
        """INSERT INTO loan_contracts
        (貸款名稱, 授信額度, 年利率, 狀態, 動撥金額, 到期日, 備註)
        VALUES (?,?,?,?,?,?,?)""",
        ("都更土建融", construction_loan, 2.3, "已動撥", construction_loan * 0.8, "2029-12-31", "建築工程融資"),
    )

    # ========== 3. 銷售庫存 ==========
    c.execute(
        """INSERT INTO inventory
        (物件名稱, 預計售價, 總數量, 地主分回數量, 已售數量, 銷售狀態)
        VALUES (?,?,?,?,?,?)""",
        ("都更住宅", 3000.0, 100, 60, 15, "銷售中"),
    )
    c.execute(
        """INSERT INTO inventory
        (物件名稱, 預計售價, 總數量, 地主分回數量, 已售數量, 銷售狀態)
        VALUES (?,?,?,?,?,?)""",
        ("車位", 350.0, 80, 48, 10, "銷售中"),
    )

    # ========== 4. 地主資料 (含釘子戶) ==========
    landowners_data = [
        ("王大明", 15.5, 45.0, "3房", "已簽署", "0912-345-678"),
        ("李美玲", 12.0, 36.0, "2房", "已簽署", "0923-456-789"),
        ("張志強", 8.5, 25.5, "2房", "協商中", "0934-567-890"),
        ("陳淑芬", 10.0, 30.0, "2房", "已簽署", "0945-678-901"),
        ("林建宏", 6.0, 18.0, "1房", "已簽署", "0956-789-012"),
        ("黃雅婷", 8.0, 24.0, "2房", "已簽署", "0967-890-123"),
        # 釘子戶 - 持分小但開價高
        ("周先生", 1.0, 3.0, "套房", "拒絕中", "0978-901-234"),
    ]
    for ld in landowners_data:
        c.execute(
            """INSERT INTO landowners
            (姓名, 持分比例, 分回坪數, 分回戶型, 同意書狀態, 聯絡電話)
            VALUES (?,?,?,?,?,?)""",
            ld,
        )

    # ========== 5. 土地取得成本 (含收購與合作) ==========
    # 土地合作 (以土地參與)
    land_costs_data = [
        ("王大明", "土地合作", 50.0, 4500.0, 0, "已完成"),
        ("李美玲", "土地合作", 38.0, 3420.0, 0, "已完成"),
        ("張志強", "土地合作", 27.0, 2430.0, 0, "協商中"),
        # 收購案例
        ("陳淑芬", "收購", 32.0, 2880.0, 2880.0, "已付清"),
        ("林建宏", "收購", 19.0, 1710.0, 1710.0, "已付清"),
        # 釘子戶 - 市價 350萬/坪 × 3坪 = 1050萬，開價 2000萬
        ("周先生", "收購", 3.0, 1050.0, 0, "談判中"),  # 尚未成交
    ]
    for lc in land_costs_data:
        c.execute(
            """INSERT INTO land_costs
            (地主姓名, 取得方式, 土地面積坪, 權利價值萬, 收購價格萬, 付款狀態)
            VALUES (?,?,?,?,?,?)""",
            lc,
        )

    # ========== 6. 里程碑 ==========
    base_date = datetime.date(2026, 1, 20)
    milestones_data = [
        ("都更計畫核定", "政府審核", 30, "已完成"),
        ("事業計畫核定", "政府審核", 180, "進行中"),
        ("建照取得", "政府審核", 365, "待進行"),
        ("拆遷作業開始", "工程", 400, "待進行"),
        ("結構體完成", "工程", 800, "待進行"),
        ("使用執照取得", "政府審核", 1000, "待進行"),
        ("交屋作業", "銷售", 1100, "待進行"),
    ]
    for md in milestones_data:
        target_date = base_date + datetime.timedelta(days=md[2])
        actual_date = target_date if md[3] == "已完成" else None
        c.execute(
            """INSERT INTO milestones
            (里程碑名稱, 類型, 預計日期, 實際日期, 狀態)
            VALUES (?,?,?,?,?)""",
            (md[0], md[1], target_date.isoformat(), actual_date, md[3]),
        )

    # ========== 7. 風險事件 (含釘子戶風險) ==========
    risks_data = [
        ("成本風險", "營造原物料價格上漲", total_budget * 0.05, "中", "高", "合約價格鎖定條款"),
        ("時程風險", "政府審核延遲", total_budget * 0.02, "中", "中", "提前送件、定期追蹤"),
        ("市場風險", "房市價格下跌", total_budget * 0.10, "低", "高", "分散銷售時程"),
        ("地主風險", "周先生收購談判 (開價2000萬 vs 市價1050萬)", 950.0, "高", "高", "評估替代方案或提高補償"),
        ("融資風險", "利率上升影響", total_budget * 0.03, "中", "中", "固定利率或利率避險"),
    ]
    for rd in risks_data:
        c.execute(
            """INSERT INTO risk_events
            (風險類型, 描述, 影響金額, 發生機率, 影響程度, 緩解措施)
            VALUES (?,?,?,?,?,?)""",
            rd,
        )

    # ========== 8. 預設情境 ==========
    scenarios_data = [
        ("基準情境", 0, 0, 0, 0),
        ("銷售延遲 6 個月", 6, 0, 0, 0),
        ("成本上漲 10%", 0, 10, 0, 0),
        ("利率上升 1%", 0, 0, 1.0, 0),
        ("悲觀情境", 6, 10, 1.0, -5),
        ("釘子戶接受開價", 0, 0, 0, 0),  # 需額外支出 950萬
    ]
    for sd in scenarios_data:
        c.execute(
            """INSERT INTO scenarios
            (情境名稱, 銷售延遲月數, 成本增幅比例, 利率調整, 市場價格調整)
            VALUES (?,?,?,?,?)""",
            sd,
        )

    # ========== 9. 現金流模擬 (含土地融資利息和設計費) ==========
    salable_val = (3000.0 * 100) * 0.4

    # 專案前期支出 (月份 -6 到 0)
    for m in range(-6, 1):
        # 設計費用
        if m >= -6 and m < 0:
            c.execute(
                "INSERT INTO finance_transactions VALUES (?, '實際', '規劃設計費', ?, '支出')",
                (m if m != 0 else 1, design_cost / 6),
            )
        # 土地融資利息 (專案前就開始)
        if m % 3 == 0:
            land_interest = land_loan * 0.028 / 4
            c.execute(
                "INSERT INTO finance_transactions VALUES (?, '實際', '土地融資利息', ?, '支出')",
                (m if m != 0 else 1, land_interest),
            )

    # 36 個月現金流
    for m in range(1, 37):
        # 工程支出
        cost_value = float(
            sum(
                (total_budget * s[3] / 100) / s[2]
                for s in stages
                if s[1] <= m - 1 < s[1] + s[2] and s[1] >= 0
            )
        )
        if cost_value > 0:
            c.execute(
                "INSERT INTO finance_transactions VALUES (?, '實際', '工程支出', ?, '支出')",
                (m, cost_value * 1.05),
            )

        # 每季土建融資利息
        if m % 3 == 0:
            c.execute(
                "INSERT INTO finance_transactions VALUES (?, '實際', '土建融資利息', ?, '支出')",
                (m, construction_loan * 0.023 / 4),
            )

        # 第 24 個月開始預售收入
        if m >= 24 and m % 4 == 0:
            presale_income = salable_val * 0.15
            c.execute(
                "INSERT INTO finance_transactions VALUES (?, '實際', '預售訂金', ?, '收入')",
                (m, presale_income),
            )

        # 最後一個月結算
        if m == 36:
            c.execute(
                "INSERT INTO finance_transactions VALUES (?, '實際', '交屋尾款', ?, '收入')",
                (m, salable_val * 0.55),
            )
            c.execute(
                "INSERT INTO finance_transactions VALUES (?, '實際', '代銷與稅費', ?, '支出')",
                (m, salable_val * 0.06),
            )

    conn.commit()
    conn.close()


def backup_current_project() -> str:

    """Backup current database to timestamped file.

    Returns:
        str: Path to the backup file.
    """
    if os.path.exists(DB_PATH):
        ts = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
        backup_path = f"backup_{ts}.db"
        shutil.copy2(DB_PATH, backup_path)
        return backup_path
    return ""


def get_database_file() -> Optional[bytes]:
    """Read current database file as bytes.

    Returns:
        bytes: Database file content or None if file missing.
    """
    if os.path.exists(DB_PATH):
        with open(DB_PATH, "rb") as f:
            return f.read()
    return None


def restore_database_from_bytes(file_bytes: bytes) -> bool:
    """Restore database from bytes.

    Args:
        file_bytes: Database file content to restore.

    Returns:
        bool: True if successful.
    """
    try:
        # Create a backup before overwriting
        backup_current_project()
        
        with open(DB_PATH, "wb") as f:
            f.write(file_bytes)
        return True
    except Exception as e:
        print(f"Restore failed: {e}")
        return False


# ============ 查詢輔助函數 ============


def get_landowner_stats() -> dict[str, Any]:
    """Get landowner agreement statistics.

    Returns:
        dict: Statistics including total count, agreement rate, and status breakdown.
    """
    conn = get_connection()
    df = pd.read_sql_query("SELECT * FROM landowners", conn)
    conn.close()

    if df.empty:
        return {"total": 0, "agreement_rate": 0, "status_counts": {}}

    status_counts = df["同意書狀態"].value_counts().to_dict()
    agreed = status_counts.get("已簽署", 0)
    total = len(df)

    return {
        "total": total,
        "agreement_rate": (agreed / total * 100) if total > 0 else 0,
        "status_counts": status_counts,
        "total_share": df["持分比例"].sum(),
        "agreed_share": df[df["同意書狀態"] == "已簽署"]["持分比例"].sum(),
    }


def get_milestone_summary() -> dict[str, Any]:
    """Get milestone progress summary.

    Returns:
        dict: Summary including completed, in-progress, and upcoming milestones.
    """
    conn = get_connection()
    df = pd.read_sql_query("SELECT * FROM milestones ORDER BY 預計日期", conn)
    conn.close()

    if df.empty:
        return {"total": 0, "completed": 0, "in_progress": 0, "upcoming": 0}

    status_counts = df["狀態"].value_counts().to_dict()

    return {
        "total": len(df),
        "completed": status_counts.get("已完成", 0),
        "in_progress": status_counts.get("進行中", 0),
        "upcoming": status_counts.get("待進行", 0),
    }


def calculate_scenario_impact(
    scenario_id: int, base_profit: float, base_loan: float
) -> dict[str, float]:
    """Calculate financial impact of a scenario.

    Args:
        scenario_id: ID of the scenario to calculate.
        base_profit: Base profit amount.
        base_loan: Base loan amount.

    Returns:
        dict: Impact calculations including adjusted profit and additional costs.
    """
    conn = get_connection()
    scenario = pd.read_sql_query(
        f"SELECT * FROM scenarios WHERE id = {scenario_id}", conn  # noqa: S608
    )
    conn.close()

    if scenario.empty:
        return {"adjusted_profit": base_profit, "additional_cost": 0}

    s = scenario.iloc[0]

    # 計算影響
    delay_cost = s["銷售延遲月數"] * (base_loan * 0.023 / 12)  # 延遲的利息成本
    cost_increase = base_profit * (s["成本增幅比例"] / 100)
    rate_impact = base_loan * (s["利率調整"] / 100)
    market_impact = base_profit * (s["市場價格調整"] / 100)

    additional_cost = delay_cost + cost_increase + rate_impact
    adjusted_profit = base_profit - additional_cost + market_impact

    return {
        "adjusted_profit": adjusted_profit,
        "additional_cost": additional_cost,
        "delay_cost": delay_cost,
        "cost_increase": cost_increase,
        "rate_impact": rate_impact,
        "market_impact": market_impact,
    }


def get_budget_vs_actual() -> pd.DataFrame:
    """Get budget vs actual comparison for project stages.

    Returns:
        pd.DataFrame: Comparison data with variance calculations.
    """
    conn = get_connection()
    df = pd.read_sql_query(
        "SELECT 工項名稱, 預算金額, 實際支出 FROM project_stages", conn
    )
    conn.close()

    if not df.empty:
        df["差異"] = df["實際支出"] - df["預算金額"]
        df["差異率"] = ((df["差異"] / df["預算金額"]) * 100).round(1)

    return df