import uuid
from datetime import datetime, timezone
from decimal import Decimal
from sqlalchemy import Column, String, Boolean, DateTime, Date, Integer
from sqlalchemy import Numeric, Text, ForeignKey, UniqueConstraint
from sqlalchemy.dialects.postgresql import UUID, JSONB
from sqlalchemy.orm import relationship, Mapped, mapped_column
from app.core.database import Base
 
def gen_uuid():
    return str(uuid.uuid4())
 
def now_utc():
    return datetime.now(timezone.utc)
 
# ── LOOKUP MASTERS ────────────────────────────────────
class ProcurementSourceMaster(Base):
    __tablename__ = 'procurement_source_master'
    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
 
class MaterialTypeMaster(Base):
    __tablename__ = 'material_type_master'
    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
 
class PoStatusMaster(Base):
    __tablename__ = 'po_status_master'
    id          = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name        = Column(String(100), nullable=False)
    code        = Column(String(50), unique=True, nullable=False)
    # is_terminal = Column(Boolean, default=False)
    description = Column(Text)
    # sort_order  = Column(Integer, default=0)
    created_at  = Column(DateTime(timezone=True), default=now_utc)
 
# ── CORE MASTERS ──────────────────────────────────────
class RmMaster(Base):
    """
    Core Raw Material Master Table.
    Purpose: Defines all raw materials available in the system, their base properties
    (like UOM, Part Number), and rules like minimum stock levels.
    """
    __tablename__ = 'rm_master'
    rm_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name: Mapped[str] = mapped_column(String(255), nullable=False)
    part_no: Mapped[str | None] = mapped_column(String(100), unique=True)
    unit_of_measurement: Mapped[str] = mapped_column(String(50), nullable=False)
    description: Mapped[str | None] = mapped_column(Text)
    material_type_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey('material_type_master.id'))
    procurement_source_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey('procurement_source_master.id'))
    station_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey('station_master.station_id'))
    # reorder_level         = Column(Numeric(14,3))
    minimum_stock: Mapped[Decimal | None] = mapped_column(Numeric(14,3))
    lead_time_days: Mapped[int | None] = mapped_column(Integer)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)
 
class VendorMaster(Base):
    """
    Core Vendor Master Table.
    Purpose: Stores details of all suppliers/vendors from whom raw materials 
    are procured, including contact details and GST info.
    """
    __tablename__ = 'vendor_master'
    vendor_id      = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    vendor_code    = Column(String(50), unique=True)
    name           = Column(String(255), nullable=False)
    contact_person = Column(String(150))
    phones         = Column(JSONB, nullable=False, default=list)
    emails         = Column(JSONB, nullable=False, default=list)
    gst_number     = Column(String(30), unique=True)
    # pan_number     = Column(String(20))
    address_line1  = Column(String(255))
    city           = Column(String(100))
    state          = Column(String(100))
    payment_terms  = Column(String(100))
    portal_enabled = Column(Boolean, nullable=False, default=False)
    portal_username = Column(String(100), unique=True)
    portal_password_hash = Column(String(255))
    is_active      = Column(Boolean, nullable=False, default=True)
    created_at     = Column(DateTime(timezone=True), default=now_utc)
    updated_at     = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)
 
class StoreMaster(Base):
    __tablename__ = 'store_master'
    store_id   = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    store_name = Column(String(150), nullable=False)
    # store_code = Column(String(30), unique=True)
    # store_type = Column(String(50), nullable=False)
    location   = Column(String(200))
    is_active  = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)

class StationMaster(Base):
    __tablename__ = "station_master"
    station_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    station_code = Column(String(50), unique=True, nullable=False)
    station_name = Column(String(255), nullable=False)
    station_description = Column(Text)
    
    location_id = Column(UUID(as_uuid=True), ForeignKey('chassis_location_master.location_id'), nullable=True)
    operation_id = Column(UUID(as_uuid=True))
    primary_operator_id = Column(UUID(as_uuid=True))

    sequence_no = Column(Integer)

    requires_qa = Column(Boolean, nullable=False, default=True)
    is_rework_station = Column(Boolean, nullable=False, default=False)
    backflush_enabled = Column(Boolean, nullable=False, default=False)

    standard_cycle_time_min = Column(Integer)
    alert_threshold_min = Column(Integer, nullable=True)

    is_active = Column(Boolean, nullable=False, default=True)

    custom_fields = Column(JSONB, default={})

    created_at = Column(DateTime(timezone=True), default=now_utc)


# ── MAPPING TABLES ────────────────────────────────────
class RmVendorMapping(Base):
    __tablename__ = 'rm_vendor_mapping'
    __table_args__ = (UniqueConstraint('rm_id','vendor_id'),)
    mapping_id    = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    rm_id         = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    vendor_id     = Column(UUID(as_uuid=True), ForeignKey('vendor_master.vendor_id'), nullable=False)
    standard_cost = Column(Numeric(14,2))
    lead_time_days = Column(Integer)
    # currency      = Column(String(10), default='INR')
    # is_preferred  = Column(Boolean, default=False)
    is_active     = Column(Boolean, nullable=False, default=True)
    description   = Column(Text)
    created_at    = Column(DateTime(timezone=True), default=now_utc)
 
