from app.core.database import Base
from sqlalchemy import Column, Integer, String, Text, DateTime, Date, Boolean, DECIMAL, Enum, ForeignKey, JSON, UniqueConstraint
from sqlalchemy.sql import func
from sqlalchemy.orm import relationship
import enum


# ===== ENUMS =====

class UserRole(str, enum.Enum):
    admin = "admin"
    staff = "staff"


class UserStatus(str, enum.Enum):
    active = "active"
    disabled = "disabled"


class CustomerType(str, enum.Enum):
    project = "project"
    subscription = "subscription"
    mixed = "mixed"


class CustomerStatus(str, enum.Enum):
    active = "active"
    paused = "paused"


class ProductType(str, enum.Enum):
    project = "project"
    subscription = "subscription"
    product = "product"


class ProductStatus(str, enum.Enum):
    active = "active"
    archived = "archived"


class InvoiceStatus(str, enum.Enum):
    draft = "draft"
    issued = "issued"
    sent = "sent"
    partially_paid = "partially_paid"
    paid = "paid"
    overdue = "overdue"
    void = "void"


class BillingCycle(str, enum.Enum):
    monthly = "monthly"
    yearly = "yearly"


class SubscriptionStatus(str, enum.Enum):
    active = "active"
    cancelled = "cancelled"
    expired = "expired"


class ReminderType(str, enum.Enum):
    subscription_expiry = "subscription_expiry"
    invoice_overdue = "invoice_overdue"
    custom = "custom"


class ReminderActiveStatus(str, enum.Enum):
    active = "active"
    disabled = "disabled"


class EmailLogStatus(str, enum.Enum):
    sent = "sent"
    failed = "failed"


class TelegramLogStatus(str, enum.Enum):
    sent = "sent"
    failed = "failed"


# ===== MODELS =====

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(100), nullable=False, unique=True)
    password_hash = Column(String(255), nullable=False)
    display_name = Column(String(100))
    role = Column(Enum(UserRole), default=UserRole.admin)
    status = Column(Enum(UserStatus), default=UserStatus.active)
    last_login_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, server_default=func.now())


class Customer(Base):
    __tablename__ = "customers"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(200), nullable=False)
    company_name = Column(String(200))
    contact_name = Column(String(100))
    email = Column(String(200))
    phone = Column(String(50))
    mobile = Column(String(50))
    address = Column(Text)
    website = Column(String(300))
    customer_type = Column(Enum(CustomerType), default=CustomerType.project)
    status = Column(Enum(CustomerStatus), default=CustomerStatus.active)
    notes = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # Relationships
    contacts = relationship("CustomerContact", back_populates="customer", cascade="all, delete-orphan")
    invoices = relationship("Invoice", back_populates="customer")
    subscriptions = relationship("Subscription", back_populates="customer")


class CustomerContact(Base):
    __tablename__ = "customer_contacts"

    id = Column(Integer, primary_key=True, autoincrement=True)
    customer_id = Column(Integer, ForeignKey("customers.id", ondelete="CASCADE"), nullable=False)
    name = Column(String(100), nullable=False)
    title = Column(String(100))
    email = Column(String(200))
    phone = Column(String(50))
    mobile = Column(String(50))
    is_primary = Column(Boolean, default=False)
    notes = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    customer = relationship("Customer", back_populates="contacts")


class Product(Base):
    __tablename__ = "products"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(200), nullable=False)
    description = Column(Text)
    product_type = Column(Enum(ProductType), default=ProductType.project)
    unit_price = Column(DECIMAL(12, 2), default=0)
    tax_rate = Column(DECIMAL(5, 4), default=0.1500)
    unit = Column(String(50), default="unit")
    status = Column(Enum(ProductStatus), default=ProductStatus.active)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    invoice_items = relationship("InvoiceItem", back_populates="product")
    subscriptions = relationship("Subscription", back_populates="product")


class Invoice(Base):
    __tablename__ = "invoices"

    id = Column(Integer, primary_key=True, autoincrement=True)
    invoice_number = Column(String(50), nullable=False, unique=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False)
    invoice_date = Column(Date, nullable=False)
    due_date = Column(Date)
    subtotal = Column(DECIMAL(12, 2), default=0)
    total_tax = Column(DECIMAL(12, 2), default=0)
    total_amount = Column(DECIMAL(12, 2), default=0)
    currency = Column(String(10), default="NZD")
    status = Column(Enum(InvoiceStatus), default=InvoiceStatus.draft)
    notes = Column(Text)
    issued_at = Column(DateTime, nullable=True)
    sent_at = Column(DateTime, nullable=True)
    paid_at = Column(DateTime, nullable=True)
    voided_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    # Relationships
    customer = relationship("Customer", back_populates="invoices")
    items = relationship("InvoiceItem", back_populates="invoice", cascade="all, delete-orphan")
    payments = relationship("Payment", back_populates="invoice")
    events = relationship("InvoiceEvent", back_populates="invoice")


