"""Database seed functions — create/update system-owned defaults on startup."""

import json
import logging
from datetime import datetime, timezone

from sqlalchemy import text
from sqlmodel import select
from sqlmodel.ext.asyncio.session import AsyncSession

from .engine import get_engine
from .models import (
    CommandConfig,
    CommandTemplateConfig,
    CommandTemplateSlot,
    ServiceProvider,
    TemplateConfig,
    TemplateSlot,
    TrackingConfig,
    User,
)

_LOGGER = logging.getLogger(__name__)


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

async def _seed_provider_template(
    session: AsyncSession,
    provider_type: str,
    label: str,
) -> None:
    """Seed templates for a single provider type across all locales.

    Creates a single TemplateConfig per provider with locale-aware slots
    (each slot has an EN and RU version stored as separate rows).
    """
    from notify_bridge_core.templates.defaults import load_default_templates

    result = await session.exec(
        select(TemplateConfig).where(
            TemplateConfig.user_id == 0,
            TemplateConfig.provider_type == provider_type,
        )
    )
    configs = result.all()

    if not configs:
        config = TemplateConfig(
            user_id=0,
            provider_type=provider_type,
            name=f"Default {label}",
            description=f"Default {label} templates",
        )
        session.add(config)
        await session.flush()
    else:
        config = configs[0]

    for locale in ("en", "ru"):
        slots = load_default_templates(locale, provider_type=provider_type)
        if not slots:
            continue
        for slot_name, template_text in slots.items():
            slot_result = await session.exec(
                select(TemplateSlot).where(
                    TemplateSlot.config_id == config.id,
                    TemplateSlot.slot_name == slot_name,
                    TemplateSlot.locale == locale,
                )
            )
            existing = slot_result.first()
            if existing:
                existing.template = template_text
                session.add(existing)
            else:
                session.add(TemplateSlot(
                    config_id=config.id,
                    slot_name=slot_name,
                    locale=locale,
                    template=template_text,
                ))


async def _seed_provider_command_template(
    session: AsyncSession,
    provider_type: str,
    name: str,
    description: str,
) -> None:
    """Seed command templates for a single provider type across all locales."""
    from notify_bridge_core.templates.command_defaults import load_default_command_templates

    result = await session.exec(
        select(CommandTemplateConfig).where(
            CommandTemplateConfig.user_id == 0,
            CommandTemplateConfig.provider_type == provider_type,
        )
    )
    configs = result.all()

    if not configs:
        config = CommandTemplateConfig(
            user_id=0,
            provider_type=provider_type,
            name=name,
            description=description,
        )
        session.add(config)
        await session.flush()
    else:
        config = configs[0]
        if config.name != name or config.description != description:
            config.name = name
            config.description = description
            session.add(config)

    for locale in ("en", "ru"):
        slots = load_default_command_templates(locale, provider_type=provider_type)
        if not slots:
            continue
        for slot_name, template_text in slots.items():
            slot_result = await session.exec(
                select(CommandTemplateSlot).where(
                    CommandTemplateSlot.config_id == config.id,
                    CommandTemplateSlot.slot_name == slot_name,
                    CommandTemplateSlot.locale == locale,
                )
            )
            existing = slot_result.first()
            if existing:
                existing.template = template_text
                session.add(existing)
            else:
                session.add(CommandTemplateSlot(
                    config_id=config.id,
                    slot_name=slot_name,
                    locale=locale,
                    template=template_text,
                ))


# ---------------------------------------------------------------------------
# Top-level seed functions
# ---------------------------------------------------------------------------

