"""Create and load the Vet Clinic database with SQLModel and Faker.

Author: TODO your name

Answer each question in two or three sentences.

1. What happens if build() does not drop the view? Run the program twice to find out.
   TODO

2. Why can't a CHECK constraint say that vet_id must be an employee whose emp_type is 'vet'?
   TODO
"""

import random
from datetime import date, datetime, time, timedelta
from decimal import Decimal

from faker import Faker
from sqlalchemy import CheckConstraint, UniqueConstraint, text
from sqlmodel import Field, Relationship, Session, SQLModel, create_engine

engine = create_engine("sqlite:///hw4.db", echo=True)

fake = Faker()
Faker.seed(374)
random.seed(374)


class Employee(SQLModel, table=True):
    __table_args__ = (CheckConstraint("emp_type IN ('vet', 'groomer', 'other')"),)

    eid: int | None = Field(default=None, primary_key=True)
    name: str = Field(max_length=45)
    address: str | None = Field(default=None, max_length=128)
    emp_type: str = Field(max_length=12)


class PetOwner(SQLModel, table=True):
    __tablename__ = "pet_owner"

    owner_id: int | None = Field(default=None, primary_key=True)
    name: str = Field(max_length=45)
    address: str | None = Field(default=None, max_length=128)

    animals: list["Animal"] = Relationship(back_populates="owner")


class Animal(SQLModel, table=True):
    pet_id: int | None = Field(default=None, primary_key=True)
    name: str = Field(max_length=45)
    species: str = Field(max_length=45)
    owner_id: int = Field(foreign_key="pet_owner.owner_id")

    owner: PetOwner = Relationship(back_populates="animals")


# TODO Write the Appointment, Service, and ServiceAtAppt classes.
# Add Appointment.performed and ServiceAtAppt.appointment as a Relationship pair.


# Weekday slots on the half hour, 8:00 through 16:30.
SLOTS = [(hour, minute) for hour in range(8, 17) for minute in (0, 30)]

# TODO Define the clinic's fixed data here, as lists or dicts:
# the species to choose from, the menu of services and their fees
# (marking which ones are for grooming), and reasons for each type of
# vet appointment.


def random_time() -> datetime:
    """Return a random weekday half-hour slot in 2026."""
    while True:
        day = date(2026, 1, 1) + timedelta(days=random.randrange(365))
        if day.weekday() < 5:
            return datetime.combine(day, time(*random.choice(SLOTS)))


def make_employees(vets: int, groomers: int, others: int) -> list[Employee]:
    """Return new Employee objects with the given number of each type."""
    types = ["vet"] * vets + ["groomer"] * groomers + ["other"] * others
    return [
        Employee(name=fake.name(), address=fake.address().replace("\n", ", "), emp_type=t)
        for t in types
    ]


def make_services() -> list:
    """Return a new Service object for each item on the clinic's menu."""
    return []


def make_owners(count: int) -> list[PetOwner]:
    """Return new PetOwner objects, each with 1 to 3 animals."""
    return []


def make_appointments(count: int, animals: list, employees: list, services: list) -> list:
    """Return new Appointment objects, each with 1 to 3 services.

    The animals, employees, and services must already have ids.
    """
    return []


def build(engine):
    """Drop and create the tables and the appt_bill view."""
    # TODO Drop the view
    SQLModel.metadata.drop_all(engine)
    SQLModel.metadata.create_all(engine)
    # TODO Create the view


def load(engine):
    """Insert 7 employees, the menu, 20 owners with their animals, and 60 appointments."""
    with Session(engine) as session:
        employees = make_employees(3, 2, 2)
        services = make_services()
        owners = make_owners(20)
        session.add_all(employees + services + owners)
        session.commit()

        # The commit gave every object its id, but it also expired every object.
        # We are still inside the session, so touching an attribute reloads it.
        # Outside the with block, the objects would be detached, and the same
        # line would raise DetachedInstanceError.
        animals = [animal for owner in owners for animal in owner.animals]
        session.add_all(make_appointments(60, animals, employees, services))
        session.commit()


if __name__ == "__main__":
    build(engine)
    load(engine)