"""Migration: add the `is_team_representative` flag to `team_players`.

A team's responsible ("joueur responsable") is a player an admin names to
manage that team's matches and practices. The flag lives on the roster row,
beside `status` and `position`.

`db.create_all()` creates missing tables and never alters an existing one, so
a database that already has `team_players` needs this script. Fresh installs
get the column from the model.

Where it connects
-----------------
The script finds its own database, so there is no URL to remember:

1. If `DATABASE_URL` is already set, it is used and nothing else is touched.
   That is the production and CI path.
2. Otherwise the local values from `app/.env` are used. If the development
   server in `instance/pgdata/` answers a connection it is reused as-is; if it
   does not, it is (re)started and stopped again when this script exits. This
   is the `LOCAL_PG=true` block of `app/.env.example`.
3. If neither is available the script explains what to set and stops.

So a laptop with the `LOCAL_PG=true` block runs the migration with a single
command:

    python migrations/add_team_representative.py

Usage: python migrations/add_team_representative.py
"""

import os
import subprocess
import sys

PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.insert(0, PROJECT_ROOT)

# Loaded before anything else reads the environment: app.local_postgres reads
# LOCAL_PG_PORT / LOCAL_PG_USER / LOCAL_PG_DATABASE at import time, and without
# this they would fall back to that module's defaults instead of app/.env.
from dotenv import load_dotenv

_ENV_FILE = os.path.join(PROJECT_ROOT, 'app', '.env')
load_dotenv(_ENV_FILE if os.path.exists(_ENV_FILE) else None)

from sqlalchemy import text

from app.extensions import db


def _local_database():
    """The development URL and port, read from the LOCAL_PG_* variables."""
    from app.local_postgres import DATABASE_URL, PORT

    return DATABASE_URL, PORT


def _postgres_answers(url):
    """Whether `url` is a working PostgreSQL connection right now.

    A bare TCP probe is not enough here: a server killed part-way through its
    startup can leave the port listening while every connection is refused,
    and trusting that evidence fails the migration for a reason that looks
    nothing like its cause.
    """
    from sqlalchemy import create_engine

    from app.app import normalise_database_url

    try:
        engine = create_engine(
            normalise_database_url(url), connect_args={'connect_timeout': 2}
        )
    except Exception:
        return False

    try:
        with engine.connect():
            return True
    except Exception:
        return False
    finally:
        engine.dispose()


def _stop_local_server():
    """Fast-stop the local development server, if a postmaster is alive.

    Called only before (re)starting it: `pg_ctl status` calls a half-dead
    postmaster "running", so `start()` would leave the broken one in place.
    """
    from app.local_postgres import DATADIR, _find_binary

    pg_ctl = _find_binary('pg_ctl')
    if not pg_ctl:
        return
    subprocess.run(
        [str(pg_ctl), 'stop', '-m', 'fast', '-D', str(DATADIR)],
        capture_output=True,
        text=True,
    )


def resolve_database_url():
    """Pick the database to migrate.

    Returns:
        tuple[str, str]: the connection URL and a note saying where it came
        from, for the log line.

    Raises:
        SystemExit: when neither a URL nor a usable local server is available.
    """
    explicit = os.environ.get('DATABASE_URL')
    if explicit:
        return explicit, 'DATABASE_URL'

    url, port = _local_database()
    if _postgres_answers(url):
        return url, f'local PostgreSQL already running on port {port}'

    if os.getenv('LOCAL_PG', '').strip().lower() == 'true':
        from app.local_postgres import start

        _stop_local_server()
        return start(), f'local PostgreSQL started on port {port}'

    raise SystemExit(
        'No database to migrate.\n'
        '  Set DATABASE_URL to a connection string, or set LOCAL_PG=true in\n'
        '  app/.env to use the local development server (see app/.env.example,\n'
        '  DEVELOPMENT block).'
    )


def main():
    # A migration must never start the Discord bot: two instances fight over
    # the same gateway session, and neither belongs to a script whose only job
    # is to run an ALTER.
    os.environ['ENABLE_DISCORD_BOT'] = 'false'

    url, source = resolve_database_url()
    os.environ['DATABASE_URL'] = url

    # Imported here, after the environment is settled, so create_app() reads
    # the URL this script resolved rather than a missing one.
    from app.app import create_app

    print(f'[INFO] Database: {source}.')

    app = create_app()
    with app.app_context():
        already_there = db.session.execute(
            text(
                "SELECT 1 FROM information_schema.columns "
                "WHERE table_name = 'team_players' "
                "AND column_name = 'is_team_representative'"
            )
        ).scalar()

        if already_there:
            print("Column 'team_players.is_team_representative' already exists. Skipping.")
            return

        db.session.execute(
            text(
                "ALTER TABLE team_players "
                "ADD COLUMN is_team_representative BOOLEAN NOT NULL DEFAULT FALSE"
            )
        )
        db.session.commit()
        print("Added 'team_players.is_team_representative'.")


if __name__ == '__main__':
    main()