"""
Test Database Query CLI Commands - Issue #14

This test validates the implementation of database query CLI commands for
delivering the core USP "Relational Document Metadata" through queryable
database interface.

Requirements tested:
- markitect query <sql> command with safety constraints
- markitect schema command for database structure inspection
- markitect db-data <file> command for file metadata display (updated from metadata in Issue #38)
- Multiple output format support (table, JSON, YAML)
- Read-only access and SQL injection protection
- Integration with existing DatabaseManager
"""

import pytest
import tempfile
import os
import json
from pathlib import Path
from click.testing import CliRunner
from unittest.mock import patch, MagicMock

# Import the CLI module (will be extended during implementation)
try:
    from markitect.cli import cli, query_command, schema_command, db_data_command
except ImportError:
    # Commands don't exist yet - this is expected in TDD
    from markitect.cli import cli


class TestQueryCommand:
    """Test suite for markitect query command."""

    def setup_method(self):
        """Set up test fixtures."""
        self.runner = CliRunner()

    def test_query_command_exists(self):
        """
        Test that the query command is accessible.

        Issue #14: SQL query interface with safety constraints
        """
        # Test that the main query command exists and is callable
        result = self.runner.invoke(cli, ['db-query', '--help'])
        assert result.exit_code == 0
        assert 'query' in result.output.lower()
        assert 'execute sql query' in result.output.lower() or 'sql' in result.output.lower()

    def test_query_command_executes_select(self):
        """
        Test that query command can execute SELECT statements.

        Issue #14: SQL query interface with safety constraints
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            # Mock query result
            mock_db_instance.execute_query.return_value = [
                {'id': 1, 'filename': 'test.md', 'created_at': '2025-09-25'}
            ]

            result = self.runner.invoke(cli, ['db-query', 'SELECT * FROM markdown_files LIMIT 1'])

            assert result.exit_code == 0
            assert 'test.md' in result.output

    def test_database_query_command_prevents_dangerous_write_operations(self):
        """
        Test that query command blocks dangerous SQL operations.

        Issue #14: SQL query interface with safety constraints
        """
        dangerous_queries = [
            'DROP TABLE markdown_files',
            'DELETE FROM markdown_files',
            'UPDATE markdown_files SET filename = "hacked"',
            'INSERT INTO markdown_files VALUES (1, "hack")',
            'CREATE TABLE hack (id INT)',
            'ALTER TABLE markdown_files ADD COLUMN hack TEXT'
        ]

        for dangerous_sql in dangerous_queries:
            result = self.runner.invoke(cli, ['db-query', dangerous_sql])
            assert result.exit_code != 0
            assert ('not allowed' in result.output.lower() or
                    'allowed' in result.output.lower() or
                    'denied' in result.output.lower() or
                    'read-only' in result.output.lower())


    def test_query_command_handles_empty_results(self):
        """
        Test that query command handles empty query results gracefully.

        Issue #14: SQL query interface with safety constraints
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            # Mock empty result
            mock_db_instance.execute_query.return_value = []

            result = self.runner.invoke(cli, ['db-query', 'SELECT * FROM markdown_files WHERE id = -1'])

            assert result.exit_code == 0
            assert 'no results' in result.output.lower() or len(result.output.strip()) == 0

    def test_query_command_handles_invalid_sql(self):
        """
        Test that query command handles invalid SQL gracefully.

        Issue #14: SQL query interface with safety constraints
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            # Mock SQL error
            mock_db_instance.execute_query.side_effect = Exception("SQL syntax error")

            result = self.runner.invoke(cli, ['db-query', 'SELECT * FROM nonexistent_table'])

            assert result.exit_code != 0
            assert 'error' in result.output.lower()


class TestSchemaCommand:
    """Test suite for markitect schema command."""

    def setup_method(self):
        """Set up test fixtures."""
        self.runner = CliRunner()

    def test_schema_command_exists(self):
        """
        Test that the schema command is accessible.

        Issue #14: Schema inspection commands
        """
        result = self.runner.invoke(cli, ['db-schema', '--help'])
        assert result.exit_code == 0
        assert 'schema' in result.output.lower()
        assert ('database' in result.output.lower() or
                'table' in result.output.lower() or
                'structure' in result.output.lower())

    def test_schema_command_shows_database_structure(self):
        """
        Test that schema command displays database structure.

        Issue #14: Schema inspection commands
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            # Mock schema information
            mock_db_instance.get_schema.return_value = {
                'markdown_files': {
                    'columns': [
                        {'name': 'id', 'type': 'INTEGER', 'primary_key': True},
                        {'name': 'filename', 'type': 'TEXT', 'primary_key': False},
                        {'name': 'front_matter', 'type': 'TEXT', 'primary_key': False},
                        {'name': 'content', 'type': 'TEXT', 'primary_key': False},
                        {'name': 'created_at', 'type': 'TIMESTAMP', 'primary_key': False}
                    ]
                }
            }

            result = self.runner.invoke(cli, ['db-schema'])

            assert result.exit_code == 0
            assert 'markdown_files' in result.output
            assert 'filename' in result.output
            assert 'front_matter' in result.output

    def test_schema_command_supports_output_formats(self):
        """
        Test that schema command supports multiple output formats.

        Issue #14: Multiple output format support
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            mock_schema = {
                'markdown_files': {
                    'columns': [{'name': 'id', 'type': 'INTEGER', 'primary_key': True}]
                }
            }
            mock_db_instance.get_schema.return_value = mock_schema

            # Test JSON format
            result = self.runner.invoke(cli, ['db-schema', '--format', 'json'])
            assert result.exit_code == 0

            # Test YAML format
            result = self.runner.invoke(cli, ['db-schema', '--format', 'yaml'])
            assert result.exit_code == 0


class TestDbDataCommand:
    """Test suite for markitect db-data command."""

    def setup_method(self):
        """Set up test fixtures."""
        self.runner = CliRunner()

    def test_db_data_command_exists(self):
        """
        Test that the db-data command is accessible.

        Issue #14: Metadata display functionality (updated for Issue #38)
        """
        result = self.runner.invoke(cli, ['db-data', '--help'])
        assert result.exit_code == 0
        assert 'db-data' in result.output.lower()
        assert 'file' in result.output.lower()

    def test_db_data_command_displays_file_info(self):
        """
        Test that db-data command displays file metadata and front matter.

        Issue #14: Metadata display functionality (updated for Issue #38)
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            # Mock file metadata
            mock_db_instance.get_markdown_file.return_value = {
                'id': 1,
                'filename': 'test.md',
                'front_matter': '{"title": "Test Document", "author": "Test Author"}',
                'content': '# Test\nContent here',
                'created_at': '2025-09-25 12:00:00'
            }

            result = self.runner.invoke(cli, ['db-data', 'test.md'])

            assert result.exit_code == 0
            assert 'test.md' in result.output
            assert 'Test Document' in result.output
            assert 'Test Author' in result.output

    def test_db_data_command_handles_missing_file(self):
        """
        Test that db-data command handles missing files gracefully.

        Issue #14: Metadata display functionality (updated for Issue #38)
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            # Mock file not found
            mock_db_instance.get_markdown_file.return_value = None

            result = self.runner.invoke(cli, ['db-data', 'nonexistent.md'])

            assert result.exit_code != 0
            assert 'not found' in result.output.lower()

    def test_db_data_command_supports_output_formats(self):
        """
        Test that db-data command supports multiple output formats.

        Issue #14: Multiple output format support (updated for Issue #38)
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance

            mock_metadata = {
                'filename': 'test.md',
                'front_matter': '{"title": "Test"}',
                'created_at': '2025-09-25'
            }
            mock_db_instance.get_markdown_file.return_value = mock_metadata

            # Test JSON format
            result = self.runner.invoke(cli, ['db-data', 'test.md', '--format', 'json'])
            assert result.exit_code == 0

            # Test YAML format
            result = self.runner.invoke(cli, ['db-data', 'test.md', '--format', 'yaml'])
            assert result.exit_code == 0


