from typing import Optional
from datetime import datetime, timezone
from decimal import Decimal

from sqlalchemy import Boolean, DateTime, ForeignKey, Integer, Numeric, String, Text
from sqlalchemy.types import TypeDecorator
from sqlalchemy.orm import Mapped, mapped_column, relationship

from .db import Base


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


Money = Numeric(12, 2)


class UTCDateTime(TypeDecorator):
    """Stores UTC. MySQL has no time-zone column, so values come back naive: attach UTC on the way out."""
    impl = DateTime
    cache_ok = True

    def process_bind_param(self, value, dialect):
        return value if value is None or value.tzinfo is None else value.astimezone(timezone.utc)

    def process_result_value(self, value, dialect):
        return value if value is None or value.tzinfo is not None else value.replace(tzinfo=timezone.utc)


class User(Base):
    __tablename__ = "users"
    id: Mapped[int] = mapped_column(primary_key=True)
    email: Mapped[str] = mapped_column(String(200), unique=True)
    name: Mapped[str] = mapped_column(String(120))
    phone: Mapped[str] = mapped_column(String(30), default="")
    address: Mapped[str] = mapped_column(Text, default="")
    password_hash: Mapped[str] = mapped_column(String(255))
    role: Mapped[str] = mapped_column(String(20), default="customer")  # owner | staff | customer
    active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now)
    last_login: Mapped[Optional[datetime]] = mapped_column(UTCDateTime(timezone=True))


class Category(Base):
    __tablename__ = "categories"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80), unique=True)
    sort: Mapped[int] = mapped_column(Integer, default=0)


class Supplier(Base):
    __tablename__ = "suppliers"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(120), unique=True)
    contact: Mapped[str] = mapped_column(String(120), default="")
    phone: Mapped[str] = mapped_column(String(30), default="")
    email: Mapped[str] = mapped_column(String(200), default="")
    country: Mapped[str] = mapped_column(String(60), default="Ghana")
    lead_days: Mapped[int] = mapped_column(Integer, default=7)


class Product(Base):
    __tablename__ = "products"
    id: Mapped[int] = mapped_column(primary_key=True)
    sku: Mapped[str] = mapped_column(String(40), unique=True)
    name: Mapped[str] = mapped_column(String(160))
    brand: Mapped[str] = mapped_column(String(80), default="")
    size: Mapped[str] = mapped_column(String(40), default="")
    description: Mapped[str] = mapped_column(Text, default="")
    category_id: Mapped[Optional[int]] = mapped_column(ForeignKey("categories.id"))
    price: Mapped[Decimal] = mapped_column(Money)
    cost: Mapped[Decimal] = mapped_column(Money, default=0)
    stock: Mapped[int] = mapped_column(Integer, default=0)
    reorder_level: Mapped[int] = mapped_column(Integer, default=10)
    badge: Mapped[str] = mapped_column(String(30), default="")
    image_url: Mapped[str] = mapped_column(String(255), default="")
    supplier_id: Mapped[Optional[int]] = mapped_column(ForeignKey("suppliers.id"))
    active: Mapped[bool] = mapped_column(Boolean, default=True)
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now)
    category: Mapped[Optional[Category]] = relationship()


class PurchaseOrder(Base):
    __tablename__ = "purchase_orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    number: Mapped[str] = mapped_column(String(20), unique=True)
    supplier_id: Mapped[int] = mapped_column(ForeignKey("suppliers.id"))
    status: Mapped[str] = mapped_column(String(20), default="ordered")  # ordered | received | cancelled
    note: Mapped[str] = mapped_column(Text, default="")
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now)
    received_at: Mapped[Optional[datetime]] = mapped_column(UTCDateTime(timezone=True))
    supplier: Mapped[Supplier] = relationship()
    lines: Mapped[list["PurchaseLine"]] = relationship(cascade="all, delete-orphan")


class PurchaseLine(Base):
    __tablename__ = "purchase_lines"
    id: Mapped[int] = mapped_column(primary_key=True)
    po_id: Mapped[int] = mapped_column(ForeignKey("purchase_orders.id"))
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"))
    quantity: Mapped[int] = mapped_column(Integer)
    unit_cost: Mapped[Decimal] = mapped_column(Money)
    product: Mapped[Product] = relationship()


class DeliveryZone(Base):
    __tablename__ = "delivery_zones"
    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str] = mapped_column(String(80), unique=True)
    fee: Mapped[Decimal] = mapped_column(Money)
    eta: Mapped[str] = mapped_column(String(60), default="Same day")
    active: Mapped[bool] = mapped_column(Boolean, default=True)


