-- =============================================================================
-- MIGRATION 001: Create courses table
-- PURPOSE: Supports multiple simultaneous courses.
--          moodle_course_id is nullable until Moodle integration is built.
-- =============================================================================

CREATE TABLE IF NOT EXISTS public.courses (
    course_id        SERIAL PRIMARY KEY,
    name             VARCHAR(100) NOT NULL,
    moodle_course_id INT,                          -- MOODLE_SYNC: populated on Moodle integration
    language         VARCHAR(5)  NOT NULL DEFAULT 'es',
    active           BOOLEAN     NOT NULL DEFAULT true,
    created_at       valid_timestamp DEFAULT CURRENT_TIMESTAMP
);

-- Default course: single course until Moodle integration
INSERT INTO courses (name, moodle_course_id, language, active)
VALUES ('STC Core', NULL, 'es', true);

-- Grants
GRANT SELECT ON public.courses TO "elfi1953_stc-eu";
GRANT USAGE, SELECT ON SEQUENCE public.courses_course_id_seq TO "elfi1953_stc-eu";