from __future__ import annotations
from datetime import date, datetime, timezone
from sqlalchemy import Boolean, Date, DateTime, ForeignKey, Integer, JSON, Numeric, String, Text, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column
from app.core.db.common import CommonBase


def utcnow(): return datetime.now(timezone.utc)

class RegistrationType(CommonBase):
    __tablename__="registration_types"
    __table_args__=(UniqueConstraint("code", name="uq_registration_types_code"),)
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    code: Mapped[str]=mapped_column(String(60), nullable=False, index=True)
    name: Mapped[str]=mapped_column(String(160), nullable=False)
    category: Mapped[str]=mapped_column(String(80), nullable=False, default="registration")
    issuing_authority: Mapped[str|None]=mapped_column(String(200))
    identifier_label: Mapped[str]=mapped_column(String(100), nullable=False, default="Registration Number")
    supports_expiry: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    supports_related_person: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    is_system: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    is_active: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    sort_order: Mapped[int]=mapped_column(Integer, nullable=False, default=0)
    created_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, nullable=False)
    updated_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)

class ClientRelatedPerson(CommonBase):
    __tablename__="client_related_persons"
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int]=mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    client_id: Mapped[int]=mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    business_unit_id: Mapped[int|None]=mapped_column(ForeignKey("client_business_units.id", ondelete="SET NULL"), index=True)
    client_branch_id: Mapped[int|None]=mapped_column(ForeignKey("client_branches.id", ondelete="SET NULL"), index=True)
    person_type: Mapped[str]=mapped_column(String(60), nullable=False, index=True)
    full_name: Mapped[str]=mapped_column(String(200), nullable=False)
    designation: Mapped[str|None]=mapped_column(String(120))
    pan: Mapped[str|None]=mapped_column(String(20), index=True)
    din: Mapped[str|None]=mapped_column(String(20), index=True)
    date_of_birth: Mapped[date|None]=mapped_column(Date)
    appointment_date: Mapped[date|None]=mapped_column(Date)
    cessation_date: Mapped[date|None]=mapped_column(Date)
    mobile: Mapped[str|None]=mapped_column(String(20))
    email: Mapped[str|None]=mapped_column(String(255))
    authorised_signatory: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    is_active: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    notes: Mapped[str|None]=mapped_column(Text)
    created_by_user_id: Mapped[int|None]=mapped_column(ForeignKey("users.id", ondelete="SET NULL"))
    created_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, nullable=False)
    updated_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)

class ClientRegistration(CommonBase):
    __tablename__="client_registrations"
    __table_args__=(UniqueConstraint("tenant_id","registration_type_id","registration_number", name="uq_client_registration_number"),)
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int]=mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int|None]=mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), index=True)
    client_id: Mapped[int]=mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    business_unit_id: Mapped[int|None]=mapped_column(ForeignKey("client_business_units.id", ondelete="SET NULL"), index=True)
    client_branch_id: Mapped[int|None]=mapped_column(ForeignKey("client_branches.id", ondelete="SET NULL"), index=True)
    registration_type_id: Mapped[int]=mapped_column(ForeignKey("registration_types.id"), nullable=False, index=True)
    related_person_id: Mapped[int|None]=mapped_column(ForeignKey("client_related_persons.id", ondelete="SET NULL"), index=True)
    registration_number: Mapped[str]=mapped_column(String(120), nullable=False, index=True)
    legal_name: Mapped[str|None]=mapped_column(String(200))
    trade_name: Mapped[str|None]=mapped_column(String(200))
    state: Mapped[str|None]=mapped_column(String(100))
    jurisdiction: Mapped[str|None]=mapped_column(String(160))
    state_code: Mapped[str|None]=mapped_column(String(10))
    issue_date: Mapped[date|None]=mapped_column(Date)
    effective_from: Mapped[date|None]=mapped_column(Date)
    valid_until: Mapped[date|None]=mapped_column(Date, index=True)
    next_action_date: Mapped[date|None]=mapped_column(Date, index=True)
    status: Mapped[str]=mapped_column(String(40), nullable=False, default="active", index=True)
    primary_registration: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    lifecycle_rule_id: Mapped[int|None]=mapped_column(ForeignKey("registration_lifecycle_rules.id", use_alter=True, name="fk_client_registration_rule"), index=True)
    responsible_party: Mapped[str]=mapped_column(String(30), nullable=False, default="firm")
    assigned_user_id: Mapped[int|None]=mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
    assigned_consultant_id: Mapped[int|None]=mapped_column(ForeignKey("consultant_profiles.id", ondelete="SET NULL"), index=True)
    auto_create_task: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    portal_url: Mapped[str|None]=mapped_column(String(500))
    notes: Mapped[str|None]=mapped_column(Text)
    metadata_json: Mapped[dict|None]=mapped_column(JSON)
    created_by_user_id: Mapped[int|None]=mapped_column(ForeignKey("users.id", ondelete="SET NULL"))
    created_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, nullable=False)
    updated_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)

