

-- 2. Master Data Akademik
CREATE TABLE levels (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE groups (
    id SERIAL PRIMARY KEY,
    level_id INT NOT NULL REFERENCES levels(id) ON DELETE CASCADE,
    code VARCHAR(50) NOT NULL,
    nama VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(level_id, code)
);

CREATE TABLE programs (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE mapel (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    kategori VARCHAR(50),
    program_id INT REFERENCES programs(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 4. Bank Soal
CREATE TYPE tipe_soal AS ENUM (
    'pg',
    'pgk',
    'isian',
    'essay',
    'benar',
    'matriks',
    'menjodohkan'
);

CREATE TABLE bank_soal (
    id SERIAL PRIMARY KEY,
    code VARCHAR(100) UNIQUE NOT NULL,
    title VARCHAR(255) NOT NULL,
    owner_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    mapel_id INT REFERENCES mapel(id) ON DELETE SET NULL,
    is_active BOOLEAN DEFAULT true,
    soal_pg INT DEFAULT 0 CHECK (soal_pg >= 0),
    soal_essay INT DEFAULT 0 CHECK (soal_essay >= 0),
    soal_pgk INT DEFAULT 0 CHECK (soal_pgk >= 0),
    soal_isian INT DEFAULT 0 CHECK (soal_isian >= 0),
    soal_benar INT DEFAULT 0 CHECK (soal_benar >= 0),
    soal_matriks INT DEFAULT 0 CHECK (soal_matriks >= 0),
    soal_menjodohkan INT DEFAULT 0 CHECK (soal_menjodohkan >= 0),
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE bank_soal_collaborators (
    id SERIAL PRIMARY KEY,
    bank_soal_id INT REFERENCES bank_soal(id) ON DELETE CASCADE,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 5. Soal
CREATE TABLE soal (
    id SERIAL PRIMARY KEY,
    bank_id INT NOT NULL REFERENCES bank_soal(id) ON DELETE CASCADE,
    tipe_soal tipe_soal NOT NULL,
    bobot INT,
    pertanyaan TEXT NOT NULL,
    opsi JSONB,
    kunci_jawaban JSONB,
    pembahasan TEXT,
    tingkat_kesulitan VARCHAR(20) CHECK (tingkat_kesulitan IN ('mudah', 'sedang', 'sulit')),
    is_active BOOLEAN DEFAULT true,
    created_by INT REFERENCES users(id) ON DELETE SET NULL,
    parsial BOOLEAN DEFAULT false NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE ujian_group (
    id SERIAL PRIMARY KEY,
    ujian_id INT NOT NULL REFERENCES ujian(id) ON DELETE CASCADE,
    group_id INT NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON TABLE ujian_group IS 'Assign groups to exam (for peserta_type = umum)';

CREATE TABLE jadwal_collaborators (
    id SERIAL PRIMARY KEY,
    jadwal_id INT REFERENCES ujian(id) ON DELETE CASCADE,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 8. Jawaban Siswa
CREATE TABLE jawaban_ujian (
    id SERIAL PRIMARY KEY,
    ujian_id INT NOT NULL REFERENCES ujian(id) ON DELETE CASCADE,
    siswa_id INT NOT NULL REFERENCES siswa(id) ON DELETE CASCADE,
    soal_id INT NOT NULL REFERENCES soal(id) ON DELETE CASCADE,
    answer_text TEXT,
    is_submitted BOOLEAN DEFAULT false,
    is_correct BOOLEAN,
    nilai NUMERIC(5,2),
    submitted_at TIMESTAMP,
    updated_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(ujian_id, siswa_id, soal_id)
);

-- 9. Log Aktivitas
CREATE TABLE audit_logs (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE SET NULL,
    siswa_id INT REFERENCES siswa(id) ON DELETE SET NULL,
    action VARCHAR(50) NOT NULL,
    resource_type VARCHAR(50),
    resource_id INT,
    details JSONB,
    ip_address INET,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 10. Konfigurasi Universal
CREATE TABLE keamanan (
    id SERIAL PRIMARY KEY,
    autoblock_enabled BOOLEAN DEFAULT false NOT NULL,
    max_violations INT DEFAULT 5 NOT NULL,
    logout_code_enabled BOOLEAN DEFAULT false NOT NULL,
    logout_code VARCHAR(10) DEFAULT '' NOT NULL,
    device_restriction_enabled BOOLEAN DEFAULT false,
    allowed_user_agents TEXT,
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE lembaga (
    id SERIAL PRIMARY KEY,
    nama VARCHAR(200) DEFAULT '' NOT NULL,
    alamat TEXT DEFAULT '',
    kota TEXT DEFAULT '',
    logo_path TEXT DEFAULT '',
    kop_path TEXT DEFAULT '',
    kontak TEXT DEFAULT '',
	uuid VARCHAR(36),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE tampilan (
    id SERIAL PRIMARY KEY,
    color_theme VARCHAR(20) DEFAULT 'blue' NOT NULL,
    welcome_text TEXT DEFAULT '',
    finish_text TEXT DEFAULT '',
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE privilege (
    id SERIAL PRIMARY KEY,
    config JSONB DEFAULT '{}' NOT NULL,
    updated_at TIMESTAMP DEFAULT NOW()
);
CREATE INDEX idx_soal_bank ON soal(bank_id);
CREATE INDEX idx_jawaban_ujian_submission ON jawaban_ujian(siswa_id, ujian_id, is_submitted);

-- 2. Keamanan
INSERT INTO keamanan (id, autoblock_enabled, max_violations, logout_code, logout_code_enabled) VALUES (1, false, 5, '', false) ON CONFLICT(id) DO NOTHING;

-- 3. Lembaga
INSERT INTO lembaga (id, nama) VALUES (1, 'Hazen CBT') ON CONFLICT(id) DO NOTHING;

-- 4. Tampilan
INSERT INTO tampilan (id, color_theme) VALUES (1, 'blue') ON CONFLICT(id) DO NOTHING;

-- 5. Privilege
INSERT INTO privilege (id, config) VALUES (1, '{}') ON CONFLICT(id) DO NOTHING;