"""Create and load the hotel DB with fake data."""

import random
from pprint import pprint

import psycopg
from faker import Faker

# No password here: psycopg finds it in your ~/.pgpass file.
CONNSTR = "host=localhost dbname=sec1 user=demo"

# Leaving a "with psycopg.connect()" block commits the transaction and closes
# the connection. If an exception is raised inside the block, it rolls back.

fake = Faker()

# Seeding makes the "random" data the same every run, which makes bugs repeatable.
Faker.seed(374)
random.seed(374)


def create_tables() -> None:
    with psycopg.connect(CONNSTR) as conn, conn.cursor() as cur:
        cur.execute("DROP TABLE IF EXISTS hotel_phone")
        cur.execute("DROP TABLE IF EXISTS hotel")
        cur.execute("""
            CREATE TABLE hotel (
                hotel_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
                name text NOT NULL,
                street text NOT NULL,
                city text NOT NULL,
                country text NOT NULL,
                year integer NOT NULL CHECK (year BETWEEN 1800 AND 2026)
            )
        """)
        cur.execute("""
            CREATE TABLE hotel_phone (
                hotel_id integer NOT NULL REFERENCES hotel,
                sort integer NOT NULL CHECK (sort > 0),
                phone text NOT NULL,
                label text,
                PRIMARY KEY (hotel_id, sort)
            )
        """)


def make_hotels(count: int) -> list[tuple]:
    """Return rows for the hotel table, without the generated hotel_id."""
    hotels = []
    for _ in range(count):
        name = fake.last_name() + " " + random.choice(["Hotel", "Inn", "Suites"])
        year = fake.random_int(1900, 2025)
        hotels.append((name, fake.street_address(), fake.city(), fake.country(), year))
    return hotels


def insert_hotels(hotels: list[tuple]) -> list[int]:
    """Insert the hotels, and return the ids that the database generated."""
    ids = []
    with psycopg.connect(CONNSTR) as conn, conn.cursor() as cur:
        for row in hotels:
            cur.execute(
                "INSERT INTO hotel (name, street, city, country, year) "
                "VALUES (%s, %s, %s, %s, %s) RETURNING hotel_id",
                row,
            )
            ids.append(cur.fetchone()[0])
    return ids


def make_hotel_phones(hotel_ids: list[int]) -> list[tuple]:
    """Return rows for the hotel_phone table, 1 to 4 phones per hotel."""
    hotel_phones = []
    for hotel_id in hotel_ids:
        for sort in range(1, random.randint(1, 4) + 1):
            label = random.choice([None, "Front Desk", "Reservations", "Security"])
            hotel_phones.append((hotel_id, sort, fake.phone_number(), label))
    return hotel_phones


def insert_hotel_phones(hotel_phones: list[tuple]) -> None:
    with psycopg.connect(CONNSTR) as conn, conn.cursor() as cur:
        cur.executemany("INSERT INTO hotel_phone VALUES (%s, %s, %s, %s)", hotel_phones)


if __name__ == "__main__":
    create_tables()
    hotels = make_hotels(20)
    pprint(hotels, width=100)
    hotel_ids = insert_hotels(hotels)
    hotel_phones = make_hotel_phones(hotel_ids)
    pprint(hotel_phones, width=100)
    insert_hotel_phones(hotel_phones)