class ClientDigitalSignature(CommonBase):
    __tablename__="client_digital_signatures"
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int]=mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    client_id: Mapped[int]=mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    related_person_id: Mapped[int]=mapped_column(ForeignKey("client_related_persons.id", ondelete="CASCADE"), nullable=False, index=True)
    certificate_serial_number: Mapped[str|None]=mapped_column(String(200), index=True)
    provider: Mapped[str|None]=mapped_column(String(120))
    token_make: Mapped[str|None]=mapped_column(String(120))
    class_type: Mapped[str|None]=mapped_column(String(60))
    issued_on: Mapped[date|None]=mapped_column(Date)
    expires_on: Mapped[date]=mapped_column(Date, nullable=False, index=True)
    status: Mapped[str]=mapped_column(String(30), nullable=False, default="active", index=True)
    custody_location: Mapped[str|None]=mapped_column(String(200))
    renewal_responsibility: Mapped[str]=mapped_column(String(30), nullable=False, default="firm")
    assigned_consultant_id: Mapped[int|None]=mapped_column(ForeignKey("consultant_profiles.id", ondelete="SET NULL"), index=True)
    notes: Mapped[str|None]=mapped_column(Text)
    created_by_user_id: Mapped[int|None]=mapped_column(ForeignKey("users.id", ondelete="SET NULL"))
    created_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, nullable=False)
    updated_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)

class RegistrationLifecycleRule(CommonBase):
    __tablename__="registration_lifecycle_rules"
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int|None]=mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), index=True)
    registration_type_id: Mapped[int]=mapped_column(ForeignKey("registration_types.id"), nullable=False, index=True)
    entity_type: Mapped[str|None]=mapped_column(String(100), index=True)
    jurisdiction: Mapped[str|None]=mapped_column(String(160))
    state_code: Mapped[str|None]=mapped_column(String(10))
    cycle_type: Mapped[str]=mapped_column(String(40), nullable=False, default="expiry_renewal")
    frequency_months: Mapped[int|None]=mapped_column(Integer)
    renewal_window_days: Mapped[int]=mapped_column(Integer, nullable=False, default=60)
    reminder_days_csv: Mapped[str]=mapped_column(String(100), nullable=False, default="90,60,30,15,7,0")
    grace_period_days: Mapped[int]=mapped_column(Integer, nullable=False, default=0)
    service_catalogue_id: Mapped[int|None]=mapped_column(ForeignKey("service_catalogue.id", ondelete="SET NULL"), index=True)
    requires_client_approval: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    requires_partner_review: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    auto_create_task: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    is_active: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    effective_from: Mapped[date|None]=mapped_column(Date)
    effective_to: Mapped[date|None]=mapped_column(Date)
    created_by_user_id: Mapped[int|None]=mapped_column(ForeignKey("users.id", ondelete="SET NULL"))
    created_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, nullable=False)
    updated_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)

class RegistrationDocumentTemplate(CommonBase):
    __tablename__="registration_document_templates"
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int|None]=mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), index=True)
    registration_type_id: Mapped[int]=mapped_column(ForeignKey("registration_types.id"), nullable=False, index=True)
    action_type: Mapped[str]=mapped_column(String(40), nullable=False, default="renewal", index=True)
    document_name: Mapped[str]=mapped_column(String(200), nullable=False)
    description: Mapped[str|None]=mapped_column(Text)
    mandatory: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    sort_order: Mapped[int]=mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)

