from __future__ import annotations

from fastapi import APIRouter, Depends
from findata_core.db import connection

from findata_api import auth as A
from findata_api.errors import ApiError

router = APIRouter(prefix="/catalog", tags=["catalog"])

LICENSE_TEXT = {
    "frd-private": "FirstRate Data license: private use by the subscriber only. Raw bars/tables may not be redistributed to third parties "
                   "(no public API, CSV or tables). Derived results and charts may be published with attribution to FirstRate Data.",
    "public-domain": "U.S. government work (SEC EDGAR): public domain; SEC fair-access policy applies to retrieval.",
    "pending": "License not yet determined.",
}


@router.get("/datasets")
def datasets(p: A.Principal = Depends(A.require("read:market"))):
    with connection() as conn:
        rows = conn.execute(
            "SELECT d.*, s.name source_name, s.vendor, s.product, v.snapshot_id, v.file_count, v.row_count, v.bytes, v.created_at version_created_at, "
            "(SELECT min(session_date) FROM coverage c WHERE c.dataset_id=d.id) first_session, (SELECT max(session_date) FROM coverage c WHERE c.dataset_id=d.id) last_session, "
            "(SELECT count(DISTINCT symbol) FROM coverage c WHERE c.dataset_id=d.id) symbols "
            "FROM datasets d JOIN sources s ON s.id=d.source_id LEFT JOIN dataset_versions v ON v.dataset_id=d.id AND v.version=d.current_version ORDER BY d.kind, d.id").fetchall()
    out = []
    for r in rows:
        r = dict(r)
        r["license_text"] = LICENSE_TEXT.get(r["license_class"], "")
        out.append(r)
    return {"data": out, "meta": {"count": len(out)}}


@router.get("/datasets/{dataset_id}")
def dataset(dataset_id: str, p: A.Principal = Depends(A.require("read:market"))):
    with connection() as conn:
        d = conn.execute("SELECT d.*, s.name source_name, s.vendor, s.license_class src_license FROM datasets d JOIN sources s ON s.id=d.source_id WHERE d.id=%s", (dataset_id,)).fetchone()
        if not d:
            raise ApiError(404, "not_found", "unknown dataset")
        versions = conn.execute("SELECT version, snapshot_id, file_count, row_count, bytes, created_at, note FROM dataset_versions WHERE dataset_id=%s ORDER BY version DESC LIMIT 50", (dataset_id,)).fetchall()
        files = conn.execute("SELECT layout, count(*) n, sum(row_count) rows, sum(bytes) bytes, min(date_min) d0, max(date_max) d1 FROM lake_files WHERE dataset_id=%s AND version_removed IS NULL GROUP BY layout", (dataset_id,)).fetchall()
        ck = conn.execute("SELECT key, value, updated_at FROM checkpoints WHERE scope=%s", (f"dataset:{dataset_id}",)).fetchall()
    d = dict(d)
    d["license_text"] = LICENSE_TEXT.get(d["license_class"], "")
    return {"data": d, "versions": versions, "files": files, "checkpoints": ck}


@router.get("/sources")
def sources(p: A.Principal = Depends(A.require("read:market"))):
    with connection() as conn:
        rows = conn.execute("SELECT * FROM sources ORDER BY id").fetchall()
    return {"data": rows}


@router.get("/schema/bars")
def schema_bars(p: A.Principal = Depends(A.require("read:market"))):
    from findata_schema import BARS_SCHEMA, BARS_SCHEMA_VERSION
    return {"schema_version": BARS_SCHEMA_VERSION, "timestamp_convention": "bar_open", "timezone_storage": "UTC",
            "fields": [{"name": f.name, "type": str(f.type), "nullable": f.nullable} for f in BARS_SCHEMA]}