import sys
from pathlib import Path

# Add src to python path
sys.path.insert(0, str(Path(__file__).resolve().parents[1] / "src"))

import pytest
from unittest.mock import MagicMock, patch, call
from actions.database import (
    handle_database_create,
    handle_database_drop,
    handle_database_user_create,
    handle_database_user_delete,
    handle_database_user_reset_password,
    handle_database_user_update_grants,
)


@pytest.fixture(autouse=True)
def mock_load_config():
    with patch("actions.database.load_config") as mock_load, \
         patch("shutil.which", return_value="/usr/bin/mysql") as mock_which:
        config = MagicMock()
        config.mysql_user = "root"
        config.mysql_password = "root_password"
        config.mysql_socket = "/var/run/mysqld/mysqld.sock"
        mock_load.return_value = config
        yield mock_load


# 1. handle_database_create tests
def test_database_create_success():
    payload = {"name": "testdb"}
    with patch("actions.database.db_exists", return_value=False) as mock_exists, \
         patch("subprocess.run") as mock_run:
        
        mock_sub = MagicMock()
        mock_sub.returncode = 0
        mock_run.return_value = mock_sub
        
        res = handle_database_create(payload)
        assert res["success"] is True
        assert res["database"] == "wolf_testdb"
        mock_exists.assert_called_once()
        
        # Verify CLI args do NOT contain root_password
        cmd = mock_run.call_args[0][0]
        assert "root_password" not in cmd
        
        # Verify SQL statement contains creation query
        sql = mock_run.call_args[1]["input"]
        assert "CREATE DATABASE wolf_testdb" in sql


def test_database_create_invalid_name():
    payload = {"name": "invalid-name;drop table;"}
    res = handle_database_create(payload)
    assert res["success"] is False
    assert "Invalid database name" in res["error"]


def test_database_create_already_exists():
    payload = {"name": "testdb"}
    with patch("actions.database.db_exists", return_value=True):
        res = handle_database_create(payload)
        assert res["success"] is False
        assert "already exists" in res["error"]


# 2. handle_database_drop tests
def test_database_drop_success():
    payload = {"name": "wolf_testdb"}
    with patch("actions.database.db_exists", return_value=True) as mock_exists, \
         patch("subprocess.run") as mock_run:
        
        mock_sub = MagicMock()
        mock_sub.returncode = 0
        mock_run.return_value = mock_sub
        
        res = handle_database_drop(payload)
        assert res["success"] is True
        assert res["database"] == "wolf_testdb"
        
        cmd = mock_run.call_args[0][0]
        assert "root_password" not in cmd
        sql = mock_run.call_args[1]["input"]
        assert "DROP DATABASE wolf_testdb" in sql


def test_database_drop_invalid_prefix():
    payload = {"name": "testdb"}
    res = handle_database_drop(payload)
    assert res["success"] is False
    assert "must start with" in res["error"]


# 3. handle_database_user_create tests
def test_database_user_create_success():
    payload = {
        "username": "wolf_user",
        "password": "secret_user_pass",
        "database": "wolf_db"
    }
    with patch("actions.database.user_exists", return_value=False) as mock_exists, \
         patch("subprocess.run") as mock_run:
        
        mock_sub = MagicMock()
        mock_sub.returncode = 0
        mock_run.return_value = mock_sub
        
        res = handle_database_user_create(payload)
        assert res["success"] is True
        assert res["username"] == "wolf_user"
        
        # Verify CLI args do NOT contain root_password or user password
        cmd = mock_run.call_args[0][0]
        assert "root_password" not in cmd
        assert "secret_user_pass" not in cmd
        
        # Verify SQL statement contains query and password
        sql = mock_run.call_args[1]["input"]
        assert "CREATE USER 'wolf_user'@'localhost' IDENTIFIED BY 'secret_user_pass'" in sql
        assert "GRANT ALL PRIVILEGES ON wolf_db.* TO 'wolf_user'@'localhost'" in sql


def test_database_user_create_invalid_prefix():
    payload = {
        "username": "user",
        "password": "pass",
        "database": "wolf_db"
    }
    res = handle_database_user_create(payload)
    assert res["success"] is False
    assert "Username must start with" in res["error"]


# 4. handle_database_user_delete tests
def test_database_user_delete_success():
    payload = {"username": "wolf_user"}
    with patch("actions.database.user_exists", return_value=True), \
         patch("subprocess.run") as mock_run:
        
        mock_sub = MagicMock()
        mock_sub.returncode = 0
        mock_run.return_value = mock_sub
        
        res = handle_database_user_delete(payload)
        assert res["success"] is True
        assert res["username"] == "wolf_user"
        
        sql = mock_run.call_args[1]["input"]
        assert "DROP USER 'wolf_user'@'localhost'" in sql


def test_database_user_delete_invalid_prefix():
    payload = {"username": "user"}
    res = handle_database_user_delete(payload)
    assert res["success"] is False
    assert "must start with" in res["error"]


# 5. handle_database_user_reset_password tests
def test_database_user_reset_password_success():
    payload = {
        "username": "wolf_user",
        "new_password": "new_secret_pass"
    }
    with patch("actions.database.user_exists", return_value=True), \
         patch("subprocess.run") as mock_run:
        
        mock_sub = MagicMock()
        mock_sub.returncode = 0
        mock_run.return_value = mock_sub
        
        res = handle_database_user_reset_password(payload)
        assert res["success"] is True
        assert res["username"] == "wolf_user"
        
        cmd = mock_run.call_args[0][0]
        assert "new_secret_pass" not in cmd
        sql = mock_run.call_args[1]["input"]
        assert "ALTER USER 'wolf_user'@'localhost' IDENTIFIED BY 'new_secret_pass'" in sql


def test_database_user_reset_password_invalid_prefix():
    payload = {
        "username": "user",
        "new_password": "new_secret_pass"
    }
    res = handle_database_user_reset_password(payload)
    assert res["success"] is False
    assert "must start with" in res["error"]


# 6. handle_database_user_update_grants tests
def test_database_user_update_grants_success():
    payload = {
        "username": "wolf_user",
        "database": "wolf_db",
        "privileges": "SELECT,INSERT,UPDATE"
    }
    with patch("actions.database.user_exists", return_value=True), \
         patch("actions.database.db_exists", return_value=True), \
         patch("subprocess.run") as mock_run:
        
        mock_sub = MagicMock()
        mock_sub.returncode = 0
        mock_run.return_value = mock_sub
        
        res = handle_database_user_update_grants(payload)
        assert res["success"] is True
        assert res["username"] == "wolf_user"
        
        # Verify 2 calls (REVOKE and GRANT)
        assert mock_run.call_count == 2
        
        # Verify first call is REVOKE
        revoke_sql = mock_run.call_args_list[0][1]["input"]
        assert "REVOKE ALL PRIVILEGES ON wolf_db.* FROM 'wolf_user'@'localhost'" in revoke_sql
        
        # Verify second call is GRANT with privileges list
        grant_sql = mock_run.call_args_list[1][1]["input"]
        assert "GRANT SELECT, INSERT, UPDATE ON wolf_db.* TO 'wolf_user'@'localhost'" in grant_sql


def test_database_user_update_grants_invalid_privs():
    payload = {
        "username": "wolf_user",
        "database": "wolf_db",
        "privileges": "SELECT,INVALID_PRIV"
    }
    res = handle_database_user_update_grants(payload)
    assert res["success"] is False
    assert "Invalid privilege(s)" in res["error"]