import os
import sys

from sqlalchemy import inspect, text

PROJECT_ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
if PROJECT_ROOT not in sys.path:
    sys.path.insert(0, PROJECT_ROOT)

from app import create_app
from app.extensions import db


def _ensure_telegram_chat_id_column() -> None:
    inspector = inspect(db.engine)
    columns = {column["name"] for column in inspector.get_columns("users")}
    if "telegram_chat_id" not in columns:
        db.session.execute(text("ALTER TABLE users ADD COLUMN telegram_chat_id VARCHAR(64) NULL"))
        db.session.commit()
        inspector = inspect(db.engine)

    indexes = {index["name"] for index in inspector.get_indexes("users")}
    if "ix_users_telegram_chat_id" not in indexes:
        db.session.execute(text("CREATE INDEX ix_users_telegram_chat_id ON users (telegram_chat_id)"))
        db.session.commit()


def _enable_email_mfa_for_all_users() -> None:
    db.session.execute(text("UPDATE users SET two_factor_enabled = 1"))
    try:
        if db.engine.dialect.name == "mysql":
            db.session.execute(text("ALTER TABLE users MODIFY COLUMN two_factor_enabled BOOLEAN NOT NULL DEFAULT 1"))
    finally:
        db.session.commit()


def _ensure_telegram_link_sessions_table() -> None:
    inspector = inspect(db.engine)
    if "telegram_link_sessions" in inspector.get_table_names():
        return

    db.session.execute(
        text(
            """
            CREATE TABLE telegram_link_sessions (
                id INTEGER NOT NULL AUTO_INCREMENT,
                user_id INTEGER NOT NULL,
                token VARCHAR(128) NOT NULL,
                chat_id VARCHAR(64) NULL,
                telegram_user_id BIGINT NULL,
                telegram_username VARCHAR(120) NULL,
                telegram_first_name VARCHAR(120) NULL,
                telegram_phone VARCHAR(50) NULL,
                status VARCHAR(32) NOT NULL,
                expires_at DATETIME NOT NULL,
                completed_at DATETIME NULL,
                consumed_at DATETIME NULL,
                last_error VARCHAR(255) NULL,
                created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                CONSTRAINT fk_telegram_link_sessions_user_id
                    FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE,
                UNIQUE KEY uq_telegram_link_sessions_token (token),
                INDEX ix_telegram_link_sessions_user_id (user_id),
                INDEX ix_telegram_link_sessions_chat_id (chat_id),
                INDEX ix_telegram_link_sessions_telegram_user_id (telegram_user_id),
                INDEX ix_telegram_link_sessions_status (status),
                INDEX ix_telegram_link_sessions_expires_at (expires_at)
            )
            """
        )
    )
    db.session.commit()


def main() -> int:
    app = create_app(os.getenv("FLASK_CONFIG", "development"))
    with app.app_context():
        _ensure_telegram_chat_id_column()
        _ensure_telegram_link_sessions_table()
        _enable_email_mfa_for_all_users()
    print("User security update applied successfully.")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())