async def _seed_default_templates() -> None:
    """Seed or update default (system-owned) templates on startup.

    Uses TemplateSlot child rows for template content.
    """
    engine = get_engine()
    async with AsyncSession(engine) as session:
        await _seed_provider_template(session, "immich", "Immich")
        await _seed_provider_template(session, "gitea", "Gitea")
        await _seed_provider_template(session, "planka", "Planka")
        await _seed_provider_template(session, "scheduler", "Scheduler")
        await _seed_provider_template(session, "nut", "NUT")
        await _seed_provider_template(session, "google_photos", "Google Photos")
        await _seed_provider_template(session, "webhook", "Generic Webhook")
        await _seed_provider_template(session, "home_assistant", "Home Assistant")
        await _seed_provider_template(session, "bridge_self", "Bridge Self-Monitoring")
        await session.commit()


async def _seed_default_command_templates() -> None:
    """Seed or update default command response templates on startup.

    Creates a single config per provider with locale-aware slots
    (each slot has an EN and RU version stored as separate rows).
    """
    engine = get_engine()
    async with AsyncSession(engine) as session:
        await _seed_provider_command_template(
            session, "immich", "Default Immich Commands", "Default Immich command templates",
        )
        await _seed_provider_command_template(
            session, "gitea", "Default Gitea Commands", "Default Gitea command templates",
        )
        await _seed_provider_command_template(
            session, "planka", "Default Planka Commands", "Default Planka command templates",
        )
        await _seed_provider_command_template(
            session, "nut", "Default NUT Commands", "Default NUT command templates",
        )
        await _seed_provider_command_template(
            session, "google_photos", "Default Google Photos Commands", "Default Google Photos command templates",
        )
        await _seed_provider_command_template(
            session, "webhook", "Default Webhook Commands", "Default Generic Webhook command templates",
        )
        await _seed_provider_command_template(
            session, "home_assistant", "Default Home Assistant Commands",
            "Default Home Assistant command templates",
        )
        await _seed_provider_command_template(
            session, "bridge_self", "Default Bridge Self-Monitoring Commands",
            "Default Bridge Self-Monitoring command templates",
        )
        await session.commit()


async def _seed_default_tracking_configs() -> None:
    """Seed system-owned default tracking configs for each provider type."""
    engine = get_engine()
    async with AsyncSession(engine) as session:
        result = await session.exec(
            select(TrackingConfig).where(TrackingConfig.user_id == 0)
        )
        existing = {c.provider_type: c for c in result.all()}

        defaults = [
            {
                "provider_type": "gitea",
                "name": "Default Gitea",
                "track_push": True,
                "track_issue_opened": True,
                "track_issue_closed": True,
                "track_issue_commented": False,
                "track_pr_opened": True,
                "track_pr_closed": True,
                "track_pr_merged": True,
                "track_pr_commented": False,
                "track_release_published": True,
            },
            {
                "provider_type": "planka",
                "name": "Default Planka",
                "track_card_created": True,
                "track_card_updated": False,
                "track_card_moved": True,
                "track_card_deleted": False,
                "track_card_commented": True,
                "track_comment_updated": False,
                "track_board_created": True,
                "track_board_updated": False,
                "track_board_deleted": True,
                "track_list_created": False,
                "track_list_updated": False,
                "track_list_deleted": False,
                "track_attachment_created": True,
                "track_card_label_added": False,
                "track_task_completed": True,
            },
            {
                "provider_type": "scheduler",
                "name": "Default Scheduler",
                "track_scheduled_message": True,
            },
            {
                "provider_type": "webhook",
                "name": "Default Webhook",
                "track_webhook_received": True,
            },
            {
                "provider_type": "immich",
                "name": "Default Immich",
                "track_assets_added": True,
                "track_assets_removed": False,
                "track_collection_renamed": True,
                "track_collection_deleted": True,
                "track_sharing_changed": False,
            },
            {
                "provider_type": "google_photos",
                "name": "Default Google Photos",
                "track_assets_added": True,
                "track_assets_removed": False,
                "track_collection_renamed": True,
                "track_collection_deleted": True,
                "track_sharing_changed": False,
            },
            {
                "provider_type": "nut",
                "name": "Default NUT",
                "track_ups_online": True,
                "track_ups_on_battery": True,
                "track_ups_low_battery": True,
                "track_ups_battery_restored": True,
                "track_ups_comms_lost": True,
                "track_ups_comms_restored": True,
                "track_ups_replace_battery": True,
                "track_ups_overload": True,
            },
            {
                "provider_type": "home_assistant",
                "name": "Default Home Assistant",
                "track_ha_state_changed": True,
                "track_ha_automation_triggered": False,
                "track_ha_service_called": False,
                "track_ha_event_fired": False,
            },
            {
                "provider_type": "bridge_self",
                "name": "Default Bridge Self-Monitoring",
                "track_bridge_self_poll_failures": True,
                "track_bridge_self_deferred_backlog": True,
                "track_bridge_self_target_failures": True,
            },
        ]

        for cfg in defaults:
            ptype = cfg["provider_type"]
            if ptype in existing:
                continue
            session.add(TrackingConfig(user_id=0, **cfg))

        await session.commit()


