"""Templates in a table: GalaxiQ's defaults in the master database, an organisation's own in its schema. Every
product reads both the same way. A file is only ever a seed."""
from __future__ import annotations

import json
from pathlib import Path
from typing import Any, Dict, List, Optional, Union

from .load import template_from_dict, template_from_file
from .spec import Template

SQL = Path(__file__).with_name("sql") / "content_templates.sql"
PLATFORM = "public"
# A picture template stands outside the design versions: a block design and a picture may share a name ("Welcome"),
# the picture is what a gallery lists and a pick follows, the design is what the art director draws from.
PICTURE_VERSION = 0


def _table(schema: str) -> str:
    return f'"{schema}".content_templates'


def ensure_template_table(conn, schema: str = PLATFORM) -> None:
    conn.execute(SQL.read_text(encoding="utf-8").replace("{schema}", schema))


def save_template(conn, template: Template, schema: str = PLATFORM) -> str:
    """Upsert by (channel, name, version): the same template saved twice changes nothing but updated_at. Returns
    the row id. What the template fills is derived from its blocks and stored beside it, so a gallery can say it
    without reading the spec."""
    ensure_template_table(conn, schema)
    row = conn.execute(
        f"INSERT INTO {_table(schema)} (name, channel, kind, owner, spec, version, fills) VALUES (%s, %s, %s, %s, %s::jsonb, %s, %s::jsonb) "
        "ON CONFLICT (channel, name, version) DO UPDATE SET spec = EXCLUDED.spec, kind = EXCLUDED.kind, owner = EXCLUDED.owner, "
        "fills = EXCLUDED.fills, updated_at = now() RETURNING id",
        (template.name, template.channel, template.kind, template.owner, template.model_dump_json(), template.version,
         json.dumps(fills_of(template)))).fetchone()
    return str(row[0])


def save_picture_template(conn, name: str, kind: str, description: str, image_url: str, owner: str = "galaxiq",
                          channel: str = "email", schema: str = PLATFORM) -> str:
    """A template that is a picture: what Media lists and what the design is read from when it is picked. Upsert by
    (channel, name, PICTURE_VERSION); the same name saved again takes the new picture. Returns the row id."""
    ensure_template_table(conn, schema)
    row = conn.execute(
        f"INSERT INTO {_table(schema)} (name, channel, kind, owner, spec, version, image_url, description, listed) "
        "VALUES (%s, %s, %s, %s, NULL, %s, %s, %s, true) "
        "ON CONFLICT (channel, name, version) DO UPDATE SET kind = EXCLUDED.kind, owner = EXCLUDED.owner, spec = NULL, "
        "image_url = EXCLUDED.image_url, description = EXCLUDED.description, listed = true, enabled = true, updated_at = now() RETURNING id",
        (name, channel, kind, owner, PICTURE_VERSION, image_url, description)).fetchone()
    return str(row[0])


def set_listed(conn, names: List[str], listed: bool, channel: str = "email", schema: str = PLATFORM) -> int:
    """Whether a gallery shows these block designs. A picture template of the same name is not touched: it is the
    template; the design is what the art director keeps, unlisted, not disabled."""
    ensure_template_table(conn, schema)
    return conn.execute(f"UPDATE {_table(schema)} SET listed = %s, updated_at = now() WHERE channel = %s AND name = ANY(%s) AND spec IS NOT NULL",
                        (listed, channel, list(names))).rowcount


def picture_template(conn, reference: str, channel: Optional[str] = "email", schema: Optional[str] = None) -> Optional[Dict[str, Any]]:
    """The picture template a reference names, by row id or by name (case does not matter): the organisation's own
    first when a schema is given, then the platform's. On one channel, or on any when channel is None. None when the
    reference names no picture template."""
    for where in ([schema] if schema and schema != PLATFORM else []) + [PLATFORM]:
        if not _exists(conn, where):
            continue
        ensure_template_table(conn, where)
        row = conn.execute(
            f"SELECT id, name, kind, owner, image_url, description, channel FROM {_table(where)} "
            f"WHERE (%s::text IS NULL OR channel = %s) AND enabled AND spec IS NULL AND image_url IS NOT NULL "
            f"AND (id::text = %s OR lower(name) = lower(%s)) ORDER BY version DESC LIMIT 1",
            (channel, channel, reference, reference)).fetchone()
        if row:
            return {"id": str(row[0]), "name": row[1], "kind": row[2], "owner": row[3], "image_url": row[4], "description": row[5],
                    "channel": row[6]}
    return None


