-- Migration 016: property systems and components.
-- MySQL syntax (not MariaDB). InnoDB, utf8mb4. Tracks major building systems
-- (roof, AC, water heater, appliances, etc.) with install, warranty and
-- service dates, so the owner can see a component's age before a maintenance
-- request ever comes in. The category column mirrors the maintenance ticket
-- types, so a request of a given type surfaces the matching systems on that
-- property. IF NOT EXISTS keeps this safe to re-run.

CREATE TABLE IF NOT EXISTS property_components (
    id                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id           INT UNSIGNED NOT NULL,
    category              VARCHAR(30) NOT NULL DEFAULT 'general',
    name                  VARCHAR(150) NOT NULL,
    brand                 VARCHAR(120) NULL,
    model                 VARCHAR(120) NULL,
    serial_number         VARCHAR(120) NULL,
    location              VARCHAR(150) NULL,
    installed_on          DATE NULL,
    warranty_until        DATE NULL,
    life_expectancy_years TINYINT UNSIGNED NULL,
    last_serviced         DATE NULL,
    notes                 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_property_components_property (property_id),
    KEY idx_property_components_category (property_id, category),
    CONSTRAINT fk_property_components_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;