models.py

from sqlalchemy import Column, Integer, String, Text, DateTime, Boolean, ForeignKey, Time, Float
from sqlalchemy.orm import relationship
from sqlalchemy.sql import func
from pgvector.sqlalchemy import Vector
from database import Base


class Customer(Base):
    __tablename__ = "customers"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    hashed_password = Column(String, nullable=True)  # Nullable for Google users
    full_name = Column(String)
    phone = Column(String)
    location = Column(String, nullable=True)  # Customer location/city
    profile_picture = Column(String, nullable=True)  # URL from Google
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Google OAuth
    google_id = Column(String, nullable=True, index=True)
    
    # Preferences
    skin_type = Column(String, nullable=True)  # e.g., "Oily", "Dry", "Combination", "Sensitive", "Normal"
    categories = Column(Text, nullable=True)  # JSON array: ["Skincare", "Haircare", "Makeup"]
    budget_range = Column(String, nullable=True)  # e.g., "0-50", "50-100", "100-200", "200+"
    
    # Relationships
    payments = relationship("Payment", back_populates="customer", foreign_keys="Payment.customer_id")


class ServiceProvider(Base):
    __tablename__ = "service_providers"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String, unique=True, index=True, nullable=False)
    hashed_password = Column(String, nullable=True)  # Nullable for Google users
    full_name = Column(String)
    business_name = Column(String)
    phone = Column(String)
    city = Column(String)
    bio = Column(Text)
    profile_photo = Column(String, nullable=True)  # File path or URL
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # New fields for provider signup
    cnic_id = Column(String, nullable=True)  # CNIC/ID Number
    certificates = Column(Text, nullable=True)  # Certifications/Qualifications
    business_license = Column(String, nullable=True)  # Business license file path or number
    
    # Google OAuth
    google_id = Column(String, nullable=True, index=True)  # Removed unique=True to allow multiple NULLs
    profile_picture = Column(String, nullable=True)  # URL from Google
    
    # Timezone and Calendar Integration
    timezone = Column(String, default="UTC")  # e.g., "America/New_York", "Asia/Karachi"
    google_calendar_id = Column(String, nullable=True)  # Google Calendar ID for integration
    google_calendar_access_token = Column(Text, nullable=True)  # Encrypted access token
    google_calendar_refresh_token = Column(Text, nullable=True)  # Encrypted refresh token
    
    # Provider Level (based on ratings)
    level = Column(String, default="beginner")  # beginner, skilled, expert

    # Stripe Connect (payouts)
    stripe_account_id = Column(String, nullable=True)  # Connected account ID (acct_xxx)
    stripe_onboarding_complete = Column(Boolean, default=False)  # Bank/payout details completed
    
    # AI Assistant / Provider Embeddings
    embedding = Column(Vector(384), nullable=True)  # pgvector mapping -> vector(384)
    
    # Relationships
    portfolio_items = relationship("PortfolioItem", back_populates="provider", cascade="all, delete-orphan")
    services = relationship("Service", back_populates="provider", cascade="all, delete-orphan")
    # Use provider_id as the primary FK linking bookings to this provider
    bookings = relationship(
        "Booking",
        back_populates="provider",
        cascade="all, delete-orphan",
        foreign_keys="Booking.provider_id",
    )
    payments = relationship("Payment", back_populates="provider", foreign_keys="Payment.provider_id")


class PortfolioItem(Base):
    __tablename__ = "portfolio_items"

    id = Column(Integer, primary_key=True, index=True)
    provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=False)
    title = Column(String, nullable=False)
    description = Column(Text, nullable=True)
    experience_details = Column(Text, nullable=True)
    image_url = Column(String, nullable=True)
    video_url = Column(String, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    provider = relationship("ServiceProvider", back_populates="portfolio_items")


class Service(Base):
    __tablename__ = "services"

    id = Column(Integer, primary_key=True, index=True)
    provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=False)
    name = Column(String, nullable=False)
    category = Column(String, nullable=True)
    description = Column(Text, nullable=True)
    price = Column(String, nullable=False)
    duration = Column(String, nullable=True)  # e.g., "60 minutes"
    is_active = Column(Boolean, default=True)
    availability_schedule = Column(Text, nullable=True)  # JSON string for backward compatibility
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    provider = relationship("ServiceProvider", back_populates="services")
    bookings = relationship("Booking", back_populates="service", cascade="all, delete-orphan")
    time_slots = relationship("TimeSlot", back_populates="service", cascade="all, delete-orphan")


class TimeSlot(Base):
    __tablename__ = "time_slots"

    id = Column(Integer, primary_key=True, index=True)
    service_id = Column(Integer, ForeignKey("services.id"), nullable=False)
    slot_date = Column(DateTime(timezone=True), nullable=False)
    is_available = Column(Boolean, default=True)  # Can be marked unavailable by provider
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    service = relationship("Service", back_populates="time_slots")
    booking = relationship("Booking", back_populates="time_slot", uselist=False)  # One booking per slot


