from sqlalchemy import inspect, text

from app.database import engine


def run_migrations() -> None:
    """Apply lightweight schema migrations for existing databases."""
    inspector = inspect(engine)
    tables = inspector.get_table_names()
    dialect = engine.dialect.name

    if "document_templates" in tables:
        columns = {col["name"] for col in inspector.get_columns("document_templates")}
        if "is_public" not in columns:
            default = "0" if dialect == "sqlite" else "false"
            with engine.begin() as conn:
                conn.execute(
                    text(
                        f"ALTER TABLE document_templates "
                        f"ADD COLUMN is_public BOOLEAN NOT NULL DEFAULT {default}"
                    )
                )

    if "verification_tokens" not in tables:
        with engine.begin() as conn:
            if dialect == "postgresql":
                conn.execute(
                    text(
                        """
                        CREATE TABLE verification_tokens (
                            id SERIAL PRIMARY KEY,
                            user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                            code VARCHAR(10) NOT NULL,
                            token_type VARCHAR(20) NOT NULL,
                            expires_at TIMESTAMP NOT NULL,
                            created_at TIMESTAMP NOT NULL
                        )
                        """
                    )
                )
            else:
                conn.execute(
                    text(
                        """
                        CREATE TABLE verification_tokens (
                            id INTEGER NOT NULL PRIMARY KEY,
                            user_id INTEGER NOT NULL,
                            code VARCHAR(10) NOT NULL,
                            token_type VARCHAR(20) NOT NULL,
                            expires_at DATETIME NOT NULL,
                            created_at DATETIME NOT NULL,
                            FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE CASCADE
                        )
                        """
                    )
                )
            conn.execute(
                text(
                    "CREATE INDEX IF NOT EXISTS ix_verification_tokens_code "
                    "ON verification_tokens (code)"
                )
            )

    if "audit_logs" not in tables:
        with engine.begin() as conn:
            if dialect == "postgresql":
                conn.execute(
                    text(
                        """
                        CREATE TABLE audit_logs (
                            id SERIAL PRIMARY KEY,
                            user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
                            username VARCHAR(50),
                            action VARCHAR(100) NOT NULL,
                            resource_type VARCHAR(50),
                            resource_id INTEGER,
                            details JSON,
                            ip_address VARCHAR(45),
                            created_at TIMESTAMP NOT NULL DEFAULT NOW()
                        )
                        """
                    )
                )
            else:
                conn.execute(
                    text(
                        """
                        CREATE TABLE audit_logs (
                            id INTEGER NOT NULL PRIMARY KEY,
                            user_id INTEGER,
                            username VARCHAR(50),
                            action VARCHAR(100) NOT NULL,
                            resource_type VARCHAR(50),
                            resource_id INTEGER,
                            details JSON,
                            ip_address VARCHAR(45),
                            created_at DATETIME NOT NULL,
                            FOREIGN KEY(user_id) REFERENCES users (id) ON DELETE SET NULL
                        )
                        """
                    )
                )
            conn.execute(
                text(
                    "CREATE INDEX IF NOT EXISTS ix_audit_logs_created_at "
                    "ON audit_logs (created_at DESC)"
                )
            )
            conn.execute(
                text(
                    "CREATE INDEX IF NOT EXISTS ix_audit_logs_action "
                    "ON audit_logs (action)"
                )
            )