-- Migration 003: rooms-for-rent support, alongside whole-home rentals.
-- MySQL syntax (not MariaDB). A property is either rented whole ('home') or as
-- individual rooms ('rooms'). A listing can point at the whole property
-- (room_id NULL) or at one room. A renter can be linked to one room.

ALTER TABLE properties
    ADD COLUMN rental_type VARCHAR(10) NOT NULL DEFAULT 'home' AFTER property_type;

CREATE TABLE rooms (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id INT UNSIGNED NOT NULL,
    name        VARCHAR(120) NOT NULL,
    description TEXT NULL,
    sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_rooms_property (property_id),
    CONSTRAINT fk_rooms_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE listings
    ADD COLUMN room_id INT UNSIGNED NULL AFTER property_id,
    ADD KEY idx_listings_room (room_id),
    ADD CONSTRAINT fk_listings_room FOREIGN KEY (room_id)
        REFERENCES rooms (id) ON DELETE CASCADE;

ALTER TABLE renters
    ADD COLUMN room_id INT UNSIGNED NULL AFTER property_id,
    ADD KEY idx_renters_room (room_id),
    ADD CONSTRAINT fk_renters_room FOREIGN KEY (room_id)
        REFERENCES rooms (id) ON DELETE SET NULL;