-- Jalankan script ini di MySQL (db: absen_pkl)
-- Menambah kolom descriptor di users + membuat tabel absensi.

ALTER TABLE users
    ADD COLUMN IF NOT EXISTS face_descriptor LONGTEXT NULL AFTER foto,
    ADD COLUMN IF NOT EXISTS telepon VARCHAR(20) NULL AFTER email,
    ADD COLUMN IF NOT EXISTS alamat TEXT NULL AFTER telepon,
    ADD COLUMN IF NOT EXISTS tanggal_lahir DATE NULL AFTER alamat,
    ADD COLUMN IF NOT EXISTS jenis_kelamin ENUM('L', 'P') NULL AFTER tanggal_lahir,
    ADD COLUMN IF NOT EXISTS sekolah VARCHAR(100) NULL AFTER jenis_kelamin,
    ADD COLUMN IF NOT EXISTS jurusan VARCHAR(100) NULL AFTER sekolah,
    ADD COLUMN IF NOT EXISTS kelas VARCHAR(50) NULL AFTER jurusan;

CREATE TABLE IF NOT EXISTS absensi (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    waktu DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    foto VARCHAR(255) NULL,
    status ENUM('Hadir', 'Ditolak') NOT NULL DEFAULT 'Ditolak',
    jarak_meter DECIMAL(10,2) NULL,
    face_distance DECIMAL(10,4) NULL,
    catatan VARCHAR(255) NULL,
    created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_absensi_user_waktu (user_id, waktu),
    CONSTRAINT fk_absensi_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;