"""Integration tests for SQLModel database operations."""

import pytest
from sqlalchemy.exc import IntegrityError
from sqlmodel import Session, select

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


class TestUserModel:
    """Test User model database operations."""

    def test_user_creation_and_retrieval(self, integration_session: Session):
        """Test creating and retrieving users from database."""
        user = User(username="testuser", password_hash="hashed", role=UserRole.ADMIN)
        
        integration_session.add(user)
        integration_session.commit()
        integration_session.refresh(user)
        
        # Verify user was created with ID
        assert user.id is not None
        assert user.username == "testuser"
        assert user.role == UserRole.ADMIN
        
        # Verify retrieval from database
        retrieved_user = integration_session.get(User, user.id)
        assert retrieved_user is not None
        assert retrieved_user.username == "testuser"

    def test_user_unique_username_constraint(self, integration_session: Session):
        """Test that duplicate usernames are rejected."""
        user1 = User(username="duplicate", password_hash="hash1", role=UserRole.ADMIN)
        user2 = User(username="duplicate", password_hash="hash2", role=UserRole.READ_ONLY)
        
        integration_session.add(user1)
        integration_session.commit()
        
        integration_session.add(user2)
        with pytest.raises(IntegrityError):
            integration_session.commit()

    def test_user_role_defaults(self, integration_session: Session):
        """Test user role default values."""
        user = User(username="defaultrole", password_hash="hash")
        
        integration_session.add(user)
        integration_session.commit()
        integration_session.refresh(user)
        
        # Check default role is READ_ONLY
        assert user.role == UserRole.READ_ONLY


class TestPartnerModel:
    """Test Partner model database operations."""

    def test_partner_creation_and_types(self, integration_session: Session):
        """Test creating partners with different types."""
        partners = [
            Partner(tin_number=123456789, names="Client Partner", type=PartnerType.CLIENT, phone_number="1234567890"),
            Partner(tin_number=987654321, names="Supplier Partner", type=PartnerType.SUPPLIER, phone_number="0987654321"),
        ]
        
        for partner in partners:
            integration_session.add(partner)
        integration_session.commit()
        
        # Verify both partners were created
        client_partner = integration_session.exec(
            select(Partner).where(Partner.type == PartnerType.CLIENT)
        ).first()
        supplier_partner = integration_session.exec(
            select(Partner).where(Partner.type == PartnerType.SUPPLIER)
        ).first()
        
        assert client_partner is not None
        assert supplier_partner is not None
        assert client_partner.names == "Client Partner"
        assert supplier_partner.names == "Supplier Partner"

    def test_partner_unique_tin_constraint(self, integration_session: Session):
        """Test that duplicate TIN numbers are rejected."""
        partner1 = Partner(tin_number=123456789, names="Partner 1", type=PartnerType.CLIENT, phone_number="1234567890")
        partner2 = Partner(tin_number=123456789, names="Partner 2", type=PartnerType.SUPPLIER, phone_number="0987654321")
        
        integration_session.add(partner1)
        integration_session.commit()
        
        integration_session.add(partner2)
        with pytest.raises(IntegrityError):
            integration_session.commit()


class TestProductModel:
    """Test Product model database operations."""

    def test_product_creation(self, integration_session: Session):
        """Test basic product creation."""
        product = Product(
            product_code="TEST001",
            product_name="Test Product",
            purchase_price=100,
            selling_price=120
        )
        
        integration_session.add(product)
        integration_session.commit()
        integration_session.refresh(product)
        
        assert product.id is not None
        assert product.product_name == "Test Product"
        assert product.product_code == "TEST001"

    def test_product_unique_name_constraint(self, integration_session: Session):
        """Test that duplicate product names are rejected."""
        product1 = Product(
            product_code="DUP001",
            product_name="Duplicate Product",
            purchase_price=100,
            selling_price=120
        )
        product2 = Product(
            product_code="DUP002",
            product_name="Duplicate Product", # Same name, different code
            purchase_price=150,
            selling_price=180
        )
        
        integration_session.add(product1)
        integration_session.commit()
        
        integration_session.add(product2)
        with pytest.raises(IntegrityError):
            integration_session.commit()


class TestTransactionModel:
    """Test Transaction model with relationships."""

    def test_transaction_creation(self, integration_session: Session):
        """Test creating transaction with valid relationships."""
        # Create required entities
        user = User(username="trans_user", password_hash="hash", role=UserRole.ADMIN)
        partner = Partner(
            tin_number=123456789, 
            names="Transaction Partner", 
            type=PartnerType.CLIENT,
            phone_number="1234567890"
        )
        
        integration_session.add(user)
        integration_session.add(partner)
        integration_session.commit()
        integration_session.refresh(user)
        integration_session.refresh(partner)
        
        # Create transaction - use type assertion for nullable IDs
        transaction = Transaction(
            partner_id=partner.id,  # type: ignore
            transcation_type=TransactionType.SALE,
            transaction_status=TransactionStatus.UNPAID,
            total_amount=500,
            created_by=user.id,  # type: ignore
            updated_by=user.id   # type: ignore
        )
        
        integration_session.add(transaction)
        integration_session.commit()
        integration_session.refresh(transaction)
        
        assert transaction.id is not None
        assert transaction.partner_id == partner.id
        assert transaction.total_amount == 500


