-- Migration 031: appointment_slots table for the booking scheduler.
-- Slots are created by tenants; booked by external visitors via /schedule/{slug}.

CREATE TABLE IF NOT EXISTS appointment_slots (
    id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id          BIGINT UNSIGNED NOT NULL,
    host_user_id    BIGINT UNSIGNED NOT NULL,
    starts_at       DATETIME        NOT NULL,
    duration_mins   SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    status          ENUM('available','booked','cancelled') NOT NULL DEFAULT 'available',
    booked_by_name  VARCHAR(120)    NULL,
    booked_by_email VARCHAR(254)    NULL,
    notes           TEXT            NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    INDEX idx_apt_org_starts  (org_id, starts_at),
    INDEX idx_apt_host_status (host_user_id, status),
    CONSTRAINT fk_apt_org  FOREIGN KEY (org_id)       REFERENCES organizations(id) ON DELETE CASCADE,
    CONSTRAINT fk_apt_user FOREIGN KEY (host_user_id) REFERENCES users(id)         ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