class Setting(Base):
    __tablename__ = "settings"
    key: Mapped[str] = mapped_column(String(60), primary_key=True)
    value: Mapped[str] = mapped_column(Text, default="")


class Order(Base):
    __tablename__ = "orders"
    id: Mapped[int] = mapped_column(primary_key=True)
    number: Mapped[str] = mapped_column(String(24), unique=True)
    user_id: Mapped[Optional[int]] = mapped_column(ForeignKey("users.id"))
    customer_name: Mapped[str] = mapped_column(String(120))
    phone: Mapped[str] = mapped_column(String(30))
    email: Mapped[str] = mapped_column(String(200), default="")
    address: Mapped[str] = mapped_column(Text)
    zone_id: Mapped[Optional[int]] = mapped_column(ForeignKey("delivery_zones.id"))
    zone_name: Mapped[str] = mapped_column(String(80), default="")
    subtotal: Mapped[Decimal] = mapped_column(Money)
    delivery_fee: Mapped[Decimal] = mapped_column(Money, default=0)
    total: Mapped[Decimal] = mapped_column(Money)
    status: Mapped[str] = mapped_column(String(24), default="pending")
    payment_method: Mapped[str] = mapped_column(String(24))  # momo | cod
    payment_status: Mapped[str] = mapped_column(String(16), default="unpaid")  # unpaid | paid | refunded
    payment_ref: Mapped[str] = mapped_column(String(80), default="")
    note: Mapped[str] = mapped_column(Text, default="")
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now, index=True)
    items: Mapped[list["OrderItem"]] = relationship(cascade="all, delete-orphan", order_by="OrderItem.id")
    events: Mapped[list["OrderEvent"]] = relationship(cascade="all, delete-orphan", order_by="OrderEvent.id")


class Payment(Base):
    """One attempt to pay an online order. An order can have several (customer closed the page, tried again)."""
    __tablename__ = "payments"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"), index=True)
    reference: Mapped[str] = mapped_column(String(60), unique=True)
    amount: Mapped[Decimal] = mapped_column(Money)
    provider: Mapped[str] = mapped_column(String(12))  # paystack | demo
    status: Mapped[str] = mapped_column(String(12), default="initiated")  # initiated | success | failed
    demo_result: Mapped[str] = mapped_column(String(12), default="")  # demo provider only
    created_at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now)


class OrderItem(Base):
    __tablename__ = "order_items"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"), index=True)
    product_id: Mapped[Optional[int]] = mapped_column(ForeignKey("products.id"))
    name: Mapped[str] = mapped_column(String(160))
    category: Mapped[str] = mapped_column(String(80), default="")
    quantity: Mapped[int] = mapped_column(Integer)
    unit_price: Mapped[Decimal] = mapped_column(Money)
    unit_cost: Mapped[Decimal] = mapped_column(Money, default=0)


class OrderEvent(Base):
    __tablename__ = "order_events"
    id: Mapped[int] = mapped_column(primary_key=True)
    order_id: Mapped[int] = mapped_column(ForeignKey("orders.id"), index=True)
    status: Mapped[str] = mapped_column(String(24))
    note: Mapped[str] = mapped_column(Text, default="")
    by: Mapped[str] = mapped_column(String(120), default="")
    at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now)


class StockMovement(Base):
    __tablename__ = "stock_movements"
    id: Mapped[int] = mapped_column(primary_key=True)
    product_id: Mapped[int] = mapped_column(ForeignKey("products.id"), index=True)
    change: Mapped[int] = mapped_column(Integer)
    after: Mapped[int] = mapped_column(Integer)
    reason: Mapped[str] = mapped_column(String(30))  # sale | cancel | adjust | import | purchase
    ref: Mapped[str] = mapped_column(String(60), default="")
    by: Mapped[str] = mapped_column(String(120), default="")
    at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now)
    product: Mapped[Product] = relationship()


class AuditLog(Base):
    """Append-only: the app never updates or deletes rows here."""
    __tablename__ = "audit_log"
    id: Mapped[int] = mapped_column(primary_key=True)
    at: Mapped[datetime] = mapped_column(UTCDateTime(timezone=True), default=now)
    actor: Mapped[str] = mapped_column(String(120))
    action: Mapped[str] = mapped_column(String(60))
    detail: Mapped[str] = mapped_column(Text, default="")