from datetime import datetime, timezone
from sqlalchemy import (
    BigInteger, Boolean, Column, DateTime, ForeignKey, Index, Integer,
    Numeric, String, Text, UniqueConstraint,
)
from sqlalchemy.dialects.postgresql import ARRAY, JSONB
from sqlalchemy.orm import relationship
from database.postgres import Base


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


class CrmProduct(Base):
    __tablename__ = "crm_products"

    id           = Column(String(128), primary_key=True)   # Firestore doc ID
    firestore_id = Column(String(128), unique=True)        # same as id during transition
    name         = Column(String(500), nullable=False)
    sku          = Column(String(128))
    category     = Column(String(128))
    description  = Column(Text)
    unit_cost    = Column(Numeric(12, 2), nullable=False, default=0)
    currency     = Column(String(10), nullable=False, default="EUR")
    unit_type    = Column(String(32), nullable=False, default="pcs")
    is_active    = Column(Boolean, nullable=False, default=True)
    created_at   = Column(DateTime(timezone=True), nullable=False, default=_now)
    updated_at   = Column(DateTime(timezone=True), nullable=False, default=_now, onupdate=_now)


class CrmCustomer(Base):
    __tablename__ = "crm_customers"
    __table_args__ = (
        Index("idx_crm_customers_rel_status", "relationship_status"),
        Index("idx_crm_customers_name", "name", "surname"),
        Index("idx_crm_customers_tags", "tags", postgresql_using="gin"),
    )

    id                  = Column(String(128), primary_key=True)   # Firestore doc ID
    firestore_id        = Column(String(128), unique=True)
    title               = Column(String(32))
    name                = Column(String(255), nullable=False)
    surname             = Column(String(255))
    organization        = Column(String(500))
    religion            = Column(String(64))
    language            = Column(String(10), nullable=False, default="el")
    folder_id           = Column(String(128), unique=True, nullable=False)
    relationship_status = Column(String(64), nullable=False, default="lead")
    nextcloud_folder    = Column(String(500))
    contacts            = Column(JSONB, nullable=False, default=list)
    notes               = Column(JSONB, nullable=False, default=list)
    location            = Column(JSONB)
    tags                = Column(ARRAY(String), nullable=False, default=list)
    owned_items         = Column(JSONB, nullable=False, default=list)
    linked_user_ids     = Column(ARRAY(String), nullable=False, default=list)
    technical_issues    = Column(JSONB, nullable=False, default=list)
    install_support     = Column(JSONB, nullable=False, default=list)
    transaction_history = Column(JSONB, nullable=False, default=list)
    crm_summary         = Column(JSONB)
    created_at          = Column(DateTime(timezone=True), nullable=False)
    updated_at          = Column(DateTime(timezone=True), nullable=False)

    orders     = relationship("CrmOrder", back_populates="customer",
                              cascade="all, delete-orphan", lazy="noload")
    quotations = relationship("CrmQuotation", back_populates="customer",
                              cascade="all, delete-orphan", lazy="noload")
    comms      = relationship("CrmCommsLog", back_populates="customer",
                              cascade="all, delete-orphan", lazy="noload")
    media      = relationship("CrmMedia", back_populates="customer", lazy="noload")


class CrmOrder(Base):
    __tablename__ = "crm_orders"
    __table_args__ = (
        Index("idx_crm_orders_customer", "customer_id"),
        Index("idx_crm_orders_status", "status"),
    )

    id                 = Column(String(128), primary_key=True)   # Firestore doc ID
    customer_id        = Column(String(128), ForeignKey("crm_customers.id", ondelete="CASCADE"),
                                nullable=False)
    order_number       = Column(String(64), unique=True, nullable=False)
    title              = Column(String(500))
    created_by         = Column(String(128))
    status             = Column(String(64), nullable=False, default="negotiating")
    status_updated_date = Column(DateTime(timezone=True))
    status_updated_by  = Column(String(128))
    items              = Column(JSONB, nullable=False, default=list)
    subtotal           = Column(Numeric(12, 2), nullable=False, default=0)
    discount           = Column(JSONB)
    total_price        = Column(Numeric(12, 2), nullable=False, default=0)
    currency           = Column(String(10), nullable=False, default="EUR")
    shipping           = Column(JSONB)
    payment_status     = Column(JSONB, nullable=False, default=dict)
    invoice_path       = Column(String(500))
    notes              = Column(Text)
    timeline           = Column(JSONB, nullable=False, default=list)
    created_at         = Column(DateTime(timezone=True), nullable=False)
    updated_at         = Column(DateTime(timezone=True), nullable=False)

    customer = relationship("CrmCustomer", back_populates="orders")


class CrmCommsLog(Base):
    __tablename__ = "crm_comms_log"
    __table_args__ = (
        Index("idx_crm_comms_customer", "customer_id", "occurred_at"),
    )

    id             = Column(String(128), primary_key=True)
    customer_id    = Column(String(128), ForeignKey("crm_customers.id", ondelete="SET NULL"),
                            nullable=True)
    type           = Column(String(32), nullable=False)       # email | sms | call | note | ...
    mail_account   = Column(String(256))
    direction      = Column(String(16), nullable=False)       # inbound | outbound
    subject        = Column(String(500))
    body           = Column(Text)
    body_html      = Column(Text)
    attachments    = Column(JSONB, nullable=False, default=list)
    ext_message_id = Column(String(500))
    from_addr      = Column(String(500))
    to_addrs       = Column(Text)                             # JSON array as text or comma-sep
    logged_by      = Column(String(128))
    is_important   = Column(Boolean, nullable=False, default=False)
    is_read        = Column(Boolean, nullable=False, default=True)
    occurred_at    = Column(DateTime(timezone=True), nullable=False)
    created_at     = Column(DateTime(timezone=True), nullable=False, default=_now)

    customer = relationship("CrmCustomer", back_populates="comms")


