from utils.database import Base
from datetime import datetime, timezone, timedelta, date

from sqlalchemy import (
    Column,
    Integer,
    String,
    DATETIME,
    DateTime,
    SmallInteger,
    ForeignKey,
    Time,
    Boolean,
    Date,
    Text,
    JSON,
    BigInteger
)
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.sql import func, text
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from utils.constants import WeeklyScheduleStatus, EmailVerificationStatus


class Users(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    email = Column(String(255), unique=True, nullable=False)
    name = Column(String(150), nullable=False)
    password = Column(String(255), nullable=False)
    status = Column(
        TINYINT,
        server_default=text("1"),
        default=1,
        nullable=False,
        comment="1: active, 2: inactive, 3:deleted",
    )
    step = Column(
        SmallInteger,
        server_default=text("1"),
        default=1,
        nullable=False,
        comment="1: Setup your business, 2: Setup phone number, 3: Setup services, 4: Setup assistant, 5: Dashboard",
    )
    default_company = Column(Integer, ForeignKey("companies.id"), nullable=True)
    is_email_verified = Column(
        SmallInteger,
        server_default=text("0"),
        default=0,
        nullable=False,
        comment="1: Verified, 0: Not verified",
    )
    email_verified_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        default=datetime.now(timezone.utc),
        nullable=False,
    )
    updated_at = Column(
        DateTime(timezone=True),
        server_default=func.now(),
        default=datetime.now(timezone.utc),
        nullable=False,
    )
    deleted_at = Column(DateTime(timezone=True), nullable=True, default=None)


class OTPLog(Base):
    __tablename__ = "otp_logs"

    id = Column(Integer, primary_key=True, index=True)
    email = Column(String(255), nullable=False)
    otp = Column(String(6), nullable=False)
    type = Column(
        SmallInteger,
        nullable=False,
        comment="1: Signup confirmation, 2: Forgot password",
    )
    created_at = Column(
        DateTime(timezone=True),
        nullable=False,
        default=lambda: datetime.now(timezone.utc),
    )
    expired_at = Column(
        DateTime(timezone=True),
        nullable=False,
        default=lambda: datetime.now(timezone.utc) + timedelta(minutes=15),
    )
    used_at = Column(DateTime(timezone=True), nullable=True)


class Company(Base):
    __tablename__ = "companies"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), nullable=False)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    business_type = Column(
        SmallInteger,
        nullable=False,
        comment="1: Garage and Workshop, 2: Pet Care, 3: Pest Control, 4: Handyman, 5: Electricians, 6: Gardening Services, 7: Cleaning Service",
    )
    address1 = Column(String(255), nullable=False)
    address2 = Column(String(255), nullable=True)
    zip_code = Column(String(20), nullable=False)
    official_phone = Column(String(20), nullable=False)
    created_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )
    updated_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )
    agents = relationship("Agent", back_populates="company")


class Service(Base):
    __tablename__ = "services"

    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey("companies.id"), nullable=False)
    name = Column(String(255), nullable=False)
    price = Column(Integer, nullable=False)
    created_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )
    updated_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )


class WeeklySchedule(Base):
    __tablename__ = "weekly_schedules"

    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey("companies.id"), nullable=False)
    day_of_week = Column(SmallInteger, nullable=False, comment="0 = Monday, 6 = Sunday")
    start_time = Column(Time, nullable=False)
    end_time = Column(Time, nullable=False)
    is_day_off = Column(
        SmallInteger,
        default=WeeklyScheduleStatus.NOT_OFF,
        nullable=False,
        comment="1: Day off, 0: Not off",
    )
    created_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )
    updated_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )


class SpecialHoliday(Base):
    __tablename__ = "special_holidays"

    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey("companies.id"), nullable=False)
    date = Column(Date, nullable=False)
    name = Column(String(255), nullable=False)
    created_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )
    updated_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )


