#!/usr/bin/env python3
"""Database migration script — cumulative.

Run automatically by the updater (subprocess.run) when a new release is
applied. Idempotent and cumulative: each migration checks whether it has
already been applied (via SQLAlchemy inspector) before running. This means
a user who skips multiple versions still gets every needed migration
applied in order, in a single update.

Standalone — does NOT import app.py (which would trigger init_database()
on the old schema and fail before migrations can run). Reads DATABASE_URI
from the environment or the .env file directly.

To add a new migration in a future release:
  1. Write a function `migrate_<name>(engine, inspector)` that:
     - Checks via inspector whether the change is already present.
     - If yes, returns False (already applied).
     - If no, applies the change and returns True.
  2. Append a tuple to MIGRATIONS at the bottom: (key, description, func).
  3. Bump version in latest.json on the update server.
  4. Set requires_migration=true in latest.json.

Each migration must be:
  - Idempotent: safe to run on databases where it has already been applied.
  - Self-contained: does not depend on subsequent migrations.
  - SQL-portable: works on both SQLite and PostgreSQL.
"""

import os
import sys


# Cross-platform file locking (fcntl on Linux, msvcrt on Windows).
# Mirrors the helper in app.py:init_database so the migration runner
# behaves the same way on all platforms.
try:
    import fcntl
    def _lock(f):
        fcntl.flock(f, fcntl.LOCK_EX)
    def _unlock(f):
        fcntl.flock(f, fcntl.LOCK_UN)
except ImportError:
    try:
        import msvcrt
        def _lock(f):
            msvcrt.locking(f.fileno(), msvcrt.LK_LOCK, 1)
        def _unlock(f):
            try:
                msvcrt.locking(f.fileno(), msvcrt.LK_UNLCK, 1)
            except OSError:
                pass
    except ImportError:
        def _lock(f):
            pass
        def _unlock(f):
            pass


# ─── Helpers ──────────────────────────────────────────────────────────────

def get_database_url():
    """Read DATABASE_URI from environment or .env file."""
    url = os.environ.get('DATABASE_URI', '')
    if url:
        return url
    env_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), '.env')
    if os.path.exists(env_path):
        with open(env_path) as f:
            for line in f:
                line = line.strip()
                if line.startswith('DATABASE_URI='):
                    return line.split('=', 1)[1].strip().strip('"').strip("'")
    return ''


def column_exists(inspector, table, column):
    if table not in inspector.get_table_names():
        return False
    return column in [c['name'] for c in inspector.get_columns(table)]


# ─── Individual migrations ────────────────────────────────────────────────

def migrate_menu_items_urls_json(engine, inspector):
    """2.0.2 — Multilingual URLs for custom menu links.

    Adds `urls_json` (TEXT, nullable) to menu_items. Stores JSON dict
    {"en": "...", "de": "..."} for custom links pointing to internal files
    or external resources. NULL for page/post-linked items and pre-2.0.2
    custom links (they keep using the legacy single `url` column).
    """
    from sqlalchemy import text
    if 'menu_items' not in inspector.get_table_names():
        # Fresh install with a brand-new schema — nothing to migrate.
        return False
    if column_exists(inspector, 'menu_items', 'urls_json'):
        return False
    with engine.begin() as conn:
        conn.execute(text('ALTER TABLE menu_items ADD COLUMN urls_json TEXT'))
    return True


# ─── Registry ─────────────────────────────────────────────────────────────
# (key, description, function). Add new migrations at the end.
def migrate_pages_is_posts_page(engine, inspector):
    """2.3.0 — Posts page flag on pages.

    Adds `is_posts_page` (BOOLEAN, default false) to pages. When set, the
    public page appends a paginated list of the latest published posts in
    the page's language below its own content.
    """
    from sqlalchemy import text
    if 'pages' not in inspector.get_table_names():
        # Fresh install with a brand-new schema — nothing to migrate.
        return False
    if column_exists(inspector, 'pages', 'is_posts_page'):
        return False
    with engine.begin() as conn:
        # BOOLEAN works on PostgreSQL; SQLite accepts it too (stored as INTEGER).
        conn.execute(text(
            'ALTER TABLE pages ADD COLUMN is_posts_page BOOLEAN DEFAULT FALSE'
        ))
    return True