class RegistrationRenewalTask(CommonBase):
    __tablename__="registration_renewal_tasks"
    __table_args__=(UniqueConstraint("registration_id","cycle_due_date", name="uq_registration_renewal_cycle"),)
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int]=mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int|None]=mapped_column(ForeignKey("branches.id", ondelete="SET NULL"), index=True)
    client_id: Mapped[int]=mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    registration_id: Mapped[int]=mapped_column(ForeignKey("client_registrations.id", ondelete="CASCADE"), nullable=False, index=True)
    lifecycle_rule_id: Mapped[int|None]=mapped_column(ForeignKey("registration_lifecycle_rules.id", ondelete="SET NULL"), index=True)
    cycle_due_date: Mapped[date]=mapped_column(Date, nullable=False, index=True)
    action_type: Mapped[str]=mapped_column(String(40), nullable=False, default="renewal")
    title: Mapped[str]=mapped_column(String(255), nullable=False)
    status: Mapped[str]=mapped_column(String(40), nullable=False, default="pending", index=True)
    priority: Mapped[str]=mapped_column(String(20), nullable=False, default="normal")
    assigned_user_id: Mapped[int|None]=mapped_column(ForeignKey("users.id", ondelete="SET NULL"), index=True)
    assigned_consultant_id: Mapped[int|None]=mapped_column(ForeignKey("consultant_profiles.id", ondelete="SET NULL"), index=True)
    client_approval_required: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    partner_review_required: Mapped[bool]=mapped_column(Boolean, nullable=False, default=False)
    submitted_on: Mapped[date|None]=mapped_column(Date)
    acknowledgement_number: Mapped[str|None]=mapped_column(String(160))
    completed_on: Mapped[date|None]=mapped_column(Date)
    notes: Mapped[str|None]=mapped_column(Text)
    created_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, nullable=False)
    updated_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, onupdate=utcnow, nullable=False)

class RegistrationRenewalTaskDocument(CommonBase):
    __tablename__="registration_renewal_task_documents"
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    renewal_task_id: Mapped[int]=mapped_column(ForeignKey("registration_renewal_tasks.id", ondelete="CASCADE"), nullable=False, index=True)
    template_id: Mapped[int|None]=mapped_column(ForeignKey("registration_document_templates.id", ondelete="SET NULL"))
    document_name: Mapped[str]=mapped_column(String(200), nullable=False)
    mandatory: Mapped[bool]=mapped_column(Boolean, nullable=False, default=True)
    status: Mapped[str]=mapped_column(String(30), nullable=False, default="pending")
    document_id: Mapped[int|None]=mapped_column(Integer)
    remarks: Mapped[str|None]=mapped_column(Text)

class ClientRegistrationAction(CommonBase):
    __tablename__="client_registration_actions"
    id: Mapped[int]=mapped_column(Integer, primary_key=True)
    tenant_id: Mapped[int]=mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    client_id: Mapped[int]=mapped_column(ForeignKey("clients.id", ondelete="CASCADE"), nullable=False, index=True)
    registration_id: Mapped[int]=mapped_column(ForeignKey("client_registrations.id", ondelete="CASCADE"), nullable=False, index=True)
    renewal_task_id: Mapped[int|None]=mapped_column(ForeignKey("registration_renewal_tasks.id", ondelete="SET NULL"), index=True)
    action_type: Mapped[str]=mapped_column(String(40), nullable=False, index=True)
    previous_valid_until: Mapped[date|None]=mapped_column(Date)
    new_valid_until: Mapped[date|None]=mapped_column(Date)
    application_date: Mapped[date|None]=mapped_column(Date)
    submission_date: Mapped[date|None]=mapped_column(Date)
    acknowledgement_number: Mapped[str|None]=mapped_column(String(160))
    completion_date: Mapped[date|None]=mapped_column(Date)
    status: Mapped[str]=mapped_column(String(40), nullable=False, default="completed")
    performed_by_user_id: Mapped[int|None]=mapped_column(ForeignKey("users.id", ondelete="SET NULL"))
    performed_by_consultant_id: Mapped[int|None]=mapped_column(ForeignKey("consultant_profiles.id", ondelete="SET NULL"))
    notes: Mapped[str|None]=mapped_column(Text)
    created_at_utc: Mapped[datetime]=mapped_column(DateTime(timezone=True), default=utcnow, nullable=False)
