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

import psycopg
import socket
from faker import Faker
import random

# Note: PGUSER and PGPASSWORD must be set
#       File ~/.pgpass contains lines of format:
#       hostname:port:database:username:password
CONNSTR = "host=localhost dbname=sec1"

fake = Faker()

def create_tables():
    with psycopg.connect(CONNSTR) as conn:
        with 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 NOT NULL PRIMARY KEY,
                            name text NOT NULL,
                            street text NOT NULL,
                            city text NOT NULL,
                            country text NOT NULL,
                            year integer NOT NULL);
                        """)
            cur.execute("""
                        CREATE TABLE hotel_phone (
                            hotel_id integer NOT NULL,
                            sort integer NOT NULL,
                            phone text NOT NULL,
                            label text,
	                        PRIMARY KEY (hotel_id, sort),
	                        FOREIGN KEY (hotel_id) REFERENCES hotel);
                        """)
        conn.commit()

def make_hotels():
    hotels = []
    for id in range(1, 21):
        h = []
        h.append(id)
        h.append(fake.color_name() + " Hotel")
        h.append(fake.street_address())
        h.append(fake.city())
        h.append(fake.country())
        h.append(random.randint(1900, 2024))
        hotels.append(h)
    return hotels


def make_hotel_phones(hotels):
    hotel_phones = []
    for h in hotels:
        # a hotel has between 1 and 4 phone numbers
        phones = random.randint(1, 4)
        # phones+1 because the end of the range is not inclusive
        for i in range(1, phones+1):
            hp = []
            hp.append(h[0])  # hotel id
            hp.append(i)
            hp.append(fake.phone_number())
            # make a random choice from a list
            hp.append(random.choice(["Front Desk", "Reservations", "", "Security"]))
            hotel_phones.append(hp)
    return hotel_phones

def insert_hotels(data):
    with psycopg.connect(CONNSTR) as conn:
        with conn.cursor() as cur:
            for hotel in data:
                cur.execute("INSERT INTO hotel VALUES (%s, %s, %s, %s, %s, %s)", hotel)
        conn.commit()

def insert_hotel_phones(data):
    with psycopg.connect(CONNSTR) as conn:
        with conn.cursor() as cur:
            for phone in data:
                cur.execute("INSERT INTO hotel_phone VALUES (%s, %s, %s, %s)", phone)
        conn.commit()

if __name__ == "__main__":
    create_tables()
    hotels = make_hotels()
    hotel_phones = make_hotel_phones(hotels)
    insert_hotels(hotels)
    insert_hotel_phones(hotel_phones)
    print(hotels)
    print(hotel_phones)