CREATE TABLE languages (
    code VARCHAR(5) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE riasec_areas (
    code CHAR(1) PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    color_hex VARCHAR(7) NOT NULL,
    sort_order TINYINT UNSIGNED NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE riasec_area_content (
    area_code CHAR(1) NOT NULL,
    language_code VARCHAR(5) NOT NULL,
    description TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (area_code, language_code),
    CONSTRAINT fk_riasec_area_content_area FOREIGN KEY (area_code) REFERENCES riasec_areas(code) ON DELETE CASCADE,
    CONSTRAINT fk_riasec_area_content_language FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE questions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    area_code CHAR(1) NOT NULL,
    source_key VARCHAR(50) NOT NULL,
    sort_order INT UNSIGNED NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_questions_area FOREIGN KEY (area_code) REFERENCES riasec_areas(code),
    CONSTRAINT uq_questions_source UNIQUE (source_key)
);

CREATE TABLE question_translations (
    question_id BIGINT UNSIGNED NOT NULL,
    language_code VARCHAR(5) NOT NULL,
    statement TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (question_id, language_code),
    CONSTRAINT fk_question_translations_question FOREIGN KEY (question_id) REFERENCES questions(id) ON DELETE CASCADE,
    CONSTRAINT fk_question_translations_language FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE test_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(40) NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    phone VARCHAR(50) NOT NULL,
    preferred_language VARCHAR(5) NOT NULL,
    status ENUM('in_progress', 'completed') NOT NULL DEFAULT 'in_progress',
    started_at DATETIME NOT NULL,
    completed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_test_sessions_reference UNIQUE (reference),
    CONSTRAINT fk_test_sessions_language FOREIGN KEY (preferred_language) REFERENCES languages(code)
);

CREATE TABLE participant_answers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    test_session_id BIGINT UNSIGNED NOT NULL,
    question_id BIGINT UNSIGNED NOT NULL,
    answer_value TINYINT(1) NOT NULL,
    answered_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_participant_answers_session FOREIGN KEY (test_session_id) REFERENCES test_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_participant_answers_question FOREIGN KEY (question_id) REFERENCES questions(id),
    CONSTRAINT uq_participant_answer UNIQUE (test_session_id, question_id)
);

CREATE TABLE reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    test_session_id BIGINT UNSIGNED NOT NULL,
    reference VARCHAR(40) NOT NULL,
    primary_area_code CHAR(1) NOT NULL,
    score_json JSON NOT NULL,
    ranking_json JSON NOT NULL,
    report_snapshot_json JSON NOT NULL,
    pdf_path VARCHAR(255) NULL,
    generated_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_reports_reference UNIQUE (reference),
    CONSTRAINT uq_reports_session UNIQUE (test_session_id),
    CONSTRAINT fk_reports_session FOREIGN KEY (test_session_id) REFERENCES test_sessions(id) ON DELETE CASCADE,
    CONSTRAINT fk_reports_area FOREIGN KEY (primary_area_code) REFERENCES riasec_areas(code)
);

CREATE TABLE career_opportunities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    area_code CHAR(1) NOT NULL,
    language_code VARCHAR(5) NOT NULL,
    title VARCHAR(150) NOT NULL,
    description TEXT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_career_opportunities_area FOREIGN KEY (area_code) REFERENCES riasec_areas(code),
    CONSTRAINT fk_career_opportunities_language FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE suitable_programmes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    area_code CHAR(1) NOT NULL,
    language_code VARCHAR(5) NOT NULL,
    programme_code VARCHAR(50) NULL,
    programme_name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_suitable_programmes_area FOREIGN KEY (area_code) REFERENCES riasec_areas(code),
    CONSTRAINT fk_suitable_programmes_language FOREIGN KEY (language_code) REFERENCES languages(code)
);

CREATE TABLE admin_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_admin_roles_name UNIQUE (name)
);

CREATE TABLE admin_users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id BIGINT UNSIGNED NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_admin_users_email UNIQUE (email),
    CONSTRAINT fk_admin_users_role FOREIGN KEY (role_id) REFERENCES admin_roles(id)
);

CREATE INDEX idx_test_sessions_search ON test_sessions (full_name, email, phone);
CREATE INDEX idx_reports_reference ON reports (reference, generated_at);

INSERT INTO languages (code, name) VALUES
('en', 'English'),
('ms', 'Bahasa Melayu'),
('zh', 'Chinese'),
('ta', 'Tamil');

INSERT INTO riasec_areas (code, name, color_hex, sort_order) VALUES
('R', 'Realistic', '#8A5A44', 1),
('I', 'Investigative', '#2A6F97', 2),
('A', 'Artistic', '#C66B3D', 3),
('S', 'Social', '#4C956C', 4),
('E', 'Enterprising', '#B56576', 5),
('C', 'Conventional', '#6D597A', 6);

INSERT INTO riasec_area_content (area_code, language_code, description) VALUES
('R', 'en', 'People with strong Realistic interests usually prefer practical, hands-on tasks, tools, machinery, outdoor work, and visible results.'),
('I', 'en', 'People with strong Investigative interests are drawn to analysis, research, problem solving, science, technology, and evidence-based thinking.'),
('A', 'en', 'People with strong Artistic interests often enjoy creative expression, design, aesthetics, performance, storytelling, and original ideas.'),
('S', 'en', 'People with strong Social interests usually enjoy helping, teaching, supporting, mentoring, listening, and working closely with others.'),
('E', 'en', 'People with strong Enterprising interests are typically energized by leadership, persuasion, initiative, business goals, and influencing outcomes.'),
('C', 'en', 'People with strong Conventional interests usually prefer structure, accuracy, orderly systems, detailed processes, and dependable routines.');