-- ========================================================
-- MIGRASI #1: Skema Database Minimal CBT (Synced to v1.0.0)
-- Dibuat untuk: Admin, Guru, Operator, Siswa
-- ========================================================

-- 1. Tabel pengguna internal (Admin, Guru, Operator)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    nama VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash TEXT NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('admin', 'guru', 'operator')),
    created_at TIMESTAMP DEFAULT NOW(),
    last_login TIMESTAMP
);

-- 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()
);

-- 3. Data Siswa
CREATE TABLE siswa (
    id SERIAL PRIMARY KEY,
    siswa_id VARCHAR(50) UNIQUE NOT NULL,
    nama VARCHAR(100) NOT NULL,
    password_plain TEXT NOT NULL,
    level_id INT REFERENCES levels(id) ON DELETE SET NULL,
    group_id INT REFERENCES groups(id) ON DELETE SET NULL,
    program_id INT REFERENCES programs(id) ON DELETE SET NULL,
    agama VARCHAR(20) CHECK (agama IN ('Islam', 'Protestan', 'Katolik', 'Hindu', 'Buddha', 'Konghucu')),
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'blocked')),
    blocked_reason TEXT,
    blocked_at TIMESTAMP,
    photo_path TEXT,
    sesi VARCHAR(10) DEFAULT 'Sesi 1',
    last_activity TIMESTAMP,
    current_activity VARCHAR(50) DEFAULT 'offline',
    browser_info TEXT DEFAULT '',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);
COMMENT ON COLUMN siswa.sesi IS 'Sesi 1, Sesi 2, ..., Sesi 8';
COMMENT ON COLUMN siswa.last_activity IS 'Last activity timestamp, updated every 30s';
COMMENT ON COLUMN siswa.current_activity IS 'Current activity: online, exam, view_result, offline';

-- 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()
);

-- 6. Ujian
CREATE TABLE ujian (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    event_nama VARCHAR(100),
    mapel_id INT REFERENCES mapel(id) ON DELETE SET NULL,
    bank_id INT NOT NULL REFERENCES bank_soal(id) ON DELETE RESTRICT,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    durasi INT NOT NULL CHECK (durasi > 0),
    is_randomized BOOLEAN DEFAULT false,
    show_score BOOLEAN DEFAULT false,
    show_explanation BOOLEAN DEFAULT false,
    passing_grade NUMERIC(5,2),
    created_by INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    peserta_type VARCHAR(20) DEFAULT 'umum',
	agama VARCHAR(50),
    use_token BOOLEAN DEFAULT false,
    min_durasi INT DEFAULT 0,
    is_active BOOLEAN DEFAULT false,
    randomize_opsi BOOLEAN DEFAULT false,
    allow_unanswered BOOLEAN DEFAULT true,
    sesi VARCHAR(10) DEFAULT 'Sesi 1',
    token_auto_generate BOOLEAN DEFAULT false,
    token_interval INT DEFAULT 15,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CHECK (end_time > start_time)
);
COMMENT ON COLUMN ujian.peserta_type IS 'umum: all students in groups, spesifik: manually selected';
COMMENT ON COLUMN ujian.use_token IS 'require token to start exam';
COMMENT ON COLUMN ujian.min_durasi IS 'minimum exam duration in minutes (0 = no limit)';
COMMENT ON COLUMN ujian.is_active IS 'true = visible to students, false = hidden';
COMMENT ON COLUMN ujian.sesi IS 'Sesi 1, Sesi 2, ..., Sesi 8 (exam shift)';
COMMENT ON COLUMN ujian.token_auto_generate IS 'Auto regenerate tokens every X minutes';

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()
);

-- 7. Peserta Ujian
CREATE TABLE peserta_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,
    token VARCHAR(100),
    status VARCHAR(20) DEFAULT 'not_started',
    started_at TIMESTAMP,
    submitted_at TIMESTAMP,
    score NUMERIC(5,2) DEFAULT 0,
    score_irt DOUBLE PRECISION,
    violation_count INT DEFAULT 0 NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(ujian_id, siswa_id)
);
COMMENT ON COLUMN peserta_ujian.status IS 'not_started, ongoing, submitted, absent';

-- 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 token (
    id SERIAL PRIMARY KEY,
    token VARCHAR(5) NOT NULL,
    pemakai INT DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    expired_at TIMESTAMP NOT NULL
);
COMMENT ON TABLE token IS 'Universal token pool - 1 token for all exams';
COMMENT ON COLUMN token.token IS '5-char alphanumeric token';
COMMENT ON COLUMN token.pemakai IS 'Number of students who used this token';
COMMENT ON COLUMN token.expired_at IS 'Token expires 1 hour after creation';

CREATE TABLE privilege (
    id SERIAL PRIMARY KEY,
    config JSONB DEFAULT '{}' NOT NULL,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- ============================================
-- Indexes
-- ============================================
CREATE INDEX idx_siswa_siswa_id ON siswa(siswa_id);
CREATE INDEX idx_siswa_group ON siswa(group_id);
CREATE INDEX idx_soal_bank ON soal(bank_id);
CREATE INDEX idx_ujian_time ON ujian(start_time, end_time);
CREATE INDEX idx_jawaban_ujian_submission ON jawaban_ujian(siswa_id, ujian_id, is_submitted);
CREATE INDEX idx_peserta_ujian ON peserta_ujian(ujian_id, siswa_id);

-- ============================================
-- Data Awal & Default Record
-- ============================================

-- 1. Admin Default
INSERT INTO users (id, nama, email, password_hash, role) 
VALUES (
    1,
    'Admin Utama',
    'admin@hazen.cbt',
   '$2a$10$.udBSAlwmyRTyEOND2H/RO3qlegLKMu3lOBmPERfjoQS5bIAGXNGe',
    'admin'
) ON CONFLICT(id) DO NOTHING;

-- Fix sequence for users
SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

-- 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;

-- ============================================
-- Stored Procedures
-- ============================================
CREATE OR REPLACE FUNCTION public.cleanup_expired_tokens() RETURNS void
    LANGUAGE plpgsql
    AS $$
BEGIN
  UPDATE token 
  SET is_active = false 
  WHERE expired_at < NOW() AND is_active = true;
END;
$$;