-- Booking plugin — install schema.

CREATE TABLE IF NOT EXISTS `booking_services` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id`      INT UNSIGNED NOT NULL DEFAULT 1,
    `name`           VARCHAR(160) NOT NULL,
    `slug`           VARCHAR(80) NOT NULL,
    `description`    TEXT NULL,
    `duration_min`   INT UNSIGNED NOT NULL DEFAULT 30,
    `buffer_min`     INT UNSIGNED NOT NULL DEFAULT 0,
    `price_cents`    INT UNSIGNED NOT NULL DEFAULT 0,
    `currency`       VARCHAR(8) NOT NULL DEFAULT 'USD',
    `color`          VARCHAR(16) NOT NULL DEFAULT '#2563EB',
    `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`),
    UNIQUE KEY `tenant_slug` (`tenant_id`, `slug`),
    KEY `tenant_active` (`tenant_id`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `booking_providers` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id`      INT UNSIGNED NOT NULL DEFAULT 1,
    `name`           VARCHAR(160) NOT NULL,
    `email`          VARCHAR(200) NULL,
    `timezone`       VARCHAR(60) NOT NULL DEFAULT 'UTC',
    `bio`            TEXT 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 `tenant_active` (`tenant_id`, `is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- A provider performs zero-or-more services. Many-to-many.
CREATE TABLE IF NOT EXISTS `booking_provider_services` (
    `provider_id`    INT UNSIGNED NOT NULL,
    `service_id`     INT UNSIGNED NOT NULL,
    PRIMARY KEY (`provider_id`, `service_id`),
    KEY `service_idx` (`service_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recurring weekly hours: one row per (provider, day_of_week).
-- day_of_week is 0=Sunday … 6=Saturday (matches MySQL's DAYOFWEEK-1).
CREATE TABLE IF NOT EXISTS `booking_provider_hours` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id`      INT UNSIGNED NOT NULL DEFAULT 1,
    `provider_id`    INT UNSIGNED NOT NULL,
    `day_of_week`    TINYINT NOT NULL,
    `start_time`     TIME NOT NULL,
    `end_time`       TIME NOT NULL,
    PRIMARY KEY (`id`),
    KEY `provider_day` (`provider_id`, `day_of_week`),
    KEY `tenant_provider` (`tenant_id`, `provider_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `booking_appointments` (
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tenant_id`      INT UNSIGNED NOT NULL DEFAULT 1,
    `ref`            VARCHAR(32) NOT NULL,
    `service_id`     INT UNSIGNED NOT NULL,
    `provider_id`    INT UNSIGNED NOT NULL,
    `customer_id`    INT UNSIGNED NULL,
    `customer_name`  VARCHAR(160) NOT NULL,
    `customer_email` VARCHAR(200) NOT NULL,
    `customer_phone` VARCHAR(40) NULL,
    `notes`          TEXT NULL,
    `starts_at`      DATETIME NOT NULL,
    `ends_at`        DATETIME NOT NULL,
    `status`         ENUM('confirmed','cancelled','no_show','completed') NOT NULL DEFAULT 'confirmed',
    `reminder_24h_sent` TINYINT(1) NOT NULL DEFAULT 0,
    `reminder_1h_sent`  TINYINT(1) 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`),
    UNIQUE KEY `tenant_ref` (`tenant_id`, `ref`),
    KEY `tenant_status_starts` (`tenant_id`, `status`, `starts_at`),
    KEY `provider_slot` (`provider_id`, `starts_at`, `ends_at`),
    KEY `customer_starts` (`customer_id`, `starts_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