class InvoiceItem(Base):
    __tablename__ = "invoice_items"

    id = Column(Integer, primary_key=True, autoincrement=True)
    invoice_id = Column(Integer, ForeignKey("invoices.id", ondelete="CASCADE"), nullable=False)
    product_id = Column(Integer, ForeignKey("products.id", ondelete="SET NULL"), nullable=True)
    description = Column(String(500))
    quantity = Column(DECIMAL(10, 2), default=1)
    unit_price = Column(DECIMAL(12, 2), default=0)
    tax_rate = Column(DECIMAL(5, 4), default=0.1500)
    tax_mode = Column(String(30), default="gst_15")
    custom_tax_rate = Column(DECIMAL(8, 4), nullable=True)
    subtotal = Column(DECIMAL(12, 2), default=0)
    tax_amount = Column(DECIMAL(12, 2), default=0)
    total = Column(DECIMAL(12, 2), default=0)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    invoice = relationship("Invoice", back_populates="items")
    product = relationship("Product", back_populates="invoice_items")


class Subscription(Base):
    __tablename__ = "subscriptions"

    id = Column(Integer, primary_key=True, autoincrement=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False)
    product_id = Column(Integer, ForeignKey("products.id"), nullable=False)
    start_date = Column(Date, nullable=False)
    end_date = Column(Date)
    billing_cycle = Column(Enum(BillingCycle), default=BillingCycle.monthly)
    status = Column(Enum(SubscriptionStatus), default=SubscriptionStatus.active)
    auto_renew = Column(Boolean, default=False)
    next_invoice_date = Column(Date)
    last_invoice_id = Column(Integer, ForeignKey("invoices.id", ondelete="SET NULL"), nullable=True)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    customer = relationship("Customer", back_populates="subscriptions")
    product = relationship("Product", back_populates="subscriptions")
    last_invoice = relationship("Invoice")


class Payment(Base):
    __tablename__ = "payments"

    id = Column(Integer, primary_key=True, autoincrement=True)
    invoice_id = Column(Integer, ForeignKey("invoices.id"), nullable=False)
    paid_at = Column(DateTime, server_default=func.now())
    received_date = Column(Date, nullable=True)
    amount = Column(DECIMAL(12, 2), nullable=False)
    currency = Column(String(10), default="NZD")
    method = Column(String(50), default="bank_transfer")
    reference = Column(String(200))
    notes = Column(Text)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    invoice = relationship("Invoice", back_populates="payments")


class InvoiceEvent(Base):
    __tablename__ = "invoice_events"

    id = Column(Integer, primary_key=True, autoincrement=True)
    invoice_id = Column(Integer, ForeignKey("invoices.id"), nullable=False)
    event_type = Column(String(50), nullable=False)
    actor_type = Column(String(20), default="user")
    actor_name = Column(String(100))
    message = Column(Text)
    metadata_json = Column(Text)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    invoice = relationship("Invoice", back_populates="events")


class ReceiptAsset(Base):
    __tablename__ = "receipt_assets"

    id = Column(Integer, primary_key=True, autoincrement=True)
    original_filename = Column(String(255), nullable=False)
    storage_path_original = Column(String(500), nullable=False)
    storage_path_preview = Column(String(500), nullable=False)
    mime_type = Column(String(100), nullable=False)
    file_size_original = Column(Integer, nullable=False)
    file_size_preview = Column(Integer, nullable=False)
    image_width = Column(Integer)
    image_height = Column(Integer)
    upload_user = Column(String(100))
    processing_status = Column(String(30), default="uploaded")
    ocr_snapshot_json = Column(Text)
    ai_extraction_snapshot_json = Column(Text)
    uploaded_at = Column(DateTime, server_default=func.now())

    expenses = relationship("Expense", back_populates="receipt_asset")


