"""Declarative schema DDL for the NestQuest SQLite database.

Every v1 table is declared here as plain SQL statements so later schema
tasks can append their own DDL to :data:`SCHEMA_V1_STATEMENTS` without
touching this module's tables.  All statements use
``CREATE TABLE IF NOT EXISTS`` so application is idempotent; the versioned
migration runner is a separate later concern.

Timestamp policy: all timestamps are UTC ISO-8601 strings, set by the
caller/DAO rather than a DB default, per the feature guardrails.  No
``length(created_at) = 25`` CHECK is added: the exact string shape
produced by callers is not fixed yet (``datetime.isoformat()`` may
include microseconds, changing the length), so enforcing a fixed length
here would reject valid values.  Only non-null is enforced for now.

Date policy: ``start_date``/``end_date`` are ISO date strings (YYYY-MM-DD,
no time component) compared lexically by the ``end_date >= start_date``
CHECK; due_date/due_time string shapes are caller-validated by the rule
engine and DAO layers, not by DDL.

``weekday_set`` format: comma-separated weekday integers (CSV), 0 =
Monday .. 6 = Sunday, e.g. ``'0,2,4'`` for Mon/Wed/Fri.  Chosen over JSON
because it sorts, compares and parses trivially in SQL.  The format is
enforced at the DB level: a CHECK matches ``weekday_set`` against GLOB
patterns for element counts 1..7 of single digits 0-6 (GLOB has no
repetition quantifier, so the element count is enumerated; more than 7
elements is impossible for a weekday set).  ``''``, ``'x'``, ``'7'`` and
``'0,x'`` are all rejected; NULL stays allowed for non-weekly rules.
This keeps the schema layer storage-only — the DAO layer (tasks
8c72300c+) does not need its own validator, it can rely on the CHECK.

Type affinity: SQLite applies column affinity on insert, but CHECKs
still observe text and fractional values ('abc' stays text, '1.5'
becomes REAL), so every INTEGER column carrying rule semantics carries
a ``typeof(...) = 'integer'`` guard in its CHECK.

Rule-type coherence: two CHECKs enforce that ``weekly`` carries a
non-null ``weekday_set``, ``monthly`` carries a ``day_of_month`` or an
``nth_weekday``, ``yearly`` carries a ``month``, and ``daily``/``custom``
carry none of the month-specific fields.  Relaxed deliberately: the DDL
does NOT forbid ``month`` on weekly/monthly rules (an "every 2 weeks"
rule or a monthly rule could later be pinned to a month) nor extra
fields on weekly/monthly/yearly rules generally, so the CHECKs only
reject what the rule engine definitively cannot express now.

Presence pattern format: ``presence_schedules.pattern`` encodes the
whole N-week repeating cycle in one TEXT column as pipe-separated
segments, one per week of the cycle: segment ``i`` is a ``weekday_set``
CSV (same single-digit 0-6 convention as ``schedule_rules.weekday_set``)
for week ``i``, e.g. ``'0,2,4|1,3'`` = Mon/Wed/Fri in week 0, Tue/Thu in
week 1.  An empty segment means the child is absent every day of that
week; a wholly empty pattern with ``cycle_length_weeks = 1`` means
absent every day of every week, which is distinct from having no
schedule row at all (a child with no schedule is present every day, per
the feature guardrails).  Chosen over JSON or per-week rows to stay
consistent with the CSV ``weekday_set`` convention and fully checkable
by plain CHECKs.  DB-level validation is pure CHECK, no triggers or
custom functions: char-class GLOBs reject every character outside
digits 0-6, comma and pipe; adjacency GLOBs reject empty elements
(double comma, comma next to a pipe, leading/trailing comma) and
two-digit runs; a further GLOB rejects contiguous runs of 8+ comma
separated digits, which caps a segment at 7 elements — the GLOB cannot
match across a ``|``, so cross-segment boundaries stay safe; and the
segment count is pinned to ``cycle_length_weeks`` by
``LENGTH(pattern) - LENGTH(REPLACE(pattern, '|', '')) + 1``.  Per
weekday_set this is not full CSV-set normalisation (duplicates like
``'0,0'`` or unsorted sets stay storable, as with ``schedule_rules``);
the engine treats the segment as a set.  A
one-row-per-``week_index`` normalization was considered and rejected:
the flat single-column encoding keeps the done-condition's table shape
and proved fully checkable with plain CHECKs.

``cycle_length_weeks`` is capped at 4: 2 (alternating weeks) is the
common case, and a small cap keeps anchor arithmetic and the encoded
pattern human-readable.  Revisit the cap — and re-run the pattern
validation matrix in tests — if a longer cycle is ever needed.
``anchor_date`` is an ISO date string pinning the cycle: the week index
of a date is (days since anchor) // 7 mod ``cycle_length_weeks``,
computed by the presence engine (Feature 05) from anchor-date
arithmetic, never ISO week parity.  Like other date columns it carries
no shape CHECK, matching the established date policy.

Presence overrides: ``presence_overrides.end_date`` is NOT NULL,
unlike ``schedule_rules.end_date`` — an override is always a concrete
date range; a single-day override stores the same date in both columns.
``is_present`` 0/1 marks the child absent/present for the whole range;
``note`` is optional free text.
"""
from __future__ import annotations

from .db import NestQuestDatabase

#: GLOB patterns matching a valid ``weekday_set`` CSV: one GLOB per
#: possible element count 1..7 (a weekday set cannot exceed 7 elements).
#: Each element is a single digit 0-6, separated by a literal comma.
#: GLOB has no repetition quantifier, so the element count is enumerated.
#: ``''`` and NULL are deliberately NOT matched: NULL is allowed for
#: non-weekly rules via a separate ``IS NULL`` arm, and '' is invalid
#: because weekly rules need at least one weekday.
WEEKDAY_SET_GLOB_PATTERNS: list[str] = [
    "[0-6]",
    "[0-6],[0-6]",
    "[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]",
    "[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]",
]

