"""Database-level guarantees. These are the constraints the service layer alone cannot guarantee — under concurrency, only the database can. Each of these was called out in the report as a schema-level rule that cannot be retrofitted cheaply. """ import uuid from datetime import date, time, timedelta import pytest from sqlalchemy.exc import IntegrityError from app.common.enums import CallOutcome, DCRStatus from app.doctors.models import Doctor from app.organization.models import AMHeadquarters from app.reports.models import DailyCallReport, DoctorCall async def _doctor(db, hq_id, name="Dr Sen", clinic="MG Road"): doc = Doctor(id=uuid.uuid4(), headquarters_id=hq_id, name=name, clinic_address=clinic) db.add(doc) await db.flush() return doc class TestOneReportPerMRPerDay: """V1 / A9 — the constraint the report flagged as the missing piece of the MVP.""" async def test_duplicate_date_rejected(self, db, org): for _ in range(2): db.add( DailyCallReport( id=uuid.uuid4(), mr_user_id=org["mr1"].id, headquarters_id=org["hq1"].id, report_date=date.today(), status=DCRStatus.DRAFT, ) ) with pytest.raises(IntegrityError): await db.flush() async def test_different_dates_allowed(self, db, org): for offset in (0, 1): db.add( DailyCallReport( id=uuid.uuid4(), mr_user_id=org["mr1"].id, headquarters_id=org["hq1"].id, report_date=date.today() - timedelta(days=offset), status=DCRStatus.DRAFT, ) ) await db.flush() # must not raise async def test_different_mrs_same_date_allowed(self, db, org): for mr, hq in (("mr1", "hq1"), ("mr2", "hq2")): db.add( DailyCallReport( id=uuid.uuid4(), mr_user_id=org[mr].id, headquarters_id=org[hq].id, report_date=date.today(), status=DCRStatus.DRAFT, ) ) await db.flush() # must not raise class TestNoDuplicateDoctorCall: """V5 / A11.""" async def test_same_doctor_twice_on_one_report_rejected(self, db, org): doctor = await _doctor(db, org["hq1"].id) dcr = DailyCallReport( id=uuid.uuid4(), mr_user_id=org["mr1"].id, headquarters_id=org["hq1"].id, report_date=date.today(), status=DCRStatus.DRAFT, ) db.add(dcr) await db.flush() for hour in (10, 16): db.add( DoctorCall( dcr_id=dcr.id, doctor_id=doctor.id, visit_time=time(hour, 0), call_outcome=CallOutcome.MET, ) ) with pytest.raises(IntegrityError): await db.flush() class TestOneActiveAreaManagerPerHQ: """The partial unique index `uq_active_am_per_hq`.""" async def test_second_active_am_rejected(self, db, org): db.add( AMHeadquarters( area_manager_user_id=org["am2"].id, headquarters_id=org["hq1"].id, # already managed by am1 effective_from=date.today(), ) ) with pytest.raises(IntegrityError): await db.flush() async def test_allowed_once_previous_mapping_is_closed(self, db, org): existing = ( await db.execute( AMHeadquarters.__table__.select().where( AMHeadquarters.headquarters_id == org["hq1"].id ) ) ).first() assert existing is not None await db.execute( AMHeadquarters.__table__.update() .where(AMHeadquarters.headquarters_id == org["hq1"].id) .values(effective_to=date.today()) ) db.add( AMHeadquarters( area_manager_user_id=org["am2"].id, headquarters_id=org["hq1"].id, effective_from=date.today(), ) ) await db.flush() # must not raise class TestMRRequiresHeadquarters: """The `ck_user_mr_requires_hq` check — R4/R8 enforced in the schema.""" async def test_mr_without_hq_rejected(self, db, org): from app.auth.security import hash_password from app.common.enums import Role from app.users.models import User db.add( User( id=uuid.uuid4(), company_id=org["company"].id, email="orphan@acmepharma.in", password_hash=hash_password("x" * 12), full_name="Orphan MR", employee_code="MR999", role=Role.MR, headquarters_id=None, ) ) with pytest.raises(IntegrityError): await db.flush() async def test_gm_with_hq_rejected(self, db, org): from app.auth.security import hash_password from app.common.enums import Role from app.users.models import User db.add( User( id=uuid.uuid4(), company_id=org["company"].id, email="gm2@acmepharma.in", password_hash=hash_password("x" * 12), full_name="Wrong AGM", employee_code="GM999", role=Role.AGM, headquarters_id=org["hq1"].id, ) ) with pytest.raises(IntegrityError): await db.flush() class TestApprovedAtConsistency: async def test_approved_without_timestamp_rejected(self, db, org): db.add( DailyCallReport( id=uuid.uuid4(), mr_user_id=org["mr1"].id, headquarters_id=org["hq1"].id, report_date=date.today(), status=DCRStatus.APPROVED, approved_at=None, ) ) with pytest.raises(IntegrityError): await db.flush()