"""Phase 4 registrations, related persons, renewal lifecycle and expiry dashboard.

Revision ID: 20260723_phase4_registration_lifecycle
Revises: 20260723_phase3_consultant_task_execution
"""
from alembic import op
import sqlalchemy as sa

revision="20260723_phase4_registration_lifecycle"
down_revision="20260723_phase3_consultant_task_execution"
branch_labels=None
depends_on=None

def upgrade():
    op.create_table("registration_types",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("code",sa.String(60),nullable=False),sa.Column("name",sa.String(160),nullable=False),sa.Column("category",sa.String(80),nullable=False,server_default="registration"),sa.Column("issuing_authority",sa.String(200)),sa.Column("identifier_label",sa.String(100),nullable=False,server_default="Registration Number"),sa.Column("supports_expiry",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("supports_related_person",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("is_system",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("is_active",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("sort_order",sa.Integer(),nullable=False,server_default="0"),sa.Column("created_at_utc",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at_utc",sa.DateTime(timezone=True),nullable=False),sa.UniqueConstraint("code",name="uq_registration_types_code"))
    op.create_index("ix_registration_types_code","registration_types",["code"])
    op.create_table("client_related_persons",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("tenant_id",sa.Integer(),sa.ForeignKey("tenants.id",ondelete="CASCADE"),nullable=False),sa.Column("client_id",sa.Integer(),sa.ForeignKey("clients.id",ondelete="CASCADE"),nullable=False),sa.Column("person_type",sa.String(60),nullable=False),sa.Column("full_name",sa.String(200),nullable=False),sa.Column("designation",sa.String(120)),sa.Column("pan",sa.String(20)),sa.Column("din",sa.String(20)),sa.Column("date_of_birth",sa.Date()),sa.Column("appointment_date",sa.Date()),sa.Column("cessation_date",sa.Date()),sa.Column("mobile",sa.String(20)),sa.Column("email",sa.String(255)),sa.Column("authorised_signatory",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("is_active",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("notes",sa.Text()),sa.Column("created_by_user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("created_at_utc",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at_utc",sa.DateTime(timezone=True),nullable=False))
    for c in ["tenant_id","client_id","person_type","pan","din"]: op.create_index(f"ix_client_related_persons_{c}","client_related_persons",[c])
    op.create_table("registration_lifecycle_rules",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("tenant_id",sa.Integer(),sa.ForeignKey("tenants.id",ondelete="CASCADE")),sa.Column("registration_type_id",sa.Integer(),sa.ForeignKey("registration_types.id"),nullable=False),sa.Column("entity_type",sa.String(100)),sa.Column("jurisdiction",sa.String(160)),sa.Column("state_code",sa.String(10)),sa.Column("cycle_type",sa.String(40),nullable=False,server_default="expiry_renewal"),sa.Column("frequency_months",sa.Integer()),sa.Column("renewal_window_days",sa.Integer(),nullable=False,server_default="60"),sa.Column("reminder_days_csv",sa.String(100),nullable=False,server_default="90,60,30,15,7,0"),sa.Column("grace_period_days",sa.Integer(),nullable=False,server_default="0"),sa.Column("service_catalogue_id",sa.Integer(),sa.ForeignKey("service_catalogues.id",ondelete="SET NULL")),sa.Column("requires_client_approval",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("requires_partner_review",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("auto_create_task",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("is_active",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("effective_from",sa.Date()),sa.Column("effective_to",sa.Date()),sa.Column("created_by_user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("created_at_utc",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at_utc",sa.DateTime(timezone=True),nullable=False))
    for c in ["tenant_id","registration_type_id","entity_type","service_catalogue_id"]: op.create_index(f"ix_registration_lifecycle_rules_{c}","registration_lifecycle_rules",[c])
    op.create_table("client_registrations",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("tenant_id",sa.Integer(),sa.ForeignKey("tenants.id",ondelete="CASCADE"),nullable=False),sa.Column("branch_id",sa.Integer(),sa.ForeignKey("branches.id",ondelete="SET NULL")),sa.Column("client_id",sa.Integer(),sa.ForeignKey("clients.id",ondelete="CASCADE"),nullable=False),sa.Column("registration_type_id",sa.Integer(),sa.ForeignKey("registration_types.id"),nullable=False),sa.Column("related_person_id",sa.Integer(),sa.ForeignKey("client_related_persons.id",ondelete="SET NULL")),sa.Column("registration_number",sa.String(120),nullable=False),sa.Column("jurisdiction",sa.String(160)),sa.Column("state_code",sa.String(10)),sa.Column("issue_date",sa.Date()),sa.Column("effective_from",sa.Date()),sa.Column("valid_until",sa.Date()),sa.Column("next_action_date",sa.Date()),sa.Column("status",sa.String(40),nullable=False,server_default="active"),sa.Column("primary_registration",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("lifecycle_rule_id",sa.Integer(),sa.ForeignKey("registration_lifecycle_rules.id")),sa.Column("responsible_party",sa.String(30),nullable=False,server_default="firm"),sa.Column("assigned_user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("assigned_consultant_id",sa.Integer(),sa.ForeignKey("consultant_profiles.id",ondelete="SET NULL")),sa.Column("auto_create_task",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("portal_url",sa.String(500)),sa.Column("notes",sa.Text()),sa.Column("metadata_json",sa.JSON()),sa.Column("created_by_user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("created_at_utc",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at_utc",sa.DateTime(timezone=True),nullable=False),sa.UniqueConstraint("tenant_id","registration_type_id","registration_number",name="uq_client_registration_number"))
    for c in ["tenant_id","branch_id","client_id","registration_type_id","related_person_id","registration_number","valid_until","next_action_date","status","lifecycle_rule_id","assigned_user_id","assigned_consultant_id"]: op.create_index(f"ix_client_registrations_{c}","client_registrations",[c])
    op.create_table("client_digital_signatures",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("tenant_id",sa.Integer(),sa.ForeignKey("tenants.id",ondelete="CASCADE"),nullable=False),sa.Column("client_id",sa.Integer(),sa.ForeignKey("clients.id",ondelete="CASCADE"),nullable=False),sa.Column("related_person_id",sa.Integer(),sa.ForeignKey("client_related_persons.id",ondelete="CASCADE"),nullable=False),sa.Column("certificate_serial_number",sa.String(200)),sa.Column("provider",sa.String(120)),sa.Column("token_make",sa.String(120)),sa.Column("class_type",sa.String(60)),sa.Column("issued_on",sa.Date()),sa.Column("expires_on",sa.Date(),nullable=False),sa.Column("status",sa.String(30),nullable=False,server_default="active"),sa.Column("custody_location",sa.String(200)),sa.Column("renewal_responsibility",sa.String(30),nullable=False,server_default="firm"),sa.Column("assigned_consultant_id",sa.Integer(),sa.ForeignKey("consultant_profiles.id",ondelete="SET NULL")),sa.Column("notes",sa.Text()),sa.Column("created_by_user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("created_at_utc",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at_utc",sa.DateTime(timezone=True),nullable=False))
    for c in ["tenant_id","client_id","related_person_id","certificate_serial_number","expires_on","status","assigned_consultant_id"]: op.create_index(f"ix_client_digital_signatures_{c}","client_digital_signatures",[c])
    op.create_table("registration_document_templates",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("tenant_id",sa.Integer(),sa.ForeignKey("tenants.id",ondelete="CASCADE")),sa.Column("registration_type_id",sa.Integer(),sa.ForeignKey("registration_types.id"),nullable=False),sa.Column("action_type",sa.String(40),nullable=False,server_default="renewal"),sa.Column("document_name",sa.String(200),nullable=False),sa.Column("description",sa.Text()),sa.Column("mandatory",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("sort_order",sa.Integer(),nullable=False,server_default="0"),sa.Column("is_active",sa.Boolean(),nullable=False,server_default=sa.true()))
    op.create_table("registration_renewal_tasks",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("tenant_id",sa.Integer(),sa.ForeignKey("tenants.id",ondelete="CASCADE"),nullable=False),sa.Column("branch_id",sa.Integer(),sa.ForeignKey("branches.id",ondelete="SET NULL")),sa.Column("client_id",sa.Integer(),sa.ForeignKey("clients.id",ondelete="CASCADE"),nullable=False),sa.Column("registration_id",sa.Integer(),sa.ForeignKey("client_registrations.id",ondelete="CASCADE"),nullable=False),sa.Column("lifecycle_rule_id",sa.Integer(),sa.ForeignKey("registration_lifecycle_rules.id",ondelete="SET NULL")),sa.Column("cycle_due_date",sa.Date(),nullable=False),sa.Column("action_type",sa.String(40),nullable=False,server_default="renewal"),sa.Column("title",sa.String(255),nullable=False),sa.Column("status",sa.String(40),nullable=False,server_default="pending"),sa.Column("priority",sa.String(20),nullable=False,server_default="normal"),sa.Column("assigned_user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("assigned_consultant_id",sa.Integer(),sa.ForeignKey("consultant_profiles.id",ondelete="SET NULL")),sa.Column("client_approval_required",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("partner_review_required",sa.Boolean(),nullable=False,server_default=sa.false()),sa.Column("submitted_on",sa.Date()),sa.Column("acknowledgement_number",sa.String(160)),sa.Column("completed_on",sa.Date()),sa.Column("notes",sa.Text()),sa.Column("created_at_utc",sa.DateTime(timezone=True),nullable=False),sa.Column("updated_at_utc",sa.DateTime(timezone=True),nullable=False),sa.UniqueConstraint("registration_id","cycle_due_date",name="uq_registration_renewal_cycle"))
    for c in ["tenant_id","branch_id","client_id","registration_id","lifecycle_rule_id","cycle_due_date","status","assigned_user_id","assigned_consultant_id"]: op.create_index(f"ix_registration_renewal_tasks_{c}","registration_renewal_tasks",[c])
    op.create_table("registration_renewal_task_documents",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("renewal_task_id",sa.Integer(),sa.ForeignKey("registration_renewal_tasks.id",ondelete="CASCADE"),nullable=False),sa.Column("template_id",sa.Integer(),sa.ForeignKey("registration_document_templates.id",ondelete="SET NULL")),sa.Column("document_name",sa.String(200),nullable=False),sa.Column("mandatory",sa.Boolean(),nullable=False,server_default=sa.true()),sa.Column("status",sa.String(30),nullable=False,server_default="pending"),sa.Column("document_id",sa.Integer()),sa.Column("remarks",sa.Text()))
    op.create_table("client_registration_actions",sa.Column("id",sa.Integer(),primary_key=True),sa.Column("tenant_id",sa.Integer(),sa.ForeignKey("tenants.id",ondelete="CASCADE"),nullable=False),sa.Column("client_id",sa.Integer(),sa.ForeignKey("clients.id",ondelete="CASCADE"),nullable=False),sa.Column("registration_id",sa.Integer(),sa.ForeignKey("client_registrations.id",ondelete="CASCADE"),nullable=False),sa.Column("renewal_task_id",sa.Integer(),sa.ForeignKey("registration_renewal_tasks.id",ondelete="SET NULL")),sa.Column("action_type",sa.String(40),nullable=False),sa.Column("previous_valid_until",sa.Date()),sa.Column("new_valid_until",sa.Date()),sa.Column("application_date",sa.Date()),sa.Column("submission_date",sa.Date()),sa.Column("acknowledgement_number",sa.String(160)),sa.Column("completion_date",sa.Date()),sa.Column("status",sa.String(40),nullable=False,server_default="completed"),sa.Column("performed_by_user_id",sa.Integer(),sa.ForeignKey("users.id",ondelete="SET NULL")),sa.Column("performed_by_consultant_id",sa.Integer(),sa.ForeignKey("consultant_profiles.id",ondelete="SET NULL")),sa.Column("notes",sa.Text()),sa.Column("created_at_utc",sa.DateTime(timezone=True),nullable=False))

def downgrade():
    for table in ["client_registration_actions","registration_renewal_task_documents","registration_renewal_tasks","registration_document_templates","client_digital_signatures","client_registrations","registration_lifecycle_rules","client_related_persons","registration_types"]: op.drop_table(table)

