import importlib
import sqlite3

import pytest
from fastapi.testclient import TestClient

from app.lvar import database
from app.main import app
from tests.conftest import mde_row, msi_row

LVAR_ROUTES = {
    ("/lvar/get-last-entry", ("GET",)),
    ("/lvar/get-lvar-data", ("POST",)),
    ("/lvar/get-mkt-day-end-data/filter={filter_type}/{filter_value}", ("GET",)),
    ("/lvar/get-mkt-security-data", ("GET",)),
    ("/lvar/project_init/{project_name}/{password}", ("POST",)),
    ("/lvar/project_delete/{project_name}/{password}", ("POST",)),
    ("/lvar/project_data_init_mde", ("POST",)),
    ("/lvar/project_data_init_msi", ("POST",)),
    ("/lvar/rename-security-code/{old_code}/{new_code}", ("PUT",)),
}
OCR_ROUTES = {
    ("/ocr/health", ("GET",)),
    ("/ocr/companies", ("GET",)),
    ("/ocr/companies/catalog", ("GET",)),
    ("/ocr/coverage", ("GET",)),
    ("/ocr/company/{code}/status", ("GET",)),
    ("/ocr/upload", ("POST",)),
    ("/ocr/company/{code}/year/{fy}/pages", ("GET",)),
    ("/ocr/company/{code}/year/{fy}/pdf", ("GET",)),
    ("/ocr/register-document", ("POST",)),
    ("/ocr/company/{code}/year/{fy}/verify", ("POST",)),
    ("/ocr/pipeline/ocr-client-pages", ("POST",)),
    ("/ocr/pipeline/ocr", ("POST",)),
    ("/ocr/pipeline/extract", ("POST",)),
    ("/ocr/company/{code}/usage-report", ("GET",)),
    ("/ocr/company/{code}/year/{fy}/usage-report", ("GET",)),
    ("/ocr/company/{code}/verify-extraction", ("GET",)),
    ("/ocr/company/{code}/extracted-cells", ("GET",)),
    ("/ocr/company/{code}/note-cells", ("GET",)),
    ("/ocr/company/{code}/unidentified-lines", ("GET",)),
    ("/ocr/company/{code}/line-overrides", ("GET", "POST")),
    ("/ocr/company/{code}/line-overrides/{override_id}", ("DELETE",)),
    ("/ocr/pipeline/merge", ("POST",)),
    ("/ocr/pipeline/merge/stream", ("POST",)),
    ("/ocr/company/{code}/results/raw", ("GET",)),
    ("/ocr/company/{code}/results/merged", ("GET",)),
    ("/ocr/company/{code}/results/merged/row/{series_id}", ("GET",)),
    ("/ocr/company/{code}/results/merged/conflicts", ("GET",)),
    ("/ocr/company/{code}/results/merged/report", ("GET",)),
    ("/ocr/company/{code}/download/raw", ("GET",)),
    ("/ocr/company/{code}/download/raw.xlsx", ("GET",)),
    ("/ocr/company/{code}/download/merged", ("GET",)),
    ("/ocr/company/{code}/download/merged.xlsx", ("GET",)),
    ("/ocr/company/{code}/download/standard.xlsx", ("GET",)),
    ("/ocr/company/{code}/results/standard", ("GET",)),
    ("/ocr/company/{code}/series/{series_id}/active", ("PUT",)),
    ("/ocr/company/{code}/series-active", ("PUT",)),
    ("/ocr/company/{code}/enter-disclosure", ("DELETE", "POST")),
    ("/ocr/company/{code}/cell-current", ("DELETE", "PUT")),
    ("/ocr/sync-ocr-md", ("GET", "POST")),
    ("/ocr/ocr-md-files", ("GET",)),
    ("/ocr/company/{code}/cell-details", ("GET",)),
    ("/ocr/files/{file_path}", ("GET",)),
    ("/ocr/company/{code}/year/{fy}/page/{page_number}/markdown", ("GET",)),
    ("/ocr/jobs/ocr", ("POST",)),
    ("/ocr/jobs/ocr-client-pages", ("POST",)),
    ("/ocr/jobs/extract", ("POST",)),
    ("/ocr/jobs/merge", ("POST",)),
    ("/ocr/jobs", ("GET",)),
    ("/ocr/jobs/{job_id}", ("DELETE", "GET")),
    ("/ocr/jobs/{job_id}/events", ("GET",)),
    ("/ocr/events", ("GET",)),
    ("/ocr/company/{code}/series", ("GET",)),
    ("/ocr/company/{code}/series/{series_id}/evidence", ("GET",)),
    ("/ocr/company/{code}/series-formation", ("GET",)),
    ("/ocr/company/{code}/year/{fy}/compositions", ("GET",)),
    ("/ocr/company/{code}/merge/review", ("GET",)),
    ("/ocr/company/{code}/merge/review/{item_id}/resolve", ("POST",)),
    ("/ocr/company/{code}/merge/overrides", ("GET",)),
    ("/ocr/company/{code}/merge/override", ("POST",)),
    ("/ocr/company/{code}/merge/override/{override_id}", ("DELETE",)),
    ("/ocr/company/{code}/merge/override/{override_id}/decide", ("POST",)),
    ("/ocr/company/{code}/cell-by-position", ("GET",)),
}
ORIGINAL_ROUTES = {("/", ("GET",))} | LVAR_ROUTES | OCR_ROUTES