def fills_of(template: Template) -> Dict[str, List[str]]:
    """What a template fills, by who fills it, in plain words taken from the value keys themselves: {"person":
    ["first name", "cart items", "cart total"], "journey": ["code", "link"], ...}. Nothing is listed by hand; a new
    block's values appear here the moment it is in the contract."""
    out: Dict[str, List[str]] = {}
    for v in template.values():
        who = {"identity": "person", "behaviour": "person", "copy": "written", "made": "picture"}.get(v.source, v.source)
        group, _, tail = v.key.partition(".")
        # "brand.name" is "name" under brand; "cart.items" keeps its group, since "items" alone says nothing.
        word = (tail if group in (v.source, who, "person", "copy") else f"{group} {tail}").replace("_", " ")
        words = out.setdefault(who, [])
        if word not in words:
            words.append(word)
    return out


def set_template_image(conn, name: str, channel: str, version: int, image_url: str, schema: str = PLATFORM) -> int:
    """The picture a gallery shows for this template version."""
    ensure_template_table(conn, schema)   # a table from before the picture column gains it here
    return conn.execute(f"UPDATE {_table(schema)} SET image_url = %s, updated_at = now() WHERE channel = %s AND name = %s AND version = %s",
                        (image_url, channel, name, version)).rowcount


def list_template_cards(conn, channel: str, schema: Optional[str] = None, enabled_only: bool = True) -> List[Dict[str, Any]]:
    """What a gallery lists: the newest version of each name with its picture and what it fills. The platform's,
    then the organisation's own when a schema is given. The query a consumer reading the table directly would run."""
    out: Dict[str, Dict[str, Any]] = {}
    for where in [PLATFORM] + ([schema] if schema and schema != PLATFORM else []):
        if not _exists(conn, where):
            continue
        ensure_template_table(conn, where)   # a table from before the card columns gains them here
        rows = conn.execute(
            f"SELECT DISTINCT ON (name) id, name, kind, owner, version, image_url, fills, coalesce(description, spec->>'description'), "
            f"spec->'suits', spec IS NULL FROM {_table(where)} WHERE channel = %s AND listed {'AND enabled' if enabled_only else ''} "
            f"ORDER BY name, version DESC", (channel,)).fetchall()
        for id_, name, kind, owner, version, image_url, fills, description, suits, picture in rows:
            out[name] = {"id": str(id_), "name": name, "kind": kind, "owner": owner, "version": version, "image_url": image_url,
                         "picture": bool(picture),
                         "fills": fills if isinstance(fills, dict) else (json.loads(fills) if fills else {}),
                         "description": description, "suits": suits if isinstance(suits, list) else (json.loads(suits) if suits else [])}
    return list(out.values())


def list_templates(conn, channel: str, schema: Optional[str] = None, enabled_only: bool = True) -> Dict[str, Template]:
    """Templates for a channel by name: the platform's defaults, then the organisation's own when a schema is given,
    which win on a shared name. Only the newest version of each name."""
    out: Dict[str, Template] = {}
    for where in [PLATFORM] + ([schema] if schema and schema != PLATFORM else []):
        if not _exists(conn, where):
            continue
        rows = conn.execute(
            f"SELECT DISTINCT ON (name) spec FROM {_table(where)} WHERE channel = %s AND spec IS NOT NULL {'AND enabled' if enabled_only else ''} "
            "ORDER BY name, version DESC", (channel,)).fetchall()
        for (spec,) in rows:
            data = spec if isinstance(spec, dict) else json.loads(spec)
            template = Template(**data)
            out[template.name] = template
    return out


def disable_template(conn, name: str, channel: str, schema: str = PLATFORM) -> int:
    return conn.execute(f"UPDATE {_table(schema)} SET enabled = false, updated_at = now() WHERE channel = %s AND name = %s",
                        (channel, name)).rowcount


def seed_templates(conn, folder: Union[str, Path], schema: str = PLATFORM) -> int:
    """Every *.template.json in a folder saved as a GalaxiQ default. Returns how many were written."""
    n = 0
    for path in sorted(Path(folder).glob("*.template.json")):
        save_template(conn, template_from_file(path), schema)
        n += 1
    return n


def _exists(conn, schema: str) -> bool:
    return conn.execute("SELECT 1 FROM information_schema.tables WHERE table_schema = %s AND table_name = 'content_templates'",
                        (schema,)).fetchone() is not None
