from datetime import datetime

from extensions import db
from sqlalchemy import Numeric


# ============================================================
# MANAGEMENT USERS
# ============================================================

class User(db.Model):
    __tablename__ = "users"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    username = db.Column(
        db.String(80),
        unique=True,
        nullable=False
    )

    password_hash = db.Column(
        db.String(255),
        nullable=False
    )

    role = db.Column(
        db.String(20),
        nullable=False,
        default="admin"
    )

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

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )


# ============================================================
# SITE SETTINGS
# ============================================================

class SiteSetting(db.Model):
    __tablename__ = "site_settings"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    business_name = db.Column(
        db.String(150),
        nullable=False,
        default="Green Verges"
    )

    site_url = db.Column(
        db.String(255),
        nullable=True
    )

    logo = db.Column(
        db.String(255),
        nullable=True
    )

    phone = db.Column(
        db.String(50),
        nullable=True
    )

    email = db.Column(
        db.String(150),
        nullable=True
    )

    address = db.Column(
        db.Text,
        nullable=True
    )

    whatsapp_number = db.Column(
        db.String(50),
        nullable=True
    )

    facebook_url = db.Column(
        db.String(255),
        nullable=True
    )

    instagram_url = db.Column(
        db.String(255),
        nullable=True
    )

    default_meta_title = db.Column(
        db.String(255),
        nullable=True
    )

    default_meta_description = db.Column(
        db.String(320),
        nullable=True
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )


# ============================================================
# CATEGORIES
# ============================================================

class Category(db.Model):
    __tablename__ = "categories"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    name = db.Column(
        db.String(100),
        unique=True,
        nullable=False
    )

    description = db.Column(
        db.Text,
        nullable=True
    )

    image = db.Column(
        db.String(255),
        nullable=True
    )

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

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )

    products = db.relationship(
        "Product",
        backref="category",
        lazy=True
    )


# ============================================================
# PRODUCTS
# ============================================================

class Product(db.Model):
    __tablename__ = "products"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    category_id = db.Column(
        db.Integer,
        db.ForeignKey("categories.id"),
        nullable=True
    )

    name = db.Column(
        db.String(150),
        nullable=False
    )

    slug = db.Column(
        db.String(180),
        unique=True,
        nullable=False
    )

    description = db.Column(
        db.Text,
        nullable=True
    )

    meta_title = db.Column(
        db.String(255),
        nullable=True
    )

    meta_description = db.Column(
        db.String(320),
        nullable=True
    )

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

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )

    images = db.relationship(
        "ProductImage",
        backref="product",
        lazy=True,
        cascade="all, delete-orphan"
    )

    variants = db.relationship(
        "ProductVariant",
        backref="product",
        lazy=True,
        cascade="all, delete-orphan"
    )

    reviews = db.relationship(
        "Review",
        backref="product",
        lazy=True,
        cascade="all, delete-orphan"
    )


# ============================================================
# PRODUCT IMAGES
# ============================================================

class ProductImage(db.Model):
    __tablename__ = "product_images"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    product_id = db.Column(
        db.Integer,
        db.ForeignKey("products.id"),
        nullable=False
    )

    filename = db.Column(
        db.String(255),
        nullable=False
    )

    is_primary = db.Column(
        db.Boolean,
        nullable=False,
        default=False
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )


# ============================================================
# PRODUCT VARIANTS
# ============================================================

class ProductVariant(db.Model):
    __tablename__ = "product_variants"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    product_id = db.Column(
        db.Integer,
        db.ForeignKey("products.id"),
        nullable=False
    )

    mass = db.Column(
        db.String(50),
        nullable=False
    )

    price = db.Column(
        Numeric(10, 2),
        nullable=False
    )

    stock_quantity = db.Column(
        db.Integer,
        nullable=False,
        default=0
    )

    sku = db.Column(
        db.String(100),
        unique=True,
        nullable=True
    )

    is_available = db.Column(
        db.Boolean,
        nullable=False,
        default=True
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )

    stock_movements = db.relationship(
        "StockMovement",
        backref="variant",
        lazy=True,
        cascade="all, delete-orphan"
    )

    cart_items = db.relationship(
        "CartItem",
        backref="variant",
        lazy=True
    )

    order_items = db.relationship(
        "OrderItem",
        backref="variant",
        lazy=True
    )


