-- Migration 014: July 2026 feature expansion.
-- MySQL syntax (not MariaDB). Adds: room fees and deposits, renter lease
-- fields, maintenance ticket type + photos (renter and owner sides), routine
-- maintenance plans (reminders, not an engine), property walkthrough videos,
-- per-property emergency procedures, insurance policy tracking, admin
-- notifications (bell + alert modal + page), applicant screening fields,
-- billing schedules for approved renters, and a small key-value settings
-- table for integration links. Every WHERE/JOIN column is indexed.

-- Rooms: recurring monthly fees and split deposits.
ALTER TABLE rooms
    ADD COLUMN cleaning_fee      DECIMAL(10,2) NULL AFTER square_feet,
    ADD COLUMN utilities_fee     DECIMAL(10,2) NULL AFTER cleaning_fee,
    ADD COLUMN room_deposit      DECIMAL(10,2) NULL AFTER utilities_fee,
    ADD COLUMN utilities_deposit DECIMAL(10,2) NULL AFTER room_deposit;

-- Renters: lease window and free-form lease details.
ALTER TABLE renters
    ADD COLUMN lease_start DATE NULL AFTER status,
    ADD COLUMN lease_end   DATE NULL AFTER lease_start,
    ADD COLUMN lease_notes TEXT NULL AFTER lease_end,
    ADD KEY idx_renters_lease_end (lease_end);

-- Maintenance tickets: a work type alongside priority.
ALTER TABLE maintenance_tickets
    ADD COLUMN type VARCHAR(30) NOT NULL DEFAULT 'general' AFTER title,
    ADD KEY idx_tickets_type (type);

