-- phpMyAdmin SQL Dump
-- version 5.2.2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Apr 19, 2026 at 03:00 AM
-- Server version: 10.6.25-MariaDB
-- PHP Version: 8.4.19

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `bestwest_portal`
--

-- --------------------------------------------------------

--
-- Table structure for table `staff`
--

CREATE TABLE `staff` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(32) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `other_names` varchar(50) NOT NULL,
  `gender` enum('Male','Female') DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `designation` varchar(100) DEFAULT NULL,
  `email` varchar(120) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_by_user_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `staff`
--

INSERT INTO `staff` (`id`, `staff_id`, `last_name`, `first_name`, `other_names`, `gender`, `dob`, `department`, `designation`, `email`, `phone`, `status`, `created_by_user_id`, `created_at`) VALUES
(9, 'STAFF-CA1A76', 'Jumbo', 'Rawlins', 'Minye Izoma', 'Male', '1993-01-24', 'IT', 'IT Manager', 'rawcoaster@gmail.com', '08135923901', 'active', 1, '2025-08-18 12:22:37'),
(35, 'BW-2508001', 'ANYALEWECHI', 'CHINYERE', 'MARGARET', 'Female', '1987-10-21', 'Admin', 'HR /ADMIN MANAGER', 'anyalewechichinyere@gmail.com', '08034807610', 'active', NULL, '2025-08-21 11:56:35');

-- --------------------------------------------------------

--
-- Table structure for table `staff_education`
--

CREATE TABLE `staff_education` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(32) NOT NULL,
  `institution` varchar(255) NOT NULL,
  `qualification` varchar(50) DEFAULT NULL,
  `degree` varchar(100) DEFAULT NULL,
  `field_of_study` varchar(100) DEFAULT NULL,
  `start_year` year(4) DEFAULT NULL,
  `end_year` year(4) DEFAULT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `staff_education`
--

INSERT INTO `staff_education` (`id`, `staff_id`, `institution`, `qualification`, `degree`, `field_of_study`, `start_year`, `end_year`, `attachment_path`, `created_at`) VALUES
(11, 'STAFF-CA1A76', 'Enugu State University', 'SSCE/GCE', NULL, 'Computer Science', '2016', '2019', 'uploads/30b40ff96bc95e86.jpg', '2025-08-19 13:22:56'),
(12, 'STAFF-CA1A76', 'Maurid Polytechnic', 'NECO', NULL, 'Design', '2023', '2023', NULL, '2025-08-19 13:22:56');

-- --------------------------------------------------------

--
-- Table structure for table `staff_emergency`
--

CREATE TABLE `staff_emergency` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(50) NOT NULL,
  `emergency_name` varchar(150) DEFAULT NULL,
  `emergency_relationship` varchar(100) DEFAULT NULL,
  `emergency_address` text DEFAULT NULL,
  `emergency_phone` varchar(50) DEFAULT NULL,
  `nok_name` varchar(150) DEFAULT NULL,
  `nok_relationship` varchar(100) DEFAULT NULL,
  `nok_contact` varchar(100) DEFAULT NULL,
  `nok_address` text DEFAULT NULL,
  `nok_beneficiaries` text DEFAULT NULL,
  `nok_declaration` text DEFAULT NULL,
  `worship_details` text DEFAULT NULL,
  `referral_source` varchar(100) DEFAULT NULL,
  `referral_name` varchar(150) DEFAULT NULL,
  `referral_phone` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `staff_employment`
--

CREATE TABLE `staff_employment` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(50) NOT NULL,
  `company` varchar(150) NOT NULL,
  `position` varchar(100) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

-- --------------------------------------------------------

--
-- Table structure for table `staff_identification`
--

CREATE TABLE `staff_identification` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(50) NOT NULL,
  `id_type` enum('National ID Card (NIN)','Voters Card','Valid Driver''s License','International Passport') NOT NULL,
  `id_number` varchar(100) NOT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `staff_identification`
--

INSERT INTO `staff_identification` (`id`, `staff_id`, `id_type`, `id_number`, `attachment_path`, `created_at`, `updated_at`) VALUES
(1, 'STAFF-CA1A76', 'National ID Card (NIN)', 'a112345', 'uploads/cbde780c5880eeb0.png', '2025-08-19 16:54:22', '2025-08-19 19:40:06');

