-- Create database (if not already created)
-- CREATE DATABASE hotel_hr CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE hotel_hr;

CREATE TABLE IF NOT EXISTS staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id VARCHAR(32) NOT NULL UNIQUE,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender ENUM('Male','Female') NULL,
    dob DATE NULL,
    department VARCHAR(100) NULL,
    designation VARCHAR(100) NULL,
    email VARCHAR(120) UNIQUE NULL,
    phone VARCHAR(30) NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_by_user_id INT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id VARCHAR(32) NULL,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(120) UNIQUE NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','hr','staff') NOT NULL DEFAULT 'staff',
    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS staff_profiles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id VARCHAR(32) NOT NULL UNIQUE,
    address VARCHAR(255) NULL,
    state VARCHAR(100) NULL,
    lga VARCHAR(120) NULL,
    marital_status VARCHAR(50) NULL,
    nok_name VARCHAR(120) NULL,
    nok_phone VARCHAR(40) NULL,
    bank_name VARCHAR(120) NULL,
    bank_account_name VARCHAR(120) NULL,
    bank_account_number VARCHAR(40) NULL,
    photo_path VARCHAR(255) NULL,
    resume_path VARCHAR(255) NULL,
    certificate_path VARCHAR(255) NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

-- Seed an initial admin user (change password on first login)
-- Replace the password hash below with a freshly generated one if desired.
-- Default credentials: username `admin`, password `Admin@123`
INSERT INTO users (username, password_hash, role, must_change_password)
VALUES (
  'admin',
  '$2y$10$7M3wJfRZ5X1A8C6nZl8o2uQmQn8q2m6Gf0cJq6b8rW4mOQbQ5x9Qe', -- hash for Admin@123 (example)
  'admin',
  1
)
ON DUPLICATE KEY UPDATE username = username;