-- Migration 0005: create_scheduling
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 4 — SCHEDULING
-- =====================================================================

CREATE TABLE IF NOT EXISTS service_types (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    name              VARCHAR(120)    NOT NULL,
    default_duration_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 60,
    default_price     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency          CHAR(3)         NOT NULL DEFAULT 'CAD',
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    KEY idx_service_types_business_line (business_line_id),
    CONSTRAINT fk_service_types_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS appointments (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    customer_id       BIGINT UNSIGNED NULL,
    lead_id           BIGINT UNSIGNED NULL COMMENT 'Pre-sale assessments booked before conversion',
    service_type_id   BIGINT UNSIGNED NULL,
    assigned_staff_id BIGINT UNSIGNED NULL,
    scheduled_start   DATETIME        NOT NULL,
    scheduled_end     DATETIME        NOT NULL,
    status            ENUM('scheduled','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
    location_address  VARCHAR(255)    NULL,
    notes             TEXT            NULL,
    cancelled_reason  VARCHAR(255)    NULL,
    created_by        BIGINT UNSIGNED NULL,
    updated_by        BIGINT UNSIGNED NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at        DATETIME        NULL,
    KEY idx_appointments_business_line (business_line_id),
    KEY idx_appointments_customer (customer_id),
    KEY idx_appointments_lead (lead_id),
    KEY idx_appointments_staff_time (assigned_staff_id, scheduled_start),
    KEY idx_appointments_status (status),
    KEY idx_appointments_deleted_at (deleted_at),
    CONSTRAINT fk_appointments_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_service_type FOREIGN KEY (service_type_id) REFERENCES service_types(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_staff FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT chk_appointments_time_order CHECK (scheduled_end > scheduled_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