def test_route_table_unchanged():
    # Included routers are not flattened into app.routes, so read the OpenAPI schema.
    routes = {
        (path, tuple(sorted(m.upper() for m in ops)))
        for path, ops in app.openapi()["paths"].items()
    }
    assert routes == ORIGINAL_ROUTES


# ---------------------------------------------------------------- auth
def test_open_when_api_key_unset(client):
    assert client.get("/lvar/get-mkt-security-data").status_code in (200, 500)
    assert client.get("/").json() == {"message": "Hello World"}


def test_api_key_required_when_set(client, monkeypatch):
    monkeypatch.setenv("API_KEY", "k3y")
    assert client.get("/").status_code == 200  # root stays open
    protected = [
        ("get", "/lvar/get-last-entry"),
        ("get", "/lvar/get-mkt-security-data"),
        ("get", "/lvar/get-mkt-day-end-data/filter=limit/5"),
        ("post", "/lvar/project_init/a/b"),
        ("post", "/lvar/project_delete/a/b"),
        ("put", "/lvar/rename-security-code/A/B"),
    ]
    for method, path in protected:
        assert getattr(client, method)(path).status_code == 401, path
        r = getattr(client, method)(path, headers={"X-API-Key": "wrong"})
        assert r.status_code == 401, path
    for path in ("/lvar/project_data_init_mde", "/lvar/project_data_init_msi", "/lvar/get-lvar-data"):
        assert client.post(path, json={}).status_code == 401
    ok = client.get("/lvar/get-mkt-security-data", headers={"X-API-Key": "k3y"})
    assert ok.status_code != 401


def test_api_key_valid_reaches_handler(client, monkeypatch):
    monkeypatch.setenv("API_KEY", "k3y")
    monkeypatch.setenv("PROJECT_ADMIN_NAME", "admin")
    monkeypatch.setenv("PROJECT_ADMIN_PASSWORD", "pw")
    r = client.post("/lvar/project_init/admin/pw", headers={"X-API-Key": "k3y"})
    assert r.json() == {"message": "Project initialized successfully", "project_name": "admin"}


def test_authorization_header_not_accepted(client, monkeypatch):
    monkeypatch.setenv("API_KEY", "k3y")
    for auth in ("Bearer k3y", "Bearer wrong", "Basic k3y", "k3y"):
        r = client.get("/lvar/get-last-entry", headers={"Authorization": auth})
        assert r.status_code == 401, auth
        assert r.json() == {"detail": "Not authenticated"}


# ---------------------------------------------------------------- admin creds
def test_creds_fail_closed_when_env_unset(client):
    for path in ("/lvar/project_init/rt01/123456", "/lvar/project_delete/rt01/123456", "/lvar/project_init/ /x"):
        r = client.post(path)
        assert r.status_code == 200
        assert r.json() == {"message": "Project not found or invalid credentials"}


def test_old_hardcoded_creds_rejected(client, monkeypatch):
    monkeypatch.setenv("PROJECT_ADMIN_NAME", "admin")
    monkeypatch.setenv("PROJECT_ADMIN_PASSWORD", "pw")
    r = client.post("/lvar/project_init/rt01/123456")
    assert r.json() == {"message": "Project not found or invalid credentials"}
    r = client.post("/lvar/project_init/admin/bad")
    assert r.json() == {"message": "Project not found or invalid credentials"}


