import uuid
from datetime import datetime, date, timezone
from decimal import Decimal
from sqlalchemy import String, Boolean, DateTime, Date, Integer, Numeric, Text, ForeignKey
from sqlalchemy.dialects.postgresql import UUID
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)


class MaintenanceAsset(Base):
    __tablename__ = 'maintenance_asset'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name: Mapped[str] = mapped_column(String(200), nullable=False)
    machine_number: Mapped[int | None] = mapped_column(Integer)
    machine_type: Mapped[str | None] = mapped_column(String(50))
    year_of_commissioning: Mapped[int | None] = mapped_column(Integer)
    location: Mapped[str | None] = mapped_column(String(200))
    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 | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)

    records = relationship('MaintenanceRecord', back_populates='asset', cascade='all, delete-orphan')
    pm_schedules = relationship('MaintenancePMSchedule', back_populates='asset', cascade='all, delete-orphan')


class MaintenanceChecklistItem(Base):
    __tablename__ = 'maintenance_checklist_item'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    machine_type: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    sr_no: Mapped[int | None] = mapped_column(Integer)
    check_point: Mapped[str] = mapped_column(Text, nullable=False)
    specification: 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 MaintenanceRecord(Base):
    __tablename__ = 'maintenance_record'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    asset_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey('maintenance_asset.id'), nullable=False)
    wo_number: Mapped[str | None] = mapped_column(String(30), index=True)  # e.g. WO-20260717-001
    record_date: Mapped[date] = mapped_column(Date, nullable=False)
    maintenance_type: Mapped[str] = mapped_column(String(10), nullable=False, default='PM')
    status: Mapped[str] = mapped_column(String(20), nullable=False, default='OPEN')  # OPEN, IN_PROGRESS, COMPLETED, CLOSED
    priority: Mapped[str] = mapped_column(String(20), nullable=False, default='MEDIUM')  # CRITICAL, HIGH, MEDIUM, LOW
    assigned_technician: Mapped[str | None] = mapped_column(String(150))
    estimated_time: Mapped[str | None] = mapped_column(String(50))
    problem_faced: Mapped[str | None] = mapped_column(Text)
    root_cause: Mapped[str | None] = mapped_column(Text)
    material_used: Mapped[str | None] = mapped_column(Text)
    material_cost: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
    time_taken: Mapped[str | None] = mapped_column(String(50))
    supervisor_name: Mapped[str | None] = mapped_column(String(150))
    remarks: Mapped[str | None] = mapped_column(Text)
    photo_url: Mapped[str | None] = mapped_column(String(500))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)

    asset = relationship('MaintenanceAsset', back_populates='records')
    actions = relationship('MaintenanceRecordAction', back_populates='record', cascade='all, delete-orphan', order_by='MaintenanceRecordAction.sr_no')


class MaintenanceRecordAction(Base):
    __tablename__ = 'maintenance_record_action'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    record_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey('maintenance_record.id'), nullable=False)
    sr_no: Mapped[int | None] = mapped_column(Integer)
    action_taken: Mapped[str] = mapped_column(Text, nullable=False)
    is_ok: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)

    record = relationship('MaintenanceRecord', back_populates='actions')


class MaintenancePMSchedule(Base):
    __tablename__ = 'maintenance_pm_schedule'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    asset_id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), ForeignKey('maintenance_asset.id'), nullable=False)
    scheduled_date: Mapped[date] = mapped_column(Date, nullable=False)
    year: Mapped[int] = mapped_column(Integer, nullable=False)
    month: Mapped[int] = mapped_column(Integer, nullable=False)
    status: Mapped[str] = mapped_column(String(20), nullable=False, default='PLANNED')
    completed_date: Mapped[date | None] = mapped_column(Date)
    notes: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)

    asset = relationship('MaintenanceAsset', back_populates='pm_schedules')


# ─── NEW MODELS ───────────────────────────────────────────────────────────────


