CREATE DATABASE IF NOT EXISTS db_attendance;
USE db_attendance;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nip VARCHAR(50) UNIQUE,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'guru', 'kepala_sekolah') NOT NULL,
    phone VARCHAR(20),
    profile_image VARCHAR(255),
    face_embedding LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    date DATE NOT NULL,
    check_in TIME,
    check_out TIME,
    lat VARCHAR(50),
    lng VARCHAR(50),
    image_check_in VARCHAR(255),
    image_check_out VARCHAR(255),
    status ENUM('hadir', 'terlambat', 'izin', 'sakit', 'alpa') DEFAULT 'hadir',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS schedules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    day VARCHAR(20) NOT NULL,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL
);

-- Insert sample admin
INSERT INTO users (name, email, password, role) VALUES 
('Admin Utama', 'admin@gmail.com', '$2y$10$857pa3XpZP31UFXhh0qYc.nYDLlJA5QZzcVx0bfBWdZCojJb0BFme', 'admin'); -- password: password

-- Insert sample guru
INSERT INTO users (name, email, password, role, nip) VALUES 
('Ahmad Sutrisno', 'guru@gmail.com', '$2y$10$857pa3XpZP31UFXhh0qYc.nYDLlJA5QZzcVx0bfBWdZCojJb0BFme', 'guru', '12345678');

-- Insert sample kepala sekolah
INSERT INTO users (name, email, password, role) VALUES 
('Kepala Sekolah', 'kepala@gmail.com', '$2y$10$857pa3XpZP31UFXhh0qYc.nYDLlJA5QZzcVx0bfBWdZCojJb0BFme', 'kepala_sekolah');

-- Sample Schedules
INSERT INTO schedules (day, start_time, end_time) VALUES 
('Senin', '07:00:00', '15:00:00'),
('Selasa', '07:00:00', '15:00:00'),
('Rabu', '07:00:00', '15:00:00'),
('Kamis', '07:00:00', '15:00:00'),
('Jumat', '07:00:00', '11:00:00'),
('Sabtu', '07:00:00', '13:00:00');

CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    school_lat VARCHAR(50),
    school_lng VARCHAR(50),
    radius INT DEFAULT 100,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO settings (school_lat, school_lng, radius) VALUES ('-7.6145', '110.7121', 100);