from app.extensions import db
from datetime import datetime, date


# type key -> (label, icon, color, default usage unit)
UTILITY_TYPE_META = {
    'electricity': ('Electricity', 'bi-lightning-charge', '#f59e0b', 'kWh'),
    'water':       ('Water',       'bi-droplet',          '#0ea5e9', 'm³'),
    'gas':         ('Gas',         'bi-fire',             '#ef4444', 'therms'),
    'internet':    ('Internet',    'bi-wifi',             '#6366f1', 'GB'),
    'phone':       ('Phone',       'bi-phone',            '#8b5cf6', 'GB'),
    'trash':       ('Trash',       'bi-trash3',           '#64748b', ''),
    'other':       ('Other',       'bi-plug',             '#94a3b8', ''),
}

UTILITY_TYPES = list(UTILITY_TYPE_META.keys())

# A bill is flagged "due soon" this many days before its due date
DUE_SOON_DAYS = 7


class UtilityProvider(db.Model):
    """A utility company / service you receive bills from (PG&E, Comcast, ...)."""

    __tablename__ = 'utility_providers'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), nullable=False)
    utility_type = db.Column(
        db.Enum(*UTILITY_TYPES),
        nullable=False,
        default='electricity'
    )
    account_number = db.Column(db.String(100), nullable=True)   # customer/meter account no.
    usage_unit = db.Column(db.String(20), nullable=True)        # kWh, m³, GB — blank = no usage tracking

    # Where bills get paid from, and what expense category they land in
    default_account_id = db.Column(db.Integer, db.ForeignKey('accounts.id'), nullable=True)
    category_id = db.Column(db.Integer, db.ForeignKey('categories.id'), nullable=True)

    billing_day = db.Column(db.Integer, nullable=True)          # day of month the bill arrives
    color = db.Column(db.String(7), default='#8b5cf6')
    icon = db.Column(db.String(50), default='bi-lightning-charge')
    is_active = db.Column(db.Boolean, default=True)
    notes = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    default_account = db.relationship('Account')
    category = db.relationship('Category')
    bills = db.relationship('UtilityBill', back_populates='provider',
                            lazy='dynamic', cascade='all, delete-orphan')

    @property
    def type_label(self):
        return UTILITY_TYPE_META.get(self.utility_type, UTILITY_TYPE_META['other'])[0]

    @property
    def tracks_usage(self):
        return bool(self.usage_unit)

    def __repr__(self):
        return f'<UtilityProvider {self.name} ({self.utility_type})>'


class UtilityBill(db.Model):
    """One billing period from a provider — amount, due date, and consumption."""

    __tablename__ = 'utility_bills'
    __table_args__ = (
        db.UniqueConstraint('provider_id', 'period_start', name='uq_utility_bill_period'),
    )

    id = db.Column(db.Integer, primary_key=True)
    provider_id = db.Column(db.Integer, db.ForeignKey('utility_providers.id'), nullable=False)

    period_start = db.Column(db.Date, nullable=False, index=True)
    period_end = db.Column(db.Date, nullable=False)
    amount = db.Column(db.Numeric(15, 2), nullable=False)
    due_date = db.Column(db.Date, nullable=True, index=True)

    is_paid = db.Column(db.Boolean, default=False, index=True)
    paid_date = db.Column(db.Date, nullable=True)
    transaction_id = db.Column(db.Integer, db.ForeignKey('transactions.id'), nullable=True)

    # Consumption. usage is either typed directly or derived from meter readings.
    # Column is named usage_amount — USAGE is a reserved word in MySQL.
    usage = db.Column('usage_amount', db.Numeric(15, 3), nullable=True)
    usage_unit = db.Column(db.String(20), nullable=True)   # snapshot of provider unit at entry time
    meter_start = db.Column(db.Numeric(15, 3), nullable=True)
    meter_end = db.Column(db.Numeric(15, 3), nullable=True)

    notes = db.Column(db.Text, nullable=True)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    provider = db.relationship('UtilityProvider', back_populates='bills')
    transaction = db.relationship('Transaction')

    # ── derived ──────────────────────────────────────────────────────────────

    @property
    def period_label(self):
        if self.period_start.year == self.period_end.year:
            return f"{self.period_start.strftime('%b %d')} – {self.period_end.strftime('%b %d, %Y')}"
        return f"{self.period_start.strftime('%b %d, %Y')} – {self.period_end.strftime('%b %d, %Y')}"

    @property
    def period_month(self):
        """Month the bill is attributed to, for grouping — the period start month."""
        return self.period_start.strftime('%Y-%m')

    @property
    def days_until_due(self):
        if not self.due_date or self.is_paid:
            return None
        return (self.due_date - date.today()).days

    @property
    def status(self):
        """paid | overdue | due_soon | unpaid"""
        if self.is_paid:
            return 'paid'
        days = self.days_until_due
        if days is None:
            return 'unpaid'
        if days < 0:
            return 'overdue'
        if days <= DUE_SOON_DAYS:
            return 'due_soon'
        return 'unpaid'

    @property
    def rate_per_unit(self):
        """Cost per kWh / m³ / GB for this period, or None when usage isn't tracked."""
        if not self.usage:
            return None
        u = float(self.usage)
        if u <= 0:
            return None
        return float(self.amount) / u

    @property
    def days_in_period(self):
        return max((self.period_end - self.period_start).days + 1, 1)

    @property
    def daily_cost(self):
        return float(self.amount) / self.days_in_period

    def sync_usage_from_meter(self):
        """If both meter readings are present, usage is the difference between them."""
        if self.meter_start is not None and self.meter_end is not None:
            diff = float(self.meter_end) - float(self.meter_start)
            if diff >= 0:
                self.usage = diff
        return self.usage

    def __repr__(self):
        return f'<UtilityBill provider={self.provider_id} {self.period_start} {self.amount}>'