from __future__ import annotations

from fastapi import FastAPI
from datetime import date, datetime, timezone

from sqlalchemy import inspect, select, text

from app.core.db.common import CommonBase, CommonEngine, CommonSessionLocal
from app.core.security.passwords import hash_password
from app.core.settings import get_settings
from app.modules.core.iam.models import User
from app.modules.core.iam.password_flows_models import InviteToken, PasswordResetToken
from app.modules.core.audit.models import AuditLog
from app.modules.core.rbac.models import Permission, Role, RolePermission, UserRole
from app.modules.core.rbac.permissions_registry import PERMISSIONS
from app.modules.core.tenancy.models import Branch, FinancialYear, Tenant
from app.modules.core.tenancy.settings_models import BranchSettings
from app.modules.employees.models import Employee, EmployeeAttendance, EmployeeRegistrationRequest, EmployeeLeaveType, EmployeeLeaveBalance, EmployeeLeaveRequest, EmployeeDocumentType, EmployeeDocument, EmployeeOnboardingChecklistItem, EmployeeOnboardingTask, EmployeeOffboardingRequest, EmployeeOffboardingTask, EmployeeSalaryStructure, EmployeePayrollRun, EmployeePayslip
from app.modules.consultants.models import ClientConsultantLink, ConsultantManagedClient, ConsultantProfile, ConsultantWorkspace, ConsultantServiceRequest
from app.modules.services.models import FirmServiceSelection, FirmServiceTaskTemplate, ServiceCatalogue, ServiceCategory, FirmTaskDocumentRequirement, FirmTaskDocumentTemplate, EngagementQualityDeclaration, EngagementKycVerification, EngagementLetter
from app.modules.billing.models import BillingSettings, BillingInvoice, BillingInvoiceLine, BillingFeeGroup, BillingFeeGroupService
from app.modules.platform_billing.models import PlatformBillingAccount, PlatformInvoice, PlatformInvoiceLine, PlatformPayment, PlatformPlan, PlatformPlanFeature, PlatformSubscription
from app.modules.marketplace.models import MarketplaceLead, MarketplaceLeadAssignment
from app.modules.documents.models import EngagementDocument, EngagementDocumentVersion, DocumentAccessLog
from app.modules.alerts.models import UserAlert
from app.modules.notice_cases.models import NoticeCase, NoticeCaseEvent, NoticeCaseHearing, NoticeCaseOrder, NoticeCaseDocument
from app.modules.notifications.automation import start_notification_scheduler
from app.modules.registrations.service import seed_registration_types
from app.modules.registrations import models as registration_models  # noqa: F401
from app.modules.credential_vault import models as credential_vault_models  # noqa: F401

DEFAULT_ROLES = [
    "System Admin",
    "Firm Admin",
    "Partner",
    "Branch Manager",
    "Staff",
    "Client",
    "Consultant",
]

LEGACY_ROLE_RENAMES = {
    "SystemAdmin": "System Admin",
    "Manager": "Branch Manager",
}

DEFAULT_PERMISSIONS = list(PERMISSIONS.items())


def _ensure_user_lifecycle_columns() -> None:
    inspector = inspect(CommonEngine)
    existing = {c["name"] for c in inspector.get_columns("users")} if "users" in inspector.get_table_names() else set()
    dialect = CommonEngine.dialect.name
    ddl_map = {
        "allow_login": "BOOLEAN DEFAULT TRUE",
        "is_locked": "BOOLEAN DEFAULT FALSE",
        "locked_at_utc": "TIMESTAMP NULL",
        "deleted_at": "TIMESTAMP NULL",
        "must_change_password": "BOOLEAN DEFAULT FALSE",
        "password_changed_at_utc": "TIMESTAMP NULL",
    }
    for col, ddl in ddl_map.items():
        if col in existing:
            continue
        with CommonEngine.begin() as conn:
            conn.execute(text(f"ALTER TABLE users ADD COLUMN {col} {ddl}"))
            if dialect == "postgres" and col in {"allow_login", "is_locked"}:
                default_value = "TRUE" if col == "allow_login" else "FALSE"
                conn.execute(text(f"UPDATE users SET {col} = {default_value} WHERE {col} IS NULL"))


