"""
dbml_helpers.py — reshape PyDBML parsed objects into template-ready dicts.

Rules (generator-coding skill):
  - All data reshaping lives here, NOT in templates.
  - Templates iterate; helpers compute.
"""

from __future__ import annotations
from typing import Any

# ---------------------------------------------------------------------------
# Type mapping
# ---------------------------------------------------------------------------

_SQLITE_TYPE_MAP: dict[str, str] = {
    "integer": "INTEGER",
    "int":     "INTEGER",
    "bigint":  "INTEGER",
    "smallint":"INTEGER",
    "tinyint": "INTEGER",
    "bool":    "INTEGER",
    "boolean": "INTEGER",
    "varchar": "TEXT",
    "char":    "TEXT",
    "text":    "TEXT",
    "string":  "TEXT",
    "date":    "TEXT",      # SQLite stores dates as TEXT (ISO 8601)
    "datetime":"TEXT",
    "timestamp":"TEXT",
    "float":   "REAL",
    "double":  "REAL",
    "decimal": "REAL",
    "numeric": "REAL",
    "blob":    "BLOB",
}


def dbml_to_sqlite_type(type_str: str) -> str:
    """Map a DBML type string to its SQLite affinity keyword."""
    key = type_str.lower().split("(")[0].strip()  # strip length, e.g. varchar(255)
    return _SQLITE_TYPE_MAP.get(key, "TEXT")


# ---------------------------------------------------------------------------
# Column constraints
# ---------------------------------------------------------------------------

def column_constraints(col: Any) -> str:
    """
    Return the SQLite constraint fragment for a column (after the type).

    PK columns become:  PRIMARY KEY AUTOINCREMENT
    NOT NULL columns:   NOT NULL
    Plain nullable:     (empty string)
    """
    parts: list[str] = []
    if col.pk:
        parts.append("PRIMARY KEY AUTOINCREMENT")
    elif col.not_null:
        parts.append("NOT NULL")
    return (" " + " ".join(parts)) if parts else ""


# ---------------------------------------------------------------------------
# Foreign-key extraction
# ---------------------------------------------------------------------------

def foreign_keys_for_table(table_name: str, all_refs: list[Any]) -> list[dict]:
    """
    Return FK dicts for *table_name* from the global refs list.

    Each dict: { "col": str, "ref_table": str, "ref_col": str }

    PyDBML ref.type ">" means col1 → col2 (many-to-one from col1's table).
    """
    fks: list[dict] = []
    for ref in all_refs:
        # ref.type ">" : table1.col1 references table2.col2
        if ref.type == ">":
            src_cols = ref.col1   # list of Column objects
            tgt_cols = ref.col2
        elif ref.type == "<":
            src_cols = ref.col2
            tgt_cols = ref.col1
        else:
            continue  # many-to-many or self; skip

        if not src_cols:
            continue
        src_table = src_cols[0].table.name
        if src_table != table_name:
            continue

        tgt_table = tgt_cols[0].table.name
        fks.append({
            "col":       src_cols[0].name,
            "ref_table": tgt_table,
            "ref_col":   tgt_cols[0].name,
        })
    return fks


# ---------------------------------------------------------------------------
# Main reshape: build template-ready table list
# ---------------------------------------------------------------------------

def prepare_tables(db: Any) -> list[dict]:
    """
    Convert a PyDBML Database object into a list of table dicts suitable for
    Jinja2 template iteration.

    Each table dict:
      {
        "name": str,
        "columns": [
          { "name": str, "sqlite_type": str, "constraints": str },
          ...
        ],
        "foreign_keys": [
          { "col": str, "ref_table": str, "ref_col": str },
          ...
        ],
      }
    """
    tables: list[dict] = []
    all_refs = list(db.refs)

    for table in db.tables:
        columns = [
            {
                "name":        col.name,
                "sqlite_type": dbml_to_sqlite_type(str(col.type)),
                "constraints": column_constraints(col),
            }
            for col in table.columns
        ]
        fks = foreign_keys_for_table(table.name, all_refs)
        tables.append({
            "name":         table.name,
            "columns":      columns,
            "foreign_keys": fks,
        })

    return tables