START TRANSACTION;

CREATE TABLE IF NOT EXISTS listings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    zpid VARCHAR(20) NOT NULL,
    address VARCHAR(255) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(2) NOT NULL,
    zipcode VARCHAR(10) NOT NULL,
    latitude FLOAT NOT NULL,
    longitude FLOAT NOT NULL,
    price DECIMAL(10, 2),
    date_listed DATE,
    status VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS zestimates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    zestimate DECIMAL(10, 2),
    rent_zestimate DECIMAL(10, 2),
    last_updated DATE,
    FOREIGN KEY (listing_id) REFERENCES listings(id)
);

CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    sale_price DECIMAL(10, 2),
    sale_date DATE,
    FOREIGN KEY (listing_id) REFERENCES listings(id)
);

CREATE TABLE IF NOT EXISTS public_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    listing_id INT NOT NULL,
    built_year INT,
    lot_size INT,
    square_feet INT,
    bathrooms FLOAT,
    bedrooms INT,
    FOREIGN KEY (listing_id) REFERENCES listings(id)
);

CREATE TABLE IF NOT EXISTS mortgage_rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rate FLOAT,
    type VARCHAR(50),
    date DATE
);

CREATE TABLE IF NOT EXISTS lender_reviews (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lender_id INT,
    name VARCHAR(255),
    review TEXT,
    rating FLOAT,
    date DATE
);

COMMIT;
