-- Migration 001: initial schema for the rental site.
-- MySQL syntax (not MariaDB). InnoDB, utf8mb4 throughout.
-- Tables are created in dependency order so the foreign keys resolve.
-- IF NOT EXISTS keeps this safe to record on a database whose tables were
-- created by pasting this file into phpMyAdmin before the runner tracked it.

-- Homes / units.
CREATE TABLE IF NOT EXISTS properties (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name            VARCHAR(150) NOT NULL,
    address_line1   VARCHAR(200) NOT NULL,
    address_line2   VARCHAR(200) NULL,
    city            VARCHAR(100) NOT NULL,
    state           CHAR(2) NOT NULL,
    zip             VARCHAR(10) NOT NULL,
    property_type   VARCHAR(40) NOT NULL,
    bedrooms        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    bathrooms       DECIMAL(3,1) NOT NULL DEFAULT 0.0,
    square_feet     INT UNSIGNED NULL,
    description     TEXT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_properties_city (city),
    KEY idx_properties_bedrooms (bedrooms),
    KEY idx_properties_type (property_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- What is shown publicly as available; ties to a property.
CREATE TABLE IF NOT EXISTS listings (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'available',
    rent_amount     DECIMAL(10,2) NOT NULL,
    deposit_amount  DECIMAL(10,2) NULL,
    date_available  DATE NULL,
    headline        VARCHAR(200) NULL,
    is_published    TINYINT(1) NOT NULL DEFAULT 0,
    published_at    DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_listings_property (property_id),
    KEY idx_listings_status (status),
    KEY idx_listings_published (is_published),
    KEY idx_listings_rent (rent_amount),
    CONSTRAINT fk_listings_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gallery photos for a property (drives the listing gallery / lightbox).
CREATE TABLE IF NOT EXISTS property_images (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id     INT UNSIGNED NOT NULL,
    file_path       VARCHAR(255) NOT NULL,
    caption         VARCHAR(200) NULL,
    sort_order      SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    is_primary      TINYINT(1) NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_property_images_property (property_id),
    KEY idx_property_images_sort (property_id, sort_order),
    CONSTRAINT fk_property_images_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- People renting; the login identity for the renter portal.
CREATE TABLE IF NOT EXISTS renters (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email           VARCHAR(190) NOT NULL,
    password_hash   VARCHAR(255) NULL,
    first_name      VARCHAR(80) NOT NULL,
    last_name       VARCHAR(80) NOT NULL,
    phone           VARCHAR(30) NULL,
    property_id     INT UNSIGNED NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'active',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_renters_email (email),
    KEY idx_renters_property (property_id),
    KEY idx_renters_status (status),
    CONSTRAINT fk_renters_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prospective-renter submissions from the public application flow.
CREATE TABLE IF NOT EXISTS applications (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    listing_id      INT UNSIGNED NULL,
    property_id     INT UNSIGNED NULL,
    first_name      VARCHAR(80) NOT NULL,
    last_name       VARCHAR(80) NOT NULL,
    email           VARCHAR(190) NOT NULL,
    phone           VARCHAR(30) NULL,
    desired_move_in DATE NULL,
    message         TEXT NULL,
    status          VARCHAR(20) NOT NULL DEFAULT 'new',
    submitted_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_applications_listing (listing_id),
    KEY idx_applications_property (property_id),
    KEY idx_applications_status (status),
    KEY idx_applications_email (email),
    CONSTRAINT fk_applications_listing FOREIGN KEY (listing_id)
        REFERENCES listings (id) ON DELETE SET NULL,
    CONSTRAINT fk_applications_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Submitted maintenance requests with a status lifecycle.
CREATE TABLE IF NOT EXISTS maintenance_tickets (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    renter_id       INT UNSIGNED NOT NULL,
    property_id     INT UNSIGNED NOT NULL,
    title           VARCHAR(150) NOT NULL,
    description     TEXT NOT NULL,
    priority        VARCHAR(20) NOT NULL DEFAULT 'normal',
    status          VARCHAR(20) NOT NULL DEFAULT 'open',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    resolved_at     DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_tickets_renter (renter_id),
    KEY idx_tickets_property (property_id),
    KEY idx_tickets_status (status),
    CONSTRAINT fk_tickets_renter FOREIGN KEY (renter_id)
        REFERENCES renters (id) ON DELETE CASCADE,
    CONSTRAINT fk_tickets_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Rent payment records. Status lifecycle exists from day one because ACH
-- settles over days: submitted -> pending -> cleared (or failed / returned).
-- Only provider tokens/references are stored, never raw card or bank details.
CREATE TABLE IF NOT EXISTS payments (
    id                 INT UNSIGNED NOT NULL AUTO_INCREMENT,
    renter_id          INT UNSIGNED NOT NULL,
    property_id        INT UNSIGNED NULL,
    amount             DECIMAL(10,2) NOT NULL,
    method             VARCHAR(20) NOT NULL DEFAULT 'ach',
    status             VARCHAR(20) NOT NULL DEFAULT 'submitted',
    provider           VARCHAR(30) NULL,
    provider_reference VARCHAR(190) NULL,
    period_start       DATE NULL,
    period_end         DATE NULL,
    submitted_at       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    cleared_at         DATETIME NULL,
    updated_at         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_payments_renter (renter_id),
    KEY idx_payments_property (property_id),
    KEY idx_payments_status (status),
    KEY idx_payments_provider_ref (provider_reference),
    CONSTRAINT fk_payments_renter FOREIGN KEY (renter_id)
        REFERENCES renters (id) ON DELETE CASCADE,
    CONSTRAINT fk_payments_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The single business admin login, kept distinct from renters.
CREATE TABLE IF NOT EXISTS admins (
    id              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    email           VARCHAR(190) NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    full_name       VARCHAR(120) NOT NULL,
    role            VARCHAR(20) NOT NULL DEFAULT 'manager',
    last_login_at   DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_admins_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;