from sqlalchemy import (
    Column, Integer, SmallInteger, String, Text,
    DECIMAL, Date, DateTime, Boolean, Enum, ForeignKey, UniqueConstraint,
)
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from .database import Base


class User(Base):
    __tablename__ = 'users'
    id            = Column(Integer, primary_key=True, autoincrement=True)
    username      = Column(String(50), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    role          = Column(Enum('SUPER_ADMIN', 'OWNER', 'MANAGER'), nullable=False, default='MANAGER')
    full_name     = Column(String(100), nullable=False)
    contact       = Column(String(20))
    is_active     = Column(Boolean, nullable=False, default=True)
    created_at    = Column(DateTime, server_default=func.now())


class Staff(Base):
    __tablename__ = 'staff'
    id           = Column(Integer, primary_key=True, autoincrement=True)
    employee_id  = Column(String(20), unique=True)
    full_name    = Column(String(100), nullable=False)
    contact      = Column(String(20))
    basic_salary = Column(DECIMAL(10, 2))
    join_date    = Column(Date)
    status       = Column(Enum('ACTIVE', 'INACTIVE'), nullable=False, default='ACTIVE')
    category     = Column(Enum('MANAGER', 'PUMPER'), nullable=False, default='PUMPER')
    notes        = Column(Text)
    created_at   = Column(DateTime, server_default=func.now())
    shifts       = relationship('DailyShift', back_populates='staff_member',
                                foreign_keys='DailyShift.staff_id')


class Pump(Base):
    __tablename__ = 'pumps'
    id                 = Column(Integer, primary_key=True, autoincrement=True)
    pump_code          = Column(String(10), unique=True, nullable=False)
    pump_number        = Column(SmallInteger, nullable=False)
    fuel_type          = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'), nullable=False)
    description        = Column(String(100))
    is_active          = Column(Boolean, nullable=False, default=True)
    status             = Column(Enum('ACTIVE', 'MAINTENANCE', 'INACTIVE'), nullable=False, default='ACTIVE')
    maintenance_reason = Column(String(255))
    sort_order         = Column(Integer, nullable=False, default=0)
    readings           = relationship('PumpReading', back_populates='pump')
    __table_args__     = (UniqueConstraint('pump_number', 'fuel_type', name='uq_pump_number_fuel_type'),)


class FuelRate(Base):
    __tablename__ = 'fuel_rates'
    id             = Column(Integer, primary_key=True, autoincrement=True)
    fuel_type      = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'), nullable=False)
    rate           = Column(DECIMAL(8, 2), nullable=False)
    effective_from = Column(Date, nullable=False)
    effective_to   = Column(Date)
    set_by         = Column(Integer, ForeignKey('users.id'))
    created_at     = Column(DateTime, server_default=func.now())


class PumpShiftConfig(Base):
    __tablename__ = 'pump_shift_configs'
    id                = Column(Integer, primary_key=True, autoincrement=True)
    pump_id           = Column(Integer, ForeignKey('pumps.id', ondelete='CASCADE'), nullable=False)
    shift_template_id = Column(Integer, ForeignKey('shift_templates.id', ondelete='CASCADE'), nullable=False)
    is_active         = Column(Boolean, nullable=False, default=True)
    sort_order        = Column(Integer, nullable=False, default=0)
    created_at        = Column(DateTime, server_default=func.now())
    __table_args__    = (UniqueConstraint('pump_id', 'shift_template_id', name='uq_pump_shift_config'),)

    pump           = relationship('Pump',          foreign_keys=[pump_id])
    shift_template = relationship('ShiftTemplate', foreign_keys=[shift_template_id])


class ShiftTemplate(Base):
    __tablename__ = 'shift_templates'
    id               = Column(Integer, primary_key=True, autoincrement=True)
    name             = Column(String(100), nullable=False)
    code             = Column(String(10),  nullable=False, unique=True)
    start_time       = Column(String(5),   nullable=False, default='00:00')
    end_time         = Column(String(5),   nullable=False, default='00:00')
    crosses_midnight = Column(Boolean,     nullable=False, default=False)
    color            = Column(String(20),  nullable=False, default='#6366f1')
    is_active        = Column(Boolean,     nullable=False, default=True)
    sort_order       = Column(Integer,     nullable=False, default=0)
    created_at       = Column(DateTime,    server_default=func.now())

    shifts = relationship('DailyShift', back_populates='shift_template', foreign_keys='DailyShift.shift_template_id')


class DailyShift(Base):
    __tablename__ = 'daily_shifts'
    id                 = Column(Integer, primary_key=True, autoincrement=True)
    record_date        = Column(Date, nullable=False)
    shift_type         = Column(String(20), nullable=False)
    shift_template_id  = Column(Integer, ForeignKey('shift_templates.id'), nullable=True)
    shift_start_time   = Column(String(5), nullable=True)
    shift_end_time     = Column(String(5), nullable=True)
    staff_id        = Column(Integer, ForeignKey('staff.id'))
    cash_collected  = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_visa       = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_amex       = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_touch      = Column(DECIMAL(12, 2), nullable=False, default=0)
    credit_total    = Column(DECIMAL(12, 2), nullable=False, default=0)
    other_income    = Column(DECIMAL(12, 2), nullable=False, default=0)
    shortage        = Column(DECIMAL(12, 2), nullable=False, default=0)
    advance         = Column(DECIMAL(12, 2), nullable=False, default=0)
    total_sale_calc = Column(DECIMAL(12, 2), nullable=False, default=0)
    total_collected = Column(DECIMAL(12, 2), nullable=False, default=0)
    difference      = Column(DECIMAL(12, 2), nullable=False, default=0)
    is_locked       = Column(Boolean, nullable=False, default=False)
    status          = Column(Enum('DRAFT', 'ACTIVE', 'FINALIZED', 'LOCKED'),
                              nullable=False, default='DRAFT')
    notes           = Column(Text)
    created_by      = Column(Integer, ForeignKey('users.id'))
    updated_by      = Column(Integer, ForeignKey('users.id'))
    created_at      = Column(DateTime, server_default=func.now())
    updated_at      = Column(DateTime, onupdate=func.now())

    staff_member       = relationship('Staff', back_populates='shifts',
                                      foreign_keys=[staff_id])
    shift_template     = relationship('ShiftTemplate', back_populates='shifts',
                                      foreign_keys=[shift_template_id])
    pump_readings      = relationship('PumpReading', back_populates='shift',
                                      cascade='all, delete-orphan')
    cash_denominations = relationship('CashDenomination', back_populates='shift',
                                      cascade='all, delete-orphan')
    credit_sales       = relationship('CreditSale', back_populates='shift')
    collection_cycles  = relationship('ShiftCollectionCycle', back_populates='shift',
                                      cascade='all, delete-orphan')


class PumpReading(Base):
    __tablename__ = 'pump_readings'
    id             = Column(Integer, primary_key=True, autoincrement=True)
    shift_id       = Column(Integer, ForeignKey('daily_shifts.id', ondelete='CASCADE'),
                             nullable=False)
    pump_id        = Column(Integer, ForeignKey('pumps.id'), nullable=False)
    staff_id       = Column(Integer, ForeignKey('staff.id', ondelete='SET NULL'))
    starting_meter = Column(DECIMAL(12, 3), nullable=False, default=0)
    ending_meter   = Column(DECIMAL(12, 3), nullable=False, default=0)
    meter_out      = Column(DECIMAL(12, 3), nullable=False, default=0)
    testing_ltr    = Column(DECIMAL(8, 3),  nullable=False, default=0)
    sale_ltr       = Column(DECIMAL(10, 3), nullable=False, default=0)
    fuel_rate      = Column(DECIMAL(8, 2),  nullable=False, default=0)
    sale_amount    = Column(DECIMAL(12, 2), nullable=False, default=0)
    status         = Column(Enum('PENDING', 'ENTERED', 'VERIFIED'),
                             nullable=False, default='ENTERED')
    created_at     = Column(DateTime, server_default=func.now())

    shift   = relationship('DailyShift', back_populates='pump_readings')
    pump    = relationship('Pump', back_populates='readings')
    pumper  = relationship('Staff', foreign_keys=[staff_id])


class CashDenomination(Base):
    __tablename__ = 'cash_denominations'
    id               = Column(Integer, primary_key=True, autoincrement=True)
    shift_id         = Column(Integer, ForeignKey('daily_shifts.id', ondelete='CASCADE'),
                               nullable=False)
    staff_id         = Column(Integer, ForeignKey('staff.id', ondelete='SET NULL'))
    bag_number       = Column(SmallInteger, nullable=False)
    note_5000        = Column(Integer, nullable=False, default=0)
    note_2000        = Column(Integer, nullable=False, default=0)
    note_1000        = Column(Integer, nullable=False, default=0)
    note_500         = Column(Integer, nullable=False, default=0)
    note_100         = Column(Integer, nullable=False, default=0)
    note_50          = Column(Integer, nullable=False, default=0)
    note_20          = Column(Integer, nullable=False, default=0)
    note_10          = Column(Integer, nullable=False, default=0)
    calculated_total = Column(DECIMAL(12, 2), nullable=False, default=0)
    shift            = relationship('DailyShift', back_populates='cash_denominations')
    pumper           = relationship('Staff', foreign_keys=[staff_id])


class CreditCustomer(Base):
    __tablename__ = 'credit_customers'
    id              = Column(Integer, primary_key=True, autoincrement=True)
    company_name    = Column(String(100), nullable=False)
    contact_person  = Column(String(100))
    contact_phone   = Column(String(20))
    address         = Column(Text)
    credit_limit    = Column(DECIMAL(12, 2), nullable=False, default=0)
    current_balance = Column(DECIMAL(12, 2), nullable=False, default=0)
    status          = Column(Enum('ACTIVE', 'SUSPENDED', 'CLOSED'),
                              nullable=False, default='ACTIVE')
    approved_by     = Column(Integer, ForeignKey('users.id'))
    created_at      = Column(DateTime, server_default=func.now())
    sales    = relationship('CreditSale',    back_populates='customer')
    payments = relationship('CreditPayment', back_populates='customer', cascade='all, delete-orphan')
    vehicles = relationship('CustomerVehicle', back_populates='customer', cascade='all, delete-orphan')


class CreditSale(Base):
    __tablename__ = 'credit_sales'
    id          = Column(Integer, primary_key=True, autoincrement=True)
    sale_date   = Column(Date, nullable=False)
    shift_id    = Column(Integer, ForeignKey('daily_shifts.id'))
    customer_id = Column(Integer, ForeignKey('credit_customers.id'), nullable=False)
    pumper_id   = Column(Integer, ForeignKey('staff.id', ondelete='SET NULL'))
    bill_no     = Column(String(20), nullable=False)
    amount      = Column(DECIMAL(12, 2), nullable=False)
    vehicle_no  = Column(String(20))
    fuel_type   = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'))
    liters      = Column(DECIMAL(10, 3))
    notes       = Column(Text)
    created_at  = Column(DateTime, server_default=func.now())

    shift    = relationship('DailyShift', back_populates='credit_sales')
    customer = relationship('CreditCustomer', back_populates='sales')
    pumper   = relationship('Staff', foreign_keys=[pumper_id])


# ── Staff Management Module ────────────────────────────────────────────────

class SystemSetting(Base):
    __tablename__ = 'system_settings'
    id            = Column(Integer, primary_key=True, autoincrement=True)
    setting_key   = Column(String(100), unique=True, nullable=False)
    setting_value = Column(String(500), nullable=False)
    description   = Column(String(255))
    updated_by    = Column(Integer, ForeignKey('users.id'))
    updated_at    = Column(DateTime, server_default=func.now(), onupdate=func.now())


class LeaveRequest(Base):
    __tablename__ = 'leave_requests'
    id          = Column(Integer, primary_key=True, autoincrement=True)
    staff_id    = Column(Integer, ForeignKey('staff.id'), nullable=False)
    leave_type  = Column(Enum('ANNUAL', 'SICK', 'CASUAL'), nullable=False)
    date_from   = Column(Date, nullable=False)
    date_to     = Column(Date, nullable=False)
    days_count  = Column(Integer, nullable=False)
    reason      = Column(Text)
    status      = Column(Enum('PENDING', 'APPROVED', 'REJECTED'), nullable=False, default='PENDING')
    reviewed_by = Column(Integer, ForeignKey('users.id'))
    reviewed_at = Column(DateTime)
    review_note = Column(Text)
    created_at  = Column(DateTime, server_default=func.now())

    staff_member = relationship('Staff', foreign_keys=[staff_id])
    reviewer     = relationship('User', foreign_keys=[reviewed_by])


class LeaveBalance(Base):
    __tablename__ = 'leave_balances'
    id           = Column(Integer, primary_key=True, autoincrement=True)
    staff_id     = Column(Integer, ForeignKey('staff.id'), nullable=False)
    year         = Column(Integer, nullable=False)
    annual_total = Column(Integer, nullable=False, default=14)
    sick_total   = Column(Integer, nullable=False, default=7)
    casual_total = Column(Integer, nullable=False, default=3)
    annual_used  = Column(Integer, nullable=False, default=0)
    sick_used    = Column(Integer, nullable=False, default=0)
    casual_used  = Column(Integer, nullable=False, default=0)

    staff_member = relationship('Staff', foreign_keys=[staff_id])


class ShiftCollectionCycle(Base):
    """One collection cycle within a shift — multiple cycles allowed per shift."""
    __tablename__ = 'shift_collection_cycles'
    id           = Column(Integer, primary_key=True, autoincrement=True)
    shift_id     = Column(Integer, ForeignKey('daily_shifts.id', ondelete='CASCADE'),
                           nullable=False)
    pump_id      = Column(Integer, ForeignKey('pumps.id', ondelete='SET NULL'), nullable=True)
    cycle_number = Column(Integer, nullable=False, default=1)
    cash_total   = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_visa    = Column(DECIMAL(10, 2), nullable=False, default=0)
    card_amex    = Column(DECIMAL(10, 2), nullable=False, default=0)
    card_touch   = Column(DECIMAL(10, 2), nullable=False, default=0)
    credit_total = Column(DECIMAL(10, 2), nullable=False, default=0)
    other_income = Column(DECIMAL(10, 2), nullable=False, default=0)
    shortage     = Column(DECIMAL(10, 2), nullable=False, default=0)
    advance      = Column(DECIMAL(10, 2), nullable=False, default=0)
    is_final     = Column(Boolean, nullable=False, default=False)
    collected_by = Column(Integer, ForeignKey('users.id'))
    collected_at = Column(DateTime, server_default=func.now())
    notes        = Column(Text)

    shift     = relationship('DailyShift', back_populates='collection_cycles')
    collector = relationship('User', foreign_keys=[collected_by])


class CashHandover(Base):
    """Standalone cash handover — recorded any time during or after a shift."""
    __tablename__ = 'cash_handovers'
    id               = Column(Integer, primary_key=True, autoincrement=True)
    collection_date  = Column(Date, nullable=False)
    shift_type       = Column(String(20), nullable=False)
    pumper_id        = Column(Integer, ForeignKey('staff.id', ondelete='SET NULL'))
    note_5000        = Column(Integer, nullable=False, default=0)
    note_2000        = Column(Integer, nullable=False, default=0)
    note_1000        = Column(Integer, nullable=False, default=0)
    note_500         = Column(Integer, nullable=False, default=0)
    note_100         = Column(Integer, nullable=False, default=0)
    note_50          = Column(Integer, nullable=False, default=0)
    note_20          = Column(Integer, nullable=False, default=0)
    note_10          = Column(Integer, nullable=False, default=0)
    calculated_total = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_visa        = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_amex        = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_touch       = Column(DECIMAL(12, 2), nullable=False, default=0)
    card_total       = Column(DECIMAL(12, 2), nullable=False, default=0)
    credit_total     = Column(DECIMAL(12, 2), nullable=False, default=0)
    collected_at     = Column(DateTime, server_default=func.now())
    notes            = Column(Text)
    collected_by     = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    shift_id         = Column(Integer, ForeignKey('daily_shifts.id', ondelete='SET NULL'))
    cycle_id         = Column(Integer, ForeignKey('shift_collection_cycles.id', ondelete='SET NULL'))

    pumper    = relationship('Staff', foreign_keys=[pumper_id])
    collector = relationship('User', foreign_keys=[collected_by])


class ShiftOtherCollections(Base):
    """Card payments, credit, shortage etc. entered via Cash Handovers page per shift."""
    __tablename__ = 'shift_other_collections'
    id           = Column(Integer, primary_key=True, autoincrement=True)
    record_date  = Column(Date, nullable=False)
    shift_type   = Column(String(20), nullable=False)
    card_visa    = Column(DECIMAL(10, 2), nullable=False, default=0)
    card_amex    = Column(DECIMAL(10, 2), nullable=False, default=0)
    card_touch   = Column(DECIMAL(10, 2), nullable=False, default=0)
    credit_total = Column(DECIMAL(10, 2), nullable=False, default=0)
    other_income = Column(DECIMAL(10, 2), nullable=False, default=0)
    shortage     = Column(DECIMAL(10, 2), nullable=False, default=0)
    advance      = Column(DECIMAL(10, 2), nullable=False, default=0)
    updated_at   = Column(DateTime, server_default=func.now(), onupdate=func.now())


class TankDipReading(Base):
    """One morning dip reading per tank per day."""
    __tablename__ = 'tank_dip_readings'
    id          = Column(Integer, primary_key=True, autoincrement=True)
    read_date   = Column(Date, nullable=False)
    tank_name   = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'), nullable=False)
    height_cm   = Column(DECIMAL(6, 1), nullable=False, default=0)
    volume_ltr  = Column(DECIMAL(10, 1), nullable=False, default=0)
    recorded_by = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at  = Column(DateTime, server_default=func.now(), onupdate=func.now())

    recorder = relationship('User', foreign_keys=[recorded_by])


class Allowance(Base):
    __tablename__ = 'allowances'
    id             = Column(Integer, primary_key=True, autoincrement=True)
    staff_id       = Column(Integer, ForeignKey('staff.id'), nullable=False)
    month          = Column(Integer, nullable=False)
    year           = Column(Integer, nullable=False)
    allowance_type = Column(Enum('OVERTIME', 'PERFORMANCE', 'ATTENDANCE', 'DAILY'), nullable=False)
    amount         = Column(DECIMAL(10, 2), nullable=False, default=0)
    notes          = Column(Text)
    created_by     = Column(Integer, ForeignKey('users.id'))
    created_at     = Column(DateTime, server_default=func.now())

    staff_member = relationship('Staff', foreign_keys=[staff_id])
    creator      = relationship('User', foreign_keys=[created_by])


class CreditPayment(Base):
    __tablename__ = 'credit_payments'
    id             = Column(Integer, primary_key=True, autoincrement=True)
    customer_id    = Column(Integer, ForeignKey('credit_customers.id', ondelete='CASCADE'), nullable=False)
    payment_date   = Column(Date, nullable=False)
    amount         = Column(DECIMAL(12, 2), nullable=False)
    reference_no   = Column(String(50))
    payment_method = Column(Enum('CASH', 'BANK_TRANSFER', 'CHEQUE'), nullable=False, default='CASH')
    notes          = Column(Text)
    received_by    = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at     = Column(DateTime, server_default=func.now())

    customer = relationship('CreditCustomer', back_populates='payments')
    receiver = relationship('User', foreign_keys=[received_by])


class CustomerVehicle(Base):
    __tablename__ = 'customer_vehicles'
    id           = Column(Integer, primary_key=True, autoincrement=True)
    customer_id  = Column(Integer, ForeignKey('credit_customers.id', ondelete='CASCADE'), nullable=False)
    vehicle_no   = Column(String(20), nullable=False)
    vehicle_type = Column(String(50))
    notes        = Column(Text)
    created_at   = Column(DateTime, server_default=func.now())

    customer = relationship('CreditCustomer', back_populates='vehicles')


class StockDelivery(Base):
    __tablename__ = 'stock_deliveries'
    id            = Column(Integer, primary_key=True, autoincrement=True)
    delivery_date = Column(Date, nullable=False)
    fuel_type     = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'), nullable=False)
    liters        = Column(DECIMAL(10, 2), nullable=False)
    supplier      = Column(String(100))
    invoice_no    = Column(String(50))
    rate_per_ltr  = Column(DECIMAL(8, 2))
    total_cost    = Column(DECIMAL(12, 2))
    notes         = Column(Text)
    received_by   = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at    = Column(DateTime, server_default=func.now())

    receiver = relationship('User', foreign_keys=[received_by])


class AuditLog(Base):
    __tablename__ = 'audit_log'
    id         = Column(Integer, primary_key=True, autoincrement=True)
    user_id    = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    action     = Column(String(50), nullable=False)   # CREATE|UPDATE|DELETE|LOGIN
    table_name = Column(String(50))
    record_id  = Column(Integer)
    summary    = Column(String(255))
    created_at = Column(DateTime, server_default=func.now())

    actor = relationship('User', foreign_keys=[user_id])


# ── Expansion Models ──────────────────────────────────────────────────────


class UserPermission(Base):
    __tablename__ = 'user_permissions'
    id         = Column(Integer, primary_key=True, autoincrement=True)
    user_id    = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'), nullable=False)
    module     = Column(String(50), nullable=False)
    can_view   = Column(Boolean, default=True)
    can_create = Column(Boolean, default=True)
    can_edit   = Column(Boolean, default=True)
    can_delete = Column(Boolean, default=False)
    granted_by = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at = Column(DateTime, server_default=func.now())

    user    = relationship('User', foreign_keys=[user_id])
    granter = relationship('User', foreign_keys=[granted_by])


class RolePagePermission(Base):
    __tablename__ = 'role_page_permissions'
    id         = Column(Integer, primary_key=True, autoincrement=True)
    role       = Column(String(20), nullable=False)
    module     = Column(String(50), nullable=False)
    can_access = Column(Boolean, nullable=False, default=True)
    __table_args__ = (UniqueConstraint('role', 'module', name='uq_role_module'),)


class Invoice(Base):
    __tablename__ = 'invoices'
    id              = Column(Integer, primary_key=True, autoincrement=True)
    invoice_no      = Column(String(30), unique=True, nullable=False)
    invoice_type    = Column(Enum('CASH_RECEIPT', 'CREDIT_INVOICE', 'VAT_INVOICE'), nullable=False)
    customer_id     = Column(Integer, ForeignKey('credit_customers.id', ondelete='SET NULL'))
    shift_id        = Column(Integer, ForeignKey('daily_shifts.id', ondelete='SET NULL'))
    invoice_date    = Column(Date, nullable=False)
    due_date        = Column(Date)
    subtotal        = Column(DECIMAL(12, 2), nullable=False, default=0)
    vat_rate        = Column(DECIMAL(5, 2),  nullable=False, default=0)
    vat_amount      = Column(DECIMAL(12, 2), nullable=False, default=0)
    total           = Column(DECIMAL(12, 2), nullable=False, default=0)
    status          = Column(Enum('DRAFT', 'ISSUED', 'PAID', 'CANCELLED'), nullable=False, default='DRAFT')
    payment_method  = Column(Enum('CASH', 'BANK_TRANSFER', 'CHEQUE', 'CREDIT'), default='CASH')
    customer_vat_no = Column(String(50))
    notes           = Column(Text)
    created_by      = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at      = Column(DateTime, server_default=func.now())
    updated_at      = Column(DateTime, onupdate=func.now())

    customer   = relationship('CreditCustomer', foreign_keys=[customer_id])
    creator    = relationship('User', foreign_keys=[created_by])
    line_items = relationship('InvoiceLineItem', back_populates='invoice', cascade='all, delete-orphan')


class InvoiceLineItem(Base):
    __tablename__ = 'invoice_line_items'
    id          = Column(Integer, primary_key=True, autoincrement=True)
    invoice_id  = Column(Integer, ForeignKey('invoices.id', ondelete='CASCADE'), nullable=False)
    fuel_type   = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'))
    description = Column(String(200), nullable=False)
    quantity    = Column(DECIMAL(10, 3), nullable=False, default=0)
    unit_rate   = Column(DECIMAL(8, 2),  nullable=False, default=0)
    amount      = Column(DECIMAL(12, 2), nullable=False, default=0)

    invoice = relationship('Invoice', back_populates='line_items')


class BulkSale(Base):
    __tablename__ = 'bulk_sales'
    id                = Column(Integer, primary_key=True, autoincrement=True)
    sale_date         = Column(Date, nullable=False)
    fuel_type         = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'), nullable=False)
    liters            = Column(DECIMAL(10, 2), nullable=False)
    custom_rate       = Column(DECIMAL(8, 2),  nullable=False)
    standard_rate     = Column(DECIMAL(8, 2),  nullable=False)
    amount            = Column(DECIMAL(12, 2), nullable=False)
    rate_variance_pct = Column(DECIMAL(5, 2))
    customer_id       = Column(Integer, ForeignKey('credit_customers.id', ondelete='SET NULL'))
    vehicle_no        = Column(String(20))
    shift_id          = Column(Integer, ForeignKey('daily_shifts.id', ondelete='SET NULL'))
    notes             = Column(Text)
    requires_approval = Column(Boolean, default=False)
    approved_by       = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    approved_at       = Column(DateTime)
    created_by        = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at        = Column(DateTime, server_default=func.now())

    customer = relationship('CreditCustomer', foreign_keys=[customer_id])
    approver = relationship('User', foreign_keys=[approved_by])
    creator  = relationship('User', foreign_keys=[created_by])


class Notification(Base):
    __tablename__ = 'notifications'
    id          = Column(Integer, primary_key=True, autoincrement=True)
    user_id     = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'))
    target_role = Column(Enum('ALL', 'OWNER', 'MANAGER', 'SUPER_ADMIN'), default='ALL')
    type        = Column(Enum('UNCLOSED_SHIFT', 'CREDIT_LIMIT', 'LOW_STOCK',
                               'PRICE_OVERRIDE', 'SYSTEM', 'INFO'), nullable=False)
    title       = Column(String(200), nullable=False)
    message     = Column(Text)
    link        = Column(String(200))
    is_read     = Column(Boolean, default=False)
    read_at     = Column(DateTime)
    created_at  = Column(DateTime, server_default=func.now())
    expires_at  = Column(DateTime)

    user = relationship('User', foreign_keys=[user_id])


class NotificationPreference(Base):
    __tablename__ = 'notification_preferences'
    id                   = Column(Integer, primary_key=True, autoincrement=True)
    user_id              = Column(Integer, ForeignKey('users.id', ondelete='CASCADE'),
                                   nullable=False, unique=True)
    email_enabled        = Column(Boolean, default=False)
    email                = Column(String(100))
    whatsapp_enabled     = Column(Boolean, default=False)
    whatsapp_number      = Column(String(20))
    alert_unclosed_shift = Column(Boolean, default=True)
    alert_credit_limit   = Column(Boolean, default=True)
    alert_low_stock      = Column(Boolean, default=True)
    alert_price_override = Column(Boolean, default=True)

    user = relationship('User', foreign_keys=[user_id])


class StockAdjustment(Base):
    __tablename__ = 'stock_adjustments'
    id              = Column(Integer, primary_key=True, autoincrement=True)
    adjustment_date = Column(Date, nullable=False)
    fuel_type       = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'), nullable=False)
    adjustment_type = Column(Enum('LOSS', 'EVAPORATION', 'CORRECTION',
                                   'OPENING_STOCK', 'SPILLAGE'), nullable=False)
    quantity_ltr    = Column(DECIMAL(10, 2), nullable=False)
    reason          = Column(Text)
    reference_no    = Column(String(50))
    approved_by     = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_by      = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at      = Column(DateTime, server_default=func.now())

    approver = relationship('User', foreign_keys=[approved_by])
    creator  = relationship('User', foreign_keys=[created_by])


class Expense(Base):
    __tablename__ = 'expenses'
    id             = Column(Integer, primary_key=True, autoincrement=True)
    expense_date   = Column(Date, nullable=False)
    category       = Column(Enum('ELECTRICITY', 'MAINTENANCE', 'SALARY_ADVANCE',
                                  'SUPPLIES', 'TRANSPORT', 'OTHER'), nullable=False)
    description    = Column(String(200), nullable=False)
    amount         = Column(DECIMAL(10, 2), nullable=False)
    payment_method = Column(Enum('CASH', 'BANK_TRANSFER'), default='CASH')
    reference_no   = Column(String(50))
    shift_id       = Column(Integer, ForeignKey('daily_shifts.id', ondelete='SET NULL'))
    staff_id       = Column(Integer, ForeignKey('staff.id', ondelete='SET NULL'))
    approved_by    = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_by     = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    created_at     = Column(DateTime, server_default=func.now())

    approver     = relationship('User',  foreign_keys=[approved_by])
    creator      = relationship('User',  foreign_keys=[created_by])
    staff_member = relationship('Staff', foreign_keys=[staff_id])


class ShiftHandoverNote(Base):
    __tablename__ = 'shift_handover_notes'
    id                     = Column(Integer, primary_key=True, autoincrement=True)
    shift_id               = Column(Integer, ForeignKey('daily_shifts.id', ondelete='CASCADE'),
                                     nullable=False, unique=True)
    outgoing_notes         = Column(Text)
    equipment_concerns     = Column(Text)
    cash_discrepancy_notes = Column(Text)
    created_by             = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    updated_at             = Column(DateTime, server_default=func.now(), onupdate=func.now())

    creator = relationship('User', foreign_keys=[created_by])


class TransactionLog(Base):
    __tablename__ = 'transaction_logs'
    id          = Column(Integer, primary_key=True, autoincrement=True)
    user_id     = Column(Integer, ForeignKey('users.id', ondelete='SET NULL'))
    endpoint    = Column(String(200))
    method      = Column(String(10))
    status_code = Column(Integer)
    duration_ms = Column(Integer)
    ip_address  = Column(String(45))
    created_at  = Column(DateTime, server_default=func.now())

    user = relationship('User', foreign_keys=[user_id])


class TankDipChart(Base):
    __tablename__ = 'tank_dip_charts'
    id         = Column(Integer, primary_key=True, autoincrement=True)
    tank_type  = Column(Enum('LP92', 'EURO3', 'LAD', 'LADXM'), nullable=False)
    height_cm  = Column(DECIMAL(5, 1), nullable=False)
    volume_ltr = Column(DECIMAL(10, 2), nullable=False)
    __table_args__ = (UniqueConstraint('tank_type', 'height_cm', name='uq_tank_dip_chart'),)