CMT/backend/tests/integration/test_api_database.py

"""Integration tests for API endpoints with database interactions."""

import pytest
from fastapi.testclient import TestClient
from sqlmodel import Session, select

from app.schemas.models import User, Partner, Product, Transaction, Credit, Inventory, Payment
from app.schemas.base import UserRole, PartnerType, TransactionType, TransactionStatus, PaymentMethod


class TestUserAPIIntegration:
    """Test User API endpoints with database integration."""

    def test_create_user_endpoint_with_database(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test creating a user through API endpoint and verifying database storage."""
        user_data = {
            "username": "api_test_user",
            "password": "test_password",
            "role": "READ_ONLY"
        }
        
        response = integration_client.post("/api/v1/users/", json=user_data, headers=integration_admin_token)
        assert response.status_code == 201
        
        created_user = response.json()
        assert created_user["username"] == "api_test_user"
        assert created_user["role"] == "read_only"
        assert "id" in created_user

    def test_get_user_endpoint_with_database(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test retrieving a user through API endpoint from database."""
        # Create user directly in database
        user = User(username="db_user", password_hash="hashed", role=UserRole.ADMIN)
        integration_session.add(user)
        integration_session.commit()
        integration_session.refresh(user)
        
        # Retrieve through API
        response = integration_client.get(f"/api/v1/users/{user.id}", headers=integration_admin_token)
        assert response.status_code == 200
        
        returned_user = response.json()
        assert returned_user["username"] == "db_user"
        assert returned_user["role"] == "admin"

    def test_update_user_endpoint_with_database(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test updating a user through API endpoint and verifying database changes."""
        # Create user directly in database
        user = User(username="update_user", password_hash="hashed", role=UserRole.READ_ONLY)
        integration_session.add(user)
        integration_session.commit()
        integration_session.refresh(user)
        
        # Update through API
        update_data = {"role": "write"}
        response = integration_client.put(f"/api/v1/users/{user.id}", json=update_data, headers=integration_admin_token)
        assert response.status_code == 200
        
        # Verify in database
        integration_session.refresh(user)
        assert user.role == UserRole.WRITE

    def test_delete_user_endpoint_with_database(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test deleting a user through API endpoint and verifying database removal."""
        # Create user directly in database
        user = User(username="delete_user", password_hash="hashed", role=UserRole.READ_ONLY)
        integration_session.add(user)
        integration_session.commit()
        user_id = user.id
        
        # Delete through API
        response = integration_client.delete(f"/api/v1/users/{user_id}", headers=integration_admin_token)
        assert response.status_code == 200
        
        # Verify removed from database
        deleted_user = integration_session.get(User, user_id)
        assert deleted_user is None


class TestPartnerAPIIntegration:
    """Test Partner API endpoints with database integration."""

    def test_create_partner_endpoint_with_database(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test creating a partner through API endpoint and verifying database storage."""
        partner_data = {
            "tin_number": 123456789,
            "names": "Test Partner Co.",
            "type": "SUPPLIER",
            "phone_number": "1234567890"
        }
        
        response = integration_client.post("/api/v1/partners/", json=partner_data, headers=integration_admin_token)
        assert response.status_code == 201
        
        created_partner = response.json()
        assert created_partner["tin_number"] == 123456789
        assert created_partner["names"] == "Test Partner Co."
        assert created_partner["type"] == "SUPPLIER"

    def test_get_partners_endpoint_with_database(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test retrieving partners through API endpoint from database."""
        # Create partner directly in database
        partner = Partner(
            tin_number=987654321,
            names="DB Partner",
            type=PartnerType.CLIENT,
            phone_number="9876543210"
        )
        integration_session.add(partner)
        integration_session.commit()
        
        # Retrieve through API
        response = integration_client.get("/api/v1/partners/", headers=integration_admin_token)
        assert response.status_code == 200
        
        partners = response.json()
        assert len(partners) >= 1
        partner_names = [p["names"] for p in partners]
        assert "DB Partner" in partner_names

    def test_partner_unique_constraint_through_api(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test partner unique constraint enforcement through API."""
        # Create partner directly in database
        partner = Partner(
            tin_number=999999999,
            names="Unique Partner",
            type=PartnerType.CLIENT,
            phone_number="5555555555"
        )
        integration_session.add(partner)
        integration_session.commit()
        
        # Try to create duplicate through API
        duplicate_data = {
            "tin_number": 999999999,
            "names": "Different Name",
            "type": "SUPPLIER",
            "phone_number": "8888888888"
        }
        
        response = integration_client.post("/api/v1/partners/", json=duplicate_data, headers=integration_admin_token)
        assert response.status_code == 400  # Should fail due to unique constraint


class TestTransactionAPIIntegration:
    """Test Transaction API endpoints with database integration."""

    def test_create_transaction_with_valid_relationships(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test creating a transaction through API with valid partner and user relationships."""
        # Create required entities in database
        partner = Partner(tin_number=111111111, names="Trans Partner", type=PartnerType.CLIENT, phone_number="1111111111")
        user = User(username="trans_user", password_hash="hashed", role=UserRole.WRITE)
        integration_session.add(partner)
        integration_session.add(user)
        integration_session.commit()
        integration_session.refresh(partner)
        integration_session.refresh(user)
        
        transaction_data = {
            "amount": 1000.50,
            "transaction_type": "SALE",
            "status": "COMPLETED",
            "partner_id": partner.id,
            "user_id": user.id
        }
        
        response = integration_client.post("/api/v1/transactions/", json=transaction_data, headers=integration_admin_token)
        assert response.status_code == 201
        
        created_transaction = response.json()
        assert created_transaction["amount"] == 1000.50
        assert created_transaction["partner_id"] == partner.id

    def test_create_transaction_with_invalid_partner(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test creating a transaction with invalid partner ID through API."""
        transaction_data = {
            "amount": 500.00,
            "transaction_type": "PURCHASE",
            "status": "PENDING",
            "partner_id": 99999,  # Invalid partner ID
            "user_id": 1
        }
        
        response = integration_client.post("/api/v1/transactions/", json=transaction_data, headers=integration_admin_token)
        assert response.status_code == 400  # Should fail due to foreign key constraint

    def test_get_transactions_by_partner(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test retrieving transactions filtered by partner through API."""
        # Create test data
        partner1 = Partner(tin_number=222222222, names="Partner 1", type=PartnerType.CLIENT, phone_number="2222222222")
        partner2 = Partner(tin_number=333333333, names="Partner 2", type=PartnerType.SUPPLIER, phone_number="3333333333")
        user = User(username="filter_user", password_hash="hashed", role=UserRole.WRITE)
        
        integration_session.add_all([partner1, partner2, user])
        integration_session.commit()
        integration_session.refresh(partner1)
        integration_session.refresh(partner2)
        integration_session.refresh(user)
        
        # Create transactions for both partners
        assert partner1.id is not None
        assert partner2.id is not None
        assert user.id is not None
        
        trans1 = Transaction(
            total_amount=100, transcation_type=TransactionType.SALE, transaction_status=TransactionStatus.PAID,
            partner_id=partner1.id, created_by=user.id, updated_by=user.id
        )
        trans2 = Transaction(
            total_amount=200, transcation_type=TransactionType.PURCHASE, transaction_status=TransactionStatus.UNPAID,
            partner_id=partner2.id, created_by=user.id, updated_by=user.id
        )
        integration_session.add_all([trans1, trans2])
        integration_session.commit()
        
        # Filter transactions by partner1
        response = integration_client.get(f"/api/v1/transactions/?partner_id={partner1.id}", headers=integration_admin_token)
        assert response.status_code == 200
        
        transactions = response.json()
        assert len(transactions) == 1
        assert transactions[0]["partner_id"] == partner1.id


class TestInventoryAPIIntegration:
    """Test Inventory API endpoints with database integration."""

    def test_create_inventory_with_product_relationship(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test creating inventory through API with valid product relationship."""
        # Create product in database
        product = Product(product_code="TST001", product_name="Test Product", purchase_price=90, selling_price=100)
        integration_session.add(product)
        integration_session.commit()
        integration_session.refresh(product)
        
        inventory_data = {
            "total_qty": 50,
            "product_id": product.id
        }
        
        response = integration_client.post("/api/v1/inventory/", json=inventory_data, headers=integration_admin_token)
        assert response.status_code == 201
        
        created_inventory = response.json()
        assert created_inventory["total_qty"] == 50
        assert created_inventory["product_id"] == product.id

    def test_inventory_unique_product_constraint_through_api(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test inventory unique product constraint enforcement through API."""
        # Create product and inventory directly in database
        product = Product(product_code="UNQ001", product_name="Unique Product", purchase_price=180, selling_price=200)
        integration_session.add(product)
        integration_session.commit()
        integration_session.refresh(product)
        
        assert product.id is not None
        inventory = Inventory(
            total_qty=30, product_id=product.id
        )
        integration_session.add(inventory)
        integration_session.commit()
        
        # Try to create duplicate inventory for same product through API
        duplicate_data = {
            "total_qty": 20,
            "product_id": product.id
        }
        
        response = integration_client.post("/api/v1/inventory/", json=duplicate_data, headers=integration_admin_token)
        assert response.status_code == 400  # Should fail due to unique constraint


class TestCreditAPIIntegration:
    """Test Credit API endpoints with database integration."""

    def test_create_credit_with_relationships(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test creating credit through API with valid partner relationship."""
        # Create partner in database
        partner = Partner(tin_number=444444444, names="Credit Partner", type=PartnerType.CLIENT, phone_number="4444444444")
        integration_session.add(partner)
        integration_session.commit()
        integration_session.refresh(partner)
        
        credit_data = {
            "amount": 5000.00,
            "due_date": "2024-12-31",
            "interest_rate": 5.5,
            "partner_id": partner.id
        }
        
        response = integration_client.post("/api/v1/credit/", json=credit_data, headers=integration_admin_token)
        assert response.status_code == 201
        
        created_credit = response.json()
        assert created_credit["amount"] == 5000.00
        assert created_credit["partner_id"] == partner.id


class TestAPITransactionRollback:
    """Test API transaction rollback behavior on database errors."""

    def test_api_transaction_rollback_on_error(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test that API transactions are properly rolled back on validation errors."""
        # Create a user first
        user = User(username="rollback_test", password_hash="hashed", role=UserRole.ADMIN)
        integration_session.add(user)
        integration_session.commit()
        
        # Try to create duplicate user (should fail)
        duplicate_data = {
            "username": "rollback_test",
            "password": "different_password",
            "role": "WRITE"
        }
        
        response = integration_client.post("/api/v1/users/", json=duplicate_data, headers=integration_admin_token)
        assert response.status_code == 400
        
        # Verify original user is still intact
        original_user = integration_session.get(User, user.id)
        assert original_user is not None
        assert original_user.role == UserRole.ADMIN

    def test_complex_operation_rollback(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test rollback behavior for complex operations involving multiple entities."""
        # Create valid partner and user
        partner = Partner(tin_number=555555555, names="Complex Partner", type=PartnerType.CLIENT, phone_number="5555555555")
        user = User(username="complex_user", password_hash="hashed", role=UserRole.WRITE)
        integration_session.add_all([partner, user])
        integration_session.commit()
        integration_session.refresh(partner)
        integration_session.refresh(user)
        
        # Try to create transaction with invalid data (should trigger rollback)
        invalid_transaction_data = {
            "amount": -1000.0,  # Negative amount should fail validation
            "transaction_type": "INVALID_TYPE",
            "status": "COMPLETED",
            "partner_id": partner.id,
            "user_id": user.id
        }
        
        response = integration_client.post("/api/v1/transactions/", json=invalid_transaction_data, headers=integration_admin_token)
        assert response.status_code in [400, 422]  # Should fail validation
        
        # Verify no partial data was committed
        transactions = integration_session.exec(select(Transaction)).all()
        assert len([t for t in transactions if t.partner_id == partner.id]) == 0


class TestAPIConstraintValidation:
    """Test database constraint validation through API endpoints."""

    def test_foreign_key_validation_through_api(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test foreign key constraint validation through API."""
        # Try to create payment with invalid transaction ID
        payment_data = {
            "amount": 100.00,
            "method": "CASH",
            "transaction_id": 99999  # Invalid transaction ID
        }
        
        response = integration_client.post("/api/v1/payments/", json=payment_data, headers=integration_admin_token)
        assert response.status_code in [400, 422]  # Should fail due to foreign key constraint

    def test_data_validation_through_api(self, integration_client: TestClient, integration_session: Session, integration_admin_token):
        """Test data type and format validation through API."""
        # Try to create user with invalid data
        invalid_user_data = {
            "username": "",  # Empty username should fail validation
            "password": "short",  # Too short password
            "role": "INVALID_ROLE"  # Invalid role
        }
        
        response = integration_client.post("/api/v1/users/", json=invalid_user_data, headers=integration_admin_token)
        assert response.status_code == 422  # Should fail validation