"""Repository functions for profile and cv_section tables."""

from __future__ import annotations

import json
from typing import Any

from app.db import execute, fetch_all, fetch_one


def get_or_create_profile() -> dict[str, Any] | None:
    """Get the single profile row, or create a default one if none exists."""
    row = fetch_one("SELECT * FROM profile LIMIT 1")
    if row is not None:
        return _normalize_profile(row)
    # Create default profile
    row = execute(
        """
        INSERT INTO profile (full_name, email)
        VALUES ('', '')
        RETURNING *
        """
    )
    if row is None:
        return None
    return _normalize_profile(row)


def update_profile(data: dict[str, Any]) -> dict[str, Any] | None:
    """Update the single profile row with provided fields."""
    profile = get_or_create_profile()
    if profile is None:
        return None
    pid = profile["id"]
    fields = []
    values: list[Any] = []
    for key in ("full_name", "email", "phone", "location", "headline", "summary"):
        if key in data and data[key] is not None:
            fields.append(f"{key} = %s")
            values.append(data[key])
    if "languages" in data and data["languages"] is not None:
        fields.append("languages = %s")
        values.append(json.dumps(data["languages"]))
    if "hard_rules" in data and data["hard_rules"] is not None:
        fields.append("hard_rules = %s")
        values.append(json.dumps(data["hard_rules"]))
    if not fields:
        return profile
    fields.append("updated_at = now()")
    values.append(pid)
    sql = f"UPDATE profile SET {', '.join(fields)} WHERE id = %s RETURNING *"
    row = execute(sql, tuple(values))
    if row is None:
        return None
    return _normalize_profile(row)


def _normalize_profile(row: dict[str, Any]) -> dict[str, Any]:
    return {
        "id": str(row["id"]),
        "full_name": row["full_name"],
        "email": row["email"],
        "phone": row.get("phone"),
        "location": row.get("location"),
        "headline": row.get("headline"),
        "summary": row.get("summary"),
        "languages": row.get("languages", []) if isinstance(row.get("languages"), list) else json.loads(row.get("languages", "[]")),
        "hard_rules": row.get("hard_rules", {}) if isinstance(row.get("hard_rules"), dict) else json.loads(row.get("hard_rules", "{}")),
    }


# --- CV Sections ---

def list_sections() -> list[dict[str, Any]]:
    rows = fetch_all(
        "SELECT * FROM cv_section ORDER BY kind, sort_order"
    )
    return [_normalize_section(r) for r in rows]


def create_section(profile_id: str, data: dict[str, Any]) -> dict[str, Any]:
    row = execute(
        """
        INSERT INTO cv_section
            (profile_id, kind, title, org, location, start_date, end_date, bullets, tags, sort_order)
        VALUES
            (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        RETURNING *
        """,
        (
            profile_id,
            data["kind"],
            data["title"],
            data.get("org"),
            data.get("location"),
            data.get("start_date"),
            data.get("end_date"),
            json.dumps(data.get("bullets", [])),
            data.get("tags", []),
            data.get("sort_order", 0),
        ),
    )
    if row is None:
        raise RuntimeError("insert failed")
    return _normalize_section(row)


def update_section(section_id: str, data: dict[str, Any]) -> dict[str, Any] | None:
    fields = []
    values: list[Any] = []
    for key in ("kind", "title", "org", "location", "start_date", "end_date", "sort_order"):
        if key in data and data[key] is not None:
            fields.append(f"{key} = %s")
            values.append(data[key])
    if "bullets" in data and data["bullets"] is not None:
        fields.append("bullets = %s")
        values.append(json.dumps(data["bullets"]))
    if "tags" in data and data["tags"] is not None:
        fields.append("tags = %s")
        values.append(data["tags"])
    if not fields:
        return get_section(section_id)
    fields.append("updated_at = now()")
    values.append(section_id)
    sql = f"UPDATE cv_section SET {', '.join(fields)} WHERE id = %s RETURNING *"
    row = execute(sql, tuple(values))
    if row is None:
        return None
    return _normalize_section(row)


def get_section(section_id: str) -> dict[str, Any] | None:
    row = fetch_one("SELECT * FROM cv_section WHERE id = %s", (section_id,))
    if row is None:
        return None
    return _normalize_section(row)


def delete_section(section_id: str) -> bool:
    row = execute("DELETE FROM cv_section WHERE id = %s RETURNING id", (section_id,))
    return row is not None


def _normalize_section(row: dict[str, Any]) -> dict[str, Any]:
    return {
        "id": str(row["id"]),
        "profile_id": str(row["profile_id"]),
        "kind": row["kind"],
        "title": row["title"],
        "org": row.get("org"),
        "location": row.get("location"),
        "start_date": row.get("start_date").isoformat() if row.get("start_date") else None,
        "end_date": row.get("end_date").isoformat() if row.get("end_date") else None,
        "bullets": row.get("bullets", []) if isinstance(row.get("bullets"), list) else json.loads(row.get("bullets", "[]")),
        "tags": list(row.get("tags", [])) if row.get("tags") else [],
        "sort_order": row.get("sort_order", 0),
    }