"""
Database migration tests
=========================

Verify that SQL migrations preserve data integrity.
"""

import os
import subprocess
from pathlib import Path

import pytest

pytestmark = pytest.mark.migration

# Project paths
PROJECT_ROOT = Path(__file__).parent.parent.parent
MIGRATIONS_DIR = PROJECT_ROOT / 'database' / 'migrations'


class TestMigrationFiles:
    """Tests for migration file structure."""

    def test_migrations_directory_exists(self):
        """Migrations directory should exist."""
        assert MIGRATIONS_DIR.exists(), f"Migrations directory not found: {MIGRATIONS_DIR}"

    def test_migration_files_have_sequence(self):
        """Migration files should have sequence numbers."""
        if not MIGRATIONS_DIR.exists():
            pytest.skip("Migrations directory not found")

        migration_files = list(MIGRATIONS_DIR.glob('*.sql'))

        for f in migration_files:
            # Files should start with number like 001_ or 20240101_
            name = f.stem
            assert name[0].isdigit(), f"Migration {f.name} should start with sequence number"

    def test_migration_files_are_valid_sql(self):
        """Migration files should be valid SQL."""
        if not MIGRATIONS_DIR.exists():
            pytest.skip("Migrations directory not found")

        migration_files = list(MIGRATIONS_DIR.glob('*.sql'))

        for f in migration_files:
            content = f.read_text()

            # Should not be empty
            assert content.strip(), f"Migration {f.name} is empty"

            # Should not have obvious syntax errors
            assert content.count('(') == content.count(')'), f"Unbalanced parentheses in {f.name}"


class TestMigrationRunner:
    """Tests for migration runner script."""

    def test_migration_runner_exists(self):
        """Migration runner script should exist."""
        runner = PROJECT_ROOT / 'scripts' / 'run_migration.py'
        assert runner.exists(), f"Migration runner not found: {runner}"

    def test_migration_runner_has_dry_run(self):
        """Migration runner should have dry-run option."""
        runner = PROJECT_ROOT / 'scripts' / 'run_migration.py'

        if not runner.exists():
            pytest.skip("Migration runner not found")

        content = runner.read_text()
        assert 'dry' in content.lower() or 'preview' in content.lower(), \
            "Migration runner should have dry-run option"


class TestMigrationIntegrity:
    """Integration tests for migration integrity."""

    @pytest.mark.slow
    def test_migration_preserves_company_count(self, app, db):
        """Migration should not change company count."""
        # This test would need a staging database

        with app.app_context():
            # Get current count
            from database import Company
            count_before = db.session.query(Company).count()

            # Here you would run migration
            # run_migration("test_migration.sql")

            # Verify count unchanged
            count_after = db.session.query(Company).count()

            assert count_before == count_after, "Migration changed company count"

    @pytest.mark.slow
    def test_migration_preserves_user_count(self, app, db):
        """Migration should not change user count."""
        with app.app_context():
            from database import User
            count_before = db.session.query(User).count()

            # Run migration here

            count_after = db.session.query(User).count()
            assert count_before == count_after