ROLE_PERMISSION_MAP = {
    "System Admin": [
        "system.settings.view",
        "system.settings.edit",
        "system.settings.manage",
        "users.view",
        "users.manage",
        "users.invite",
        "users.reset_password",
        "rbac.view",
        "rbac.manage",
        "audit.view",
        "alerts.view_self",
        "alerts.manage",
        "services.view",
        "services.create",
        "services.edit",
        "services.selection.manage",
        "services.deactivate",
        "services.cross_branch",
        "services.cross_tenant",
        "services.catalogue.manage",
        "service_tasks.view",
        "service_tasks.create",
        "service_tasks.edit",
        "service_tasks.deactivate",
        "clients.view",
        "clients.create",
        "clients.import",
        "clients.edit",
        "clients.deactivate",
        "clients.activate",
        "clients.archive",
        "clients.restore",
        "clients.assign_partner",
        "clients.cross_branch",
        "clients.cross_tenant",
        "clients.export",
        "clients.audit_log.view",
        "employees.dashboard.view",
        "employees.view",
        "employees.create",
        "employees.edit",
        "employees.status",
        "employees.cross_branch",
        "employees.cross_tenant",
        "consultants.view",
        "consultants.manage",
        "consultants.link_clients",
        "consultants.cross_branch",
        "consultants.managed_clients.manage",
        "consultants.workspace.manage",
        "consultants.service_requests.manage",
        "consultants.conversions.manage",

        # System Admin has billing support/view access only.
        # System Admin must not create, import, generate, approve, post, cancel,
        # or record firm-level client bills.
        "billing.view",
        "billing.payment.view",
        "billing.reports",
        "billing.cross_branch",
        "billing.cross_tenant",
        "billing_fee_structure.view",

        # Platform/SaaS billing is System Admin revenue layer.
        "platform_billing.view",
        "platform_billing.create",
        "platform_billing.edit",
        "platform_billing.generate",
        "platform_billing.post",
        "platform_billing.cancel",
        "platform_billing.payment.create",
        "platform_billing.payment.view",
        "platform_billing.reports",
        "platform_plans.manage",
        "platform_subscriptions.manage",

        # Marketplace / public lead management.
        "marketplace_leads.view",
        "marketplace_leads.create",
        "marketplace_leads.assign",
        "marketplace_leads.update",
        "marketplace_leads.convert",
        "marketplace_leads.reports",
        "marketplace_leads.view_assigned",

        "alerts.view_self",
        "employees.ess.view",        "employees.ess.profile.edit",
        "employees.work.view_self",
        "employees.work.manage",
        "employees.progress.view",
        "employees.registration.request",
        "employees.registration.approve",
        "employees.attendance.punch",
        "employees.attendance.view_self",
        "employees.attendance.view_all",
        "employees.attendance.approve",
        "employees.leave.apply",
        "employees.leave.view_self",
        "employees.leave.view_all",
        "employees.leave.approve",
        "employees.leave_type.manage",
        "employees.leave_balance.manage",
        "employees.documents.view_self",
        "employees.documents.upload_self",
        "employees.documents.view_all",
        "employees.documents.manage",
        "employees.documents.verify",
        "employees.documents.delete",
        "employees.document_type.manage",
        "employees.onboarding.view",
        "employees.onboarding.manage",
        "employees.onboarding.approve",
        "employees.offboarding.view",
        "employees.offboarding.manage",
        "employees.offboarding.approve",
        "employees.offboarding.request_self",
        "employees.payroll.payout",
        "employees.payroll.view_self",
        "employees.payroll.view",
        "employees.payroll.run",
        "employees.payroll.structure.manage",
        "employees.import",
        "employees.import.employee",
        "employees.import.leave_type",
        "employees.import.leave_balance",
        "employees.import.salary_structure",
    ],
    "Firm Admin": [
        "system.settings.view",
        "system.settings.edit",
        "users.view",
        "users.manage",
        "users.invite",
        "users.reset_password",
        "audit.view",
        "alerts.view_self",
        "alerts.manage",
        "services.view",
        "services.create",
        "services.edit",
        "services.selection.manage",
        "services.deactivate",
        "services.cross_branch",
        "service_tasks.view",
        "service_tasks.create",
        "service_tasks.edit",
        "service_tasks.deactivate",
        "clients.view",
        "clients.create",
        "clients.import",
        "clients.edit",
        "clients.deactivate",
        "clients.activate",
        "clients.archive",
        "clients.restore",
        "clients.assign_partner",
        "clients.cross_branch",
        "clients.export",
        "clients.audit_log.view",
        "documents.view",
        "documents.upload",
        "documents.download",
        "documents.delete",
        "documents.audit.view",
        "employees.dashboard.view",
        "employees.view",
        "employees.create",
        "employees.edit",
        "employees.status",
        "employees.cross_branch",
        "consultants.view",
        "consultants.manage",
        "consultants.link_clients",
        "consultants.cross_branch",
        "consultants.managed_clients.manage",
        "consultants.workspace.manage",
        "consultants.service_requests.manage",
        "consultants.conversions.manage",

        "billing.view",
        "billing.create",
        "billing.edit",
        "billing.approve",
        "billing.post",
        "billing.cancel",
        "billing.payment.create",
        "billing.payment.view",
        "billing.reports",
        "billing.cross_branch",
        "billing_fee_structure.view",
        "billing_fee_structure.import",
        "billing_fee_structure.edit",
        "billing_fee_structure.delete",
        "billing_invoice.generate",
        "billing_invoice.bulk_generate",

        # Audit Firm can work on leads assigned to its audit firm.
        "marketplace_leads.view_assigned",
        "marketplace_leads.update",
        "marketplace_leads.convert",

        "employees.ess.view",        "employees.ess.profile.edit",
        "employees.work.view_self",
        "employees.work.manage",
        "employees.progress.view",
        "employees.registration.request",
        "employees.registration.approve",
        "employees.attendance.punch",
        "employees.attendance.view_self",
        "employees.attendance.view_all",
        "employees.attendance.approve",
        "employees.leave.apply",
        "employees.leave.view_self",
        "employees.leave.view_all",
        "employees.leave.approve",
        "employees.leave_type.manage",
        "employees.leave_balance.manage",
        "employees.documents.view_self",
        "employees.documents.upload_self",
        "employees.documents.view_all",
        "employees.documents.manage",
        "employees.documents.verify",
        "employees.documents.delete",
        "employees.document_type.manage",
        "employees.onboarding.view",
        "employees.onboarding.manage",
        "employees.onboarding.approve",
        "employees.offboarding.view",
        "employees.offboarding.manage",
        "employees.offboarding.approve",
        "employees.offboarding.request_self",
        "employees.payroll.payout",
        "employees.payroll.view_self",
        "employees.payroll.view",
        "employees.payroll.run",
        "employees.payroll.structure.manage",
        "employees.import",
        "employees.import.employee",
        "employees.import.leave_type",
        "employees.import.leave_balance",
        "employees.import.salary_structure",
    ],
    "Partner": [
        "users.view",
        "system.settings.view",
        "services.view",
        "services.cross_branch",
        "service_tasks.view",
        "clients.view",
        "clients.create",
        "clients.import",
        "clients.edit",
        "clients.deactivate",
        "clients.activate",
        "clients.archive",
        "clients.restore",
        "clients.export",
        "clients.audit_log.view",
        "documents.view",
        "documents.upload",
        "documents.download",
        "documents.delete",
        "clients.view.own_only",
        "employees.dashboard.view",
        "employees.view",
        "employees.create",
        "employees.edit",
        "employees.status",
        "consultants.view",
        "consultants.link_clients",
        "consultants.cross_branch",
        "consultants.managed_clients.manage",
        "consultants.workspace.manage",

        # Partner has almost the same firm-billing privileges as Firm Admin,
        # but is intentionally scoped to own clients through billing.view_own.
        "billing.view",
        "billing.create",
        "billing.edit",
        "billing.approve",
        "billing.post",
        "billing.cancel",
        "billing.payment.create",
        "billing.payment.view",
        "billing.reports",
        "billing.view_own",
        "billing_fee_structure.view",
        "billing_fee_structure.import",
        "billing_fee_structure.edit",
        "billing_fee_structure.delete",
        "billing_invoice.generate",
        "billing_invoice.bulk_generate",

        # Partner can handle assigned marketplace leads for own clients/work.
        "marketplace_leads.view_assigned",
        "marketplace_leads.update",
        "marketplace_leads.convert",

        "employees.ess.view",        "employees.ess.profile.edit",
        "employees.work.view_self",
        "employees.work.manage",
        "employees.progress.view",
        "employees.registration.request",
        "employees.registration.approve",
        "employees.attendance.punch",
        "employees.attendance.view_self",
        "employees.attendance.view_all",
        "employees.attendance.approve",
        "employees.leave.apply",
        "employees.leave.view_self",
        "employees.leave.view_all",
        "employees.leave.approve",
        "employees.leave_type.manage",
        "employees.leave_balance.manage",
        "employees.documents.view_self",
        "employees.documents.upload_self",
        "employees.documents.view_all",
        "employees.documents.manage",
        "employees.documents.verify",
        "employees.documents.delete",
        "employees.document_type.manage",
        "employees.onboarding.view",
        "employees.onboarding.manage",
        "employees.onboarding.approve",
        "employees.offboarding.view",
        "employees.offboarding.manage",
        "employees.offboarding.approve",
        "employees.offboarding.request_self",
        "employees.payroll.payout",
        "employees.payroll.view_self",
        "employees.payroll.view",
        "employees.payroll.run",
        "employees.payroll.structure.manage",
        "employees.import",
        "employees.import.employee",
        "employees.import.leave_type",
        "employees.import.leave_balance",
        "employees.import.salary_structure",
    ],
    "Branch Manager": [
        "users.view",
        "services.view",
        "services.create",
        "services.edit",
        "service_tasks.view",
        "service_tasks.create",
        "service_tasks.edit",
        "clients.view",
        "clients.create",
        "clients.edit",
        "clients.deactivate",
        "clients.activate",
        "clients.export",
        "clients.audit_log.view",
        "documents.view",
        "documents.upload",
        "documents.download",
        "employees.dashboard.view",
        "employees.view",
        "employees.create",
        "employees.edit",
        "employees.status",
        "consultants.view",

        "billing.view",
        "billing.create",
        "billing_fee_structure.view",
        "employees.ess.view",        "employees.ess.profile.edit",
        "employees.work.view_self",
        "employees.work.manage",
        "employees.progress.view",
        "employees.registration.request",
        "employees.registration.approve",
        "employees.attendance.punch",
        "employees.attendance.view_self",
        "employees.attendance.view_all",
        "employees.attendance.approve",
        "employees.leave.apply",
        "employees.leave.view_self",
        "employees.leave.view_all",
        "employees.leave.approve",
        "employees.leave_type.manage",
        "employees.leave_balance.manage",
        "employees.documents.view_self",
        "employees.documents.upload_self",
        "employees.documents.view_all",
        "employees.documents.manage",
        "employees.documents.verify",
        "employees.documents.delete",
        "employees.document_type.manage",
        "employees.onboarding.view",
        "employees.onboarding.manage",
        "employees.onboarding.approve",
        "employees.offboarding.view",
        "employees.offboarding.manage",
        "employees.offboarding.approve",
        "employees.offboarding.request_self",
        "employees.payroll.view_self",
        "employees.payroll.view",
        "employees.payroll.run",
        "employees.payroll.structure.manage",
        "employees.import",
        "employees.import.employee",
        "employees.import.leave_type",
        "employees.import.leave_balance",
        "employees.import.salary_structure",
    ],
    "Staff": [
        "alerts.view_self",
        "employees.ess.view",
        "employees.ess.profile.edit",
        "employees.work.view_self",
        "employees.registration.request",
        "employees.attendance.punch",
        "employees.attendance.view_self",
        "employees.leave.apply",
        "employees.leave.view_self",
        "employees.documents.view_self",
        "employees.documents.upload_self",
        "employees.offboarding.request_self",
        "employees.payroll.view_self",
        "documents.view",
        "documents.upload",
        "documents.download",
    ],
    "Client": [],
    "Consultant": [
        "alerts.view_self",
        "consultants.portal.view",
        "consultants.managed_clients.manage",
        "consultants.workspace.manage",
    ],
}


