-- ============================================================
-- BOAFO - Mentorship & University Transition Support System
-- Full Database Schema v1.0
-- MySQL 8.0+
-- ============================================================

CREATE DATABASE IF NOT EXISTS boafo_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE boafo_db;

-- ============================================================
-- TABLE: users
-- Core authentication table for all user types
-- ============================================================
CREATE TABLE IF NOT EXISTS users (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid           CHAR(36) NOT NULL UNIQUE,
    first_name     VARCHAR(100) NOT NULL,
    last_name      VARCHAR(100) NOT NULL,
    email          VARCHAR(191) NOT NULL UNIQUE,
    phone          VARCHAR(20),
    password_hash  VARCHAR(255) NOT NULL,
    role           ENUM('admin','student','mentor','tutor') NOT NULL DEFAULT 'student',
    avatar         VARCHAR(255) DEFAULT NULL,
    status         ENUM('active','inactive','suspended','pending') NOT NULL DEFAULT 'pending',
    email_verified TINYINT(1) NOT NULL DEFAULT 0,
    verify_token   VARCHAR(100) DEFAULT NULL,
    reset_token    VARCHAR(100) DEFAULT NULL,
    reset_expires  DATETIME DEFAULT NULL,
    login_attempts INT NOT NULL DEFAULT 0,
    locked_until   DATETIME DEFAULT NULL,
    last_login     DATETIME DEFAULT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_role  (role),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: countries
-- ============================================================
CREATE TABLE IF NOT EXISTS countries (
    id        SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    code      CHAR(2) NOT NULL UNIQUE,
    continent VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: regions
-- ============================================================
CREATE TABLE IF NOT EXISTS regions (
    id         SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    country_id SMALLINT UNSIGNED NOT NULL,
    name       VARCHAR(150) NOT NULL,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: student_profiles
-- ============================================================
CREATE TABLE IF NOT EXISTS student_profiles (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL UNIQUE,
    date_of_birth       DATE DEFAULT NULL,
    gender              ENUM('male','female','other','prefer_not') DEFAULT NULL,
    country_id          SMALLINT UNSIGNED DEFAULT NULL,
    region_id           SMALLINT UNSIGNED DEFAULT NULL,
    city                VARCHAR(100) DEFAULT NULL,
    shs_school          VARCHAR(200) DEFAULT NULL,
    shs_program         ENUM('general','business','visual_arts','home_economics','agriculture','technical','vocational') DEFAULT NULL,
    graduation_year     YEAR DEFAULT NULL,
    intended_program    VARCHAR(200) DEFAULT NULL,
    preferred_university VARCHAR(200) DEFAULT NULL,
    career_interests    TEXT DEFAULT NULL,
    academic_strengths  TEXT DEFAULT NULL,
    financial_need      ENUM('high','moderate','low','none') DEFAULT 'moderate',
    bio                 TEXT DEFAULT NULL,
    profile_completed   TINYINT(1) NOT NULL DEFAULT 0,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL,
    FOREIGN KEY (region_id)  REFERENCES regions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: mentor_profiles
-- ============================================================
CREATE TABLE IF NOT EXISTS mentor_profiles (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL UNIQUE,
    university_id       INT UNSIGNED DEFAULT NULL,
    program             VARCHAR(200) DEFAULT NULL,
    faculty             VARCHAR(200) DEFAULT NULL,
    year_level          TINYINT UNSIGNED DEFAULT NULL COMMENT '1-8',
    expertise_areas     TEXT DEFAULT NULL,
    career_goals        TEXT DEFAULT NULL,
    bio                 TEXT DEFAULT NULL,
    linkedin_url        VARCHAR(255) DEFAULT NULL,
    is_available        TINYINT(1) NOT NULL DEFAULT 1,
    max_mentees         TINYINT UNSIGNED NOT NULL DEFAULT 5,
    current_mentees     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    sessions_completed  INT UNSIGNED NOT NULL DEFAULT 0,
    rating              DECIMAL(3,2) DEFAULT NULL,
    verified            TINYINT(1) NOT NULL DEFAULT 0,
    country_id          SMALLINT UNSIGNED DEFAULT NULL,
    region_id           SMALLINT UNSIGNED DEFAULT NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)       REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id)    REFERENCES countries(id) ON DELETE SET NULL,
    FOREIGN KEY (region_id)     REFERENCES regions(id) ON DELETE SET NULL,
    INDEX idx_university (university_id),
    INDEX idx_available  (is_available)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: tutor_profiles
-- ============================================================
CREATE TABLE IF NOT EXISTS tutor_profiles (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL UNIQUE,
    specializations     TEXT DEFAULT NULL,
    subjects            TEXT DEFAULT NULL,
    qualification       VARCHAR(255) DEFAULT NULL,
    institution         VARCHAR(255) DEFAULT NULL,
    bio                 TEXT DEFAULT NULL,
    is_available        TINYINT(1) NOT NULL DEFAULT 1,
    hourly_rate         DECIMAL(10,2) DEFAULT NULL,
    rating              DECIMAL(3,2) DEFAULT NULL,
    sessions_completed  INT UNSIGNED NOT NULL DEFAULT 0,
    country_id          SMALLINT UNSIGNED DEFAULT NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id)    REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (country_id) REFERENCES countries(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- ============================================================
-- TABLE: user_availability
-- ============================================================
CREATE TABLE IF NOT EXISTS user_availability (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id             INT UNSIGNED NOT NULL,
    day_of_week         TINYINT UNSIGNED NOT NULL COMMENT '0=Sunday, 1=Monday, ..., 6=Saturday',
    start_time          TIME NOT NULL,
    end_time            TIME NOT NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_day  (user_id, day_of_week)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- ============================================================
-- TABLE: universities
-- ============================================================
CREATE TABLE IF NOT EXISTS universities (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                VARCHAR(255) NOT NULL,
    slug                VARCHAR(255) NOT NULL UNIQUE,
    country_id          SMALLINT UNSIGNED NOT NULL,
    region_id           SMALLINT UNSIGNED DEFAULT NULL,
    city                VARCHAR(150) DEFAULT NULL,
    university_type     ENUM('public','private','technical','polytechnic','distance','international') NOT NULL DEFAULT 'public',
    website             VARCHAR(255) DEFAULT NULL,
    logo                VARCHAR(255) DEFAULT NULL,
    banner              VARCHAR(255) DEFAULT NULL,
    description         TEXT DEFAULT NULL,
    established_year    YEAR DEFAULT NULL,
    student_population  INT UNSIGNED DEFAULT NULL,
    ranking_local       SMALLINT UNSIGNED DEFAULT NULL,
    ranking_global      SMALLINT UNSIGNED DEFAULT NULL,
    accreditation       TEXT DEFAULT NULL,
    contact_email       VARCHAR(191) DEFAULT NULL,
    contact_phone       VARCHAR(50) DEFAULT NULL,
    contact_address     TEXT DEFAULT NULL,
    application_portal  VARCHAR(255) DEFAULT NULL,
    is_featured         TINYINT(1) NOT NULL DEFAULT 0,
    status              ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (country_id) REFERENCES countries(id),
    FOREIGN KEY (region_id)  REFERENCES regions(id) ON DELETE SET NULL,
    FULLTEXT INDEX ft_university (name, description),
    INDEX idx_country (country_id),
    INDEX idx_type    (university_type),
    INDEX idx_featured (is_featured)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: faculties
-- ============================================================
CREATE TABLE IF NOT EXISTS faculties (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    university_id INT UNSIGNED NOT NULL,
    name          VARCHAR(255) NOT NULL,
    dean          VARCHAR(200) DEFAULT NULL,
    description   TEXT DEFAULT NULL,
    FOREIGN KEY (university_id) REFERENCES universities(id) ON DELETE CASCADE,
    INDEX idx_university (university_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: programs
-- ============================================================
CREATE TABLE IF NOT EXISTS programs (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    university_id         INT UNSIGNED NOT NULL,
    faculty_id            INT UNSIGNED DEFAULT NULL,
    name                  VARCHAR(255) NOT NULL,
    degree_level          ENUM('certificate','diploma','bachelor','master','phd') NOT NULL DEFAULT 'bachelor',
    duration_years        TINYINT UNSIGNED DEFAULT 4,
    description           TEXT DEFAULT NULL,
    admission_requirements TEXT DEFAULT NULL,
    min_aggregate         TINYINT UNSIGNED DEFAULT NULL COMMENT 'WASSCE aggregate cutoff',
    required_subjects     TEXT DEFAULT NULL COMMENT 'JSON array of required subjects',
    tuition_local         DECIMAL(12,2) DEFAULT NULL,
    tuition_international DECIMAL(12,2) DEFAULT NULL,
    tuition_currency      CHAR(3) DEFAULT 'GHS',
    application_deadline  DATE DEFAULT NULL,
    intake_semester       ENUM('first','second','both') DEFAULT 'both',
    available_slots       SMALLINT UNSIGNED DEFAULT NULL,
    is_active             TINYINT(1) NOT NULL DEFAULT 1,
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (university_id) REFERENCES universities(id) ON DELETE CASCADE,
    FOREIGN KEY (faculty_id)    REFERENCES faculties(id) ON DELETE SET NULL,
    FULLTEXT INDEX ft_program (name, description),
    INDEX idx_university  (university_id),
    INDEX idx_degree_level (degree_level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: scholarships
-- ============================================================
CREATE TABLE IF NOT EXISTS scholarships (
    id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                  VARCHAR(255) NOT NULL,
    slug                  VARCHAR(255) NOT NULL UNIQUE,
    provider              VARCHAR(255) NOT NULL,
    category              ENUM('general','university_specific','government','corporate','ngo','international') NOT NULL DEFAULT 'general',
    scholarship_type      ENUM('full','partial','grant','loan','bursary') NOT NULL DEFAULT 'full',
    coverage              TEXT DEFAULT NULL COMMENT 'What is covered (tuition, accommodation, etc.)',
    value_amount          DECIMAL(14,2) DEFAULT NULL,
    value_currency        CHAR(3) DEFAULT 'GHS',
    num_beneficiaries     SMALLINT UNSIGNED DEFAULT NULL,
    num_applicants_est    INT UNSIGNED DEFAULT NULL COMMENT 'Estimated annual applicants',
    description           TEXT DEFAULT NULL,
    eligibility_requirements TEXT DEFAULT NULL,
    required_documents    TEXT DEFAULT NULL,
    application_deadline  DATE DEFAULT NULL,
    academic_year         VARCHAR(20) DEFAULT NULL,
    min_gpa               DECIMAL(3,2) DEFAULT NULL,
    min_aggregate         TINYINT UNSIGNED DEFAULT NULL,
    nationality_required  VARCHAR(255) DEFAULT NULL COMMENT 'CSV country codes or "all"',
    financial_need        TINYINT(1) NOT NULL DEFAULT 0,
    gender_restriction    ENUM('all','male','female') DEFAULT 'all',
    disability_inclusive  TINYINT(1) NOT NULL DEFAULT 0,
    application_link      VARCHAR(255) DEFAULT NULL,
    contact_email         VARCHAR(191) DEFAULT NULL,
    is_active             TINYINT(1) NOT NULL DEFAULT 1,
    is_featured           TINYINT(1) NOT NULL DEFAULT 0,
    status                ENUM('open','closed','upcoming') NOT NULL DEFAULT 'open',
    created_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FULLTEXT INDEX ft_scholarship (name, description, provider),
    INDEX idx_category  (category),
    INDEX idx_type      (scholarship_type),
    INDEX idx_status    (status),
    INDEX idx_featured  (is_featured)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: scholarship_universities
-- Links scholarships to specific universities
-- ============================================================
CREATE TABLE IF NOT EXISTS scholarship_universities (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scholarship_id INT UNSIGNED NOT NULL,
    university_id  INT UNSIGNED NOT NULL,
    program_id     INT UNSIGNED DEFAULT NULL,
    UNIQUE KEY uq_sch_uni (scholarship_id, university_id),
    FOREIGN KEY (scholarship_id) REFERENCES scholarships(id) ON DELETE CASCADE,
    FOREIGN KEY (university_id)  REFERENCES universities(id) ON DELETE CASCADE,
    FOREIGN KEY (program_id)     REFERENCES programs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: wassce_results
-- Student WASSCE subject grades
-- ============================================================
CREATE TABLE IF NOT EXISTS wassce_results (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    subject    VARCHAR(100) NOT NULL,
    grade      ENUM('A1','B2','B3','C4','C5','C6','D7','E8','F9') NOT NULL,
    year       YEAR DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: saved_universities
-- ============================================================
CREATE TABLE IF NOT EXISTS saved_universities (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       INT UNSIGNED NOT NULL,
    university_id INT UNSIGNED NOT NULL,
    notes         TEXT DEFAULT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_saved_uni (user_id, university_id),
    FOREIGN KEY (user_id)       REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (university_id) REFERENCES universities(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: saved_scholarships
-- ============================================================
CREATE TABLE IF NOT EXISTS saved_scholarships (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id        INT UNSIGNED NOT NULL,
    scholarship_id INT UNSIGNED NOT NULL,
    notes          TEXT DEFAULT NULL,
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_saved_sch (user_id, scholarship_id),
    FOREIGN KEY (user_id)        REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (scholarship_id) REFERENCES scholarships(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: mentorship_requests
-- ============================================================
CREATE TABLE IF NOT EXISTS mentorship_requests (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id  INT UNSIGNED NOT NULL,
    mentor_id   INT UNSIGNED NOT NULL,
    message     TEXT DEFAULT NULL,
    goals       TEXT DEFAULT NULL,
    status      ENUM('pending','accepted','declined','cancelled','completed') NOT NULL DEFAULT 'pending',
    responded_at DATETIME DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_request (student_id, mentor_id),
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (mentor_id)  REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_student (student_id),
    INDEX idx_mentor  (mentor_id),
    INDEX idx_status  (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: sessions (mentoring/tutoring sessions)
-- ============================================================
CREATE TABLE IF NOT EXISTS sessions (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id    INT UNSIGNED DEFAULT NULL,
    student_id    INT UNSIGNED NOT NULL,
    mentor_id     INT UNSIGNED NOT NULL,
    title         VARCHAR(255) DEFAULT NULL,
    description   TEXT DEFAULT NULL,
    session_type  ENUM('mentorship','tutoring','career','admission','scholarship') NOT NULL DEFAULT 'mentorship',
    scheduled_at  DATETIME NOT NULL,
    duration_min  SMALLINT UNSIGNED NOT NULL DEFAULT 60,
    location_type ENUM('online','in_person') DEFAULT 'online',
    meeting_link  VARCHAR(255) DEFAULT NULL,
    location      VARCHAR(255) DEFAULT NULL,
    status        ENUM('pending','confirmed','completed','cancelled','no_show') NOT NULL DEFAULT 'pending',
    rating        TINYINT UNSIGNED DEFAULT NULL COMMENT '1-5',
    feedback      TEXT DEFAULT NULL,
    notes         TEXT DEFAULT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (mentor_id)  REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (request_id) REFERENCES mentorship_requests(id) ON DELETE SET NULL,
    INDEX idx_student     (student_id),
    INDEX idx_mentor      (mentor_id),
    INDEX idx_scheduled   (scheduled_at),
    INDEX idx_status      (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: conversations
-- ============================================================
CREATE TABLE IF NOT EXISTS conversations (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    student_id   INT UNSIGNED NOT NULL,
    mentor_id    INT UNSIGNED NOT NULL,
    last_message_at DATETIME DEFAULT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_conversation (student_id, mentor_id),
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (mentor_id)  REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: messages
-- ============================================================
CREATE TABLE IF NOT EXISTS messages (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT UNSIGNED NOT NULL,
    sender_id       INT UNSIGNED NOT NULL,
    body            TEXT NOT NULL,
    is_read         TINYINT(1) NOT NULL DEFAULT 0,
    attachment      VARCHAR(255) DEFAULT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id)       REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_conversation (conversation_id),
    INDEX idx_sender       (sender_id),
    INDEX idx_read         (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: notifications
-- ============================================================
CREATE TABLE IF NOT EXISTS notifications (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    type       VARCHAR(50) NOT NULL,
    title      VARCHAR(255) NOT NULL,
    body       TEXT DEFAULT NULL,
    url        VARCHAR(255) DEFAULT NULL,
    is_read    TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user    (user_id),
    INDEX idx_read    (is_read),
    INDEX idx_type    (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: career_categories
-- ============================================================
CREATE TABLE IF NOT EXISTS career_categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(150) NOT NULL,
    icon        VARCHAR(100) DEFAULT NULL,
    description TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: careers
-- ============================================================
CREATE TABLE IF NOT EXISTS careers (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id         INT UNSIGNED DEFAULT NULL,
    title               VARCHAR(200) NOT NULL,
    slug                VARCHAR(200) NOT NULL UNIQUE,
    description         TEXT DEFAULT NULL,
    required_programs   TEXT DEFAULT NULL COMMENT 'JSON array',
    industry            VARCHAR(150) DEFAULT NULL,
    avg_salary_ghs      DECIMAL(12,2) DEFAULT NULL,
    growth_outlook      ENUM('high','moderate','low') DEFAULT 'moderate',
    skills_required     TEXT DEFAULT NULL,
    work_environment    TEXT DEFAULT NULL,
    career_path         TEXT DEFAULT NULL,
    image               VARCHAR(255) DEFAULT NULL,
    FOREIGN KEY (category_id) REFERENCES career_categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: career_assessments
-- ============================================================
CREATE TABLE IF NOT EXISTS career_assessments (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NOT NULL,
    responses    JSON NOT NULL COMMENT 'Assessment answers as JSON',
    results      JSON DEFAULT NULL COMMENT 'Computed results as JSON',
    completed_at DATETIME DEFAULT NULL,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: resources
-- ============================================================
CREATE TABLE IF NOT EXISTS resources (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title        VARCHAR(255) NOT NULL,
    slug         VARCHAR(255) NOT NULL UNIQUE,
    category     ENUM('pdf','video','article','guide','checklist','template','tip') NOT NULL DEFAULT 'article',
    module       ENUM('transition','career','scholarship','admission','study_skills','general') DEFAULT 'general',
    description  TEXT DEFAULT NULL,
    content      LONGTEXT DEFAULT NULL,
    file_path    VARCHAR(255) DEFAULT NULL,
    external_url VARCHAR(255) DEFAULT NULL,
    thumbnail    VARCHAR(255) DEFAULT NULL,
    author_id    INT UNSIGNED DEFAULT NULL,
    views        INT UNSIGNED NOT NULL DEFAULT 0,
    is_featured  TINYINT(1) NOT NULL DEFAULT 0,
    is_published TINYINT(1) NOT NULL DEFAULT 1,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL,
    FULLTEXT INDEX ft_resource (title, description),
    INDEX idx_category (category),
    INDEX idx_module   (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: university_reviews
-- ============================================================
CREATE TABLE IF NOT EXISTS university_reviews (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    university_id INT UNSIGNED NOT NULL,
    user_id       INT UNSIGNED NOT NULL,
    rating        TINYINT UNSIGNED NOT NULL COMMENT '1-5',
    title         VARCHAR(255) DEFAULT NULL,
    review        TEXT DEFAULT NULL,
    pros          TEXT DEFAULT NULL,
    cons          TEXT DEFAULT NULL,
    is_approved   TINYINT(1) NOT NULL DEFAULT 0,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (university_id) REFERENCES universities(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)       REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: mentor_reviews
-- ============================================================
CREATE TABLE IF NOT EXISTS mentor_reviews (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    mentor_id  INT UNSIGNED NOT NULL,
    student_id INT UNSIGNED NOT NULL,
    session_id INT UNSIGNED DEFAULT NULL,
    rating     TINYINT UNSIGNED NOT NULL COMMENT '1-5',
    review     TEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (mentor_id)  REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (student_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (session_id) REFERENCES sessions(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: audit_logs
-- ============================================================
CREATE TABLE IF NOT EXISTS audit_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED DEFAULT NULL,
    action      VARCHAR(100) NOT NULL,
    entity_type VARCHAR(100) DEFAULT NULL,
    entity_id   INT UNSIGNED DEFAULT NULL,
    old_values  JSON DEFAULT NULL,
    new_values  JSON DEFAULT NULL,
    ip_address  VARCHAR(45) DEFAULT NULL,
    user_agent  VARCHAR(500) DEFAULT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_user   (user_id),
    INDEX idx_action (action),
    INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: transition_checklists
-- ============================================================
CREATE TABLE IF NOT EXISTS transition_checklists (
    id           INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      INT UNSIGNED NOT NULL,
    item         VARCHAR(255) NOT NULL,
    category     VARCHAR(100) DEFAULT NULL,
    is_completed TINYINT(1) NOT NULL DEFAULT 0,
    completed_at DATETIME DEFAULT NULL,
    due_date     DATE DEFAULT NULL,
    sort_order   TINYINT UNSIGNED DEFAULT 0,
    created_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- TABLE: recommendation_logs
-- Track AI recommendation events
-- ============================================================
CREATE TABLE IF NOT EXISTS recommendation_logs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    type            ENUM('mentor','university','scholarship') NOT NULL,
    input_snapshot  JSON DEFAULT NULL,
    results         JSON DEFAULT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SEED DATA: Countries
-- ============================================================
INSERT INTO countries (name, code, continent) VALUES
('Ghana', 'GH', 'Africa'),
('Nigeria', 'NG', 'Africa'),
('Kenya', 'KE', 'Africa'),
('South Africa', 'ZA', 'Africa'),
('United Kingdom', 'GB', 'Europe'),
('United States', 'US', 'North America'),
('Canada', 'CA', 'North America'),
('Germany', 'DE', 'Europe'),
('Australia', 'AU', 'Oceania'),
('France', 'FR', 'Europe');

-- ============================================================
-- SEED DATA: Ghana Regions
-- ============================================================
INSERT INTO regions (country_id, name) VALUES
(1, 'Greater Accra'),
(1, 'Ashanti'),
(1, 'Western'),
(1, 'Eastern'),
(1, 'Central'),
(1, 'Northern'),
(1, 'Upper East'),
(1, 'Upper West'),
(1, 'Volta'),
(1, 'Brong-Ahafo'),
(1, 'Western North'),
(1, 'Ahafo'),
(1, 'Bono East'),
(1, 'Oti'),
(1, 'Savannah'),
(1, 'North East');

-- ============================================================
-- SEED DATA: Universities
-- ============================================================
INSERT INTO universities (name, slug, country_id, region_id, city, university_type, website, description, established_year, is_featured, status) VALUES
('University of Ghana', 'university-of-ghana', 1, 1, 'Accra', 'public', 'https://www.ug.edu.gh', 'The University of Ghana is the oldest and largest of the thirteen Ghanaian universities. It was founded in 1948 as the University College of the Gold Coast.', 1948, 1, 'active'),
('Kwame Nkrumah University of Science and Technology', 'knust', 1, 2, 'Kumasi', 'public', 'https://www.knust.edu.gh', 'KNUST is a public research university in Kumasi, Ghana. It was established in 1952 and is one of the top science and technology universities in Africa.', 1952, 1, 'active'),
('Ashesi University', 'ashesi-university', 1, 4, 'Berekuso', 'private', 'https://www.ashesi.edu.gh', 'Ashesi University is a private liberal arts university in Ghana, noted for its focus on ethics and leadership.', 2002, 1, 'active'),
('University of Cape Coast', 'university-of-cape-coast', 1, 5, 'Cape Coast', 'public', 'https://www.ucc.edu.gh', 'The University of Cape Coast is a public research university in Cape Coast, Ghana. It was founded in 1962.', 1962, 1, 'active'),
('Ghana Institute of Management and Public Administration', 'gimpa', 1, 1, 'Accra', 'public', 'https://www.gimpa.edu.gh', 'GIMPA is a public higher education institution in Ghana specializing in management and public administration.', 1961, 0, 'active'),
('University for Development Studies', 'uds', 1, 6, 'Tamale', 'public', 'https://www.uds.edu.gh', 'UDS is a public research university in northern Ghana, focusing on development-oriented education.', 1992, 0, 'active'),
('Central University', 'central-university', 1, 1, 'Accra', 'private', 'https://www.central.edu.gh', 'Central University is a private Christian university in Ghana.', 1988, 0, 'active'),
('University of Professional Studies', 'upsa', 1, 1, 'Accra', 'public', 'https://www.upsa.edu.gh', 'UPSA is a public university specializing in professional studies in Ghana.', 1965, 0, 'active');

-- ============================================================
-- SEED DATA: Faculties
-- ============================================================
INSERT INTO faculties (university_id, name, description) VALUES
(1, 'School of Engineering Sciences', 'Engineering and applied sciences'),
(1, 'School of Business', 'Business administration and management'),
(1, 'School of Social Sciences', 'Social sciences and humanities'),
(1, 'School of Medicine and Dentistry', 'Medical and dental studies'),
(1, 'School of Law', 'Legal studies'),
(2, 'College of Engineering', 'Engineering disciplines'),
(2, 'College of Science', 'Pure and applied sciences'),
(2, 'College of Art and Social Sciences', 'Art and social sciences'),
(2, 'College of Health Sciences', 'Health and medical sciences'),
(3, 'School of Business Administration', 'Business programs'),
(3, 'School of Engineering', 'Engineering programs'),
(4, 'School of Education', 'Teacher education and training'),
(4, 'School of Physical Sciences', 'Sciences and mathematics');

-- ============================================================
-- SEED DATA: Programs
-- ============================================================
INSERT INTO programs (university_id, faculty_id, name, degree_level, duration_years, description, min_aggregate, required_subjects, tuition_local, tuition_currency, is_active) VALUES
(1, 3, 'Computer Science', 'bachelor', 4, 'BSc Computer Science program covering software engineering, algorithms, and computing systems.', 12, '["Mathematics","Science","English Language"]', 3500.00, 'GHS', 1),
(1, 3, 'Information Technology', 'bachelor', 4, 'BSc Information Technology focusing on systems and network administration.', 14, '["Mathematics","Science","English Language"]', 3500.00, 'GHS', 1),
(1, 2, 'Business Administration', 'bachelor', 4, 'BBA program covering marketing, finance, management and entrepreneurship.', 16, '["Mathematics","English Language"]', 3200.00, 'GHS', 1),
(1, 4, 'Medicine and Surgery', 'bachelor', 6, 'MBChB - Medical training for future doctors.', 8, '["Biology","Chemistry","Mathematics","Physics","English Language"]', 6000.00, 'GHS', 1),
(1, 5, 'Law', 'bachelor', 4, 'LLB Law degree program.', 12, '["English Language","Literature","History","Government"]', 4000.00, 'GHS', 1),
(2, 6, 'Computer Engineering', 'bachelor', 4, 'BSc Computer Engineering combining hardware and software.', 12, '["Mathematics","Physics","English Language"]', 3800.00, 'GHS', 1),
(2, 6, 'Electrical Engineering', 'bachelor', 4, 'BSc Electrical Engineering.', 12, '["Mathematics","Physics","English Language"]', 3800.00, 'GHS', 1),
(2, 7, 'Statistics', 'bachelor', 4, 'BSc Statistics and actuarial science.', 14, '["Mathematics","English Language"]', 3200.00, 'GHS', 1),
(2, 9, 'Human Medicine', 'bachelor', 6, 'Medical program at KNUST.', 8, '["Biology","Chemistry","Mathematics","English Language"]', 5800.00, 'GHS', 1),
(3, 10, 'Computer Science', 'bachelor', 4, 'CS program at Ashesi with liberal arts approach.', 14, '["Mathematics","Science","English Language"]', 12000.00, 'GHS', 1),
(3, 10, 'Business Administration', 'bachelor', 4, 'BA at Ashesi with entrepreneurship focus.', 16, '["Mathematics","English Language"]', 11000.00, 'GHS', 1),
(4, 12, 'Education (Science)', 'bachelor', 4, 'BSc Education in Science subjects.', 16, '["Science","Mathematics","English Language"]', 2800.00, 'GHS', 1),
(4, 13, 'Mathematics', 'bachelor', 4, 'BSc Mathematics.', 14, '["Mathematics","Science","English Language"]', 2600.00, 'GHS', 1);

-- ============================================================
-- SEED DATA: Scholarships
-- ============================================================
INSERT INTO scholarships (name, slug, provider, category, scholarship_type, coverage, num_beneficiaries, description, eligibility_requirements, application_deadline, min_aggregate, financial_need, application_link, is_featured, status) VALUES
('Mastercard Foundation Scholars Program', 'mastercard-foundation-scholars', 'Mastercard Foundation', 'international', 'full', 'Full tuition, accommodation, stipend, travel', 500, 'The Mastercard Foundation Scholars Program enables academically talented young people from Africa to access quality education.', 'Academically talented, financially disadvantaged African students', '2025-03-31', 20, 1, 'https://mastercardfdn.org/scholars', 1, 'open'),
('Commonwealth Scholarship', 'commonwealth-scholarship', 'Commonwealth Scholarship Commission', 'international', 'full', 'Full tuition, flights, living allowance', 100, 'Commonwealth Scholarships for postgraduate study in the UK.', 'Citizens of Commonwealth countries, strong academic record', '2024-12-15', NULL, 0, 'https://cscuk.fcdo.gov.uk', 1, 'open'),
('DAAD Scholarship', 'daad-scholarship', 'German Academic Exchange Service', 'international', 'full', 'Tuition, monthly stipend, health insurance, travel', 200, 'DAAD scholarships for study and research in Germany.', 'Strong academic record, relevant work experience', '2025-10-15', NULL, 0, 'https://www.daad.de', 1, 'open'),
('Government of Ghana Scholarship', 'ghana-government-scholarship', 'Ghana Scholarship Secretariat', 'government', 'full', 'Full sponsorship for study abroad', 300, 'Government of Ghana scholarship for Ghanaian students to study abroad.', 'Ghanaian citizen, admitted to foreign university, strong academic performance', '2025-06-30', 16, 1, 'https://scholarships.gov.gh', 1, 'open'),
('University of Ghana Merit Scholarship', 'ug-merit-scholarship', 'University of Ghana', 'university_specific', 'partial', '50% tuition waiver', 50, 'Merit-based scholarship for incoming UG students with exceptional WASSCE results.', 'Aggregate 6 or better in WASSCE, admitted to UG', '2025-08-31', 6, 0, 'https://www.ug.edu.gh/scholarships', 1, 'open'),
('KNUST Presidential Scholarship', 'knust-presidential-scholarship', 'KNUST', 'university_specific', 'full', 'Full tuition, accommodation, monthly stipend', 20, 'KNUST Presidential Scholarship for top WASSCE students pursuing STEM.', 'Best aggregate in WASSCE, STEM program at KNUST', '2025-09-30', 4, 0, 'https://www.knust.edu.gh/scholarships', 1, 'open'),
('Ashesi University Need-Based Grant', 'ashesi-need-based-grant', 'Ashesi University', 'university_specific', 'partial', 'Up to 80% tuition reduction', 100, 'Need-based financial aid for students admitted to Ashesi.', 'Admitted to Ashesi, demonstrated financial need', '2025-08-01', NULL, 1, 'https://www.ashesi.edu.gh/financial-aid', 0, 'open'),
('Erasmus+ Scholarship', 'erasmus-plus', 'European Commission', 'international', 'full', 'Tuition, monthly stipend, travel', 150, 'Erasmus+ supports students studying in Europe.', 'Enrolled in participating institution, academic merit', '2025-04-30', NULL, 0, 'https://erasmus-plus.ec.europa.eu', 0, 'open');

-- Link some scholarships to universities
INSERT INTO scholarship_universities (scholarship_id, university_id) VALUES
(5, 1), -- UG Merit → UG
(6, 2), -- KNUST Presidential → KNUST
(7, 3); -- Ashesi Grant → Ashesi

-- ============================================================
-- SEED DATA: Career Categories
-- ============================================================
INSERT INTO career_categories (name, icon, description) VALUES
('Technology & Computing', 'fas fa-laptop-code', 'Careers in software, IT, data, and technology'),
('Health & Medicine', 'fas fa-heartbeat', 'Medical, nursing, pharmacy, public health careers'),
('Business & Finance', 'fas fa-chart-line', 'Banking, accounting, entrepreneurship, management'),
('Engineering', 'fas fa-cogs', 'Civil, electrical, mechanical, chemical engineering'),
('Education', 'fas fa-graduation-cap', 'Teaching, lecturing, academic research'),
('Law & Governance', 'fas fa-balance-scale', 'Legal practice, public service, policy'),
('Arts & Communication', 'fas fa-palette', 'Journalism, design, media, PR'),
('Science & Research', 'fas fa-flask', 'Biology, chemistry, physics, research');

-- ============================================================
-- SEED DATA: Careers
-- ============================================================
INSERT INTO careers (category_id, title, slug, description, industry, growth_outlook, skills_required, career_path) VALUES
(1, 'Software Engineer', 'software-engineer', 'Design, develop and maintain software applications and systems.', 'Technology', 'high', 'Python, Java, JavaScript, Problem Solving, Version Control', 'Junior Dev → Mid Developer → Senior → Tech Lead → CTO'),
(1, 'Data Scientist', 'data-scientist', 'Analyze complex data to help organizations make better decisions.', 'Technology', 'high', 'Python, R, Machine Learning, Statistics, SQL', 'Junior Analyst → Data Analyst → Data Scientist → Lead Data Scientist'),
(1, 'Cybersecurity Analyst', 'cybersecurity-analyst', 'Protect computer systems and networks from cyber threats.', 'Technology', 'high', 'Network Security, Ethical Hacking, Risk Assessment, Python', 'IT Support → Security Analyst → Senior Analyst → CISO'),
(2, 'Medical Doctor', 'medical-doctor', 'Diagnose and treat patients in clinical settings.', 'Healthcare', 'moderate', 'Clinical Knowledge, Communication, Decision Making, Empathy', 'Medical Student → Intern → Resident → Specialist'),
(2, 'Pharmacist', 'pharmacist', 'Dispense medications and counsel patients on drug use.', 'Healthcare', 'moderate', 'Pharmacology, Chemistry, Patient Care, Attention to Detail', 'Intern Pharmacist → Junior → Senior → Chief Pharmacist'),
(3, 'Accountant', 'accountant', 'Prepare and examine financial records and ensure accuracy.', 'Finance', 'moderate', 'Accounting, Excel, IFRS, Attention to Detail, Analysis', 'Junior Accountant → Senior → Finance Manager → CFO'),
(3, 'Entrepreneur', 'entrepreneur', 'Build and grow businesses that solve problems.', 'Business', 'high', 'Leadership, Risk Management, Marketing, Finance, Resilience', 'Ideation → Startup → Growth → Scale → Exit/IPO'),
(4, 'Civil Engineer', 'civil-engineer', 'Design and oversee construction of infrastructure.', 'Engineering', 'moderate', 'AutoCAD, Structural Analysis, Project Management, Math', 'Graduate → Junior → Project Engineer → Senior → Director'),
(5, 'Teacher', 'teacher', 'Educate students at various levels of schooling.', 'Education', 'moderate', 'Communication, Subject Knowledge, Patience, Creativity', 'Student Teacher → Classroom Teacher → HOD → Principal'),
(6, 'Lawyer', 'lawyer', 'Advise and represent clients in legal matters.', 'Law', 'moderate', 'Legal Research, Argumentation, Writing, Critical Thinking', 'Pupil → Associate → Senior Associate → Partner');

-- ============================================================
-- SEED DATA: Admin user
-- password: Admin@1234
-- ============================================================
INSERT INTO users (uuid, first_name, last_name, email, phone, password_hash, role, status, email_verified) VALUES
(UUID(), 'BOAFO', 'Administrator', 'admin@boafo.edu.gh', '+233200000000',
 '$2y$12$YKXs4E1GBG3z7hVOl9X7yumGTRv4Z0GoCOJkX4UjKOGLhpxg3d82a',
 'admin', 'active', 1);