class RmStoreMapping(Base):
    __tablename__ = 'rm_store_mapping'
    __table_args__ = (UniqueConstraint('rm_id','store_id'),)
    mapping_id      = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    rm_id           = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    store_id        = Column(UUID(as_uuid=True), ForeignKey('store_master.store_id'), nullable=False)
    min_stock_level = Column(Numeric(14,3))
    max_stock_level = Column(Numeric(14,3))
    is_active       = Column(Boolean, nullable=False, default=True)
    created_at      = Column(DateTime(timezone=True), default=now_utc)
 
# ── PROCUREMENT ───────────────────────────────────────
class RmPurchaseOrder(Base):
    """
    Purchase Order (PO) Header Table.
    Purpose: Represents a formal request to a Vendor to supply specific quantities
    of raw materials by a given date. Acts as the parent for `RmPoDetail` lines.
    """
    __tablename__ = 'rm_purchase_order'
    po_id                  = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    po_number              = Column(String(50), unique=True)
    vendor_id              = Column(UUID(as_uuid=True), ForeignKey('vendor_master.vendor_id'))
    order_date             = Column(Date, nullable=False)
    expected_delivery_date = Column(Date)
    status_id              = Column(UUID(as_uuid=True), ForeignKey('po_status_master.id'))
    total_amount           = Column(Numeric(14,2))
    notes                  = Column(Text)
    created_by             = Column(UUID(as_uuid=True))
    approved_by            = Column(UUID(as_uuid=True))
    approved_at            = Column(DateTime(timezone=True))
    created_at             = Column(DateTime(timezone=True), default=now_utc)
    updated_at             = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)
 
    details = relationship("RmPoDetail", back_populates="purchase_order", cascade="all, delete-orphan")
    history = relationship("RmPoHistory", back_populates="purchase_order", cascade="all, delete-orphan")

class RmPoHistory(Base):
    __tablename__ = 'rm_po_history'
    history_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    po_id = Column(UUID(as_uuid=True), ForeignKey('rm_purchase_order.po_id'), nullable=False)
    action = Column(String(50), nullable=False)
    description = Column(Text)
    created_by_name = Column(String(100))
    created_at = Column(DateTime(timezone=True), default=now_utc)

    purchase_order = relationship("RmPurchaseOrder", back_populates="history")

 
class RmPoDetail(Base):
    __tablename__ = 'rm_purchase_order_detail'
    po_detail_id  = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    po_id         = Column(UUID(as_uuid=True), ForeignKey('rm_purchase_order.po_id'), nullable=False)
    rm_id         = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    order_qty     = Column(Numeric(14,3), nullable=False)
    received_qty  = Column(Numeric(14,3), default=0)
    unit_price    = Column(Numeric(14,2), nullable=False)
    gst_percent   = Column(Numeric(5,2))
    line_amount   = Column(Numeric(14,2))  #order_qty × unit_price (auto-calculated)
    line_status   = Column(String(30), default='OPEN') #line_status	VARCHAR(30)		OPEN | PARTIALLY_RECEIVED | FULLY_RECEIVED | CANCELLED
    created_at    = Column(DateTime(timezone=True), default=now_utc)
 
    purchase_order = relationship("RmPurchaseOrder", back_populates="details")
 
# ── RECEIVING / GRN ───────────────────────────────────
class RmReceivingLog(Base):
    """
    Goods Receipt Note (GRN) Header Table.
    Purpose: Logs the physical arrival of raw materials from a vendor. 
    It bridges the Procurement (PO) side with the Inventory (Stock) side.
    """
    __tablename__ = 'rm_receiving_log'
    grn_id        = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    grn_number    = Column(String(50), unique=True)
    po_id         = Column(UUID(as_uuid=True), ForeignKey('rm_purchase_order.po_id'))
    vendor_id     = Column(UUID(as_uuid=True), ForeignKey('vendor_master.vendor_id'))
    asn_id        = Column(UUID(as_uuid=True), ForeignKey('asn_log.asn_id'), nullable=True)
    received_date = Column(Date, nullable=False)
    vehicle_number= Column(String(50))
    dc_number     = Column(String(100), nullable=True)
    grn_status    = Column(String(30), default='PENDING_QA')
    remarks       = Column(Text)
    created_by    = Column(UUID(as_uuid=True))
    approved_by   = Column(UUID(as_uuid=True))
    approved_at   = Column(DateTime(timezone=True))
    created_at    = Column(DateTime(timezone=True), default=now_utc)
    updated_at    = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)
 
    details = relationship("GrnDetail", back_populates="receiving_log", cascade="all, delete-orphan")
 