async def _seed_default_command_configs() -> None:
    """Seed system-owned default command configs for each provider type."""
    engine = get_engine()
    async with AsyncSession(engine) as session:
        result = await session.exec(
            select(CommandConfig).where(CommandConfig.user_id == 0)
        )
        existing = {c.provider_type: c for c in result.all()}

        # Find system command template configs to link
        tmpl_result = await session.exec(
            select(CommandTemplateConfig).where(CommandTemplateConfig.user_id == 0)
        )
        tmpl_by_type = {t.provider_type: t.id for t in tmpl_result.all()}

        defaults = [
            {
                "provider_type": "immich",
                "name": "Default Immich",
                "enabled_commands": [
                    "help", "status", "albums", "events", "latest",
                    "random", "favorites", "summary", "memory",
                ],
                "response_mode": "media",
                "default_count": 5,
                "rate_limits": {"search": 30, "default": 10},
            },
            {
                "provider_type": "gitea",
                "name": "Default Gitea",
                "enabled_commands": [
                    "help", "status", "repos", "issues", "prs", "commits",
                ],
                "response_mode": "text",
                "default_count": 10,
                "rate_limits": {"api": 15, "default": 10},
            },
            {
                "provider_type": "planka",
                "name": "Default Planka",
                "enabled_commands": [
                    "help", "status", "boards", "cards", "lists",
                ],
                "response_mode": "text",
                "default_count": 10,
                "rate_limits": {"api": 15, "default": 10},
            },
            {
                "provider_type": "nut",
                "name": "Default NUT",
                "enabled_commands": [
                    "help", "status", "devices", "battery",
                ],
                "response_mode": "text",
                "default_count": 5,
                "rate_limits": {"api": 15, "default": 10},
            },
            {
                "provider_type": "webhook",
                "name": "Default Webhook",
                "enabled_commands": ["help", "status"],
                "response_mode": "text",
                "default_count": 5,
                "rate_limits": {"default": 10},
            },
            {
                "provider_type": "google_photos",
                "name": "Default Google Photos",
                "enabled_commands": [
                    "help", "status", "albums", "latest", "search", "random",
                ],
                "response_mode": "media",
                "default_count": 5,
                "rate_limits": {"search": 30, "default": 10},
            },
            {
                "provider_type": "bridge_self",
                "name": "Default Bridge Self-Monitoring",
                "enabled_commands": [
                    "help", "status", "thresholds", "reset", "health",
                ],
                "response_mode": "text",
                "default_count": 5,
                "rate_limits": {"default": 10},
            },
        ]

        for cfg in defaults:
            ptype = cfg["provider_type"]
            if ptype in existing:
                continue
            cmd_tmpl_id = tmpl_by_type.get(ptype)
            await session.execute(
                text(
                    "INSERT INTO command_config "
                    "(user_id, provider_type, name, icon, enabled_commands, locale, "
                    "response_mode, default_count, rate_limits, command_template_config_id, created_at) "
                    "VALUES (:uid, :pt, :name, :icon, :cmds, :locale, :rm, :dc, :rl, :ctid, :ca)"
                ),
                {
                    "uid": 0,
                    "pt": ptype,
                    "name": cfg["name"],
                    "icon": "",
                    "cmds": json.dumps(cfg["enabled_commands"]),
                    "locale": "en",
                    "rm": cfg["response_mode"],
                    "dc": cfg["default_count"],
                    "rl": json.dumps(cfg["rate_limits"]),
                    "ctid": cmd_tmpl_id,
                    "ca": datetime.now(timezone.utc).isoformat(),
                },
            )

        await session.commit()