# Keep existing databases aligned with the billing permission policy.
# The normal startup seed only adds missing permissions; it does not remove
# permissions that were granted in an earlier patch. This sync is limited to
# billing permissions for these default roles so existing non-billing features
# and custom modules are not touched.
BILLING_PERMISSION_CODES = {
    "billing.view",
    "billing.create",
    "billing.edit",
    "billing.approve",
    "billing.post",
    "billing.cancel",
    "billing.payment.create",
    "billing.payment.view",
    "billing.reports",
    "billing.cross_branch",
    "billing.cross_tenant",
    "billing.view_own",
    "billing_fee_structure.view",
    "billing_fee_structure.import",
    "billing_fee_structure.edit",
    "billing_fee_structure.delete",
    "billing_invoice.generate",
    "billing_invoice.bulk_generate",
}

BILLING_ROLE_PERMISSION_SYNC = {
    "System Admin": {
        "billing.view",
        "billing.payment.view",
        "billing.reports",
        "billing.cross_branch",
        "billing.cross_tenant",
        "billing_fee_structure.view",
    },
    "Firm Admin": {
        "billing.view",
        "billing.create",
        "billing.edit",
        "billing.approve",
        "billing.post",
        "billing.cancel",
        "billing.payment.create",
        "billing.payment.view",
        "billing.reports",
        "billing.cross_branch",
        "billing_fee_structure.view",
        "billing_fee_structure.import",
        "billing_fee_structure.edit",
        "billing_fee_structure.delete",
        "billing_invoice.generate",
        "billing_invoice.bulk_generate",
    },
    "Partner": {
        "billing.view",
        "billing.create",
        "billing.edit",
        "billing.approve",
        "billing.post",
        "billing.cancel",
        "billing.payment.create",
        "billing.payment.view",
        "billing.reports",
        "billing.view_own",
        "billing_fee_structure.view",
        "billing_fee_structure.import",
        "billing_fee_structure.edit",
        "billing_fee_structure.delete",
        "billing_invoice.generate",
        "billing_invoice.bulk_generate",
    },
}


