-- One-off migration: adds the new_student_requests table, a staging area for
-- "new student" leads submitted by an external webhook (e.g. the marketing
-- site's signup form). Deliberately NOT inserted straight into `students` --
-- these rows only carry a name/phone, no IIN/class/tariff/login, so an admin
-- reviews each one on /admin/leads.php and hands it off to a specific
-- teacher (a simple assignment record here, not group enrollment -- actually
-- enrolling the student stays a manual follow-up via the normal
-- add-to-group flow). Run once against the app database:
--
--   mysql -h <host> -u <user> -p <database> < scripts/create_new_student_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 new_student_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,
    goal                   VARCHAR(255) 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_new_student_requests_status (status),
    CONSTRAINT fk_new_student_requests_teacher FOREIGN KEY (assigned_teacher_id) REFERENCES users(id),
    CONSTRAINT fk_new_student_requests_assigned_by FOREIGN KEY (assigned_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