class TestDatabaseIntegration:
    """Test suite for database integration functionality."""

    def test_database_manager_query_method(self):
        """
        Test that DatabaseManager supports query execution.

        Issue #14: Integration with existing DatabaseManager functionality
        """
        # This test ensures the DatabaseManager has or will have query capabilities
        from markitect.database import DatabaseManager

        # The DatabaseManager should have a method for executing queries
        db_manager = DatabaseManager(':memory:')
        db_manager.initialize_database()

        # This method will be implemented as part of Issue #14
        assert hasattr(db_manager, 'execute_query') or hasattr(db_manager, 'query')

    def test_database_manager_schema_inspection(self):
        """
        Test that DatabaseManager supports schema inspection.

        Issue #14: Schema inspection commands
        """
        from markitect.database import DatabaseManager

        db_manager = DatabaseManager(':memory:')
        db_manager.initialize_database()

        # The DatabaseManager should have a method for getting schema info
        assert hasattr(db_manager, 'get_schema') or hasattr(db_manager, 'describe_schema')


class TestQuerySafety:
    """Test suite for SQL query safety and security."""


    def test_database_query_enforces_read_only_access_restrictions(self):
        """
        Test that only read operations are allowed.

        Issue #14: SQL query interface with safety constraints
        """
        write_operations = [
            'INSERT', 'UPDATE', 'DELETE', 'DROP', 'CREATE', 'ALTER', 'TRUNCATE'
        ]

        runner = CliRunner()

        for operation in write_operations:
            query = f"{operation} markdown_files"
            result = runner.invoke(cli, ['db-query', query])

            # Should be rejected
            assert result.exit_code != 0 or 'not allowed' in result.output.lower()


class TestQueryTemplates:
    """Test suite for query templates and examples."""

    def test_common_query_templates_available(self):
        """
        Test that common query templates are available.

        Issue #14: Query templates and examples
        """
        runner = CliRunner()

        # Test that templates or examples are shown in help
        result = runner.invoke(cli, ['db-query', '--help'])
        assert result.exit_code == 0

        # Should mention examples or templates
        assert ('example' in result.output.lower() or
                'template' in result.output.lower() or
                'SELECT' in result.output)

    def test_template_execution(self):
        """
        Test that query templates can be executed.

        Issue #14: Query templates and examples
        """
        with patch('markitect.cli.DatabaseManager') as mock_db_mgr:
            mock_db_instance = MagicMock()
            mock_db_mgr.return_value = mock_db_instance
            mock_db_instance.execute_query.return_value = []

            runner = CliRunner()

            # Test common templates that should work
            common_queries = [
                'SELECT COUNT(*) FROM markdown_files',
                'SELECT filename FROM markdown_files',
                'SELECT * FROM markdown_files ORDER BY created_at DESC'
            ]

            for query in common_queries:
                result = runner.invoke(cli, ['db-query', query])
                assert result.exit_code == 0