class DieselRecord(Base):
    """
    DG Set Diesel Record
    From 'DESAL AND LPG  GAS.xlsx' -> DISAL
    """
    __tablename__ = 'maintenance_diesel_record'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    entry_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    dg_set_no: Mapped[str] = mapped_column(String(50), nullable=False)
    diesel_ltr: Mapped[Decimal] = mapped_column(Numeric(18, 2), nullable=False)
    oil_ltr: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class LPGGasRecord(Base):
    """
    LPG Gas Cylinder Tracking
    From 'DESAL AND LPG  GAS.xlsx' -> LPG GAS
    """
    __tablename__ = 'maintenance_lpg_gas_record'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    entry_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    gas_name: Mapped[str] = mapped_column(String(100), nullable=False)
    quantity: Mapped[int] = mapped_column(Integer, nullable=False)
    kg: Mapped[Decimal] = mapped_column(Numeric(18, 2), nullable=False)
    price: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
    total_amount: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
    bill_no: Mapped[str | None] = mapped_column(String(100))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class SolarMaintenanceRecord(Base):
    """
    Solar panel cleaning and maintenance
    From 'DESAL AND LPG  GAS.xlsx' -> TEHTI ENERGY
    """
    __tablename__ = 'maintenance_solar_record'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    entry_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    part_name: Mapped[str] = mapped_column(String(200), nullable=False)
    quantity: Mapped[Decimal] = mapped_column(Numeric(18, 2), nullable=False)
    price: Mapped[Decimal] = mapped_column(Numeric(18, 2), nullable=False)
    total_amount: Mapped[Decimal] = mapped_column(Numeric(18, 2), nullable=False)
    bill_no: Mapped[str | None] = mapped_column(String(100))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class IncomingMaterial(Base):
    """
    Spare parts / consumables purchase register.
    From multiple incoming material Excel files.
    """
    __tablename__ = 'maintenance_incoming_material'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    entry_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    vendor: Mapped[str | None] = mapped_column(String(200)) # Made optional
    category: Mapped[str | None] = mapped_column(String(100))
    material_name: Mapped[str] = mapped_column(String(300), nullable=False)
    quantity: Mapped[Decimal] = mapped_column(Numeric(18, 3), nullable=False)
    rate: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
    total_amount: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
    bill_no: Mapped[str | None] = mapped_column(String(100))
    discount: Mapped[Decimal | None] = mapped_column(Numeric(18, 2))
    challan_no: Mapped[str | None] = mapped_column(String(100))
    remarks: Mapped[str | None] = mapped_column(Text)
    # Optional: link this spare purchase to a specific machine for per-machine cost tracking
    asset_id: Mapped[uuid.UUID | None] = mapped_column(UUID(as_uuid=True), ForeignKey('maintenance_asset.id', ondelete='SET NULL'), nullable=True, index=True)
    bill_photo_url: Mapped[str | None] = mapped_column(String(500))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class MonthlyStock(Base):
    """
    Monthly material stock summary.
    Replaces DailyConsumption to match JAN 2026.xlsx Excel format exactly.
    """
    __tablename__ = 'maintenance_monthly_stock'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    stock_month: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    stock_year: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    
    category: Mapped[str] = mapped_column(String(100), nullable=False)  # e.g. CO2 MIG WELDING
    category_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)  # 1-28, group order from Excel
    material_name: Mapped[str] = mapped_column(String(300), nullable=False)
    uom: Mapped[str] = mapped_column(String(50), nullable=False)
    cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)

    opening_qty: Mapped[Decimal] = mapped_column(Numeric(12, 3), nullable=False, default=0)
    opening_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False, default=0)

    incoming_qty: Mapped[Decimal] = mapped_column(Numeric(12, 3), nullable=False, default=0)
    incoming_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False, default=0)

    total_qty: Mapped[Decimal] = mapped_column(Numeric(12, 3), nullable=False, default=0)
    total_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False, default=0)

    consumption_qty: Mapped[Decimal] = mapped_column(Numeric(12, 3), nullable=False, default=0)
    consumption_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False, default=0)

    closing_qty: Mapped[Decimal] = mapped_column(Numeric(12, 3), nullable=False, default=0)
    closing_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False, default=0)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class EquipmentPurchase(Base):
    """
    Plant equipment purchase log (monthly).
    From 'MONTHALY COST 2026.xlsx' PLANT COST sheet.
    total_amount = quantity × unit_price (stored).
    """
    __tablename__ = 'maintenance_equipment_purchase'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    purchase_month: Mapped[int] = mapped_column(Integer, nullable=False)
    purchase_year: Mapped[int] = mapped_column(Integer, nullable=False)
    equipment_name: Mapped[str] = mapped_column(String(300), nullable=False)
    quantity: Mapped[Decimal] = mapped_column(Numeric(10, 2), nullable=False)
    unit_price: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False)
    total_amount: Mapped[Decimal] = mapped_column(Numeric(14, 2), nullable=False)
    remarks: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class EquipmentRental(Base):
    """
    External hydra / crane / forklift rental log.
    From 'incoming material/hedra record.xlsx'.
    hours stored as decimal (e.g. 1.5 = 1 hr 30 min).
    """
    __tablename__ = 'maintenance_equipment_rental'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    rental_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    vendor: Mapped[str] = mapped_column(String(200), nullable=False)
    equipment_type: Mapped[str] = mapped_column(String(100), nullable=False)
    in_time: Mapped[str | None] = mapped_column(String(20))
    out_time: Mapped[str | None] = mapped_column(String(20))
    hours: Mapped[Decimal | None] = mapped_column(Numeric(6, 2))
    work_description: Mapped[str | None] = mapped_column(Text)
    bill_no: Mapped[str | None] = mapped_column(String(100))
    amount: Mapped[Decimal | None] = mapped_column(Numeric(12, 2))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class MachineConsumptionCost(Base):
    """
    Monthly consumption cost per machine (AUDI only).
    Tracks costs month by month for a given year.
    """
    __tablename__ = 'maintenance_machine_consumption_cost'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    machine_name: Mapped[str] = mapped_column(String(300), nullable=False)
    display_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    year: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    
    jan_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    feb_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    mar_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    apr_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    may_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    jun_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    jul_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    aug_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    sep_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    oct_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    nov_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)
    dec_cost: Mapped[Decimal] = mapped_column(Numeric(12, 2), nullable=False, default=0)

    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)


class DailyConsumableStock(Base):
    """
    Daily tracking for CO2 Gas and MIG Wire.
    Tracks opening, receiving, consumed, and closing stock day-by-day.
    """
    __tablename__ = 'maintenance_daily_consumable_stock'

    id: Mapped[uuid.UUID] = mapped_column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    record_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    consumable_type: Mapped[str] = mapped_column(String(50), nullable=False, index=True)  # 'CO2_GAS' or 'MIG_WIRE'
    
    opening_stock: Mapped[Decimal] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    received_stock: Mapped[Decimal] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    total_stock: Mapped[Decimal] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    consumed_stock: Mapped[Decimal] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    closing_stock: Mapped[Decimal] = mapped_column(Numeric(10, 2), nullable=False, default=0)
    
    remark: Mapped[str | None] = mapped_column(Text)
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=now_utc)
    updated_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), onupdate=now_utc)