# ============================================================
# STOCK MOVEMENTS
# ============================================================

class StockMovement(db.Model):
    __tablename__ = "stock_movements"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    variant_id = db.Column(
        db.Integer,
        db.ForeignKey("product_variants.id"),
        nullable=False
    )

    quantity = db.Column(
        db.Integer,
        nullable=False
    )

    movement_type = db.Column(
        db.String(30),
        nullable=False
    )

    reference = db.Column(
        db.String(255),
        nullable=True
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )


# ============================================================
# CARTS
# ============================================================

class Cart(db.Model):
    __tablename__ = "carts"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    session_id = db.Column(
        db.String(255),
        nullable=True,
        unique=True
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )

    items = db.relationship(
        "CartItem",
        backref="cart",
        lazy=True,
        cascade="all, delete-orphan"
    )


# ============================================================
# CART ITEMS
# ============================================================

class CartItem(db.Model):
    __tablename__ = "cart_items"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    cart_id = db.Column(
        db.Integer,
        db.ForeignKey("carts.id"),
        nullable=False
    )

    variant_id = db.Column(
        db.Integer,
        db.ForeignKey("product_variants.id"),
        nullable=False
    )

    quantity = db.Column(
        db.Integer,
        nullable=False,
        default=1
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )


# ============================================================
# ORDERS
# ============================================================

class Order(db.Model):
    __tablename__ = "orders"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    order_number = db.Column(
        db.String(50),
        unique=True,
        nullable=False
    )

    customer_name = db.Column(
        db.String(200),
        nullable=False
    )

    customer_email = db.Column(
        db.String(150),
        nullable=True
    )

    customer_phone = db.Column(
        db.String(30),
        nullable=False
    )

    delivery_method = db.Column(
        db.String(30),
        nullable=False
    )

    delivery_address = db.Column(
        db.Text,
        nullable=True
    )

    delivery_notes = db.Column(
        db.Text,
        nullable=True
    )

    subtotal = db.Column(
        Numeric(10, 2),
        nullable=False
    )

    delivery_fee = db.Column(
        Numeric(10, 2),
        nullable=False,
        default=0
    )

    total = db.Column(
        Numeric(10, 2),
        nullable=False
    )

    order_status = db.Column(
        db.String(30),
        nullable=False,
        default="pending"
    )

    payment_status = db.Column(
        db.String(30),
        nullable=False,
        default="pending"
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )

    items = db.relationship(
        "OrderItem",
        backref="order",
        lazy=True,
        cascade="all, delete-orphan"
    )

    payments = db.relationship(
        "Payment",
        backref="order",
        lazy=True,
        cascade="all, delete-orphan"
    )

    delivery = db.relationship(
        "Delivery",
        backref="order",
        uselist=False,
        cascade="all, delete-orphan"
    )

    status_history = db.relationship(
        "OrderStatusHistory",
        backref="order",
        lazy=True,
        cascade="all, delete-orphan"
    )


# ============================================================
# ORDER ITEMS
# ============================================================

class OrderItem(db.Model):
    __tablename__ = "order_items"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    order_id = db.Column(
        db.Integer,
        db.ForeignKey("orders.id"),
        nullable=False
    )

    variant_id = db.Column(
        db.Integer,
        db.ForeignKey("product_variants.id"),
        nullable=True
    )

    product_name = db.Column(
        db.String(150),
        nullable=False
    )

    mass = db.Column(
        db.String(50),
        nullable=False
    )

    quantity = db.Column(
        db.Integer,
        nullable=False
    )

    unit_price = db.Column(
        Numeric(10, 2),
        nullable=False
    )

    subtotal = db.Column(
        Numeric(10, 2),
        nullable=False
    )


# ============================================================
# PAYMENTS
# ============================================================