SCHEMA_V1_CHILDREN_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS children (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        display_name TEXT NOT NULL,
        colour TEXT,
        avatar_ref TEXT,
        sort_order INTEGER NOT NULL DEFAULT 0 CHECK (typeof(sort_order) = 'integer'),
        is_active INTEGER NOT NULL DEFAULT 1 CHECK (typeof(is_active) = 'integer' AND is_active IN (0, 1)),
        created_at TEXT NOT NULL
    )
    """,
]

SCHEMA_V1_ADMIN_USERS_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS admin_users (
        ha_user_id TEXT PRIMARY KEY NOT NULL,
        added_at TEXT NOT NULL
    )
    """,
]

_WEEKDAY_SET_CHECK = " OR ".join(
    f"weekday_set GLOB '{pattern}'" for pattern in WEEKDAY_SET_GLOB_PATTERNS
)

SCHEMA_V1_SCHEDULE_RULES_DDL: list[str] = [
    f"""
    CREATE TABLE IF NOT EXISTS schedule_rules (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        rule_type TEXT NOT NULL CHECK (rule_type IN ('daily', 'weekly', 'monthly', 'yearly', 'custom')),
        interval INTEGER NOT NULL DEFAULT 1 CHECK (typeof(interval) = 'integer' AND interval >= 1),
        weekday_set TEXT CHECK (weekday_set IS NULL OR ({_WEEKDAY_SET_CHECK})),
        day_of_month INTEGER CHECK (day_of_month IS NULL OR (typeof(day_of_month) = 'integer' AND day_of_month >= 1 AND day_of_month <= 31)),
        nth_weekday INTEGER CHECK (nth_weekday IS NULL OR (typeof(nth_weekday) = 'integer' AND nth_weekday IN (-1, 1, 2, 3, 4, 5))),
        month INTEGER CHECK (month IS NULL OR (typeof(month) = 'integer' AND month >= 1 AND month <= 12)),
        start_date TEXT NOT NULL,
        end_date TEXT,
        CHECK (end_date IS NULL OR end_date >= start_date),
        CHECK (
            (rule_type = 'weekly' AND weekday_set IS NOT NULL)
            OR (rule_type = 'monthly' AND (day_of_month IS NOT NULL OR nth_weekday IS NOT NULL))
            OR (rule_type = 'yearly' AND month IS NOT NULL)
            OR (rule_type IN ('daily', 'custom') AND day_of_month IS NULL AND nth_weekday IS NULL AND month IS NULL)
        )
    )
    """,
]

SCHEMA_V1_TASK_DEFINITIONS_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS task_definitions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        description TEXT,
        icon TEXT,
        child_id INTEGER NOT NULL REFERENCES children(id),
        schedule_rule_id INTEGER NOT NULL REFERENCES schedule_rules(id),
        due_time TEXT,
        is_active INTEGER NOT NULL DEFAULT 1 CHECK (typeof(is_active) = 'integer' AND is_active IN (0, 1)),
        created_at TEXT NOT NULL
    )
    """,
]

SCHEMA_V1_PRESENCE_SCHEDULES_DDL: list[str] = [
    f"""
    CREATE TABLE IF NOT EXISTS presence_schedules (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        child_id INTEGER NOT NULL UNIQUE REFERENCES children(id),
        cycle_length_weeks INTEGER NOT NULL CHECK (typeof(cycle_length_weeks) = 'integer' AND cycle_length_weeks >= 1 AND cycle_length_weeks <= 4),
        anchor_date TEXT NOT NULL,
        pattern TEXT NOT NULL CHECK (
            NOT pattern GLOB '*[^0-6,|]*'
            AND NOT pattern GLOB '*[0-6][0-6]*'
            AND NOT pattern GLOB '*,,*'
            AND NOT pattern GLOB '*,|*'
            AND NOT pattern GLOB '*|,*'
            AND NOT pattern GLOB ',*'
            AND NOT pattern GLOB '*,'
            AND NOT pattern GLOB '*[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6],[0-6]*'
            AND (LENGTH(pattern) - LENGTH(REPLACE(pattern, '|', '')) + 1) = cycle_length_weeks
        )
    )
    """,
]

SCHEMA_V1_PRESENCE_OVERRIDES_DDL: list[str] = [
    """
    CREATE TABLE IF NOT EXISTS presence_overrides (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        child_id INTEGER NOT NULL REFERENCES children(id),
        start_date TEXT NOT NULL,
        end_date TEXT NOT NULL,
        is_present INTEGER NOT NULL CHECK (typeof(is_present) = 'integer' AND is_present IN (0, 1)),
        note TEXT,
        CHECK (end_date >= start_date)
    )
    """,
]

SCHEMA_V1_STATEMENTS: list[str] = [
    *SCHEMA_V1_CHILDREN_DDL,
    *SCHEMA_V1_ADMIN_USERS_DDL,
    *SCHEMA_V1_SCHEDULE_RULES_DDL,
    *SCHEMA_V1_TASK_DEFINITIONS_DDL,
    *SCHEMA_V1_PRESENCE_SCHEDULES_DDL,
    *SCHEMA_V1_PRESENCE_OVERRIDES_DDL,
]


async def async_apply_ddl(
    database: NestQuestDatabase, statements: list[str]
) -> None:
    """Apply each DDL statement inside one transaction on ``database``.

    Idempotent: every statement is ``CREATE TABLE IF NOT EXISTS``, so
    applying the same list twice leaves the schema and its rows intact.
    """
    async with database.transaction():
        for sql in statements:
            await database.execute(sql)