-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- 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_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');

--
-- 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_questionnaire`
--
ALTER TABLE `staff_questionnaire`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_questionnaire_staff` (`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_questionnaire`
--
ALTER TABLE `staff_questionnaire`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- 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_questionnaire`
--
ALTER TABLE `staff_questionnaire`
  ADD CONSTRAINT `fk_questionnaire_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE CASCADE;
COMMIT;

