-- Migration 009: vendor contacts and ticket assignment.
-- Vendors are the business's own contractors (plumber, electrician, handyman).
-- They have no login; the admin assigns one to a maintenance ticket along with
-- a scheduled visit, and the renter sees who is coming and when. Phone/email
-- are stored so a later enhancement can notify vendors automatically.
-- MySQL syntax (not MariaDB).

CREATE TABLE vendors (
    id          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name        VARCHAR(120) NOT NULL,
    trade       VARCHAR(60) NULL,
    phone       VARCHAR(30) NULL,
    email       VARCHAR(190) NULL,
    notes       VARCHAR(500) 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_vendors_active (is_active),
    KEY idx_vendors_trade (trade)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE maintenance_tickets
    ADD COLUMN vendor_id INT UNSIGNED NULL AFTER priority,
    ADD COLUMN scheduled_for DATETIME NULL AFTER vendor_id,
    ADD KEY idx_tickets_vendor (vendor_id),
    ADD KEY idx_tickets_scheduled (scheduled_for),
    ADD CONSTRAINT fk_tickets_vendor FOREIGN KEY (vendor_id)
        REFERENCES vendors (id) ON DELETE SET NULL;