class CrmMedia(Base):
    __tablename__ = "crm_media"
    __table_args__ = (
        Index("idx_crm_media_customer", "customer_id"),
        Index("idx_crm_media_order", "order_id"),
    )

    id             = Column(String(128), primary_key=True)
    customer_id    = Column(String(128), ForeignKey("crm_customers.id", ondelete="SET NULL"),
                            nullable=True)
    order_id       = Column(String(128))
    filename       = Column(String(500), nullable=False)
    nextcloud_path = Column(String(1000), nullable=False)
    thumbnail_path = Column(String(1000))
    mime_type      = Column(String(128))
    direction      = Column(String(16))
    tags           = Column(JSONB, nullable=False, default=list)
    uploaded_by    = Column(String(128))
    created_at     = Column(DateTime(timezone=True), nullable=False, default=_now)

    customer = relationship("CrmCustomer", back_populates="media")


class CrmSyncState(Base):
    __tablename__ = "crm_sync_state"

    key   = Column(String(128), primary_key=True)
    value = Column(Text)


class CrmQuotation(Base):
    __tablename__ = "crm_quotations"
    __table_args__ = (
        Index("idx_crm_quotations_customer", "customer_id"),
    )

    id                       = Column(String(128), primary_key=True)
    quotation_number         = Column(String(64), unique=True, nullable=False)
    title                    = Column(String(500))
    subtitle                 = Column(String(500))
    customer_id              = Column(String(128), ForeignKey("crm_customers.id", ondelete="CASCADE"),
                                      nullable=False)
    language                 = Column(String(10), nullable=False, default="en")
    status                   = Column(String(32), nullable=False, default="draft")
    order_type               = Column(String(64))
    shipping_method          = Column(String(64))
    estimated_shipping_date  = Column(String(32))            # stored as DATE string
    global_discount_label    = Column(String(128))
    global_discount_percent  = Column(Numeric(8, 4), nullable=False, default=0)
    vat_percent              = Column(Numeric(8, 4), nullable=False, default=24)
    global_vat_percent       = Column(Numeric(8, 4), nullable=False, default=24)
    shipping_cost            = Column(Numeric(12, 2), nullable=False, default=0)
    shipping_cost_discount   = Column(Numeric(12, 2), nullable=False, default=0)
    install_cost             = Column(Numeric(12, 2), nullable=False, default=0)
    install_cost_discount    = Column(Numeric(12, 2), nullable=False, default=0)
    extras_label             = Column(String(256))
    extras_cost              = Column(Numeric(12, 2), nullable=False, default=0)
    comments                 = Column(JSONB, nullable=False, default=list)
    quick_notes              = Column(JSONB, nullable=False, default=dict)
    subtotal_before_discount = Column(Numeric(12, 2), nullable=False, default=0)
    global_discount_amount   = Column(Numeric(12, 2), nullable=False, default=0)
    new_subtotal             = Column(Numeric(12, 2), nullable=False, default=0)
    vat_amount               = Column(Numeric(12, 2), nullable=False, default=0)
    final_total              = Column(Numeric(12, 2), nullable=False, default=0)
    nextcloud_pdf_path       = Column(String(1000))
    nextcloud_pdf_url        = Column(String(1000))
    # Client snapshot fields (denormalised for PDF generation)
    client_org               = Column(String(500))
    client_name              = Column(String(500))
    client_location          = Column(String(500))
    client_phone             = Column(String(64))
    client_email             = Column(String(256))
    # Legacy quotation fields
    is_legacy                = Column(Boolean, nullable=False, default=False)
    legacy_date              = Column(String(32))
    legacy_pdf_path          = Column(String(1000))
    created_at               = Column(DateTime(timezone=True), nullable=False)
    updated_at               = Column(DateTime(timezone=True), nullable=False)

    customer = relationship("CrmCustomer", back_populates="quotations")
    items    = relationship("CrmQuotationItem", back_populates="quotation",
                            cascade="all, delete-orphan",
                            order_by="CrmQuotationItem.sort_order", lazy="noload")


class CrmQuotationItem(Base):
    __tablename__ = "crm_quotation_items"
    __table_args__ = (
        Index("idx_crm_quotation_items_quotation", "quotation_id", "sort_order"),
    )

    id               = Column(String(128), primary_key=True)
    quotation_id     = Column(String(128), ForeignKey("crm_quotations.id", ondelete="CASCADE"),
                              nullable=False)
    product_id       = Column(String(128))
    description      = Column(Text)
    description_en   = Column(Text)
    description_gr   = Column(Text)
    unit_type        = Column(String(32), nullable=False, default="pcs")
    unit_cost        = Column(Numeric(12, 4), nullable=False, default=0)
    discount_percent = Column(Numeric(8, 4), nullable=False, default=0)
    vat_percent      = Column(Numeric(8, 4), nullable=False, default=24)
    quantity         = Column(Numeric(12, 4), nullable=False, default=1)
    line_total       = Column(Numeric(12, 2), nullable=False, default=0)
    sort_order       = Column(Integer, nullable=False, default=0)

    quotation = relationship("CrmQuotation", back_populates="items")