# ---------------------------------------------------------------------------
# Bridge self-monitoring per-user provider
# ---------------------------------------------------------------------------


# Default thresholds — duplicated here as constants instead of imported so the
# seed module stays self-contained and import-cycle-free during boot.
_BRIDGE_SELF_DEFAULT_CONFIG = {
    "poll_failure_threshold": 3,
    "deferred_backlog_threshold": 100,
    "target_failure_threshold": 5,
}


async def ensure_bridge_self_provider_for_user(
    session: AsyncSession, user_id: int,
) -> ServiceProvider | None:
    """Create the user's bridge_self provider if absent. Returns the provider.

    The bridge_self provider is special — exactly one per user, auto-created
    so the operator never has to think about wiring it up. Idempotent.
    Skips ``user_id <= 0`` (the ``__system__`` placeholder) which never
    receives notifications.
    """
    if user_id <= 0:
        return None
    result = await session.exec(
        select(ServiceProvider).where(
            ServiceProvider.user_id == user_id,
            ServiceProvider.type == "bridge_self",
        )
    )
    existing = result.first()
    if existing is not None:
        return existing
    provider = ServiceProvider(
        user_id=user_id,
        type="bridge_self",
        name="Bridge Self-Monitoring",
        config=dict(_BRIDGE_SELF_DEFAULT_CONFIG),
    )
    session.add(provider)
    await session.flush()
    return provider


async def _seed_bridge_self_providers_for_existing_users() -> None:
    """Backfill bridge_self provider for every existing real user.

    Runs once at boot so deployments upgrading from a pre-bridge_self
    release pick up the auto-created provider without requiring user
    action. Skips users that already have one.
    """
    engine = get_engine()
    async with AsyncSession(engine) as session:
        users = (await session.exec(select(User).where(User.id != 0))).all()
        for user in users:
            await ensure_bridge_self_provider_for_user(session, user.id)
        await session.commit()


# ---------------------------------------------------------------------------
# Public entry point
# ---------------------------------------------------------------------------

async def _ensure_system_user() -> None:
    """Ensure a User row with id=0 exists.

    Historically the app used ``user_id=0`` as a sentinel for "system-owned"
    defaults (tracking configs, templates, etc.). Now that we enable
    ``PRAGMA foreign_keys=ON`` at connect time, those inserts would fail
    with ``FOREIGN KEY constraint failed`` unless a placeholder user row
    with the matching id exists.
    """
    engine = get_engine()
    async with engine.begin() as conn:
        # INSERT OR IGNORE so re-running seeds is cheap and idempotent.
        await conn.execute(
            text(
                "INSERT OR IGNORE INTO user "
                "(id, username, hashed_password, role, token_version, created_at) "
                "VALUES (0, :u, :p, :r, 1, :t)"
            ),
            {
                "u": "__system__",
                # Invalid bcrypt hash — nobody can ever log in as this user.
                "p": "!disabled!",
                "r": "system",
                "t": datetime.now(timezone.utc).isoformat(),
            },
        )


async def seed_all() -> None:
    """Run all seed functions in order."""
    await _ensure_system_user()
    await _seed_default_templates()
    await _seed_default_command_templates()
    await _seed_default_tracking_configs()
    await _seed_default_command_configs()
    await _seed_bridge_self_providers_for_existing_users()
