CREATE TABLE syllabus_categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(150) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE syllabi (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    programme_name VARCHAR(255) NOT NULL,
    semester VARCHAR(100) DEFAULT NULL,
    academic_session VARCHAR(50) DEFAULT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_syllabus_category
        FOREIGN KEY (category_id) REFERENCES syllabus_categories(id)
        ON UPDATE CASCADE ON DELETE RESTRICT,
    INDEX idx_category (category_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO syllabus_categories (category_name, sort_order) VALUES
('UG Syllabus (w.e.f. 2024-25)', 1),
('PG Syllabus (w.e.f. 2024-25)', 2),
('Ph.D Syllabus', 3),
('Integrated syllabus w.e.f. 2024-25', 4),
('UG Pool', 5),
('PG Pool', 6),
('Integrated Pool', 7),
('Multiskill Courses', 8),
('SEC Pool', 9);