-- Migration: enrollment_requests table + MES sequence
-- School enrollment form submissions (replacing Jotform "Enroll My School")

CREATE TABLE IF NOT EXISTS enrollment_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    enrollment_number VARCHAR(20) UNIQUE NOT NULL,
    status ENUM('new','contacted','approved','rejected') DEFAULT 'new',

    -- School info (from NCES typeahead or manual entry)
    nces_school_id VARCHAR(20),
    school_name VARCHAR(500) NOT NULL,
    district_name VARCHAR(500),
    school_city VARCHAR(255),
    school_state VARCHAR(10),
    school_zip VARCHAR(20),
    school_type VARCHAR(100),
    enrollment INT,
    is_existing_customer TINYINT(1) DEFAULT 0,
    org_id INT,

    -- Contact info
    contact_name VARCHAR(255) NOT NULL,
    contact_email VARCHAR(255) NOT NULL,
    contact_title VARCHAR(255),

    -- Territory routing
    territory VARCHAR(50),
    assigned_salesperson VARCHAR(255),
    assigned_salesperson_email VARCHAR(255),

    -- Source tracking
    source VARCHAR(50) DEFAULT 'web',  -- 'web' or 'jotform_import'
    jotform_uid VARCHAR(20),           -- Jotform Unique ID (#0460 etc)

    -- Audit
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    submitted_ip VARCHAR(45),
    user_agent TEXT,

    INDEX idx_status (status),
    INDEX idx_school_state (school_state),
    INDEX idx_submitted_at (submitted_at),
    INDEX idx_contact_email (contact_email),
    INDEX idx_jotform_uid (jotform_uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Sequence for enrollment numbers (MES prefix)
INSERT IGNORE INTO form_sequences (prefix, next_number) VALUES ('MES', 100001);