-- =============================================================================
-- PRESENSI DIGITAL CLIENT MYSQL — RECONCILED DATABASE SCHEMA
-- Architecture: Single-Client Architecture (No tenant_id, Isolated DB)
-- Engine: MySQL 5.7+ / MariaDB 10.3+ / MySQL 8.0+
-- Encoding: utf8mb4 / Collation: utf8mb4_unicode_ci
-- Phase: PHASE 2B.4 RECONCILED SCHEMA EXECUTION
-- =============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------------------------------
-- 1. TABEL: siswa (Data Pokok Peserta Didik)
-- PK Fisik: id BIGINT AUTO_INCREMENT
-- Unique Business Key: nisn VARCHAR(32) NOT NULL UNIQUE
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS siswa (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    nisn VARCHAR(32) NOT NULL,
    nis VARCHAR(32) NULL,
    nama VARCHAR(128) NOT NULL,
    kelas VARCHAR(64) NOT NULL DEFAULT '-',
    jurusan VARCHAR(64) NULL,
    jenis_kelamin VARCHAR(16) NULL,
    tanggal_lahir DATE NULL,
    agama VARCHAR(64) NULL,
    nama_ayah VARCHAR(128) NULL,
    nama_ibu VARCHAR(128) NULL,
    no_hp VARCHAR(32) NULL,
    alamat TEXT NULL,
    barcode VARCHAR(64) NULL,
    foto MEDIUMTEXT NULL,
    password VARCHAR(255) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_siswa_nisn (nisn),
    INDEX idx_siswa_nama (nama),
    INDEX idx_siswa_kelas (kelas),
    INDEX idx_siswa_nis (nis),
    INDEX idx_siswa_barcode (barcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 2. TABEL: guru (Data Tenaga Pendidik)
-- PK Fisik: id BIGINT AUTO_INCREMENT
-- Unique Business Key: username VARCHAR(64) NOT NULL UNIQUE
-- NIP: Nullable & Non-Unique (Akomodasi Guru Honorer / Non-PNS)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS guru (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    nip VARCHAR(32) NULL,
    nama VARCHAR(128) NOT NULL,
    jabatan VARCHAR(64) NOT NULL DEFAULT 'Guru',
    mapel VARCHAR(128) NULL,
    jenis_kelamin VARCHAR(16) NULL,
    no_hp VARCHAR(32) NULL,
    barcode VARCHAR(64) NULL,
    foto MEDIUMTEXT NULL,
    password VARCHAR(255) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_guru_username (username),
    INDEX idx_guru_nama (nama),
    INDEX idx_guru_nip (nip),
    INDEX idx_guru_barcode (barcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 3. TABEL: tendik (Data Tenaga Kependidikan / Staf Penunjang)
-- PK Fisik: id BIGINT AUTO_INCREMENT
-- Unique Business Key: username VARCHAR(64) NOT NULL UNIQUE
-- NIP: Nullable & Non-Unique
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS tendik (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(64) NOT NULL,
    nip VARCHAR(32) NULL,
    nama VARCHAR(128) NOT NULL,
    jabatan VARCHAR(64) NOT NULL DEFAULT 'Tendik',
    divisi VARCHAR(64) NULL,
    jenis_kelamin VARCHAR(16) NULL,
    no_hp VARCHAR(32) NULL,
    barcode VARCHAR(64) NULL,
    foto MEDIUMTEXT NULL,
    password VARCHAR(255) NULL,
    status VARCHAR(32) NOT NULL DEFAULT 'aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_tendik_username (username),
    INDEX idx_tendik_nama (nama),
    INDEX idx_tendik_nip (nip),
    INDEX idx_tendik_barcode (barcode)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 4. TABEL: attendance (Log Transaksional Absensi Polimorfik Harian)
-- Snapshot Identitas Kanonikal: actor_identifier VARCHAR(64) NOT NULL
-- Provenance Scanner: scanner_identifier VARCHAR(128) NULL
-- Catatan: TIDAK MENGGUNAKAN STATIC UNIQUE CONSTRAINT pada tanggal & actor
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS attendance (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tanggal DATE NOT NULL,
    actor_type VARCHAR(16) NOT NULL DEFAULT 'siswa',
    actor_identifier VARCHAR(64) NOT NULL,
    scanner_identifier VARCHAR(128) NULL DEFAULT NULL,
    nama VARCHAR(128) NOT NULL,
    kelompok VARCHAR(64) NOT NULL DEFAULT '-',
    jam_masuk VARCHAR(8) NOT NULL DEFAULT '-',
    jam_pulang VARCHAR(8) NOT NULL DEFAULT '-',
    scan_masuk VARCHAR(8) NOT NULL DEFAULT '-',
    scan_pulang VARCHAR(8) NOT NULL DEFAULT '-',
    status VARCHAR(32) NOT NULL DEFAULT 'Hadir',
    terlambat INT NOT NULL DEFAULT 0,
    metode VARCHAR(32) NOT NULL DEFAULT 'Barcode',
    keterangan VARCHAR(255) NULL DEFAULT 'Tepat Waktu',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_att_lookup (tanggal, actor_type, actor_identifier),
    INDEX idx_att_provenance (tanggal, scanner_identifier),
    INDEX idx_att_report (tanggal, kelompok, status),
    INDEX idx_att_history (actor_identifier, tanggal),
    INDEX idx_att_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- 5. TABEL: app_settings (Pengaturan Key-Value Operasional Sekolah)
-- PK Natural: setting_key VARCHAR(64)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS app_settings (
    setting_key VARCHAR(64) NOT NULL PRIMARY KEY,
    setting_value LONGTEXT NULL,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
