from app.core.database import Base
from sqlalchemy import Column, Integer, String, Text, DateTime, Date, Boolean, DECIMAL, Enum, ForeignKey, JSON
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)
    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())
    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 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())