NOTICE_CASE_ROLE_PERMISSIONS = {
    "System Admin": [
        "notice_cases.view", "notice_cases.create", "notice_cases.edit",
        "notice_cases.events.manage", "notice_cases.hearings.manage", "notice_cases.orders.manage",
        "notice_cases.documents.upload", "notice_cases.documents.download", "notice_cases.documents.delete",
        "notice_cases.cross_branch", "notice_cases.cross_tenant",
    ],
    "Firm Admin": [
        "notice_cases.view", "notice_cases.create", "notice_cases.edit",
        "notice_cases.events.manage", "notice_cases.hearings.manage", "notice_cases.orders.manage",
        "notice_cases.documents.upload", "notice_cases.documents.download", "notice_cases.documents.delete",
        "notice_cases.cross_branch",
    ],
    "Partner": [
        "notice_cases.view", "notice_cases.create", "notice_cases.edit",
        "notice_cases.events.manage", "notice_cases.hearings.manage", "notice_cases.orders.manage",
        "notice_cases.documents.upload", "notice_cases.documents.download",
    ],
    "Branch Manager": [
        "notice_cases.view", "notice_cases.create", "notice_cases.edit",
        "notice_cases.events.manage", "notice_cases.hearings.manage", "notice_cases.orders.manage",
        "notice_cases.documents.upload", "notice_cases.documents.download",
        "notice_cases.cross_branch",
    ],
    "Staff": [
        "notice_cases.view", "notice_cases.events.manage",
        "notice_cases.documents.upload", "notice_cases.documents.download",
    ],
}

