CREATE DATABASE IF NOT EXISTS alumni_portal_prmsu CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE alumni_portal_prmsu;

SET NAMES utf8mb4;
SET time_zone = '+08:00';

DROP TABLE IF EXISTS survey_answers;
DROP TABLE IF EXISTS employment_proofs;
DROP TABLE IF EXISTS graduate_feedback;
DROP TABLE IF EXISTS employment_records;
DROP TABLE IF EXISTS survey_response_sets;
DROP TABLE IF EXISTS tracer_campaigns;
DROP TABLE IF EXISTS survey_questions;
DROP TABLE IF EXISTS survey_sections;
DROP TABLE IF EXISTS survey_instrument_versions;
DROP TABLE IF EXISTS survey_instruments;
DROP TABLE IF EXISTS graduate_profiles;
DROP TABLE IF EXISTS homepage_slides;
DROP TABLE IF EXISTS graduate_updates;
DROP TABLE IF EXISTS testimonials;
DROP TABLE IF EXISTS news_feeds;
DROP TABLE IF EXISTS site_settings;
DROP TABLE IF EXISTS programs;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE programs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    program_code VARCHAR(30) NOT NULL UNIQUE,
    program_name VARCHAR(150) NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE graduate_profiles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    student_number VARCHAR(50) NULL,
    middle_name VARCHAR(100) NULL,
    suffix VARCHAR(20) NULL,
    sex ENUM('male','female','prefer_not_to_say') NULL,
    birth_date DATE NULL,
    civil_status VARCHAR(50) NULL,
    contact_number VARCHAR(30) NULL,
    province VARCHAR(120) NULL,
    city_municipality VARCHAR(120) NULL,
    barangay VARCHAR(120) NULL,
    street_address VARCHAR(255) NULL,
    graduation_year YEAR NULL,
    program_id BIGINT UNSIGNED NULL,
    profile_photo VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_graduate_profiles_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_graduate_profiles_program FOREIGN KEY (program_id) REFERENCES programs(id)
        ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE survey_instruments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    instrument_name VARCHAR(150) NOT NULL UNIQUE,
    description TEXT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE survey_instrument_versions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    instrument_id BIGINT UNSIGNED NOT NULL,
    version_code VARCHAR(60) NOT NULL,
    version_title VARCHAR(150) NOT NULL,
    version_notes TEXT NULL,
    status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
    is_current TINYINT(1) NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_instrument_version_code (instrument_id, version_code),
    CONSTRAINT fk_survey_instrument_versions_instrument FOREIGN KEY (instrument_id) REFERENCES survey_instruments(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE tracer_campaigns (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_title VARCHAR(180) NOT NULL,
    description TEXT NULL,
    target_graduation_year YEAR NOT NULL,
    instrument_version_id BIGINT UNSIGNED NOT NULL,
    open_date DATE NULL,
    close_date DATE NULL,
    status ENUM('draft','active','closed','inactive') NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_tracer_campaigns_version FOREIGN KEY (instrument_version_id) REFERENCES survey_instrument_versions(id)
        ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE survey_sections (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    instrument_version_id BIGINT UNSIGNED NOT NULL,
    section_title VARCHAR(150) NOT NULL,
    description VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 1,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_survey_sections_version FOREIGN KEY (instrument_version_id) REFERENCES survey_instrument_versions(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE survey_questions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    section_id BIGINT UNSIGNED NOT NULL,
    question_text VARCHAR(255) NOT NULL,
    question_type ENUM('text','textarea','select','radio','number','date') NOT NULL DEFAULT 'text',
    options_json JSON NULL,
    help_text VARCHAR(255) NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 1,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_survey_questions_section FOREIGN KEY (section_id) REFERENCES survey_sections(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE survey_response_sets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    tracer_campaign_id BIGINT UNSIGNED NOT NULL,
    instrument_version_id BIGINT UNSIGNED NOT NULL,
    status ENUM('draft','completed') NOT NULL DEFAULT 'draft',
    submitted_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_survey_response_user_campaign (user_id, tracer_campaign_id),
    CONSTRAINT fk_survey_response_sets_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_survey_response_sets_campaign FOREIGN KEY (tracer_campaign_id) REFERENCES tracer_campaigns(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_survey_response_sets_version FOREIGN KEY (instrument_version_id) REFERENCES survey_instrument_versions(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE employment_records (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    employment_status VARCHAR(50) NOT NULL,
    employment_type VARCHAR(50) NULL,
    job_title VARCHAR(150) NULL,
    company_name VARCHAR(150) NULL,
    company_address VARCHAR(255) NULL,
    salary_range VARCHAR(50) NULL,
    work_setup VARCHAR(50) NULL,
    job_aligned VARCHAR(20) NULL,
    date_hired DATE NULL,
    job_search_duration VARCHAR(100) NULL,
    currently_employed TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_employment_records_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;


CREATE TABLE employment_proofs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    proof_type VARCHAR(100) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    file_size INT UNSIGNED NOT NULL DEFAULT 0,
    remarks VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_employment_proofs_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE graduate_feedback (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    technical_skill_rating TINYINT UNSIGNED NOT NULL,
    communication_skill_rating TINYINT UNSIGNED NOT NULL,
    problem_solving_rating TINYINT UNSIGNED NOT NULL,
    teamwork_rating TINYINT UNSIGNED NOT NULL,
    curriculum_relevance_rating TINYINT UNSIGNED NOT NULL,
    most_useful_learnings TEXT NULL,
    suggested_improvements TEXT NULL,
    other_comments TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_graduate_feedback_user FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE survey_answers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    response_set_id BIGINT UNSIGNED NOT NULL,
    question_id BIGINT UNSIGNED NOT NULL,
    answer_text TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_survey_answer (response_set_id, question_id),
    CONSTRAINT fk_survey_answers_response_set FOREIGN KEY (response_set_id) REFERENCES survey_response_sets(id)
        ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_survey_answers_question FOREIGN KEY (question_id) REFERENCES survey_questions(id)
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE site_settings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE homepage_slides (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    subtitle VARCHAR(255) NULL,
    image_url VARCHAR(255) NOT NULL,
    button_label VARCHAR(80) NULL,
    button_link VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 1,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE graduate_updates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    summary TEXT NULL,
    highlight_text VARCHAR(150) NULL,
    image_url VARCHAR(255) NULL,
    published_at DATE NOT NULL,
    status ENUM('published','draft') NOT NULL DEFAULT 'published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE testimonials (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    graduate_name VARCHAR(150) NOT NULL,
    program_name VARCHAR(150) NULL,
    graduation_year YEAR NULL,
    quote_text TEXT NOT NULL,
    rating TINYINT UNSIGNED NOT NULL DEFAULT 5,
    avatar_url VARCHAR(255) NULL,
    sort_order INT NOT NULL DEFAULT 1,
    status ENUM('published','draft') NOT NULL DEFAULT 'published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE news_feeds (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    summary TEXT NULL,
    content TEXT NULL,
    image_url VARCHAR(255) NULL,
    published_at DATE NOT NULL,
    status ENUM('published','draft') NOT NULL DEFAULT 'published',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO roles (role_name, description) VALUES
('admin', 'Can manage the portal and view all future modules.'),
('graduate', 'Graduate user account for tracer survey participation.');

INSERT INTO programs (program_code, program_name, status) VALUES
('BSIT', 'Bachelor of Science in Information Technology', 'active'),
('BSCS', 'Bachelor of Science in Computer Science', 'active'),
('BSEd', 'Bachelor of Secondary Education', 'active'),
('BSBA', 'Bachelor of Science in Business Administration', 'active'),
('BSA', 'Bachelor of Science in Agriculture', 'active'),
('BTLED', 'Bachelor of Technology and Livelihood Education', 'inactive');

INSERT INTO survey_instruments (instrument_name, description, status) VALUES
('PRMSU Graduate Tracer Survey', 'Default graduate tracer study instrument managed by the administrator.', 'active');

INSERT INTO survey_instrument_versions (instrument_id, version_code, version_title, version_notes, status, is_current, published_at) VALUES
(1, '2026-V1', 'Default Defense Instrument', 'Initial managed version for the alumni portal defense build.', 'published', 1, NOW());

INSERT INTO tracer_campaigns (campaign_title, description, target_graduation_year, instrument_version_id, open_date, close_date, status) VALUES
('Batch 2024 Graduate Tracer Survey', 'Default batch-based tracer campaign assigned to graduates from batch 2024.', 2024, 1, NULL, NULL, 'active');

INSERT INTO survey_sections (instrument_version_id, section_title, description, sort_order, status) VALUES
(1, 'General Information', 'Basic tracer survey details that support the graduate profile already encoded in the portal.', 1, 'active'),
(1, 'Transition to Employment', 'Questions about the graduate journey after leaving the university.', 2, 'active'),
(1, 'Further Studies and Reflections', 'Questions about advanced studies and personal reflection on the program.', 3, 'active');

INSERT INTO survey_questions (section_id, question_text, question_type, options_json, help_text, is_required, sort_order, status) VALUES
(1, 'What is your current primary status after graduation?', 'select', JSON_ARRAY('Employed', 'Self-employed', 'Unemployed', 'Pursuing Further Studies'), 'Choose the option that best describes your present situation.', 1, 1, 'active'),
(1, 'How long did it take before you obtained your first job after graduation?', 'select', JSON_ARRAY('Already employed before graduation', 'Less than 1 month', '1 to 6 months', '7 to 12 months', 'More than 1 year', 'Not yet employed'), 'This helps measure graduate transition time.', 1, 2, 'active'),
(1, 'What best describes your place of work or current activity?', 'text', NULL, 'Example: school, company, government office, business, or not applicable.', 0, 3, 'active'),
(2, 'Did the university program you finished help prepare you for employment or professional practice?', 'radio', JSON_ARRAY('Yes', 'Partly', 'No'), 'Select the answer that best matches your experience.', 1, 1, 'active'),
(2, 'What competencies or skills from PRMSU are you using most in your current work or activity?', 'textarea', NULL, 'You may mention technical, communication, research, leadership, or other skills.', 1, 2, 'active'),
(2, 'What challenges did you face while looking for work after graduation?', 'textarea', NULL, 'This can help improve future student support services.', 0, 3, 'active'),
(3, 'Are you currently pursuing further studies or professional certification?', 'radio', JSON_ARRAY('Yes', 'No'), 'Examples include master''s degree, law school, review center, or certification.', 1, 1, 'active'),
(3, 'What suggestions can you give to improve the curriculum or student preparation in your program?', 'textarea', NULL, 'Please share realistic suggestions for future graduates.', 1, 2, 'active');

-- Password for both seeded accounts: admin123
INSERT INTO users (role_id, first_name, last_name, email, password_hash, status, last_login_at) VALUES
(1, 'System', 'Administrator', 'admin@prmsu.local', '$2y$12$Mud1pmek/yBtCw3qpneVNunj0nHuvfTIOwi4NPUXvvEydgzkAXoGC', 'active', NOW()),
(2, 'Juan', 'Dela Cruz', 'graduate@prmsu.local', '$2y$12$Mud1pmek/yBtCw3qpneVNunj0nHuvfTIOwi4NPUXvvEydgzkAXoGC', 'active', NOW());

INSERT INTO graduate_profiles (
    user_id, student_number, middle_name, sex, birth_date, civil_status,
    contact_number, province, city_municipality, barangay, street_address,
    graduation_year, program_id
) VALUES (
    2, '2019-0001', 'Santos', 'male', '2001-05-15', 'Single',
    '09171234567', 'Zambales', 'Iba', 'Amungan', 'Purok 1',
    2024, 1
);

INSERT INTO survey_response_sets (user_id, tracer_campaign_id, instrument_version_id, status, submitted_at) VALUES
(2, 1, 1, 'completed', NOW());

INSERT INTO survey_answers (response_set_id, question_id, answer_text) VALUES
(1, 1, 'Employed'),
(1, 2, '1 to 6 months'),
(1, 3, 'Government office'),
(1, 4, 'Yes'),
(1, 5, 'Database design, web development, and communication skills'),
(1, 6, 'Limited industry exposure while applying for the first job.'),
(1, 7, 'No'),
(1, 8, 'Add more real-world project integration and stronger career preparation support.');

INSERT INTO employment_records (
    user_id, employment_status, employment_type, job_title, company_name,
    company_address, salary_range, work_setup, job_aligned, date_hired,
    job_search_duration, currently_employed
) VALUES (
    2, 'Employed', 'Regular', 'Junior Web Developer', 'Zambales Digital Solutions',
    'Iba, Zambales', '20,000 - 29,999', 'On-site', 'Yes', '2024-08-01',
    '1 to 6 months', 1
);

INSERT INTO graduate_feedback (
    user_id, technical_skill_rating, communication_skill_rating, problem_solving_rating,
    teamwork_rating, curriculum_relevance_rating, most_useful_learnings,
    suggested_improvements, other_comments
) VALUES (
    2, 4, 4, 5, 4, 4,
    'Database design, web development, and communication skills were the most useful in my first job.',
    'Add more real-world project integration, career preparation workshops, and updated industry tools.',
    'The portal can later summarize these responses for program improvement reports.'
);

INSERT INTO site_settings (setting_key, setting_value) VALUES
('hero_badge', 'PRMSU Alumni Portal'),
('hero_title', 'Welcome to the PRMSU alumni community'),
('hero_subtitle', 'A centralized platform where graduates can reconnect, update records, participate in tracer studies, and stay informed about university news and alumni stories.'),
('hero_primary_label', 'Open Login'),
('hero_primary_link', 'login'),
('hero_secondary_label', 'Graduate Registration'),
('hero_secondary_link', 'auth/register'),
('hero_background_url', 'https://images.unsplash.com/photo-1523050854058-8df90110c9f1?auto=format&fit=crop&w=1400&q=80'),
('section_updates_title', 'Graduate Updates'),
('section_testimonials_title', 'Testimonials'),
('section_news_title', 'News Feed');

INSERT INTO homepage_slides (title, subtitle, image_url, button_label, button_link, sort_order, status) VALUES
('Career Readiness and Alumni Success', 'Track graduate outcomes and keep alumni data organized for tracer studies.', 'https://images.unsplash.com/photo-1523240795612-9a054b0db644?auto=format&fit=crop&w=1400&q=80', 'Open Login', 'login', 1, 'active'),
('Celebrate PRMSU Achievements', 'Feature graduate milestones, testimonials, and inspiring alumni stories.', 'https://images.unsplash.com/photo-1522202176988-66273c2fd55f?auto=format&fit=crop&w=1400&q=80', 'Graduate Registration', 'auth/register', 2, 'active'),
('Tracer Instruments Can Now Be Managed', 'Survey instruments, versions, sections, and items can now be maintained by the administrator.', 'https://images.unsplash.com/photo-1524995997946-a1c2e315a42f?auto=format&fit=crop&w=1400&q=80', 'Open Login', 'login', 3, 'active');

INSERT INTO graduate_updates (title, summary, highlight_text, image_url, published_at, status) VALUES
('Graduate Employment Milestone', 'The current build already captures employment status, job title, company, salary range, and work setup for graduates.', 'Employment tracking ready', 'https://images.unsplash.com/photo-1454165804606-c3d57bc86b40?auto=format&fit=crop&w=900&q=80', CURDATE(), 'published'),
('Tracer Instrument Management Live', 'Administrators can now manage survey instruments, versions, sections, and survey items before assigning tracer campaigns by batch.', 'Instrument management ready', 'https://images.unsplash.com/photo-1450101499163-c8848c66ca85?auto=format&fit=crop&w=900&q=80', DATE_SUB(CURDATE(), INTERVAL 3 DAY), 'published'),
('Profile Management Live', 'Graduate registration, academic details, and contact profile updating remain active for the proposal defense presentation.', 'Profiles active', 'https://images.unsplash.com/photo-1529390079861-591de354faf5?auto=format&fit=crop&w=900&q=80', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 'published');

INSERT INTO testimonials (graduate_name, program_name, graduation_year, quote_text, rating, avatar_url, sort_order, status) VALUES
('Juan Dela Cruz', 'BSIT', 2024, 'The portal makes it easier to submit my tracer survey and update my employment details in one place.', 5, 'https://ui-avatars.com/api/?background=0D6EFD&color=fff&name=Juan+Dela+Cruz', 1, 'published'),
('Maria Santos', 'BSEd', 2023, 'A modern alumni dashboard helps graduates feel connected while providing data that supports curriculum improvement.', 5, 'https://ui-avatars.com/api/?background=6f42c1&color=fff&name=Maria+Santos', 2, 'published'),
('Carlos Ramirez', 'BSBA', 2022, 'Managed instrument versions make future tracer campaigns more organized and easier to maintain.', 4, 'https://ui-avatars.com/api/?background=198754&color=fff&name=Carlos+Ramirez', 3, 'published');

INSERT INTO news_feeds (title, summary, content, image_url, published_at, status) VALUES
('Instrument Management Added to the Alumni Portal', 'The latest build now supports managed survey instruments, versioning, sections, and items for tracer workflows.', 'This milestone prepares the portal for the next subphase where tracer campaigns will be created per graduation batch and linked to the correct instrument version.', 'https://images.unsplash.com/photo-1516321318423-f06f85e504b3?auto=format&fit=crop&w=900&q=80', CURDATE(), 'published'),
('Modern UI Refresh Applied', 'The public landing page now uses a modern single-page layout with admin-maintained sections and sidebar dashboards for logged-in users.', 'The updated interface improves presentation quality by introducing a hero section, homepage slider, graduate updates, testimonials, and news feed cards.', 'https://images.unsplash.com/photo-1497366754035-f200968a6e72?auto=format&fit=crop&w=900&q=80', DATE_SUB(CURDATE(), INTERVAL 2 DAY), 'published'),
('Graduate Feedback Still Active', 'Skills and curriculum relevance ratings remain available inside the graduate dashboard for data gathering and program assessment.', 'Graduates can continue saving technical, communication, problem-solving, teamwork, and curriculum relevance feedback through the existing Phase 5 module.', 'https://images.unsplash.com/photo-1516321165247-4aa89a48be28?auto=format&fit=crop&w=900&q=80', DATE_SUB(CURDATE(), INTERVAL 5 DAY), 'published');

CREATE INDEX idx_users_role_status ON users(role_id, status);
CREATE INDEX idx_programs_status_name ON programs(status, program_name);
CREATE INDEX idx_graduate_profiles_program_year ON graduate_profiles(program_id, graduation_year);
CREATE INDEX idx_survey_versions_status_current ON survey_instrument_versions(status, is_current, instrument_id);
CREATE INDEX idx_tracer_campaigns_batch_status ON tracer_campaigns(target_graduation_year, status, instrument_version_id);
CREATE INDEX idx_survey_sections_version_order ON survey_sections(instrument_version_id, status, sort_order);
CREATE INDEX idx_survey_questions_section_order ON survey_questions(section_id, status, sort_order);
CREATE INDEX idx_survey_response_campaign_status ON survey_response_sets(tracer_campaign_id, status);
CREATE INDEX idx_survey_response_version_status ON survey_response_sets(instrument_version_id, status);
CREATE INDEX idx_employment_records_status ON employment_records(employment_status, currently_employed);
CREATE INDEX idx_employment_proofs_user_created ON employment_proofs(user_id, created_at);
CREATE INDEX idx_graduate_feedback_user ON graduate_feedback(user_id);
CREATE INDEX idx_homepage_slides_sort ON homepage_slides(status, sort_order);
CREATE INDEX idx_graduate_updates_published ON graduate_updates(status, published_at);
CREATE INDEX idx_testimonials_sort ON testimonials(status, sort_order);
CREATE INDEX idx_news_feeds_published ON news_feeds(status, published_at);