
-- 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 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;
