#!/usr/bin/env python3
import json
import os
import sqlite3
import sys
from typing import Any, Dict, List

DB_DIR = "/data/error-reports"
DB_PATH = os.path.join(DB_DIR, "error_db.sqlite3")


def ensure_schema(cur: sqlite3.Cursor) -> None:
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS tbl_claim (
            claim_Id INTEGER PRIMARY KEY AUTOINCREMENT,
            station_id INTEGER NOT NULL,
            claim_description TEXT NOT NULL,
            user_email TEXT,
            state INTEGER NOT NULL DEFAULT 0,
            observation TEXT
        )
        """
    )

    cur.execute("PRAGMA table_info(tbl_claim)")
    columns = {row[1] for row in cur.fetchall()}

    if "state" not in columns:
        cur.execute("ALTER TABLE tbl_claim ADD COLUMN state INTEGER NOT NULL DEFAULT 0")

    if "observation" not in columns:
        cur.execute("ALTER TABLE tbl_claim ADD COLUMN observation TEXT")


def list_claims_by_state(state: int) -> List[Dict[str, Any]]:
    os.makedirs(DB_DIR, mode=0o750, exist_ok=True)
    conn = sqlite3.connect(DB_PATH, timeout=5)
    conn.row_factory = sqlite3.Row
    try:
        cur = conn.cursor()
        ensure_schema(cur)
        conn.commit()

        cur.execute(
            """
            SELECT
                claim_Id,
                station_id,
                claim_description,
                user_email,
                state,
                observation
            FROM tbl_claim
            WHERE state = ?
            ORDER BY claim_Id DESC
            """,
            (state,),
        )

        rows = cur.fetchall()
        return [
            {
                "claim_id": int(row["claim_Id"]),
                "station_id": int(row["station_id"]),
                "description": str(row["claim_description"]),
                "email": row["user_email"],
                "state": int(row["state"]),
                "observation": row["observation"],
            }
            for row in rows
        ]
    finally:
        conn.close()


def main() -> int:
    try:
        payload: Dict[str, Any] = json.load(sys.stdin)
        state = int(payload["state"])
        if state not in (0, 1):
            raise ValueError("state must be 0 or 1")

        claims = list_claims_by_state(state)
        sys.stdout.write(json.dumps({"ok": True, "claims": claims}))
        return 0
    except Exception as exc:
        sys.stdout.write(json.dumps({"ok": False, "error": str(exc)}))
        return 1


if __name__ == "__main__":
    raise SystemExit(main())