"""
PostgreSQL reference models for POS sync.
"""
from sqlalchemy import Column, String, DateTime, Text, Boolean, Float, Integer, JSON, ARRAY
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
from datetime import datetime

Base = declarative_base()


class CustomerRef(Base):
    """PostgreSQL reference table for customers"""
    __tablename__ = "customer_ref"
    __table_args__ = {"schema": "trans"}

    customer_id = Column(String, primary_key=True)
    merchant_id = Column(String, nullable=False, index=True)
    name = Column(String(150), nullable=False)
    phone = Column(String(20), nullable=True, index=True)
    email = Column(String(255), nullable=True, index=True)
    notes = Column(Text, nullable=True)
    status = Column(String(20), nullable=False, default="active")
    created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)


class StaffRef(Base):
    """PostgreSQL reference table for staff"""
    __tablename__ = "pos_staff_ref"
    __table_args__ = {"schema": "trans"}

    staff_id = Column(String, primary_key=True)
    merchant_id = Column(String, nullable=False, index=True)
    name = Column(String(150), nullable=False)
    phone = Column(String(20), nullable=True)
    email = Column(String(255), nullable=True)
    role = Column(String(50), nullable=True)
    specializations = Column(ARRAY(String), nullable=True)
    status = Column(String(20), nullable=False, default="active")
    created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)


class CatalogueServiceRef(Base):
    """PostgreSQL reference table for catalogue services"""
    __tablename__ = "catalogue_service_ref"
    __table_args__ = {"schema": "trans"}

    service_id = Column(String, primary_key=True)
    merchant_id = Column(String, nullable=False, index=True)
    service_name = Column(String(200), nullable=False)
    service_code = Column(String(50), nullable=False, index=True)
    category_id = Column(String, nullable=True)
    category_name = Column(String(100), nullable=True)
    description = Column(Text, nullable=True)
    duration_mins = Column(Integer, nullable=False, default=30)
    price = Column(Float, nullable=False, default=0.0)
    currency = Column(String(3), nullable=False, default="INR")
    gst_rate = Column(Float, nullable=False, default=18.0)
    status = Column(String(20), nullable=False, default="active")
    sort_order = Column(Integer, nullable=False, default=0)
    pricing = Column(JSON, nullable=True)  # Store full pricing JSON
    category = Column(JSON, nullable=True)  # Store full category JSON
    created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)