"""
Tests for db_manager.py — all PostgreSQL calls are mocked.
"""

import pytest
import json
from unittest.mock import patch, MagicMock, call


@pytest.fixture
def mock_conn():
    """Create a mock database connection context."""
    conn = MagicMock()
    cursor = MagicMock()
    conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
    conn.cursor.return_value.__exit__ = MagicMock(return_value=False)
    return conn, cursor


class TestCreateJob:
    @patch("db_manager.get_conn")
    def test_create_job_basic(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        from db_manager import create_job
        create_job("pdf_abc123", "test.pdf", ip="127.0.0.1")

        cursor.execute.assert_called_once()
        sql = cursor.execute.call_args[0][0]
        params = cursor.execute.call_args[0][1]
        assert "INSERT INTO jobs" in sql
        assert params[0] == "pdf_abc123"
        assert params[1] == "test.pdf"

    @patch("db_manager.get_conn")
    def test_create_job_with_api_key(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        from db_manager import create_job
        create_job("pdf_test", "doc.pdf", api_key="secret_key_123")

        params = cursor.execute.call_args[0][1]
        # api_key_hash should be a hash, not the raw key
        assert params[2] is not None
        assert params[2] != "secret_key_123"
        assert len(params[2]) == 16  # sha256[:16]

    @patch("db_manager.get_conn")
    def test_create_job_no_api_key(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        from db_manager import create_job
        create_job("pdf_test2", "doc.pdf")

        params = cursor.execute.call_args[0][1]
        assert params[2] is None  # api_key_hash


class TestUpdateJobStatus:
    @patch("db_manager.get_conn")
    def test_update_status_simple(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        from db_manager import update_job_status
        update_job_status("pdf_abc", "processing")

        sql = cursor.execute.call_args[0][0]
        assert "UPDATE jobs SET" in sql
        assert "status = %s" in sql

    @patch("db_manager.get_conn")
    def test_update_status_completed_with_results(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        from db_manager import update_job_status
        update_job_status(
            "pdf_abc", "completed",
            result_json={"score": 85},
            score=85, grade="B",
            total_issues=5, critical_count=0,
            error_count=1, warning_count=4,
            processing_time=12.5
        )

        sql = cursor.execute.call_args[0][0]
        assert "completed_at = NOW()" in sql
        assert "score = %s" in sql
        assert "grade = %s" in sql


class TestGetJob:
    @patch("db_manager.get_conn")
    def test_get_job_found(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        cursor.fetchone.return_value = {
            "job_id": "pdf_abc",
            "filename": "test.pdf",
            "status": "completed",
            "score": 85,
        }

        from db_manager import get_job
        result = get_job("pdf_abc")

        assert result["job_id"] == "pdf_abc"
        assert result["score"] == 85

    @patch("db_manager.get_conn")
    def test_get_job_not_found(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        cursor.fetchone.return_value = None

        from db_manager import get_job
        result = get_job("pdf_nonexistent")

        assert result is None


class TestListJobs:
    @patch("db_manager.get_conn")
    def test_list_jobs_default(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        cursor.fetchall.return_value = [
            {"job_id": "pdf_1", "status": "completed"},
            {"job_id": "pdf_2", "status": "processing"},
        ]

        from db_manager import list_jobs
        result = list_jobs()

        assert len(result) == 2
        sql = cursor.execute.call_args[0][0]
        assert "ORDER BY created_at DESC" in sql

    @patch("db_manager.get_conn")
    def test_list_jobs_with_filter(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        cursor.fetchall.return_value = []

        from db_manager import list_jobs
        result = list_jobs(limit=10, offset=5, status_filter="completed")

        sql = cursor.execute.call_args[0][0]
        assert "WHERE status = %s" in sql
        params = cursor.execute.call_args[0][1]
        assert "completed" in params


class TestLogAudit:
    @patch("db_manager.get_conn")
    def test_log_audit_basic(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        from db_manager import log_audit
        log_audit("pdf_test", "upload", details={"size": 1024}, ip="10.0.0.1")

        sql = cursor.execute.call_args[0][0]
        assert "INSERT INTO audit_log" in sql
        params = cursor.execute.call_args[0][1]
        assert params[0] == "pdf_test"
        assert params[1] == "upload"

    @patch("db_manager.get_conn")
    def test_log_audit_no_details(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        from db_manager import log_audit
        log_audit("pdf_test", "download")

        params = cursor.execute.call_args[0][1]
        # details should default to "{}"
        assert json.loads(params[2]) == {}


class TestGetStats:
    @patch("db_manager.get_conn")
    def test_get_stats(self, mock_get_conn):
        conn = MagicMock()
        cursor = MagicMock()
        ctx = MagicMock()
        ctx.__enter__ = MagicMock(return_value=conn)
        ctx.__exit__ = MagicMock(return_value=False)
        mock_get_conn.return_value = ctx
        conn.cursor.return_value.__enter__ = MagicMock(return_value=cursor)
        conn.cursor.return_value.__exit__ = MagicMock(return_value=False)

        cursor.fetchone.return_value = {
            "total_jobs": 100,
            "completed_jobs": 80,
            "failed_jobs": 5,
            "active_jobs": 2,
            "avg_score": 75,
            "avg_processing_time": 15.5,
        }

        from db_manager import get_stats
        result = get_stats()

        assert result["total_jobs"] == 100
        assert result["avg_score"] == 75


class TestGetConnContextManager:
    @patch("db_manager.psycopg2.connect")
    def test_get_conn_commits_on_success(self, mock_connect):
        conn = MagicMock()
        mock_connect.return_value = conn

        from db_manager import get_conn
        with get_conn() as c:
            pass

        conn.commit.assert_called_once()
        conn.close.assert_called_once()

    @patch("db_manager.psycopg2.connect")
    def test_get_conn_rollback_on_error(self, mock_connect):
        conn = MagicMock()
        mock_connect.return_value = conn

        from db_manager import get_conn
        with pytest.raises(ValueError):
            with get_conn() as c:
                raise ValueError("test error")

        conn.rollback.assert_called_once()
        conn.close.assert_called_once()


if __name__ == "__main__":
    pytest.main([__file__, "-v"])