-- --------------------------------------------------------

--
-- Table structure for table `staff_medical`
--

CREATE TABLE `staff_medical` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(50) NOT NULL,
  `height` varchar(20) DEFAULT NULL,
  `weight` varchar(20) DEFAULT NULL,
  `ailments` text DEFAULT NULL,
  `ailments_specify` text DEFAULT NULL,
  `state_of_residence` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `staff_medical`
--

INSERT INTO `staff_medical` (`id`, `staff_id`, `height`, `weight`, `ailments`, `ailments_specify`, `state_of_residence`, `created_at`, `updated_at`) VALUES
(1, 'STAFF-CA1A76', '9', '10', 'NO', '', 'Imo', '2025-08-19 17:16:59', '2025-08-19 17:16:59');

-- --------------------------------------------------------

--
-- Table structure for table `staff_profiles`
--

CREATE TABLE `staff_profiles` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(32) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `lga` varchar(120) DEFAULT NULL,
  `marital_status` varchar(50) DEFAULT NULL,
  `nok_name` varchar(120) DEFAULT NULL,
  `nok_phone` varchar(40) DEFAULT NULL,
  `bank_name` varchar(120) DEFAULT NULL,
  `bank_account_name` varchar(120) DEFAULT NULL,
  `bank_account_number` varchar(40) DEFAULT NULL,
  `bank_doc_path` varchar(255) DEFAULT NULL,
  `pfa_name` varchar(100) DEFAULT NULL,
  `pension_pin` varchar(50) DEFAULT NULL,
  `NIN` varchar(20) NOT NULL,
  `pension_doc_path` varchar(255) DEFAULT NULL,
  `photo_path` varchar(255) DEFAULT NULL,
  `resume_path` varchar(255) DEFAULT NULL,
  `certificate_path` varchar(255) DEFAULT NULL,
  `birthcert_path` varchar(50) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `spouse_name` varchar(255) DEFAULT NULL,
  `spouse_mobile` varchar(50) DEFAULT NULL,
  `spouse_address` text DEFAULT NULL,
  `id_type` varchar(100) DEFAULT NULL,
  `id_number` varchar(100) DEFAULT NULL,
  `id_doc_path` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `staff_profiles`
--

INSERT INTO `staff_profiles` (`id`, `staff_id`, `address`, `state`, `lga`, `marital_status`, `nok_name`, `nok_phone`, `bank_name`, `bank_account_name`, `bank_account_number`, `bank_doc_path`, `pfa_name`, `pension_pin`, `NIN`, `pension_doc_path`, `photo_path`, `resume_path`, `certificate_path`, `birthcert_path`, `updated_at`, `spouse_name`, `spouse_mobile`, `spouse_address`, `id_type`, `id_number`, `id_doc_path`) VALUES
(1, 'STAFF-CA1A76', 'Government School, No. 6 Vitch Street, behind Bonny, Island, Bonny 503101, Rivers', 'Rivers State', 'Bonny LGA', 'Single', 'Ibelema Jumbo', '08135923901', 'Guaranty Trust Bank', 'Rawlins Jumbo Izoma', '0151052287', 'uploads/8c658c5db1ec2679.png', '', '', '12355677889999', 'uploads/96471a271707ff12.png', 'uploads/cb4745346e8bd762.jpg', 'uploads/6c3f4fa750ccd9bd.pdf', 'uploads/173083bb9b9734e3.png', 'uploads/51d942fd375d8635.png', '2025-08-25 01:59:37', '', '', '', NULL, NULL, NULL);

-- --------------------------------------------------------

--
-- Table structure for table `staff_questionnaire`
--

CREATE TABLE `staff_questionnaire` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(50) NOT NULL,
  `standout` text DEFAULT NULL,
  `motivation` text DEFAULT NULL,
  `industry_understanding` tinyint(4) DEFAULT NULL,
  `hobbies` text DEFAULT NULL,
  `leadership` text DEFAULT NULL,
  `best_candidate` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `staff_questionnaire`
--