class Appointment(Base):
    __tablename__ = "appointments"

    id = Column(Integer, primary_key=True, index=True)
    company_id = Column(Integer, ForeignKey("companies.id"), nullable=False)
    appointment_no = Column(String(255), nullable=False)
    name = Column(String(255), nullable=False)
    phone = Column(String(20), nullable=False)
    start_datetime = Column(DateTime(timezone=True), nullable=False)
    end_datetime = Column(DateTime(timezone=True), nullable=False)
    summary = Column(Text, nullable=True)
    service_id = Column(Integer, ForeignKey("services.id"), nullable=False)
    status = Column(
        SmallInteger,
        server_default=text("1"),
        default=1,
        nullable=False,
        comment="1: active, 2: cancelled, 3: deleted",
    )
    created_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )
    updated_at = Column(
        DateTime(timezone=True), nullable=False, default=datetime.now(timezone.utc)
    )


class AIAssistant(Base):
    __tablename__ = "ai_assistants"

    id = Column(Integer, primary_key=True, index=True)
    retell_agent_id = Column(String(255), nullable=True)
    name = Column(String(255), nullable=False)
    gender = Column(String(50), nullable=False)
    accent = Column(String(100), nullable=False)
    personality = Column(String(255), nullable=False)
    voice_id = Column(String(255), nullable=False)
    voice_model = Column(String(255), nullable=True)
    system_prompt = Column(Text, nullable=False)
    conversation_flow = Column(JSON, nullable=False)

    created_at = Column(
        DateTime(timezone=True), server_default=func.now(), nullable=False
    )
    updated_at = Column(
        DateTime(timezone=True),
        onupdate=func.now(),
        server_default=func.now(),
        nullable=False,
    )
    
    
class SampleAgent(Base):
    __tablename__ = "sample_agents"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    name = Column(String(255), nullable=False)
    gender = Column(String(255), nullable=False)
    about = Column(Text, nullable=False)
    voice_id = Column(String(255))
    webhook_url = Column(String(255), nullable=False)
    language = Column(String(255), nullable=False)
    response_engine = Column(JSON)
    voicemail_option = Column(JSON)
    llmCreate= Column(JSON)
    
class Agent(Base):
    __tablename__ = "agents"

    id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    company_id = Column(Integer, ForeignKey("companies.id"), nullable=False)
    bot_id = Column(String(255), unique=True, nullable=False)  # from VAPI response
    name = Column(String(255), nullable=False)
    voice_id = Column(String(255))
    model = Column(String(255))
    system_prompt = Column(Text)   # enriched prompt you generated
    first_message = Column(Text)
    voicemail_message = Column(Text)
    end_call_message = Column(Text)
    serverUrl = Column(Text)
    raw_response = Column(JSON)    # store full VAPI response

    company = relationship("Company", back_populates="agents")
    
class PhoneNumber(Base):
    __tablename__ = "phone_numbers"

    id = Column(Integer, primary_key=True, index=True)
    agent_id = Column(Integer, nullable=False)
    phone_number = Column(String(20), nullable=False)  # phone numbers are short
    phone_number_type = Column(String(50), nullable=True)
    phone_number_pretty = Column(String(50), nullable=True)
    nickname = Column(String(100), nullable=True)
    inbound_agent_id = Column(String(100), nullable=False)
    inbound_agent_version = Column(String(20), nullable=True)
    area_code = Column(Integer, nullable=True)
    inbound_webhook_url = Column(String(255), nullable=True)  # URLs can be long
    last_modification_timestamp = Column(BigInteger, nullable=True)
    toll_free = Column(Boolean, default=False)
    country_code = Column(String(5), nullable=True)
    custom_sms_enabled = Column(Boolean, default=False)
    
class Booking(Base):
    __tablename__ = "bookings"

    id = Column(Integer, primary_key=True, index=True)
    customer_name = Column(String(255), nullable=False)
    phone = Column(String(50), nullable=False)
    appointment_time = Column(DateTime, nullable=False)
    service = Column(String(255), nullable=True)

    bot_id = Column(String(255), nullable=False)   # comes from Vapi (assistantId)
    company_id = Column(Integer, ForeignKey("companies.id"), nullable=True)

    created_at = Column(DateTime(timezone=True), server_default=func.now())
    updated_at = Column(DateTime(timezone=True), onupdate=func.now())