# -*- coding: utf-8 -*-
"""趋势数据库 - SQLite，存储每日价格用于趋势汇报"""

from __future__ import annotations

import json
import os
import sqlite3
from datetime import datetime, timedelta


SCHEMA_SQL = """
CREATE TABLE IF NOT EXISTS daily_prices (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    trade_date TEXT NOT NULL,
    board_id INTEGER NOT NULL,
    board_type TEXT,
    topic_id INTEGER NOT NULL,
    topic_title TEXT,
    topic_url TEXT,
    author_name TEXT,
    name TEXT NOT NULL,
    ref_price TEXT,
    price_details TEXT,
    unit TEXT,
    quantity TEXT,
    quantity_unit TEXT,
    package_req TEXT,
    quality_req TEXT,
    other_notes TEXT,
    raw_text TEXT,
    fetched_at TEXT NOT NULL,
    UNIQUE(trade_date, board_id, topic_id, name, raw_text)
);
CREATE INDEX IF NOT EXISTS idx_dp_date_name ON daily_prices(trade_date, name);
CREATE INDEX IF NOT EXISTS idx_dp_name ON daily_prices(name);

CREATE TABLE IF NOT EXISTS crawl_runs (
    trade_date TEXT NOT NULL,
    board_id INTEGER NOT NULL,
    status TEXT,
    topic_count INTEGER DEFAULT 0,
    matched_count INTEGER DEFAULT 0,
    run_at TEXT NOT NULL,
    PRIMARY KEY(trade_date, board_id)
);

CREATE TABLE IF NOT EXISTS raw_posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    trade_date TEXT NOT NULL,
    board_id INTEGER NOT NULL,
    topic_id INTEGER NOT NULL,
    title TEXT,
    url TEXT,
    author_name TEXT,
    post_text TEXT,
    fetched_at TEXT NOT NULL,
    UNIQUE(trade_date, board_id, topic_id)
);
"""


class Database:
    def __init__(self, db_path: str) -> None:
        os.makedirs(os.path.dirname(db_path), exist_ok=True)
        self.conn = sqlite3.connect(db_path)
        self.conn.row_factory = sqlite3.Row
        self.conn.execute("PRAGMA journal_mode=WAL")

    def init_schema(self) -> None:
        self.conn.executescript(SCHEMA_SQL)
        self.conn.commit()

    def is_board_crawled(self, trade_date: str, board_id: int) -> bool:
        row = self.conn.execute(
            "SELECT status FROM crawl_runs WHERE trade_date=? AND board_id=?",
            (trade_date, board_id),
        ).fetchone()
        return row is not None and row["status"] == "done"

    def save_price_record(self, rec: dict) -> None:
        self.conn.execute(
            """INSERT OR IGNORE INTO daily_prices
               (trade_date, board_id, board_type, topic_id, topic_title, topic_url,
                author_name, name, ref_price, price_details, unit, quantity,
                quantity_unit, package_req, quality_req, other_notes, raw_text, fetched_at)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (
                rec["trade_date"], rec["board_id"], rec.get("board_type"),
                rec["topic_id"], rec.get("topic_title"), rec.get("topic_url"),
                rec.get("author_name"), rec["name"], rec.get("ref_price"),
                json.dumps(rec.get("price_details") or {}, ensure_ascii=False),
                rec.get("unit"), rec.get("quantity"), rec.get("quantity_unit"),
                rec.get("package_req"), rec.get("quality_req"),
                rec.get("other_notes"), rec.get("raw_text"),
                rec.get("fetched_at", datetime.now().isoformat()),
            ),
        )
        self.conn.commit()

    def save_raw_post(self, rec: dict) -> None:
        self.conn.execute(
            """INSERT OR IGNORE INTO raw_posts
               (trade_date, board_id, topic_id, title, url, author_name, post_text, fetched_at)
               VALUES (?,?,?,?,?,?,?,?)""",
            (
                rec["trade_date"], rec["board_id"], rec["topic_id"],
                rec.get("title"), rec.get("url"), rec.get("author_name"),
                rec.get("post_text"),
                rec.get("fetched_at", datetime.now().isoformat()),
            ),
        )
        self.conn.commit()

    def record_crawl_run(self, trade_date: str, board_id: int, status: str,
                         topic_count: int = 0, matched_count: int = 0) -> None:
        self.conn.execute(
            """INSERT OR REPLACE INTO crawl_runs
               (trade_date, board_id, status, topic_count, matched_count, run_at)
               VALUES (?,?,?,?,?,?)""",
            (trade_date, board_id, status, topic_count, matched_count,
             datetime.now().isoformat()),
        )
        self.conn.commit()

    def fetch_prices_by_date(self, trade_date: str) -> list[sqlite3.Row]:
        return self.conn.execute(
            "SELECT * FROM daily_prices WHERE trade_date=? ORDER BY board_id, name",
            (trade_date,),
        ).fetchall()

    def fetch_coin_history(self, name: str, days: int = 30) -> list[sqlite3.Row]:
        cutoff = (datetime.now() - timedelta(days=days)).strftime("%Y-%m-%d")
        return self.conn.execute(
            """SELECT * FROM daily_prices
               WHERE name=? AND trade_date>=?
               ORDER BY trade_date ASC""",
            (name, cutoff),
        ).fetchall()

    def fetch_all_coin_names(self) -> list[str]:
        rows = self.conn.execute(
            "SELECT DISTINCT name FROM daily_prices WHERE name != '' ORDER BY name"
        ).fetchall()
        return [r["name"] for r in rows if r["name"]]

    def list_trade_dates(self) -> list[str]:
        rows = self.conn.execute(
            "SELECT DISTINCT trade_date FROM daily_prices ORDER BY trade_date DESC"
        ).fetchall()
        return [r["trade_date"] for r in rows]

    def close(self) -> None:
        self.conn.commit()
        self.conn.close()