INSERT INTO `staff_questionnaire` (`id`, `staff_id`, `standout`, `motivation`, `industry_understanding`, `hobbies`, `leadership`, `best_candidate`, `created_at`, `updated_at`) VALUES
(1, 'STAFF-CA1A76', 'Goodwill', 'i dont know yet', 4, 'dancing', 'Manager', 'am rare you', '2025-08-19 17:12:48', '2025-08-20 09:07:26');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `staff_id` varchar(32) DEFAULT NULL,
  `username` varchar(100) NOT NULL,
  `email` varchar(120) DEFAULT 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 DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `staff_id`, `username`, `email`, `password_hash`, `role`, `must_change_password`, `last_login`, `created_at`) VALUES
(1, NULL, 'admin', NULL, '$2y$10$IDxB9z1abNEVQxG4GWcGhuR5.X.ZnIigRUJC/yRWtv14l3RHJLtAS', 'admin', 0, '2026-01-19 20:23:56', '2025-08-18 07:16:04'),
(3, 'STAFF-CA1A76', 'rawcoaster@gmail.com', 'rawcoaster@gmail.com', '$2y$10$oTSdKLw4A8DqjcfeBjQRsedt2Iv6gbrXVerK1WbHL79.8V4ctCah6', 'staff', 0, '2025-09-17 01:14:02', '2025-08-18 12:22:37'),
(19, 'BW-2508001', 'anyalewechichinyere@gmail.com', 'anyalewechichinyere@gmail.com', '$2y$10$9kHO95Tff2MBAMaR6c/Tv.JdyrEkiu7p6khvkXZoN9.0iZgjeNgua', 'staff', 0, '2025-08-21 12:01:37', '2025-08-21 11:56:35');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `staff`
--
ALTER TABLE `staff`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `staff_id` (`staff_id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `staff_education`
--
ALTER TABLE `staff_education`
  ADD PRIMARY KEY (`id`),
  ADD KEY `staff_id` (`staff_id`);

--
-- Indexes for table `staff_emergency`
--
ALTER TABLE `staff_emergency`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_emergency_staff` (`staff_id`);

--
-- Indexes for table `staff_employment`
--
ALTER TABLE `staff_employment`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_emp_staff` (`staff_id`);

--
-- Indexes for table `staff_identification`
--
ALTER TABLE `staff_identification`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_ident_staff` (`staff_id`);

--
-- Indexes for table `staff_medical`
--
ALTER TABLE `staff_medical`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_medical_staff` (`staff_id`);

--
-- Indexes for table `staff_profiles`
--
ALTER TABLE `staff_profiles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `staff_id` (`staff_id`);

--
-- Indexes for table `staff_questionnaire`
--
ALTER TABLE `staff_questionnaire`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_questionnaire_staff` (`staff_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `staff_id` (`staff_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `staff`
--
ALTER TABLE `staff`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=36;

--
-- AUTO_INCREMENT for table `staff_education`
--
ALTER TABLE `staff_education`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `staff_emergency`
--
ALTER TABLE `staff_emergency`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `staff_employment`
--
ALTER TABLE `staff_employment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `staff_identification`
--
ALTER TABLE `staff_identification`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `staff_medical`
--
ALTER TABLE `staff_medical`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `staff_profiles`
--
ALTER TABLE `staff_profiles`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `staff_questionnaire`
--
ALTER TABLE `staff_questionnaire`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `staff_education`
--
ALTER TABLE `staff_education`
  ADD CONSTRAINT `staff_education_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `staff_emergency`
--
ALTER TABLE `staff_emergency`
  ADD CONSTRAINT `fk_emergency_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE;

--
-- Constraints for table `staff_employment`
--
ALTER TABLE `staff_employment`
  ADD CONSTRAINT `fk_emp_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE;

--
-- Constraints for table `staff_identification`
--
ALTER TABLE `staff_identification`
  ADD CONSTRAINT `fk_ident_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE;

--
-- Constraints for table `staff_medical`
--
ALTER TABLE `staff_medical`
  ADD CONSTRAINT `fk_medical_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE;

--
-- Constraints for table `staff_profiles`
--
ALTER TABLE `staff_profiles`
  ADD CONSTRAINT `staff_profiles_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `staff_questionnaire`
--
ALTER TABLE `staff_questionnaire`
  ADD CONSTRAINT `fk_questionnaire_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `users_ibfk_1` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE SET NULL ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;