class TestInventoryModel:
    """Test Inventory model operations."""

    def test_inventory_creation(self, integration_session: Session):
        """Test creating inventory with valid product reference."""
        # Create product first
        product = Product(
            product_code="INV001",
            product_name="Inventory Product",
            purchase_price=100,
            selling_price=120
        )
        
        integration_session.add(product)
        integration_session.commit()
        integration_session.refresh(product)
        
        # Create inventory
        inventory = Inventory(
            product_id=product.id,  # type: ignore
            total_qty=100
        )
        
        integration_session.add(inventory)
        integration_session.commit()
        integration_session.refresh(inventory)
        
        assert inventory.id is not None
        assert inventory.product_id == product.id
        assert inventory.total_qty == 100

    def test_inventory_unique_product_constraint(self, integration_session: Session):
        """Test that each product can only have one inventory record."""
        product = Product(
            product_code="SINGLE",
            product_name="Single Inventory",
            purchase_price=100,
            selling_price=120
        )
        
        integration_session.add(product)
        integration_session.commit()
        integration_session.refresh(product)
        
        inventory1 = Inventory(
            product_id=product.id,  # type: ignore
            total_qty=50
        )
        inventory2 = Inventory(
            product_id=product.id,  # type: ignore
            total_qty=100
        )
        
        integration_session.add(inventory1)
        integration_session.commit()
        
        integration_session.add(inventory2)
        with pytest.raises(IntegrityError):
            integration_session.commit()


class TestCreditModel:
    """Test Credit model operations."""

    def test_credit_creation(self, integration_session: Session):
        """Test creating credit with valid partner and transaction reference."""
        # Create partner, user, and transaction
        partner = Partner(
            tin_number=123456789, 
            names="Credit Partner", 
            type=PartnerType.CLIENT,
            phone_number="1234567890"
        )
        user = User(username="credit_user", password_hash="hash", role=UserRole.ADMIN)
        
        integration_session.add(partner)
        integration_session.add(user)
        integration_session.commit()
        integration_session.refresh(partner)
        integration_session.refresh(user)
        
        # Create a transaction for the credit
        transaction = Transaction(
            partner_id=partner.id,  # type: ignore
            transcation_type=TransactionType.SALE,
            transaction_status=TransactionStatus.UNPAID,
            total_amount=1000,
            created_by=user.id,  # type: ignore
            updated_by=user.id   # type: ignore
        )
        
        integration_session.add(transaction)
        integration_session.commit()
        integration_session.refresh(transaction)
        
        # Create credit account
        credit = Credit(
            partner_id=partner.id,  # type: ignore
            transaction_id=transaction.id,  # type: ignore
            credit_amount=1000,
            credit_limit=5000,
            balance=1000,
            created_by=user.id,  # type: ignore
            updated_by=user.id   # type: ignore
        )
        
        integration_session.add(credit)
        integration_session.commit()
        integration_session.refresh(credit)
        
        assert credit.id is not None
        assert credit.partner_id == partner.id
        assert credit.balance == 1000
        assert credit.credit_limit == 5000


class TestComplexQueries:
    """Test complex database queries and relationships."""

    def test_query_transactions_by_partner(self, integration_session: Session):
        """Test querying transactions by partner."""
        # Create test data
        user = User(username="query_user", password_hash="hash", role=UserRole.ADMIN)
        partner = Partner(
            tin_number=123456789, 
            names="Query Partner", 
            type=PartnerType.CLIENT,
            phone_number="1234567890"
        )
        
        integration_session.add(user)
        integration_session.add(partner)
        integration_session.commit()
        integration_session.refresh(user)
        integration_session.refresh(partner)
        
        # Create multiple transactions
        for amount in [100, 200, 300]:
            transaction = Transaction(
                partner_id=partner.id,  # type: ignore
                transcation_type=TransactionType.SALE,
                transaction_status=TransactionStatus.UNPAID,
                total_amount=amount,
                created_by=user.id,  # type: ignore
                updated_by=user.id   # type: ignore
            )
            integration_session.add(transaction)
        
        integration_session.commit()
        
        # Query transactions by partner
        transactions = integration_session.exec(
            select(Transaction).where(Transaction.partner_id == partner.id)
        ).all()
        
        assert len(transactions) == 3
        amounts = [t.total_amount for t in transactions]
        assert 100 in amounts
        assert 200 in amounts
        assert 300 in amounts

    def test_database_rollback_on_error(self, integration_session: Session):
        """Test that database properly rolls back on constraint violations."""
        user = User(username="rollback_user", password_hash="hash", role=UserRole.ADMIN)
        integration_session.add(user)
        integration_session.commit()
        
        # Attempt to create duplicate username (should fail)
        duplicate_user = User(username="rollback_user", password_hash="hash2", role=UserRole.READ_ONLY)
        integration_session.add(duplicate_user)
        
        with pytest.raises(IntegrityError):
            integration_session.commit()
        
        # Verify rollback - session should still be usable
        integration_session.rollback()
        
        new_user = User(username="new_user", password_hash="hash", role=UserRole.READ_ONLY)
        integration_session.add(new_user)
        integration_session.commit()
        integration_session.refresh(new_user)
        
        assert new_user.id is not None
        assert new_user.username == "new_user"