-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Sep 23, 2025 at 03:49 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET FOREIGN_KEY_CHECKS=0;
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: `perfexcrm`
--

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

--
-- Table structure for table `tblhr_assignments`
--

CREATE TABLE `tblhr_assignments` (
  `id` int(10) UNSIGNED NOT NULL,
  `candidate_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(191) NOT NULL,
  `description` text DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `status` enum('pending','submitted','approved','rejected') NOT NULL DEFAULT 'pending',
  `submitted_at` datetime DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblhr_assignments`
--

INSERT INTO `tblhr_assignments` (`id`, `candidate_id`, `title`, `description`, `file_path`, `status`, `submitted_at`, `approved_at`, `created_at`, `updated_at`) VALUES
(1, 2, 'ddsd', 'adsdsd', '', 'pending', NULL, NULL, '2025-09-06 05:16:46', '2025-09-06 05:16:46'),
(2, 2, 'Develop Clinic Web App', 'Test data', 'assignment_1757117986_Test.pdf', 'pending', NULL, NULL, '2025-09-06 05:49:46', '2025-09-06 05:49:46');

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

--
-- Table structure for table `tblhr_candidates`
--

CREATE TABLE `tblhr_candidates` (
  `id` int(10) UNSIGNED NOT NULL,
  `staff_id` int(11) DEFAULT NULL,
  `candidate_type` enum('permanent','freelancer','intern','contract') NOT NULL DEFAULT 'permanent',
  `first_name` varchar(191) NOT NULL,
  `last_name` varchar(191) NOT NULL,
  `email` varchar(191) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `stream_type` varchar(255) DEFAULT NULL,
  `job_role` varchar(255) DEFAULT NULL,
  `gender` varchar(255) DEFAULT NULL,
  `employee_type` varchar(255) DEFAULT NULL,
  `contract_type` varchar(255) DEFAULT NULL,
  `template_type` varchar(255) DEFAULT NULL,
  `department_type` varchar(255) DEFAULT NULL,
  `position_applied` varchar(191) DEFAULT NULL,
  `resume` text DEFAULT NULL,
  `skills` text DEFAULT NULL,
  `experience_years` int(11) DEFAULT NULL,
  `preferred_job_type` varchar(100) DEFAULT NULL,
  `rate_currency` varchar(10) DEFAULT NULL,
  `rate` decimal(10,2) DEFAULT NULL,
  `rate_type` varchar(50) DEFAULT NULL,
  `experience_month` int(11) DEFAULT 0,
  `education` text DEFAULT NULL,
  `status` enum('applied','shortlisted','interview','selected','rejected','hired') NOT NULL DEFAULT 'applied',
  `source` varchar(50) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `can_login` tinyint(1) NOT NULL DEFAULT 0,
  `login_enabled_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `created_by` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblhr_candidates`
--

INSERT INTO `tblhr_candidates` (`id`, `staff_id`, `candidate_type`, `first_name`, `last_name`, `email`, `phone`, `address`, `stream_type`, `job_role`, `gender`, `employee_type`, `contract_type`, `template_type`, `department_type`, `position_applied`, `resume`, `skills`, `experience_years`, `preferred_job_type`, `rate_currency`, `rate`, `rate_type`, `experience_month`, `education`, `status`, `source`, `notes`, `can_login`, `login_enabled_at`, `created_at`, `updated_at`, `created_by`) VALUES
(1, 2, 'permanent', 'John', 'Doe', 'johndoe@yopmail.com', '1234567890', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Full stack Developer', 'resume_2_1757115525_Test.pdf', 'node js', 10, NULL, NULL, NULL, NULL, 0, 'dfdf', 'applied', 'Google', 'ffdfsdfd', 0, NULL, '2025-09-06 04:41:07', '2025-09-06 05:08:45', 1),
(2, 3, 'freelancer', 'Alex', 'Parker', 'alexparker@yopmail.com', '1234567890', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'PHP Developer', '', 'PHP', 5, NULL, NULL, NULL, NULL, 0, 'Education', 'interview', 'Nothing', '', 0, NULL, '2025-09-06 05:10:25', '2025-09-06 05:15:16', 1),
(3, 4, 'intern', 'Deniel', 'Parker', 'deniel@yopmail.com', '7894561230', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'Designer', 'resume_4_1757136777_Test.pdf', 'Canva', 2, NULL, NULL, NULL, NULL, 0, '', 'applied', 'Linkein', 'Very good Candidate', 0, NULL, '2025-09-06 11:02:58', '2025-09-06 11:02:58', 1),
(25, NULL, 'permanent', 'Petra', 'Burton', 'feryquf@mailinator.com', '+1 (209) 733-8852', NULL, 'Reprehenderit commod', 'freelancer', 'female', 'intern', 'hourly', 'employment', 'Temporibus quasi imp', NULL, 'resume_1_1758620713_dummy-pdf_2.pdf', NULL, 10, 'freelancer', 'INR', 15000000.00, 'Month', 10, '', 'applied', NULL, '', 0, NULL, '2025-09-23 14:49:31', '2025-09-23 15:15:13', 1),
(29, NULL, 'permanent', 'Cole', 'Craig', 'jamoxuxu@mailinator.com', '+1 (886) 226-9868', NULL, 'Laborum Illo iure s', 'freelancer', 'male', 'employment', 'monthly', 'intern', 'Esse rerum voluptate', NULL, 'resume_1_1758621749_dummy-pdf_2.pdf', NULL, 2015, 'freelancer', 'USD', 24.00, 'Day', 5, '', 'applied', NULL, '', 0, NULL, '2025-09-23 15:32:17', '2025-09-23 15:32:29', 1),
(30, NULL, 'permanent', 'Irene', 'Wells', 'bujuhitylu@mailinator.com', '+1 (168) 571-6514', NULL, 'Irure obcaecati Nam', 'freelancer', 'female', 'intern', '', '', 'Molestiae corporis p', NULL, '', NULL, 0, '', '', 0.00, '', 0, '', 'applied', NULL, '', 0, NULL, '2025-09-23 15:33:00', '2025-09-23 15:33:00', 1),
(31, NULL, 'permanent', 'Gannon', 'Luna', 'genuzuby@mailinator.com', '+1 (438) 533-2277', NULL, 'Autem Nam eiusmod et', 'freelancer', 'female', 'intern', '', '', 'Anim qui maiores vel', NULL, '', NULL, 0, '', '', 0.00, '', 0, '', 'applied', NULL, '', 0, NULL, '2025-09-23 15:34:36', '2025-09-23 15:34:36', 1);

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

--
-- Table structure for table `tblhr_certifications`
--

CREATE TABLE `tblhr_certifications` (
  `id` int(10) UNSIGNED NOT NULL,
  `candidate_id` int(10) UNSIGNED NOT NULL,
  `certificate_type` varchar(191) NOT NULL,
  `certificate_number` varchar(100) DEFAULT NULL,
  `issued_at` datetime DEFAULT NULL,
  `expires_at` datetime DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `status` enum('draft','issued','expired','revoked') NOT NULL DEFAULT 'draft',
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblhr_certifications`
--

INSERT INTO `tblhr_certifications` (`id`, `candidate_id`, `certificate_type`, `certificate_number`, `issued_at`, `expires_at`, `file_path`, `status`, `created_at`, `updated_at`) VALUES
(1, 2, 'sdasd', 'sdsddddddddddddddddddddd', '2025-09-06 00:00:00', '2025-09-08 00:00:00', '', 'expired', '2025-09-06 05:17:32', '2025-09-06 05:27:26');

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

--
-- Table structure for table `tblhr_contracts`
--

CREATE TABLE `tblhr_contracts` (
  `id` int(10) UNSIGNED NOT NULL,
  `candidate_id` int(10) UNSIGNED DEFAULT NULL,
  `title` varchar(191) NOT NULL,
  `contract_type` varchar(50) NOT NULL DEFAULT 'employment',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `salary` decimal(15,2) DEFAULT NULL,
  `currency` varchar(10) DEFAULT NULL,
  `status` enum('draft','processing','signed','expired') NOT NULL DEFAULT 'draft',
  `file_path` varchar(255) DEFAULT NULL,
  `signed_at` datetime DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblhr_contracts`
--

INSERT INTO `tblhr_contracts` (`id`, `candidate_id`, `title`, `contract_type`, `start_date`, `end_date`, `salary`, `currency`, `status`, `file_path`, `signed_at`, `created_at`, `updated_at`) VALUES
(1, 2, 'Develop PHP rest API integration', 'employment', '2025-09-01', '2025-09-30', 5000.00, '', 'processing', 'contract_1757117925_Test.pdf', NULL, '2025-09-06 05:48:45', '2025-09-06 05:48:45');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `tblhr_assignments`
--
ALTER TABLE `tblhr_assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `candidate_id_idx` (`candidate_id`),
  ADD KEY `status_idx` (`status`);

--
-- Indexes for table `tblhr_candidates`
--
ALTER TABLE `tblhr_candidates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `staff_id_idx` (`staff_id`),
  ADD KEY `candidate_type_idx` (`candidate_type`),
  ADD KEY `status_idx` (`status`),
  ADD KEY `email_idx` (`email`),
  ADD KEY `can_login_idx` (`can_login`);

--
-- Indexes for table `tblhr_certifications`
--
ALTER TABLE `tblhr_certifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `candidate_id_idx` (`candidate_id`),
  ADD KEY `status_idx` (`status`);

--
-- Indexes for table `tblhr_contracts`
--
ALTER TABLE `tblhr_contracts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `candidate_id_idx` (`candidate_id`),
  ADD KEY `status_idx` (`status`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `tblhr_assignments`
--
ALTER TABLE `tblhr_assignments`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `tblhr_candidates`
--
ALTER TABLE `tblhr_candidates`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=32;

--
-- AUTO_INCREMENT for table `tblhr_certifications`
--
ALTER TABLE `tblhr_certifications`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `tblhr_contracts`
--
ALTER TABLE `tblhr_contracts`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `tblhr_assignments`
--
ALTER TABLE `tblhr_assignments`
  ADD CONSTRAINT `fk_hr_assignments_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `tblhr_candidates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblhr_candidates`
--
ALTER TABLE `tblhr_candidates`
  ADD CONSTRAINT `fk_hr_candidates_staff` FOREIGN KEY (`staff_id`) REFERENCES `tblstaff` (`staffid`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `tblhr_certifications`
--
ALTER TABLE `tblhr_certifications`
  ADD CONSTRAINT `fk_hr_certifications_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `tblhr_candidates` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tblhr_contracts`
--
ALTER TABLE `tblhr_contracts`
  ADD CONSTRAINT `fk_hr_contracts_candidate` FOREIGN KEY (`candidate_id`) REFERENCES `tblhr_candidates` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;
SET FOREIGN_KEY_CHECKS=1;
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 */;