for _role_name, _codes in NOTICE_CASE_ROLE_PERMISSIONS.items():
    _target = ROLE_PERMISSION_MAP.setdefault(_role_name, [])
    for _code in _codes:
        if isinstance(_target, set):
            _target.add(_code)
        elif _code not in _target:
            _target.append(_code)




def _fy_dates_from_code(year_code: str) -> tuple[date, date, str]:
    parts = (year_code or "").split("-", 1)
    try:
        start_year = int(parts[0])
    except Exception:
        start_year = 2025
    end_year = start_year + 1
    assessment_year = f"{end_year}-{str(end_year + 1)[-2:]}"
    return date(start_year, 4, 1), date(end_year, 3, 31), assessment_year


def _ensure_financial_year(db, tenant_id: int, year_code: str) -> FinancialYear:
    fy = db.execute(
        select(FinancialYear).where(
            FinancialYear.tenant_id == tenant_id,
            FinancialYear.year_code == year_code,
        )
    ).scalar_one_or_none()
    if fy:
        return fy

    start_date, end_date, assessment_year = _fy_dates_from_code(year_code)
    current_exists = db.execute(
        select(FinancialYear.id).where(
            FinancialYear.tenant_id == tenant_id,
            FinancialYear.is_current.is_(True),
        )
    ).first()
    now = datetime.now(timezone.utc)
    fy = FinancialYear(
        tenant_id=tenant_id,
        year_code=year_code,
        assessment_year=assessment_year,
        start_date=start_date,
        end_date=end_date,
        is_current=current_exists is None,
        is_locked=False,
        created_at_utc=now,
        updated_at_utc=now,
    )
    db.add(fy)
    db.commit()
    db.refresh(fy)
    return fy


