-- Migration 010: property inspections (move-in, move-out, routine).
-- An inspection has line items (area, condition, notes) and photos. Drafts are
-- editable in the admin; a completed inspection is the record, shown read-only
-- to the renter in the portal. "condition_rating" avoids MySQL's reserved word
-- CONDITION. MySQL syntax (not MariaDB).

CREATE TABLE inspections (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id   INT UNSIGNED NOT NULL,
    room_id       INT UNSIGNED NULL,
    renter_id     INT UNSIGNED NULL,
    type          VARCHAR(20) NOT NULL DEFAULT 'routine',
    status        VARCHAR(20) NOT NULL DEFAULT 'draft',
    inspected_at  DATE NULL,
    inspector     VARCHAR(120) NULL,
    summary       TEXT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at  DATETIME NULL,
    PRIMARY KEY (id),
    KEY idx_inspections_property (property_id),
    KEY idx_inspections_renter (renter_id),
    KEY idx_inspections_status (status),
    KEY idx_inspections_type (type),
    CONSTRAINT fk_inspections_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE,
    CONSTRAINT fk_inspections_room FOREIGN KEY (room_id)
        REFERENCES rooms (id) ON DELETE SET NULL,
    CONSTRAINT fk_inspections_renter FOREIGN KEY (renter_id)
        REFERENCES renters (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE inspection_items (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    inspection_id    INT UNSIGNED NOT NULL,
    area             VARCHAR(120) NOT NULL,
    condition_rating VARCHAR(20) NOT NULL DEFAULT 'good',
    notes            VARCHAR(500) NULL,
    sort_order       SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_inspection_items_inspection (inspection_id, sort_order),
    CONSTRAINT fk_inspection_items_inspection FOREIGN KEY (inspection_id)
        REFERENCES inspections (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE inspection_photos (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    inspection_id INT UNSIGNED NOT NULL,
    file_path     VARCHAR(255) NOT NULL,
    caption       VARCHAR(200) NULL,
    sort_order    SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_inspection_photos_inspection (inspection_id, sort_order),
    CONSTRAINT fk_inspection_photos_inspection FOREIGN KEY (inspection_id)
        REFERENCES inspections (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;