class Expense(Base):
    __tablename__ = "expenses"

    id = Column(Integer, primary_key=True, autoincrement=True)
    receipt_asset_id = Column(Integer, ForeignKey("receipt_assets.id", ondelete="SET NULL"), nullable=True)
    expense_date = Column(Date, nullable=False)
    vendor_name = Column(String(200), nullable=False)
    category = Column(String(100), nullable=False)
    description = Column(Text)
    amount_net = Column(DECIMAL(12, 2), default=0)
    gst_amount = Column(DECIMAL(12, 2), default=0)
    tax_mode = Column(String(30), default="gst_15")
    custom_tax_rate = Column(DECIMAL(8, 4), nullable=True)
    gst_amount_override = Column(DECIMAL(12, 2), nullable=True)
    gst_amount_overridden = Column(Boolean, default=False)
    amount_gross = Column(DECIMAL(12, 2), default=0)
    currency = Column(String(10), default="NZD")
    payment_method = Column(String(50))
    gst_claimable = Column(Boolean, default=True)
    source = Column(String(50), default="manual")
    status = Column(String(30), default="draft")
    notes = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    receipt_asset = relationship("ReceiptAsset", back_populates="expenses")


class Employee(Base):
    __tablename__ = "employees"

    id = Column(Integer, primary_key=True, autoincrement=True)
    full_name = Column(String(200), nullable=False)
    preferred_name = Column(String(200))
    email = Column(String(200))
    phone = Column(String(50))
    role_title = Column(String(100))
    department = Column(String(100))
    employment_type = Column(String(50))
    ird_number = Column(String(100))
    start_date = Column(Date, nullable=False)
    end_date = Column(Date)
    status = Column(String(30), default="active")
    tax_notes = Column(Text)
    notes = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    payroll_records = relationship("PayrollRecord", back_populates="employee", cascade="all, delete-orphan")


class PayrollRecord(Base):
    __tablename__ = "payroll_records"

    id = Column(Integer, primary_key=True, autoincrement=True)
    employee_id = Column(Integer, ForeignKey("employees.id", ondelete="CASCADE"), nullable=False)
    period_start = Column(Date, nullable=False)
    period_end = Column(Date, nullable=False)
    pay_date = Column(Date, nullable=False)
    gross_pay = Column(DECIMAL(12, 2), default=0)
    bonus_amount = Column(DECIMAL(12, 2), default=0)
    allowance_amount = Column(DECIMAL(12, 2), default=0)
    deduction_amount = Column(DECIMAL(12, 2), default=0)
    paye_amount = Column(DECIMAL(12, 2), default=0)
    kiwisaver_employee = Column(DECIMAL(12, 2), default=0)
    kiwisaver_employer = Column(DECIMAL(12, 2), default=0)
    acc_amount = Column(DECIMAL(12, 2), default=0)
    other_tax_amount = Column(DECIMAL(12, 2), default=0)
    net_pay = Column(DECIMAL(12, 2), default=0)
    calculation_mode = Column(String(30), default="manual")
    status = Column(String(30), default="draft")
    notes = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    employee = relationship("Employee", back_populates="payroll_records")


class GstSetting(Base):
    __tablename__ = "gst_settings"

    id = Column(Integer, primary_key=True, autoincrement=True)
    cycle_months = Column(Integer, nullable=False)
    cycle_start_date = Column(Date, nullable=False)
    enabled_from = Column(Date)
    reporting_label = Column(String(100))
    default_basis = Column(String(30), default="invoice_date")
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())


class GstReturn(Base):
    __tablename__ = "gst_returns"
    __table_args__ = (UniqueConstraint("period_start", "period_end", name="uq_gst_returns_period"),)

    id = Column(Integer, primary_key=True, autoincrement=True)
    period_start = Column(Date, nullable=False)
    period_end = Column(Date, nullable=False)
    status = Column(String(30), default="draft")
    total_sales_income = Column(DECIMAL(12, 2), default=0)
    zero_rated_supplies = Column(DECIMAL(12, 2), default=0)
    gst_taxable_income = Column(DECIMAL(12, 2), default=0)
    gst_output = Column(DECIMAL(12, 2), default=0)
    debit_adjustments = Column(DECIMAL(12, 2), default=0)
    total_purchases_expenses = Column(DECIMAL(12, 2), default=0)
    gst_input = Column(DECIMAL(12, 2), default=0)
    credit_adjustments = Column(DECIMAL(12, 2), default=0)
    gst_payable = Column(DECIMAL(12, 2), default=0)
    prepared_at = Column(DateTime, nullable=True)
    locked_at = Column(DateTime, nullable=True)
    filed_at = Column(DateTime, nullable=True)
    notes = Column(Text)
    created_at = Column(DateTime, server_default=func.now())
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())

    snapshot_rows = relationship("GstReturnSnapshotRow", back_populates="gst_return", cascade="all, delete-orphan")
    adjustments = relationship("GstAdjustment", back_populates="gst_return")


