-- Migration 015: ticket-photo trash, room features, structured emergency
-- procedures. MySQL syntax (not MariaDB).

-- Ticket photos become trashable (restore from /admin/trash) instead of
-- hard-deleted.
ALTER TABLE ticket_photos
    ADD COLUMN deleted_at DATETIME NULL,
    ADD KEY idx_ticket_photos_deleted (deleted_at);

-- Room features a room renter cares about. shared_bath is explicit (a room
-- can be listed before the bath situation is decided, so neither box checked
-- means unspecified).
ALTER TABLE rooms
    ADD COLUMN shared_bath      TINYINT(1) NOT NULL DEFAULT 0 AFTER private_bath,
    ADD COLUMN private_entrance TINYINT(1) NOT NULL DEFAULT 0 AFTER furnished,
    ADD COLUMN kitchen_access   TINYINT(1) NOT NULL DEFAULT 0 AFTER private_entrance,
    ADD COLUMN parking_included TINYINT(1) NOT NULL DEFAULT 0 AFTER kitchen_access,
    ADD COLUMN pets_allowed     TINYINT(1) NOT NULL DEFAULT 0 AFTER parking_included,
    ADD COLUMN wifi_included    TINYINT(1) NOT NULL DEFAULT 0 AFTER pets_allowed;

-- Emergency procedures become structured rows (title + details + photos)
-- instead of one property-level textarea. Each row can carry photos (where
-- the shut-off valve is, what the breaker panel looks like).
CREATE TABLE emergency_procedures (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id INT UNSIGNED NOT NULL,
    title       VARCHAR(150) NOT NULL,
    body        TEXT NULL,
    sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    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_emergency_property (property_id, sort_order),
    CONSTRAINT fk_emergency_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE emergency_photos (
    id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    procedure_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_emergency_photos_procedure (procedure_id, sort_order),
    CONSTRAINT fk_emergency_photos_procedure FOREIGN KEY (procedure_id)
        REFERENCES emergency_procedures (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Carry any text written in the old per-property textarea (migration 014)
-- into the new table as a first row, then retire the column.
INSERT INTO emergency_procedures (property_id, title, body, sort_order)
SELECT id, 'Emergency procedures', emergency_procedures, 0
  FROM properties
 WHERE emergency_procedures IS NOT NULL AND emergency_procedures != '';

ALTER TABLE properties DROP COLUMN emergency_procedures;