def test_project_delete_gated(db, monkeypatch):
    r = db.post("/lvar/project_delete/admin/s3cret")
    assert r.json() == {"message": "Project deletion failed", "project_name": "admin"}
    monkeypatch.setenv("ALLOW_PROJECT_DELETE", "true")
    r = db.post("/lvar/project_delete/admin/s3cret")
    assert r.json() == {"message": "Project deleted successfully", "project_name": "admin"}


# ---------------------------------------------------------------- data happy paths
def test_mde_and_msi_roundtrip(db):
    r = db.post("/lvar/project_data_init_mde", json={
        "status": 200, "message": "x",
        "data": [mde_row("AAA"), mde_row("BBB"), mde_row("CCC", un_close=0)],
    })
    assert r.json() == {"message": "Project data initialized successfully", "data_count": 2}
    r = db.post("/lvar/project_data_init_msi", json={
        "status": 200, "message": "x", "data": [msi_row("AAA")],
    })
    assert r.json() == {
        "message": "Project security data initialized successfully", "data_count": 1}

    r = db.get("/lvar/get-mkt-day-end-data/filter=security_code/AAA").json()
    assert r["status"] == 200 and r["records_count"] == 1
    r = db.get("/lvar/get-mkt-security-data").json()
    assert r["records_count"] == 1
    r = db.get("/lvar/get-last-entry").json()
    assert r["status"] == 200 and r["market_day_end_data"]["mded_date"] == "2025-01-02"


# ---------------------------------------------------------------- limit filter
@pytest.mark.parametrize("value,expected", [
    ("2", 2), ("abc", 3), ("-5", 3), ("0", 3), ("99999999", 3), ("1.5", 3),
])
def test_limit_filter_safe(db, value, expected):
    rows = [mde_row("AAA", date=f"2025-01-0{i}") for i in range(1, 4)]
    db.post("/lvar/project_data_init_mde", json={"status": 1, "message": "", "data": rows})
    r = db.get(f"/lvar/get-mkt-day-end-data/filter=limit/{value}")
    assert r.status_code == 200
    assert r.json()["records_count"] == expected


def test_limit_clamped_to_max(monkeypatch):
    from app.lvar.storage import mkt_day_end
    assert mkt_day_end._parse_limit("10000000") == 5000
    assert mkt_day_end._parse_limit("x") == 300
    assert mkt_day_end._parse_limit("0") == 300


def test_unknown_filter_defaults(db):
    r = db.get("/lvar/get-mkt-day-end-data/filter=bogus/1")
    assert r.status_code == 200 and r.json()["status"] == 200


# ---------------------------------------------------------------- body caps
def test_oversize_list_rejected(client, monkeypatch):
    import app.lvar.router as lr
    small = lr.MAX_LIST_ITEMS
    body = {"status": 1, "message": "", "data": [{}] * (small + 1)}
    assert client.post("/lvar/project_data_init_mde", json=body).status_code == 422


def test_max_list_env_tunable(monkeypatch):
    monkeypatch.setenv("MAX_REQUEST_LIST_ITEMS", "3")
    import app.lvar.router as lr
    import app.main as m
    try:
        importlib.reload(lr)
        importlib.reload(m)
        c = TestClient(m.app, raise_server_exceptions=False)
        ok = {"status": 1, "message": "", "data": []}
        assert c.post("/lvar/project_data_init_mde", json=ok).status_code in (200, 500)
        big = {"status": 1, "message": "", "data": [{}] * 4}
        assert c.post("/lvar/project_data_init_msi", json=big).status_code == 422
    finally:
        monkeypatch.delenv("MAX_REQUEST_LIST_ITEMS")
        importlib.reload(lr)
        importlib.reload(m)


# ---------------------------------------------------------------- rename
def _seed(db):
    db.post("/lvar/project_data_init_mde", json={
        "status": 1, "message": "", "data": [mde_row("OLD"), mde_row("TAKEN")]})
    db.post("/lvar/project_data_init_msi", json={
        "status": 1, "message": "", "data": [msi_row("OLD"), msi_row("TAKEN")]})


def test_rename_ok(db):
    _seed(db)
    r = db.put("/lvar/rename-security-code/OLD/NEW.1-x_y").json()
    assert r["status"] == 200
    assert r["mktSecurityInfo_rows_updated"] == 1
    assert r["mktDayEndData_rows_updated"] == 1


def test_rename_existing_shapes(db):
    _seed(db)
    assert db.put("/lvar/rename-security-code/A/A").json() == {
        "status": 400, "message": "old_code and new_code must be different"}
    r = db.put("/lvar/rename-security-code/NOPE/NEW").json()
    assert r == {"status": 404, "message": "Security code 'NOPE' not found in any table"}


