CREATE DATABASE IF NOT EXISTS mutiaragroup CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE mutiaragroup;

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);

INSERT INTO roles (name) VALUES ('admin'), ('staff'), ('approver');

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    login_id VARCHAR(20) NOT NULL UNIQUE,
    name VARCHAR(120) NOT NULL,
    email VARCHAR(150) NULL,
    password VARCHAR(255) NOT NULL,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

CREATE TABLE advance_salary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    approver_id INT NULL,
    nama VARCHAR(120) NOT NULL,
    jumlah DECIMAL(10,2) NOT NULL,
    tujuan TEXT NOT NULL,
    kenyataan_setuju TINYINT(1) DEFAULT 0,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    approver_remarks TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (approver_id) REFERENCES users(id)
);

CREATE TABLE leave_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    approver_id INT NULL,
    nama VARCHAR(120) NOT NULL,
    tempat_bekerja VARCHAR(150) NOT NULL,
    alasan TEXT NOT NULL,
    tarikh_mula DATE NOT NULL,
    tarikh_akhir DATE NOT NULL,
    jumlah_hari INT NOT NULL,
    kenyataan VARCHAR(50) NOT NULL,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    approver_remarks TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (approver_id) REFERENCES users(id)
);

CREATE TABLE claims (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    approver_id INT NULL,
    nama_staff VARCHAR(120) NOT NULL,
    jabatan VARCHAR(120) NOT NULL,
    tujuan TEXT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    catatan TEXT NULL,
    status ENUM('pending','approved','rejected') DEFAULT 'pending',
    approver_remarks TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (approver_id) REFERENCES users(id)
);

CREATE TABLE notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    is_read TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(120) NOT NULL,
    payload JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE company_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(150) NOT NULL,
    tagline VARCHAR(150) NULL,
    email VARCHAR(150) NULL,
    phone VARCHAR(50) NULL,
    address TEXT NULL,
    locations TEXT NULL,
    logo_path VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO users (role_id, login_id, name, email, password) VALUES
((SELECT id FROM roles WHERE name='admin'), '999999999999', 'System Admin', 'admin@mutiaragroup.com.my', '$2y$10$inQJqTlPGGC4/78WSQIT/OKLoInFIYNx5JdLffdIH/kWiuVVhCmmy'),
((SELECT id FROM roles WHERE name='approver'), '888888888888', 'Firdaus', 'firdaus@mutiaragroup.com.my', '$2y$10$YBVhT3GJDzpX257VD2WdWOA2JLhZAHh2EKPXrQr4HINNDvUCA9ShC'),
((SELECT id FROM roles WHERE name='staff'), '111111111111', 'Demo Staff', 'staff@mutiaragroup.com.my', '$2y$10$EUwVOtQjgiwI4M/MrMAUteOdGh48vO.GaShsWDIxpKi8ukRO6KkCm');

INSERT INTO company_settings (company_name, tagline, email, phone, address, logo_path)
VALUES ('Mutiara Group', 'Smart HR Portal', 'info@mutiaragroup.com.my', '+60 12-345 6789', '123, Jalan Contoh, 43000 Kajang, Selangor', NULL);

CREATE TABLE staff_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    ic_no VARCHAR(20) NOT NULL,
    address TEXT NULL,
    phone VARCHAR(50) NULL,
    hire_date DATE NULL,
    location VARCHAR(150) NULL,
    bank_name VARCHAR(100) NULL,
    account_no VARCHAR(50) NULL,
    allowed_leave INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

