"""Phase 8B - Notice and Case Management

Revision ID: 20260616_phase_8b_notice_cases
Revises: 20260615_phase_8a_task_docs
Create Date: 2026-06-16
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "20260616_phase_8b_notice_cases"
down_revision = "20260615_phase_8a_task_docs"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "notice_cases",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("branch_id", sa.Integer(), nullable=True),
        sa.Column("client_id", sa.Integer(), nullable=False),
        sa.Column("engagement_id", sa.Integer(), nullable=True),
        sa.Column("case_code", sa.String(80), nullable=False),
        sa.Column("department", sa.String(40), nullable=False),
        sa.Column("case_type", sa.String(50), nullable=False),
        sa.Column("title", sa.String(255), nullable=False),
        sa.Column("reference_no", sa.String(150), nullable=True),
        sa.Column("din_ack_no", sa.String(150), nullable=True),
        sa.Column("notice_date", sa.Date(), nullable=True),
        sa.Column("due_date", sa.Date(), nullable=True),
        sa.Column("financial_year", sa.String(9), nullable=True),
        sa.Column("assessment_year", sa.String(9), nullable=True),
        sa.Column("period_label", sa.String(60), nullable=True),
        sa.Column("status", sa.String(40), nullable=False, server_default="open"),
        sa.Column("priority", sa.String(20), nullable=False, server_default="normal"),
        sa.Column("issue_summary", sa.Text(), nullable=True),
        sa.Column("remarks", sa.Text(), nullable=True),
        sa.Column("assigned_partner_user_id", sa.Integer(), nullable=True),
        sa.Column("assigned_manager_user_id", sa.Integer(), nullable=True),
        sa.Column("assigned_staff_user_id", sa.Integer(), nullable=True),
        sa.Column("is_archived", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("archived_at_utc", sa.DateTime(timezone=True), nullable=True),
        sa.Column("archived_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("updated_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id"], ["tenants.id"], name="fk_notice_cases_tenant", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["branch_id"], ["branches.id"], name="fk_notice_cases_branch", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["client_id"], ["clients.id"], name="fk_notice_cases_client", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["engagement_id"], ["client_service_subscriptions.id"], name="fk_notice_cases_engagement", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["assigned_partner_user_id"], ["users.id"], name="fk_notice_cases_partner", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["assigned_manager_user_id"], ["users.id"], name="fk_notice_cases_manager", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["assigned_staff_user_id"], ["users.id"], name="fk_notice_cases_staff", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["archived_by_user_id"], ["users.id"], name="fk_notice_cases_archived_by", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"], name="fk_notice_cases_created_by", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["updated_by_user_id"], ["users.id"], name="fk_notice_cases_updated_by", ondelete="SET NULL"),
        sa.UniqueConstraint("tenant_id", "case_code", name="uq_notice_cases_tenant_code"),
    )
    for col in ["tenant_id","branch_id","client_id","engagement_id","case_code","department","case_type","reference_no","din_ack_no","notice_date","due_date","financial_year","assessment_year","period_label","status","priority","assigned_partner_user_id","assigned_manager_user_id","assigned_staff_user_id","is_archived"]:
        op.create_index(f"ix_notice_cases_{col}", "notice_cases", [col])

    op.create_table(
        "notice_case_events",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("branch_id", sa.Integer(), nullable=True),
        sa.Column("case_id", sa.Integer(), nullable=False),
        sa.Column("event_type", sa.String(60), nullable=False),
        sa.Column("event_date", sa.Date(), nullable=False),
        sa.Column("description", sa.Text(), nullable=False),
        sa.Column("next_due_date", sa.Date(), nullable=True),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id"], ["tenants.id"], name="fk_notice_case_events_tenant", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["branch_id"], ["branches.id"], name="fk_notice_case_events_branch", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["case_id"], ["notice_cases.id"], name="fk_notice_case_events_case", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"], name="fk_notice_case_events_created_by", ondelete="SET NULL"),
    )
    for col in ["tenant_id","branch_id","case_id","event_type","event_date","next_due_date","created_by_user_id"]:
        op.create_index(f"ix_notice_case_events_{col}", "notice_case_events", [col])

    op.create_table(
        "notice_case_hearings",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("branch_id", sa.Integer(), nullable=True),
        sa.Column("case_id", sa.Integer(), nullable=False),
        sa.Column("hearing_date", sa.Date(), nullable=False),
        sa.Column("hearing_time", sa.String(20), nullable=True),
        sa.Column("venue_or_mode", sa.String(200), nullable=True),
        sa.Column("officer_name", sa.String(150), nullable=True),
        sa.Column("agenda", sa.Text(), nullable=True),
        sa.Column("outcome", sa.Text(), nullable=True),
        sa.Column("status", sa.String(30), nullable=False, server_default="scheduled"),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.Column("updated_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id"], ["tenants.id"], name="fk_notice_case_hearings_tenant", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["branch_id"], ["branches.id"], name="fk_notice_case_hearings_branch", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["case_id"], ["notice_cases.id"], name="fk_notice_case_hearings_case", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"], name="fk_notice_case_hearings_created_by", ondelete="SET NULL"),
    )
    for col in ["tenant_id","branch_id","case_id","hearing_date","status","created_by_user_id"]:
        op.create_index(f"ix_notice_case_hearings_{col}", "notice_case_hearings", [col])

    op.create_table(
        "notice_case_orders",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("branch_id", sa.Integer(), nullable=True),
        sa.Column("case_id", sa.Integer(), nullable=False),
        sa.Column("order_type", sa.String(60), nullable=False),
        sa.Column("order_no", sa.String(150), nullable=True),
        sa.Column("order_date", sa.Date(), nullable=False),
        sa.Column("demand_amount", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("tax_amount", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("interest_amount", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("penalty_amount", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("summary", sa.Text(), nullable=True),
        sa.Column("appeal_due_date", sa.Date(), nullable=True),
        sa.Column("appeal_filed", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("created_by_user_id", sa.Integer(), nullable=True),
        sa.Column("created_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id"], ["tenants.id"], name="fk_notice_case_orders_tenant", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["branch_id"], ["branches.id"], name="fk_notice_case_orders_branch", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["case_id"], ["notice_cases.id"], name="fk_notice_case_orders_case", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["created_by_user_id"], ["users.id"], name="fk_notice_case_orders_created_by", ondelete="SET NULL"),
    )
    for col in ["tenant_id","branch_id","case_id","order_type","order_no","order_date","appeal_due_date","appeal_filed","created_by_user_id"]:
        op.create_index(f"ix_notice_case_orders_{col}", "notice_case_orders", [col])

    op.create_table(
        "notice_case_documents",
        sa.Column("id", sa.Integer(), primary_key=True, autoincrement=True),
        sa.Column("tenant_id", sa.Integer(), nullable=False),
        sa.Column("branch_id", sa.Integer(), nullable=True),
        sa.Column("case_id", sa.Integer(), nullable=False),
        sa.Column("event_id", sa.Integer(), nullable=True),
        sa.Column("document_type", sa.String(80), nullable=False, server_default="GENERAL"),
        sa.Column("title", sa.String(255), nullable=False),
        sa.Column("description", sa.Text(), nullable=True),
        sa.Column("original_filename", sa.String(255), nullable=False),
        sa.Column("stored_filename", sa.String(255), nullable=False),
        sa.Column("content_type", sa.String(150), nullable=True),
        sa.Column("file_size_bytes", sa.Integer(), nullable=False, server_default="0"),
        sa.Column("local_relative_path", sa.String(1000), nullable=False),
        sa.Column("version_no", sa.Integer(), nullable=False, server_default="1"),
        sa.Column("status", sa.String(30), nullable=False, server_default="active"),
        sa.Column("is_deleted", sa.Boolean(), nullable=False, server_default=sa.false()),
        sa.Column("deleted_at_utc", sa.DateTime(timezone=True), nullable=True),
        sa.Column("deleted_by_user_id", sa.Integer(), nullable=True),
        sa.Column("uploaded_by_user_id", sa.Integer(), nullable=True),
        sa.Column("uploaded_at_utc", sa.DateTime(timezone=True), nullable=False),
        sa.ForeignKeyConstraint(["tenant_id"], ["tenants.id"], name="fk_notice_case_documents_tenant", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["branch_id"], ["branches.id"], name="fk_notice_case_documents_branch", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["case_id"], ["notice_cases.id"], name="fk_notice_case_documents_case", ondelete="CASCADE"),
        sa.ForeignKeyConstraint(["event_id"], ["notice_case_events.id"], name="fk_notice_case_documents_event", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["deleted_by_user_id"], ["users.id"], name="fk_notice_case_documents_deleted_by", ondelete="SET NULL"),
        sa.ForeignKeyConstraint(["uploaded_by_user_id"], ["users.id"], name="fk_notice_case_documents_uploaded_by", ondelete="SET NULL"),
    )
    for col in ["tenant_id","branch_id","case_id","event_id","document_type","status","is_deleted","uploaded_by_user_id"]:
        op.create_index(f"ix_notice_case_documents_{col}", "notice_case_documents", [col])


def downgrade() -> None:
    op.drop_table("notice_case_documents")
    op.drop_table("notice_case_orders")
    op.drop_table("notice_case_hearings")
    op.drop_table("notice_case_events")
    op.drop_table("notice_cases")