class Payment(db.Model):
    __tablename__ = "payments"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    order_id = db.Column(
        db.Integer,
        db.ForeignKey("orders.id"),
        nullable=False
    )

    payment_method = db.Column(
        db.String(30),
        nullable=False
    )

    amount = db.Column(
        Numeric(10, 2),
        nullable=False
    )

    status = db.Column(
        db.String(30),
        nullable=False,
        default="pending"
    )

    transaction_reference = db.Column(
        db.String(150),
        nullable=True
    )

    gateway_reference = db.Column(
        db.String(150),
        nullable=True
    )

    paid_at = db.Column(
        db.DateTime,
        nullable=True
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )

    transactions = db.relationship(
        "PaymentTransaction",
        backref="payment",
        lazy=True,
        cascade="all, delete-orphan"
    )


# ============================================================
# PAYMENT TRANSACTIONS
# ============================================================

class PaymentTransaction(db.Model):
    __tablename__ = "payment_transactions"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    payment_id = db.Column(
        db.Integer,
        db.ForeignKey("payments.id"),
        nullable=False
    )

    transaction_reference = db.Column(
        db.String(150),
        nullable=True
    )

    gateway = db.Column(
        db.String(50),
        nullable=False
    )

    request_data = db.Column(
        db.Text,
        nullable=True
    )

    response_data = db.Column(
        db.Text,
        nullable=True
    )

    status = db.Column(
        db.String(30),
        nullable=False
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )


# ============================================================
# DELIVERIES
# ============================================================

class Delivery(db.Model):
    __tablename__ = "deliveries"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    order_id = db.Column(
        db.Integer,
        db.ForeignKey("orders.id"),
        nullable=False,
        unique=True
    )

    delivery_method = db.Column(
        db.String(30),
        nullable=False
    )

    delivery_status = db.Column(
        db.String(30),
        nullable=False,
        default="pending"
    )

    address = db.Column(
        db.Text,
        nullable=True
    )

    fee = db.Column(
        Numeric(10, 2),
        nullable=False,
        default=0
    )

    tracking_reference = db.Column(
        db.String(150),
        nullable=True
    )

    dispatched_at = db.Column(
        db.DateTime,
        nullable=True
    )

    delivered_at = db.Column(
        db.DateTime,
        nullable=True
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )


# ============================================================
# DELIVERY ZONES
# ============================================================

class DeliveryZone(db.Model):
    __tablename__ = "delivery_zones"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    name = db.Column(
        db.String(100),
        unique=True,
        nullable=False
    )

    description = db.Column(
        db.Text,
        nullable=True
    )

    fee = db.Column(
        Numeric(10, 2),
        nullable=False,
        default=0
    )

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

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )


# ============================================================
# REVIEWS
# ============================================================

class Review(db.Model):
    __tablename__ = "reviews"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    product_id = db.Column(
        db.Integer,
        db.ForeignKey("products.id"),
        nullable=False
    )

    reviewer_name = db.Column(
        db.String(150),
        nullable=False
    )

    rating = db.Column(
        db.Integer,
        nullable=False
    )

    title = db.Column(
        db.String(150),
        nullable=True
    )

    comment = db.Column(
        db.Text,
        nullable=True
    )

    is_approved = db.Column(
        db.Boolean,
        nullable=False,
        default=False
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )

    updated_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow,
        onupdate=datetime.utcnow
    )


# ============================================================
# ORDER STATUS HISTORY
# ============================================================

class OrderStatusHistory(db.Model):
    __tablename__ = "order_status_history"

    id = db.Column(
        db.Integer,
        primary_key=True
    )

    order_id = db.Column(
        db.Integer,
        db.ForeignKey("orders.id"),
        nullable=False
    )

    status = db.Column(
        db.String(30),
        nullable=False
    )

    changed_by = db.Column(
        db.String(100),
        nullable=True
    )

    note = db.Column(
        db.Text,
        nullable=True
    )

    created_at = db.Column(
        db.DateTime,
        nullable=False,
        default=datetime.utcnow
    )