@pytest.mark.parametrize("bad", ["a b", "a'b", "x;DROP", "a" * 65, "%3Cscript%3E", "caf%C3%A9"])
def test_rename_validation(db, bad):
    _seed(db)
    for path in (f"/lvar/rename-security-code/{bad}/NEW", f"/lvar/rename-security-code/OLD/{bad}"):
        r = db.put(path).json()
        assert r["status"] == 400
        assert set(r) == {"status", "message"}


def test_rename_collision_409(db):
    _seed(db)
    r = db.put("/lvar/rename-security-code/OLD/TAKEN").json()
    assert set(r) == {"status", "message"}
    assert r["status"] == 409
    # nothing was changed
    assert db.get("/lvar/get-mkt-day-end-data/filter=security_code/OLD").json()["records_count"] == 1


# ---------------------------------------------------------------- error hygiene / resources
def test_no_exception_text_leak_on_bad_rows(db):
    r = db.post("/lvar/project_data_init_mde", json={
        "status": 1, "message": "", "data": [{"date": "x", "secret_marker": 1}]})
    body = r.text
    assert "un_close" not in body and "KeyError" not in body and "Traceback" not in body
    assert r.json()["message"] == "Project data initialization failed"
    assert r.json()["data_count"] == {"status": 500, "message": "Error processing data"}


def test_no_leak_on_msi_bad_row(db):
    r = db.post("/lvar/project_data_init_msi", json={"status": 1, "message": "", "data": [{}]})
    assert r.json()["data_count"] == {"status": 500, "message": "Error processing security data"}


def test_rename_internal_error_is_generic(client, monkeypatch):
    # tables do not exist -> sqlite error must not reach the client
    monkeypatch.setenv("API_KEY", "")
    r = client.put("/lvar/rename-security-code/AAA/BBB").json()
    assert r == {"status": 500, "message": "Rename failed"}


def test_lvar_failure_is_generic(db):
    body = {"status": 1, "message": "", "mde": [], "msi": [],
            "csh": [{"nokey": "secret_marker"}]}
    r = db.post("/lvar/get-lvar-data", json=body)
    assert r.status_code == 500
    assert "secret_marker" not in r.text and "Traceback" not in r.text
    assert r.json() == {"status": 500, "message": "LVR calculation failed"}


def test_connections_closed_on_error(db, monkeypatch):
    opened = []
    real = database.sqlite3.connect

    def spy(*a, **k):
        c = real(*a, **k)
        opened.append(c)
        return c

    monkeypatch.setattr(database.sqlite3, "connect", spy)
    db.post("/lvar/project_data_init_mde", json={"status": 1, "message": "", "data": [{}]})
    db.put("/lvar/rename-security-code/NOPE/NEW")
    db.get("/lvar/get-mkt-day-end-data/filter=limit/abc")
    assert opened
    for c in opened:
        with pytest.raises(sqlite3.ProgrammingError):
            c.execute("SELECT 1")


# ---------------------------------------------------------------- misc hardening
def test_table_name_whitelist():
    with pytest.raises(ValueError):
        database.delete_table("mktDayEndData; DROP TABLE x")
    with pytest.raises(ValueError):
        database.create_table("evil", "id INTEGER")


def test_chunked_in_queries(db):
    from app.lvar.storage import mkt_day_end as m
    db.post("/lvar/project_data_init_mde", json={
        "status": 1, "message": "", "data": [mde_row("AAA"), mde_row("BBB", date="2025-01-01")]})
    codes = [f"X{i}" for i in range(1700)] + ["AAA", "BBB"]
    rows = m.get_historical_market_price(codes)
    assert [r["security_code"] for r in rows] == ["BBB", "AAA"]  # date ASC preserved
    avg = m.get_last_30_days_market_value_avg(codes + ["00DSEX"])
    assert sorted(r["security_code"] for r in avg) == ["AAA", "BBB"]


def test_docs_disabled_in_production(monkeypatch):
    import app.main as m
    monkeypatch.setenv("ENV", "production")
    try:
        importlib.reload(m)
        c = TestClient(m.app)
        for p in ("/docs", "/redoc", "/openapi.json"):
            assert c.get(p).status_code == 404
        assert c.get("/").status_code == 200
    finally:
        monkeypatch.setenv("ENV", "")
        importlib.reload(m)
        c = TestClient(m.app)
        assert c.get("/openapi.json").status_code == 200
