-- ============================================================
-- Student portal credentials
-- ============================================================
-- Adds auth columns to the existing `students` table so the
-- Student Portal can authenticate against the same MySQL DB
-- used by the Admin Panel (no separate accounts table).
--
-- Username  = students.uid    (e.g. PMSS-STU-10001)
-- Password  = bcrypt hash stored in students.password
-- Token     = opaque bearer token issued on login
-- Default initial password (for first roll-out) = the student's UID,
-- which the student MUST change on first login.
-- ============================================================

ALTER TABLE students
    ADD COLUMN IF NOT EXISTS password       VARCHAR(255) NULL AFTER uid,
    ADD COLUMN IF NOT EXISTS token          VARCHAR(128) NULL,
    ADD COLUMN IF NOT EXISTS last_activity  TIMESTAMP NULL DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS must_change_password TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS is_active      TINYINT(1) NOT NULL DEFAULT 1;

CREATE INDEX IF NOT EXISTS idx_students_token ON students(token);

-- ----- Backfill default passwords for existing students -----
-- Default password = the student's UID. Bcrypt cost 10.
-- Only fills rows where password IS NULL so re-running is safe.
-- NOTE: bcrypt hashing must be done in PHP, not in SQL. Run the
-- companion script `api/migrations/seed_student_passwords.php`
-- ONCE after this migration to populate hashes.