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

START TRANSACTION;



--
-- Database: `perfex-restoration`
--

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

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

CREATE TABLE IF NOT EXISTS `tblhr_assignments` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `candidate_id_idx` (`candidate_id`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=3 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_candidate_contracts`
--

CREATE TABLE IF NOT EXISTS `tblhr_candidate_contracts` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `candidate_id_idx` (`candidate_id`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tblhr_candidate_contracts`
--

INSERT INTO `tblhr_candidate_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');

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

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

CREATE TABLE IF NOT EXISTS `tblhr_certifications` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `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,
  PRIMARY KEY (`id`),
  KEY `candidate_id_idx` (`candidate_id`),
  KEY `status_idx` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=2 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 IF NOT EXISTS `tblhr_contracts` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(191) NOT NULL,
  `details` longtext DEFAULT NULL,
  `first_page` varchar(255) DEFAULT NULL,
  `background_page` varchar(255) DEFAULT NULL,
  `last_page` varchar(255) DEFAULT NULL,
  `fields_list` longtext DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `created_by` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `tblhr_contracts` (`id`, `title`, `details`, `first_page`, `background_page`, `last_page`, `fields_list`, `created_at`, `updated_at`, `created_by`) VALUES
(2, 'Intern', '<p><strong>Lorem Ipsum</strong><span> is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry\'s standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the </span></p>', NULL, NULL, NULL, NULL, '2025-09-24 08:15:17', '2025-09-24 08:26:39', 1);

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

