CREATE DATABASE IF NOT EXISTS sip_tahkimussunnah;
USE sip_tahkimussunnah;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'staff') DEFAULT 'staff',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS positions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT
);

CREATE TABLE IF NOT EXISTS employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nip VARCHAR(20) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    gender ENUM('Laki-laki', 'Perempuan'),
    birth_place VARCHAR(100),
    birth_date DATE,
    address TEXT,
    phone VARCHAR(20),
    email VARCHAR(100),
    join_date DATE,
    status ENUM('Aktif', 'Non-Aktif') DEFAULT 'Aktif',
    position_id INT,
    department_id INT,
    photo VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (position_id) REFERENCES positions(id),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    date DATE NOT NULL,
    status ENUM('Hadir', 'Izin', 'Sakit', 'Alpa') DEFAULT 'Hadir',
    clock_in TIME,
    clock_out TIME,
    notes TEXT,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
    UNIQUE(employee_id, date)
);

CREATE TABLE IF NOT EXISTS payroll (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_id INT NOT NULL,
    month INT NOT NULL,
    year INT NOT NULL,
    basic_salary DECIMAL(15, 2) NOT NULL,
    allowance DECIMAL(15, 2) DEFAULT 0,
    deductions DECIMAL(15, 2) DEFAULT 0,
    total_salary DECIMAL(15, 2) NOT NULL,
    status ENUM('Dibayar', 'Belum Dibayar') DEFAULT 'Belum Dibayar',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT NOT NULL
);

-- Default data
INSERT INTO users (username, password, role) VALUES ('admin', '$2a$10$7/O8V3f3E4r5zK.8.o7.6.G9.J1y.2.3.4.5.6.7.8.9.0', 'admin'); -- password: admin (hashed version placeholder)
-- Note: In real setup, I will provide a way to hash this or use a script.

INSERT INTO positions (name) VALUES ('Kepala Sekolah'), ('Guru'), ('Staff Administrasi'), ('Keamanan'), ('Kebersihan');
INSERT INTO departments (name) VALUES ('Akademik'), ('Kesantrian'), ('Umum'), ('Keuangan');

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES 
('hero_title', 'Sistem Informasi Kepegawaian'),
('hero_subtitle', 'Pondok Pesantren Tahkimussunnah Al Islamy'),
('hero_desc', 'Portal terpadu untuk pengelolaan data kepegawaian, absensi, dan administrasi pesantren dengan mudah, aman, dan efisien.'),
('slider_images', '["/images/bg_pesantren_1.png", "/images/bg_pesantren_2.png", "/images/bg_pesantren_3.png"]');