class Booking(Base):
    __tablename__ = "bookings"

    id = Column(Integer, primary_key=True, index=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False)
    provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=False)
    service_id = Column(Integer, ForeignKey("services.id"), nullable=False)
    time_slot_id = Column(Integer, ForeignKey("time_slots.id"), nullable=True)  # Reference to time slot
    booking_date = Column(DateTime(timezone=True), nullable=False)  # Kept for backward compatibility
    end_date = Column(DateTime(timezone=True), nullable=True)  # Calculated from service duration
    status = Column(String, default="pending")  # pending, confirmed, completed, cancelled, rejected

    # Optional duplicated status field for richer workflows
    booking_status = Column(String, nullable=True)  # Mirrors/extends status when using advanced flows
    notes = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    
    # Google Calendar Integration
    google_calendar_event_id = Column(String, nullable=True)  # Google Calendar event ID
    
    # Notification tracking
    reminder_sent = Column(Boolean, default=False)
    
    # Payment Integration (card capture + escrow + payouts)
    payment_status = Column(
        String,
        default="UNPAID"
    )  # UNPAID, HELD_IN_ESCROW, RELEASED_TO_PROVIDER, REFUNDED, FAILED
    payment_id = Column(Integer, ForeignKey("payments.id"), nullable=True)  # Reference to payment

    # Stripe identifiers (stored on booking for escrow / payouts / refunds)
    stripe_payment_intent_id = Column(String, nullable=True, index=True)
    stripe_charge_id = Column(String, nullable=True, index=True)
    stripe_transfer_id = Column(String, nullable=True, index=True)
    stripe_refund_id = Column(String, nullable=True, index=True)

    # Provider assignment tracking (original/assigned provider)
    original_provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=True)
    assigned_provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=True)

    customer = relationship("Customer")
    provider = relationship(
        "ServiceProvider",
        back_populates="bookings",
        foreign_keys=[provider_id],
    )

    # Relationships for advanced provider assignment
    original_provider = relationship(
        "ServiceProvider",
        foreign_keys=[original_provider_id],
        lazy="joined",
    )
    assigned_provider = relationship(
        "ServiceProvider",
        foreign_keys=[assigned_provider_id],
        lazy="joined",
    )
    service = relationship("Service", back_populates="bookings")
    time_slot = relationship("TimeSlot", back_populates="booking")
    payment = relationship("Payment", back_populates="booking", uselist=False, foreign_keys="[Payment.booking_id]", primaryjoin="Booking.id == Payment.booking_id")


class Rating(Base):
    """Customer rating for a completed booking/service."""
    __tablename__ = "ratings"

    id = Column(Integer, primary_key=True, index=True)
    booking_id = Column(Integer, ForeignKey("bookings.id"), unique=True, nullable=False)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False)
    provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=False)
    service_id = Column(Integer, ForeignKey("services.id"), nullable=False)
    rating = Column(Integer, nullable=False)  # 1-5
    comment = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())


class Payment(Base):
    __tablename__ = "payments"

    id = Column(Integer, primary_key=True, index=True)
    booking_id = Column(Integer, ForeignKey("bookings.id"), nullable=False)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False)
    provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=False)
    
    # Payment Details
    amount = Column(String, nullable=False)  # Amount in currency (e.g., "50.00")
    currency = Column(String, default="USD")  # Currency code
    payment_method = Column(String, nullable=True)  # card, bank_transfer, etc.
    
    # Stripe Integration
    stripe_payment_intent_id = Column(String, nullable=True, index=True)  # Stripe Payment Intent ID
    stripe_charge_id = Column(String, nullable=True)  # Stripe Charge ID
    stripe_customer_id = Column(String, nullable=True)  # Stripe Customer ID
    
    # Payment Status
    status = Column(String, default="pending")  # pending, succeeded, failed, refunded, cancelled
    failure_reason = Column(Text, nullable=True)  # Reason if payment failed
    
    # Timestamps
    created_at = Column(DateTime(timezone=True), server_default=func.now())
    paid_at = Column(DateTime(timezone=True), nullable=True)
    refunded_at = Column(DateTime(timezone=True), nullable=True)
    
    # Relationships
    booking = relationship("Booking", back_populates="payment", foreign_keys=[booking_id])
    customer = relationship("Customer", back_populates="payments", foreign_keys=[customer_id])
    provider = relationship("ServiceProvider", back_populates="payments", foreign_keys=[provider_id])


class Notification(Base):
    """General-purpose notification for customers and providers."""
    __tablename__ = "notifications"

    id = Column(Integer, primary_key=True, index=True)
    # One of customer_id or provider_id must be set
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=True, index=True)
    provider_id = Column(Integer, ForeignKey("service_providers.id"), nullable=True, index=True)
    booking_id = Column(Integer, ForeignKey("bookings.id"), nullable=True)
    type = Column(String, nullable=False)  # booking_cancelled_by_provider, standby_added, refund_processed, standby_selected
    title = Column(String, nullable=False)
    message = Column(Text, nullable=False)
    is_read = Column(Boolean, default=False)
    data = Column(Text, nullable=True)  # JSON string for extra payload (e.g. standby provider list)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    customer = relationship("Customer")
    provider = relationship("ServiceProvider")


class SkinAnalysis(Base):
    """Stores AI skin analysis results for a customer (text only, no image)."""
    __tablename__ = "skin_analyses"

    id = Column(Integer, primary_key=True, index=True)
    customer_id = Column(Integer, ForeignKey("customers.id"), nullable=False)
    results = Column(Text, nullable=False)   # JSON string: list of {label, display, score, rank}
    image_url = Column(String, nullable=True)  # Always None — kept for future use
    notes = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    customer = relationship("Customer")


# Standby Support System Models - Re-enabled for provider cancellation workflow