class GrnDetail(Base):
    __tablename__ = 'grn_detail'
    grn_detail_id  = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    grn_id         = Column(UUID(as_uuid=True), ForeignKey('rm_receiving_log.grn_id'))
    po_detail_id   = Column(UUID(as_uuid=True), ForeignKey('rm_purchase_order_detail.po_detail_id'))
    rm_id          = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'))
    received_qty   = Column(Numeric(14,3), nullable=False)
    accepted_qty   = Column(Numeric(14,3))
    rejected_qty   = Column(Numeric(14,3))
    rejection_reason=Column(Text)
    store_id       = Column(UUID(as_uuid=True), ForeignKey('store_master.store_id'))
    created_at     = Column(DateTime(timezone=True), default=now_utc)
 
    receiving_log = relationship("RmReceivingLog", back_populates="details")
 
# ── INVENTORY ─────────────────────────────────────────
class RmInventory(Base):
    """
    Live Stock Balance Table.
    Purpose: Stores the absolute current quantity of each raw material in each store.
    This is heavily queried and locked during stock movements to prevent race conditions.
    """
    __tablename__ = 'rm_inventory'
    __table_args__ = (UniqueConstraint('rm_id','store_id'),)
    inventory_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    rm_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    store_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey('store_master.store_id'), nullable=False)
    current_qty: Mapped[Decimal] = mapped_column(Numeric(14,3), nullable=False, default=0)
    reserved_qty: Mapped[Decimal | None] = mapped_column(Numeric(14,3), default=0)
    in_transit_qty: Mapped[Decimal | None] = mapped_column(Numeric(14,3), default=0)
    last_updated: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)

class RmInventoryLog(Base):
    """
    Immutable Stock Transaction Ledger.
    Purpose: An append-only audit trail recording every single change to stock levels 
    (GRNs, Consumptions, Transfers). Essential for historical stock reconciliation.
    """
    __tablename__ = 'rm_inventory_log'
    log_id           = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    rm_id            = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    store_id         = Column(UUID(as_uuid=True), ForeignKey('store_master.store_id'), nullable=False)
    transaction_type = Column(String(50), nullable=False) #GRN_RECEIPT | CONSUMPTION | TRANSFER_OUT | TRANSFER_IN | ADJUSTMENT_ADD | ADJUSTMENT_DEDUCT | REJECTION | RETURN_TO_VENDOR
    qty              = Column(Numeric(14,3), nullable=False)
    balance_before   = Column(Numeric(14,3), nullable=False)
    balance_after    = Column(Numeric(14,3), nullable=False)
    reference_type   = Column(String(50))
    reference_id     = Column(UUID(as_uuid=True))
    remarks          = Column(Text)
    created_at       = Column(DateTime(timezone=True), default=now_utc)
 
class RmConsumptionLog(Base):
    __tablename__ = 'rm_consumption_log'
    consumption_id   = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    rm_id            = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    store_id         = Column(UUID(as_uuid=True), ForeignKey('store_master.store_id'), nullable=False)
    qty_used         = Column(Numeric(14,3), nullable=False)
    consumption_type = Column(String(50), nullable=False, default='MANUAL')
    consumed_date    = Column(Date, nullable=False)
    description      = Column(Text)
    remarks          = Column(Text)
    created_at       = Column(DateTime(timezone=True), default=now_utc)


# ── MONTHLY PROCUREMENT PLANNING ──────────────────────
class MonthlyProcurementPlan(Base):
    """
    Monthly Planning Header.
    Purpose: One record per (vendor, year, month). Planners set the
    total expected purchase quantities for each Raw Material for the month.
    ordered_qty is NEVER stored here — it is always computed live from POs.
    """
    __tablename__ = 'monthly_procurement_plan'
    __table_args__ = (UniqueConstraint('vendor_id', 'plan_year', 'plan_month'),)
    plan_id    = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    vendor_id  = Column(UUID(as_uuid=True), ForeignKey('vendor_master.vendor_id'), nullable=False)
    plan_year  = Column(Integer, nullable=False)
    plan_month = Column(Integer, nullable=False)   # 1–12
    notes      = Column(Text)
    created_by = Column(UUID(as_uuid=True))
    created_at = Column(DateTime(timezone=True), default=now_utc)
    updated_at = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)

    lines = relationship(
        "MonthlyProcurementPlanLine",
        back_populates="plan",
        cascade="all, delete-orphan",
    )


class MonthlyProcurementPlanLine(Base):
    """
    Monthly Planning Line Item.
    Purpose: One row per RM in a monthly plan. Stores only the planned_qty;
    ordered_qty is derived at query time from rm_purchase_order_detail.
    """
    __tablename__ = 'monthly_procurement_plan_line'
    __table_args__ = (UniqueConstraint('plan_id', 'rm_id'),)
    line_id     = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    plan_id     = Column(UUID(as_uuid=True), ForeignKey('monthly_procurement_plan.plan_id'), nullable=False)
    rm_id       = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    planned_qty = Column(Numeric(14, 3), nullable=False)
    created_at  = Column(DateTime(timezone=True), default=now_utc)

    plan = relationship("MonthlyProcurementPlan", back_populates="lines")