class GstReturnSnapshotRow(Base):
    __tablename__ = "gst_return_snapshot_rows"

    id = Column(Integer, primary_key=True, autoincrement=True)
    gst_return_id = Column(Integer, ForeignKey("gst_returns.id", ondelete="CASCADE"), nullable=False)
    row_type = Column(String(30), nullable=False)
    source_id = Column(Integer, nullable=True)
    source_date = Column(Date, nullable=False)
    customer_or_vendor = Column(String(200))
    description = Column(Text)
    gross_amount = Column(DECIMAL(12, 2), default=0)
    net_amount = Column(DECIMAL(12, 2), default=0)
    gst_amount = Column(DECIMAL(12, 2), default=0)
    tax_mode = Column(String(30), default="gst_15")
    tax_rate = Column(DECIMAL(8, 4), nullable=True)
    metadata_json = Column(Text)
    created_at = Column(DateTime, server_default=func.now())

    gst_return = relationship("GstReturn", back_populates="snapshot_rows")


class GstAdjustment(Base):
    __tablename__ = "gst_adjustments"

    id = Column(Integer, primary_key=True, autoincrement=True)
    gst_return_id = Column(Integer, ForeignKey("gst_returns.id", ondelete="SET NULL"), nullable=True)
    adjustment_type = Column(String(20), nullable=False)
    amount = Column(DECIMAL(12, 2), nullable=False)
    reason = Column(Text, nullable=False)
    source_period_start = Column(Date, nullable=True)
    source_period_end = Column(Date, nullable=True)
    target_period_start = Column(Date, nullable=False)
    target_period_end = Column(Date, nullable=False)
    linked_invoice_id = Column(Integer, ForeignKey("invoices.id", ondelete="SET NULL"), nullable=True)
    linked_payment_id = Column(Integer, ForeignKey("payments.id", ondelete="SET NULL"), nullable=True)
    linked_expense_id = Column(Integer, ForeignKey("expenses.id", ondelete="SET NULL"), nullable=True)
    direction = Column(String(16), nullable=True)
    source_type = Column(String(64), nullable=True)
    source_id = Column(Integer, nullable=True)
    adjustment_date = Column(DateTime, nullable=True)
    created_at = Column(DateTime, server_default=func.now())

    gst_return = relationship("GstReturn", back_populates="adjustments")


class Reminder(Base):
    __tablename__ = "reminders"

    id = Column(Integer, primary_key=True, autoincrement=True)
    reminder_type = Column(Enum(ReminderType), default=ReminderType.custom)
    trigger_days = Column(Integer, default=3)
    send_email = Column(Boolean, default=True)
    send_telegram = Column(Boolean, default=True)
    email_template_id = Column(Integer, ForeignKey("email_templates.id", ondelete="SET NULL"), nullable=True)
    telegram_template = Column(Text)
    status = Column(Enum(ReminderActiveStatus), default=ReminderActiveStatus.active)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    email_template = relationship("EmailTemplate", back_populates="reminders")


class EmailTemplate(Base):
    __tablename__ = "email_templates"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(200), nullable=False)
    subject = Column(String(300))
    body = Column(Text)
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    reminders = relationship("Reminder", back_populates="email_template")


class EmailLog(Base):
    __tablename__ = "email_logs"

    id = Column(Integer, primary_key=True, autoincrement=True)
    sent_at = Column(DateTime, server_default=func.now())
    recipient = Column(String(300))
    subject = Column(String(300))
    body = Column(Text)
    status = Column(Enum(EmailLogStatus), default=EmailLogStatus.sent)
    error_message = Column(Text)


class TelegramLog(Base):
    __tablename__ = "telegram_logs"

    id = Column(Integer, primary_key=True, autoincrement=True)
    sent_at = Column(DateTime, server_default=func.now())
    chat_id = Column(String(50))
    message = Column(Text)
    status = Column(Enum(TelegramLogStatus), default=TelegramLogStatus.sent)
    error_message = Column(Text)


class SystemConfig(Base):
    __tablename__ = "system_config"

    key_name = Column(String(100), primary_key=True)
    key_value = Column(Text)
    encrypted_value = Column(Text)
    is_secret = Column(Boolean, default=False)
    updated_at = Column(DateTime, server_default=func.now(), onupdate=func.now())