def migrate_audit_log_via_agent(engine, inspector):
    """2.5.2 — Agent attribution in the audit log.

    Adds `via_agent` (BOOLEAN, default false) to audit_log. Entries created
    by an AI agent through WebMCP are flagged, so the log can distinguish
    the agent's actions from the human's own actions within the same
    session. Existing rows stay false — before this column the CMS could
    not tell the two apart, so false ("recorded as the user") is the
    honest historical value.
    """
    from sqlalchemy import text
    if 'audit_log' not in inspector.get_table_names():
        # Fresh install with a brand-new schema — nothing to migrate.
        return False
    if column_exists(inspector, 'audit_log', 'via_agent'):
        return False
    with engine.begin() as conn:
        # BOOLEAN works on PostgreSQL; SQLite accepts it too (stored as INTEGER).
        conn.execute(text(
            'ALTER TABLE audit_log ADD COLUMN via_agent BOOLEAN NOT NULL DEFAULT FALSE'
        ))
    return True


def migrate_users_twofa(engine, inspector):
    """2.5.2 — Optional two-factor authentication.

    Adds `twofa_secret`, `twofa_enabled`, `twofa_generation` to users and
    creates the `twofa_recovery_codes` table if the core's create_all has
    not already done so. Everything defaults to "off": existing users and
    fresh installs behave exactly as before until the owner enables the
    feature and a user opts in.
    """
    from sqlalchemy import text
    if 'users' not in inspector.get_table_names():
        # Fresh install with a brand-new schema — nothing to migrate.
        return False
    changed = False
    with engine.begin() as conn:
        if not column_exists(inspector, 'users', 'twofa_secret'):
            conn.execute(text(
                "ALTER TABLE users ADD COLUMN twofa_secret TEXT DEFAULT ''"))
            changed = True
        if not column_exists(inspector, 'users', 'twofa_enabled'):
            # BOOLEAN works on PostgreSQL; SQLite accepts it too (stored as INTEGER).
            conn.execute(text(
                'ALTER TABLE users ADD COLUMN twofa_enabled BOOLEAN NOT NULL DEFAULT FALSE'))
            changed = True
        if not column_exists(inspector, 'users', 'twofa_generation'):
            conn.execute(text(
                'ALTER TABLE users ADD COLUMN twofa_generation INTEGER NOT NULL DEFAULT 0'))
            changed = True
        if 'twofa_recovery_codes' not in inspector.get_table_names():
            # INTEGER PRIMARY KEY auto-increments on SQLite only; on
            # PostgreSQL the id column needs SERIAL, otherwise every
            # insert fails with a NOT NULL violation.
            id_col = ('SERIAL PRIMARY KEY'
                      if engine.dialect.name == 'postgresql'
                      else 'INTEGER PRIMARY KEY')
            conn.execute(text(
                'CREATE TABLE twofa_recovery_codes ('
                f'id {id_col},'
                'user_id INTEGER NOT NULL REFERENCES users (id),'
                'code_hash VARCHAR(300) NOT NULL,'
                'used_at TIMESTAMP)'))
            conn.execute(text(
                'CREATE INDEX ix_twofa_recovery_codes_user_id '
                'ON twofa_recovery_codes (user_id)'))
            changed = True
    return changed