def _ensure_financial_years_for_all_tenants(db, default_year_code: str) -> None:
    tenant_ids = db.execute(select(Tenant.id)).scalars().all()
    for tenant_id in tenant_ids:
        _ensure_financial_year(db, int(tenant_id), default_year_code)

def on_startup(app: FastAPI) -> None:
    s = get_settings()
    inspector = inspect(CommonEngine)
    existing_tables = set(inspector.get_table_names())

    if "audit_logs" not in existing_tables:
        CommonBase.metadata.create_all(bind=CommonEngine, tables=[AuditLog.__table__])
        existing_tables = set(inspect(CommonEngine).get_table_names())

    required_tables = {
        "tenants",
        "branches",
        "branch_settings",
        "users",
        "roles",
        "permissions",
        "role_permissions",
        "user_roles",
        "audit_logs",
    }

    missing_optional_tables = []
    if "invite_tokens" not in existing_tables:
        missing_optional_tables.append(InviteToken.__table__)
    if "password_reset_tokens" not in existing_tables:
        missing_optional_tables.append(PasswordResetToken.__table__)
    if "service_categories" not in existing_tables:
        missing_optional_tables.append(ServiceCategory.__table__)
    if "service_catalogues" not in existing_tables:
        missing_optional_tables.append(ServiceCatalogue.__table__)
    if "firm_service_selections" not in existing_tables:
        missing_optional_tables.append(FirmServiceSelection.__table__)
    if "firm_service_task_templates" not in existing_tables:
        missing_optional_tables.append(FirmServiceTaskTemplate.__table__)
    service_quality_tables = [
        ("engagement_quality_declarations", EngagementQualityDeclaration.__table__),
        ("engagement_kyc_verifications", EngagementKycVerification.__table__),
        ("engagement_letters", EngagementLetter.__table__),
    ]
    for table_name, table in service_quality_tables:
        if table_name not in existing_tables:
            missing_optional_tables.append(table)
    billing_tables = [
        ("billing_settings", BillingSettings.__table__),
        ("billing_fee_groups", BillingFeeGroup.__table__),
        ("billing_fee_group_services", BillingFeeGroupService.__table__),
        ("billing_invoices", BillingInvoice.__table__),
        ("billing_invoice_lines", BillingInvoiceLine.__table__),
    ]
    for table_name, table in billing_tables:
        if table_name not in existing_tables:
            missing_optional_tables.append(table)

    platform_billing_tables = [
        ("platform_plans", PlatformPlan.__table__),
        ("platform_plan_features", PlatformPlanFeature.__table__),
        ("platform_billing_accounts", PlatformBillingAccount.__table__),
        ("platform_subscriptions", PlatformSubscription.__table__),
        ("platform_invoices", PlatformInvoice.__table__),
        ("platform_invoice_lines", PlatformInvoiceLine.__table__),
        ("platform_payments", PlatformPayment.__table__),
    ]
    marketplace_tables = [
        ("marketplace_leads", MarketplaceLead.__table__),
        ("marketplace_lead_assignments", MarketplaceLeadAssignment.__table__),
    ]
    for table_name, table in platform_billing_tables:
        if table_name not in existing_tables:
            missing_optional_tables.append(table)
    for table_name, table in marketplace_tables:
        if table_name not in existing_tables:
            missing_optional_tables.append(table)

    documents_tables = [
        ("engagement_documents", EngagementDocument.__table__),
        ("engagement_document_versions", EngagementDocumentVersion.__table__),
        ("document_access_logs", DocumentAccessLog.__table__),
    ]
    for table_name, table in documents_tables:
        if table_name not in existing_tables:
            missing_optional_tables.append(table)
    if "user_alerts" not in existing_tables:
        missing_optional_tables.append(UserAlert.__table__)
    if "financial_years" not in existing_tables:
        missing_optional_tables.append(FinancialYear.__table__)

    notice_case_tables = [
        ("notice_cases", NoticeCase.__table__),
        ("notice_case_events", NoticeCaseEvent.__table__),
        ("notice_case_hearings", NoticeCaseHearing.__table__),
        ("notice_case_orders", NoticeCaseOrder.__table__),
        ("notice_case_documents", NoticeCaseDocument.__table__),
    ]
    for table_name, table in notice_case_tables:
        if table_name not in existing_tables:
            missing_optional_tables.append(table)
    if "employees" not in existing_tables:
        missing_optional_tables.append(Employee.__table__)
    if "employee_registration_requests" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeeRegistrationRequest.__table__)
    if "employee_attendance" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeeAttendance.__table__)
    if "employee_onboarding_checklist_items" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeeOnboardingChecklistItem.__table__)
    if "employee_onboarding_tasks" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeeOnboardingTask.__table__)
    if "employee_offboarding_requests" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeeOffboardingRequest.__table__)
    if "employee_offboarding_tasks" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeeOffboardingTask.__table__)
    if "employee_salary_structures" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeeSalaryStructure.__table__)
    if "employee_payroll_runs" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeePayrollRun.__table__)
    if "employee_payslips" not in existing_tables and "employees" in existing_tables:
        missing_optional_tables.append(EmployeePayslip.__table__)
    if "consultant_workspaces" not in existing_tables and "consultant_profiles" in existing_tables:
        missing_optional_tables.append(ConsultantWorkspace.__table__)
    if "consultant_service_requests" not in existing_tables and "consultant_profiles" in existing_tables:
        missing_optional_tables.append(ConsultantServiceRequest.__table__)
    if missing_optional_tables:
        CommonBase.metadata.create_all(bind=CommonEngine, tables=missing_optional_tables)

    if not required_tables.issubset(existing_tables):
        raise RuntimeError("Database schema is not initialized. Run 'alembic upgrade head' first.")

    _ensure_user_lifecycle_columns()

    db = CommonSessionLocal()
    try:
        tenant = db.execute(select(Tenant).where(Tenant.code == s.DEFAULT_TENANT_CODE)).scalar_one_or_none()
        if not tenant:
            tenant = Tenant(code=s.DEFAULT_TENANT_CODE, name="Default Tenant", is_active=True)
            db.add(tenant)
            db.commit()
            db.refresh(tenant)

        branch = db.execute(
            select(Branch).where(Branch.tenant_id == tenant.id, Branch.code == s.DEFAULT_BRANCH_CODE)
        ).scalar_one_or_none()
        if not branch:
            branch = Branch(
                tenant_id=tenant.id,
                code=s.DEFAULT_BRANCH_CODE,
                name="Main Branch",
                timezone=s.DEFAULT_TIMEZONE,
                is_active=True,
                allow_login=True,
            )
            db.add(branch)
            db.commit()
            db.refresh(branch)

        bs = db.execute(select(BranchSettings).where(BranchSettings.branch_id == branch.id)).scalar_one_or_none()
        if not bs:
            bs = BranchSettings(branch_id=branch.id)
            db.add(bs)
            db.commit()

        _ensure_financial_years_for_all_tenants(db, s.DEFAULT_YEAR_CODE)

        for legacy_name, new_name in LEGACY_ROLE_RENAMES.items():
            legacy_role = db.execute(select(Role).where(Role.name == legacy_name)).scalar_one_or_none()
            target_role = db.execute(select(Role).where(Role.name == new_name)).scalar_one_or_none()
            if legacy_role and not target_role:
                legacy_role.name = new_name
            elif legacy_role and target_role:
                for user_role in db.execute(
                    select(UserRole).where(UserRole.role_id == legacy_role.id)
                ).scalars().all():
                    exists = db.execute(
                        select(UserRole).where(
                            UserRole.user_id == user_role.user_id,
                            UserRole.role_id == target_role.id,
                        )
                    ).scalar_one_or_none()
                    if not exists:
                        db.add(UserRole(user_id=user_role.user_id, role_id=target_role.id))
                db.flush()
                db.delete(legacy_role)
        db.commit()

        for role_name in DEFAULT_ROLES:
            exists = db.execute(select(Role).where(Role.name == role_name)).scalar_one_or_none()
            if not exists:
                db.add(Role(name=role_name, is_active=True))
        db.commit()

        for code, name in DEFAULT_PERMISSIONS:
            exists = db.execute(select(Permission).where(Permission.code == code)).scalar_one_or_none()
            if not exists:
                db.add(Permission(code=code, name=name, is_active=True))
        db.commit()

        roles = {r.name: r for r in db.execute(select(Role)).scalars().all()}
        permissions = {p.code: p for p in db.execute(select(Permission)).scalars().all()}

        for role_name, permission_codes in ROLE_PERMISSION_MAP.items():
            role = roles.get(role_name)
            if not role:
                continue

            permission_codes = list(dict.fromkeys(permission_codes))

            for code in permission_codes:
                permission = permissions.get(code)
                if not permission:
                    continue

                exists = db.execute(
                    select(RolePermission).where(
                        RolePermission.role_id == role.id,
                        RolePermission.permission_id == permission.id,
                    )
                ).scalar_one_or_none()

                if not exists:
                    db.add(RolePermission(role_id=role.id, permission_id=permission.id))
        db.commit()

        # Enforce the updated billing privilege matrix for existing databases.
        # This removes stale billing permissions from System Admin and grants
        # Partner own-client billing privileges without altering other modules.
        billing_permissions = {
            code: permissions[code]
            for code in BILLING_PERMISSION_CODES
            if code in permissions
        }
        for role_name, allowed_codes in BILLING_ROLE_PERMISSION_SYNC.items():
            role = roles.get(role_name)
            if not role:
                continue

            allowed_permission_ids = {
                billing_permissions[code].id
                for code in allowed_codes
                if code in billing_permissions
            }
            billing_permission_ids = {permission.id for permission in billing_permissions.values()}

            existing_links = db.execute(
                select(RolePermission).where(
                    RolePermission.role_id == role.id,
                    RolePermission.permission_id.in_(billing_permission_ids),
                )
            ).scalars().all() if billing_permission_ids else []

            existing_ids = {link.permission_id for link in existing_links}
            for link in existing_links:
                if link.permission_id not in allowed_permission_ids:
                    db.delete(link)

            for permission_id in allowed_permission_ids - existing_ids:
                db.add(RolePermission(role_id=role.id, permission_id=permission_id))
        db.commit()

        any_user = db.execute(select(User.id)).first()
        if not any_user:
            admin = User(
                email=s.BOOTSTRAP_ADMIN_EMAIL,
                full_name="System Admin",
                password_hash=hash_password(s.BOOTSTRAP_ADMIN_PASSWORD),
                tenant_id=tenant.id,
                branch_id=branch.id,
                is_active=True,
                allow_login=True,
                is_locked=False,
                deleted_at=None,
            )
            db.add(admin)
            db.commit()
            db.refresh(admin)

            if roles.get("System Admin"):
                exists = db.execute(
                    select(UserRole).where(
                        UserRole.user_id == admin.id,
                        UserRole.role_id == roles["System Admin"].id,
                    )
                ).scalar_one_or_none()
                if not exists:
                    db.add(UserRole(user_id=admin.id, role_id=roles["System Admin"].id))
                    db.commit()
    finally:
        db.close()

    # Phase 4 registration lifecycle masters are idempotently seeded.
    with CommonSessionLocal() as registration_db:
        seed_registration_types(registration_db)

    # Phase 7O: start alert notification/escalation automation after schema and seed checks.
    start_notification_scheduler()