-- Photos on a maintenance ticket. uploaded_by records which side added the
-- photo ('renter' or 'owner'); an owner photo can be hidden from the renter.
CREATE TABLE ticket_photos (
    id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
    ticket_id         INT UNSIGNED NOT NULL,
    file_path         VARCHAR(255) NOT NULL,
    caption           VARCHAR(200) NULL,
    uploaded_by       VARCHAR(10) NOT NULL DEFAULT 'renter',
    visible_to_renter TINYINT(1) NOT NULL DEFAULT 1,
    sort_order        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_ticket_photos_ticket (ticket_id, sort_order),
    KEY idx_ticket_photos_visible (ticket_id, visible_to_renter),
    CONSTRAINT fk_ticket_photos_ticket FOREIGN KEY (ticket_id)
        REFERENCES maintenance_tickets (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Routine maintenance plans: repeat every N months (3/6/9/12 or custom).
-- These are reminders; the owner reviews what is due and creates the ticket
-- with one click. Nothing runs unattended.
CREATE TABLE maintenance_plans (
    id               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id      INT UNSIGNED NOT NULL,
    room_id          INT UNSIGNED NULL,
    vendor_id        INT UNSIGNED NULL,
    title            VARCHAR(150) NOT NULL,
    description      TEXT NULL,
    type             VARCHAR(30) NOT NULL DEFAULT 'general',
    frequency_months TINYINT UNSIGNED NOT NULL DEFAULT 6,
    next_due         DATE NOT NULL,
    last_completed   DATE NULL,
    is_active        TINYINT(1) NOT NULL DEFAULT 1,
    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_maintenance_plans_property (property_id),
    KEY idx_maintenance_plans_due (is_active, next_due),
    KEY idx_maintenance_plans_vendor (vendor_id),
    KEY idx_maintenance_plans_room (room_id),
    CONSTRAINT fk_maintenance_plans_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE,
    CONSTRAINT fk_maintenance_plans_room FOREIGN KEY (room_id)
        REFERENCES rooms (id) ON DELETE SET NULL,
    CONSTRAINT fk_maintenance_plans_vendor FOREIGN KEY (vendor_id)
        REFERENCES vendors (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Property walkthrough videos (hosted URL: YouTube, Vimeo, or a direct file).
CREATE TABLE property_videos (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id INT UNSIGNED NOT NULL,
    room_id     INT UNSIGNED NULL,
    title       VARCHAR(150) NULL,
    video_url   VARCHAR(500) NOT NULL,
    sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_property_videos_property (property_id, sort_order),
    KEY idx_property_videos_room (room_id),
    CONSTRAINT fk_property_videos_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE,
    CONSTRAINT fk_property_videos_room FOREIGN KEY (room_id)
        REFERENCES rooms (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-property emergency procedures, edited in the admin and shown to the
-- renters of that property (whole home or any room in it).
ALTER TABLE properties
    ADD COLUMN emergency_procedures TEXT NULL AFTER description;

-- Insurance tracking per property. Old policies stay as history rows.
CREATE TABLE insurance_policies (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    property_id   INT UNSIGNED NOT NULL,
    company       VARCHAR(160) NOT NULL,
    policy_number VARCHAR(100) NULL,
    phone         VARCHAR(30) NULL,
    email         VARCHAR(190) NULL,
    coverage_type VARCHAR(60) NULL,
    premium       DECIMAL(10,2) NULL,
    start_date    DATE NULL,
    end_date      DATE NULL,
    status        VARCHAR(20) NOT NULL DEFAULT 'active',
    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_insurance_property (property_id),
    KEY idx_insurance_status (status),
    KEY idx_insurance_end (end_date),
    CONSTRAINT fk_insurance_property FOREIGN KEY (property_id)
        REFERENCES properties (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Admin notifications: the bell counter, the alert modal, and the
-- notifications page all read this one table. dedupe_key stops repeat
-- reminders (e.g. the same policy expiring) from inserting twice.
CREATE TABLE notifications (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    type       VARCHAR(40) NOT NULL DEFAULT 'general',
    title      VARCHAR(200) NOT NULL,
    body       VARCHAR(600) NULL,
    link       VARCHAR(255) NULL,
    dedupe_key VARCHAR(120) NULL,
    is_alert   TINYINT(1) NOT NULL DEFAULT 0,
    is_read    TINYINT(1) NOT NULL DEFAULT 0,
    alerted_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_notifications_dedupe (dedupe_key),
    KEY idx_notifications_read (is_read, created_at),
    KEY idx_notifications_alert (is_alert, alerted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Applicant screening progress and the generated lease link.
ALTER TABLE applications
    ADD COLUMN screening_background     VARCHAR(20) NULL AFTER status,
    ADD COLUMN screening_background_ref VARCHAR(190) NULL AFTER screening_background,
    ADD COLUMN screening_credit         VARCHAR(20) NULL AFTER screening_background_ref,
    ADD COLUMN screening_credit_ref     VARCHAR(190) NULL AFTER screening_credit,
    ADD COLUMN lease_url                VARCHAR(500) NULL AFTER screening_credit_ref;

-- Billing schedules: invoice an approved renter automatically on a set day
-- of the month. The run creates provider invoices through the payments
-- chokepoint and records pending payments; references only, never card data.
CREATE TABLE billing_schedules (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    renter_id     INT UNSIGNED NOT NULL,
    amount        DECIMAL(10,2) NOT NULL,
    day_of_month  TINYINT UNSIGNED NOT NULL DEFAULT 1,
    start_date    DATE NOT NULL,
    end_date      DATE NULL,
    is_active     TINYINT(1) NOT NULL DEFAULT 1,
    last_run_ym   CHAR(7) NULL,
    notes         VARCHAR(300) 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_billing_renter (renter_id),
    KEY idx_billing_active (is_active, day_of_month),
    CONSTRAINT fk_billing_renter FOREIGN KEY (renter_id)
        REFERENCES renters (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Small key-value store for owner-editable settings (integration links for
-- background checks, credit checks, people search, and the lease template).
CREATE TABLE app_settings (
    id            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    setting_key   VARCHAR(80) NOT NULL,
    setting_value TEXT NULL,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_app_settings_key (setting_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