def migrate_twofa_codes_pg_id_fix(engine, inspector):
    """2.5.2 hotfix — repair twofa_recovery_codes on PostgreSQL.

    The first version of migrate_users_twofa created the table with a
    plain 'id INTEGER PRIMARY KEY', which auto-increments on SQLite but
    has no default on PostgreSQL — so saving recovery codes crashed with
    a NOT NULL violation during 2FA setup. This migration attaches a
    proper sequence-backed default to the id column on affected
    PostgreSQL databases. SQLite databases are untouched (the table is
    correct there), and databases created after the fix already have
    SERIAL, whose default makes this a no-op.
    """
    from sqlalchemy import text
    if engine.dialect.name != 'postgresql':
        return False
    if 'twofa_recovery_codes' not in inspector.get_table_names():
        return False
    id_col = next((c for c in inspector.get_columns('twofa_recovery_codes')
                   if c['name'] == 'id'), None)
    if id_col is None or id_col.get('default') or id_col.get('autoincrement'):
        return False  # already healthy
    with engine.begin() as conn:
        conn.execute(text(
            'CREATE SEQUENCE IF NOT EXISTS twofa_recovery_codes_id_seq '
            'OWNED BY twofa_recovery_codes.id'))
        conn.execute(text(
            "SELECT setval('twofa_recovery_codes_id_seq', "
            'COALESCE((SELECT MAX(id) FROM twofa_recovery_codes), 0) + 1, '
            'false)'))
        conn.execute(text(
            'ALTER TABLE twofa_recovery_codes ALTER COLUMN id SET DEFAULT '
            "nextval('twofa_recovery_codes_id_seq')"))
    return True


MIGRATIONS = [
    ('2.0.2_menu_items_urls_json',
     'Add urls_json to menu_items for multilingual custom links',
     migrate_menu_items_urls_json),
    ('2.3.0_pages_is_posts_page',
     'Add is_posts_page to pages (paginated latest-posts listing on a page)',
     migrate_pages_is_posts_page),
    ('2.5.2_audit_log_via_agent',
     'Add via_agent to audit_log (AI-agent attribution for WebMCP actions)',
     migrate_audit_log_via_agent),
    ('2.5.2_users_twofa',
     'Add optional two-factor authentication columns and recovery codes table',
     migrate_users_twofa),
    ('2.5.2_twofa_codes_pg_id_fix',
     'Repair twofa_recovery_codes id auto-increment on PostgreSQL',
     migrate_twofa_codes_pg_id_fix),
]


# ─── Runner ───────────────────────────────────────────────────────────────

def main():
    # ── File lock to prevent concurrent migration runs ────────────────────
    # If the user clicks "Update" twice in a row (network glitch + retry),
    # the updater could spawn two migrate.py processes simultaneously. On
    # SQLite that produces "database is locked" errors; on PostgreSQL the
    # second ALTER TABLE may deadlock waiting for the first. The lock makes
    # the second process wait until the first finishes — at which point its
    # inspector checks will see the migration is already applied and skip.
    #
    # The lock is released automatically if the process is killed (timeout,
    # OS shutdown), so a stuck lock file can't permanently block updates.
    lock_path = os.path.join(os.path.dirname(os.path.abspath(__file__)),
                             '.migrate.lock')
    lock_file = open(lock_path, 'w')
    try:
        _lock(lock_file)
        _run_migrations()
    finally:
        _unlock(lock_file)
        lock_file.close()


def _run_migrations():
    db_url = get_database_url()
    if not db_url:
        print('ERROR: DATABASE_URI not found in environment or .env')
        sys.exit(1)

    print(f'Running migrations on {db_url.split("://")[0]} database...')

    from sqlalchemy import create_engine, inspect
    engine = create_engine(db_url)

    applied = 0
    skipped = 0
    for key, desc, func in MIGRATIONS:
        # Refresh inspector before each migration in case a prior one
        # changed the schema we now need to check.
        inspector = inspect(engine)
        try:
            if func(engine, inspector):
                print(f'  + {key}: {desc}')
                applied += 1
            else:
                skipped += 1
        except Exception as e:
            print(f'  ! {key}: FAILED — {e}')
            sys.exit(1)

    print(f'Migrations: {applied} applied, {skipped} already up to date.')


if __name__ == '__main__':
    main()
