DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS properties;
DROP TABLE IF EXISTS bookings;
DROP TABLE IF EXISTS reviews;

CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
password TEXT NOT NULL,
phone_number TEXT,
created_at TEXT,
updated_at TEXT
);

CREATE TABLE properties (
id INTEGER PRIMARY KEY,
user_id INTEGER,
name TEXT NOT NULL,
address TEXT NOT NULL,
city TEXT NOT NULL,
state TEXT NOT NULL,
zip_code TEXT NOT NULL,
price_per_night REAL NOT NULL,
number_of_bedrooms INTEGER,
number_of_bathrooms INTEGER,
description TEXT,
created_at TEXT,
updated_at TEXT,
FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE bookings (
id INTEGER PRIMARY KEY,
user_id INTEGER,
property_id INTEGER,
start_date TEXT NOT NULL,
end_date TEXT NOT NULL,
total_price REAL NOT NULL,
status TEXT NOT NULL,
created_at TEXT,
updated_at TEXT,
FOREIGN KEY(user_id) REFERENCES users(id),
FOREIGN KEY(property_id) REFERENCES properties(id)
);

CREATE TABLE reviews (
id INTEGER PRIMARY KEY,
booking_id INTEGER,
user_id INTEGER,
rating INTEGER NOT NULL,
comment TEXT,
created_at TEXT,
updated_at TEXT,
FOREIGN KEY(booking_id) REFERENCES bookings(id),
FOREIGN KEY(user_id) REFERENCES users(id)
);

INSERT INTO users (name, email, password, phone_number, created_at, updated_at)
VALUES
("John Doe", "john@doe.com", "password123", "123-456-7890", "2025-01-01", "2025-01-01"),
("Jane Smith", "jane@smith.com", "password456", "098-765-4321", "2025-01-02", "2025-01-02");

INSERT INTO properties (user_id, name, address, city, state, zip_code, price_per_night, number_of_bedrooms, number_of_bathrooms, description, created_at, updated_at)
VALUES
(1, "Beach House", "123 Ocean Blvd", "Miami", "FL", "33101", 250, 3, 2, "A beautiful beach house near the ocean.", "2025-01-01", "2025-01-01"),
(2, "Mountain Cabin", "456 Mountain Rd", "Denver", "CO", "80201", 180, 2, 1, "A cozy cabin in the mountains with amazing views.", "2025-01-02", "2025-01-02");

INSERT INTO bookings (user_id, property_id, start_date, end_date, total_price, status, created_at, updated_at)
VALUES
(1, 1, "2025-02-01", "2025-02-05", 1000, "Confirmed", "2025-01-05", "2025-01-05"),
(2, 2, "2025-03-01", "2025-03-07", 1260, "Pending", "2025-01-06", "2025-01-06");

INSERT INTO reviews (booking_id, user_id, rating, comment, created_at, updated_at)
VALUES
(1, 1, 5, "Fantastic stay! The beach house was perfect.", "2025-02-06", "2025-02-06"),
(2, 2, 4, "Great cabin, but could use a bigger bathroom.", "2025-03-08", "2025-03-08");