-- One-off migration: adds the trial_lesson_requests table, a staging area
-- for "trial lesson" bookings submitted by an external webhook (e.g. the
-- marketing site's booking form). An admin reviews each one on
-- /admin/leads.php and hands it off to a specific teacher (a simple
-- assignment record here, not a scheduled lesson -- actually booking the
-- lesson stays a manual follow-up by the assigned teacher/admin). Run once
-- against the app database:
--
--   mysql -h <host> -u <user> -p <database> < scripts/create_trial_lesson_requests_table.sql
--
-- (or paste the statement below into phpMyAdmin / Adminer / your MySQL
-- client of choice). No existing tables are modified.

CREATE TABLE IF NOT EXISTS trial_lesson_requests (
    id                     INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name             VARCHAR(255) NOT NULL,
    last_name              VARCHAR(255) NOT NULL,
    phone                  VARCHAR(50) NULL,
    subject                VARCHAR(255) NULL,
    preferred_date         DATE NULL,
    preferred_time         VARCHAR(100) NULL,
    source                 VARCHAR(100) NULL,
    raw_payload            TEXT NULL,
    status                 ENUM('new', 'assigned', 'dismissed') NOT NULL DEFAULT 'new',
    assigned_teacher_id    BIGINT UNSIGNED NULL,
    assigned_by            BIGINT UNSIGNED NULL,
    assigned_at            DATETIME NULL,
    created_at             DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_trial_lesson_requests_status (status),
    CONSTRAINT fk_trial_lesson_requests_teacher FOREIGN KEY (assigned_teacher_id) REFERENCES users(id),
    CONSTRAINT fk_trial_lesson_requests_assigned_by FOREIGN KEY (assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
