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

START TRANSACTION;



--
-- Database: `mod_hr`
--

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

--
-- Table structure for table `appraisals`
--

CREATE TABLE `appraisals` (
  `id` int(11) NOT NULL,
  `employee_id` int(50) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  `department` varchar(50) DEFAULT NULL,
  `rank` varchar(50) DEFAULT NULL,
  `period` varchar(50) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `comments` text DEFAULT NULL,
  `upload_doc` text DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `appraisals`
--

INSERT INTO `appraisals` (`id`, `employee_id`, `name`, `department`, `rank`, `period`, `score`, `comments`, `upload_doc`, `status`) VALUES
(1, 1236547, 'Christian Davis', 'DMO', 'Senior Staff', 'Jan 2025', 7.00, 'work time', NULL, 'Pending');

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

--
-- Table structure for table `attendance`
--

CREATE TABLE `attendance` (
  `id` int(11) NOT NULL,
  `employee_id` int(50) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `rank` varchar(100) DEFAULT NULL,
  `clock_in` datetime DEFAULT NULL,
  `clock_out` datetime DEFAULT NULL,
  `status` enum('Present','Absent','Not Marked') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `attendance`
--

INSERT INTO `attendance` (`id`, `employee_id`, `name`, `department`, `rank`, `clock_in`, `clock_out`, `status`) VALUES
(7, 1236547, 'Christian Davis', 'DMO', 'Senior Staff', '2025-06-09 07:19:00', '2025-06-09 16:19:00', 'Present'),
(10, 1236547, 'Christian Davis', 'DMO', 'Senior Staff', '2025-06-10 08:06:00', '2025-06-10 17:07:00', 'Present'),
(11, 1236547, 'Christian Davis', 'DMO', 'Senior Staff', '2025-06-11 07:23:00', '2025-06-11 16:23:00', 'Present'),
(12, 2145785473, 'Richard A. Williams', 'ICT', 'General', '2025-06-12 07:45:00', '2025-06-12 17:20:00', 'Present'),
(14, 1236547, 'Christian Davis', 'DM0', 'Senior Staff', '2025-06-06 10:31:00', '2025-06-06 22:31:00', 'Absent'),
(15, 2145785473, 'Richard A. Williams', 'ICT', 'General', '2025-06-02 10:33:00', '2025-06-02 22:33:00', 'Present'),
(18, 1236547, 'Christian Davis', 'DM0', 'Senior Staff', '2025-06-15 23:23:00', '2025-06-15 17:29:00', 'Present'),
(21, 2145785473, 'Richard A. Williams', 'ICT', 'General', '2025-06-16 05:58:00', '2025-06-15 17:57:00', 'Absent');

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

--
-- Table structure for table `audit_trail`
--

CREATE TABLE `audit_trail` (
  `id` int(11) NOT NULL,
  `user_id` varchar(50) DEFAULT NULL,
  `username` varchar(100) DEFAULT NULL,
  `action` varchar(255) DEFAULT NULL,
  `target_table` varchar(100) DEFAULT NULL,
  `timestamp` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



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

--
-- Table structure for table `hr_policies`
--

CREATE TABLE `hr_policies` (
  `id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leave_requests`
--

CREATE TABLE `leave_requests` (
  `id` int(11) NOT NULL,
  `employee_id` int(50) NOT NULL,
  `rank` varchar(100) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `reason` text DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `upload_doc` text DEFAULT NULL,
  `created_at` timestamp(6) NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `leave_requests`
--

INSERT INTO `leave_requests` (`id`, `employee_id`, `rank`, `name`, `department`, `start_date`, `end_date`, `reason`, `status`, `upload_doc`, `created_at`) VALUES
(1, 1236547, 'Senior Staff', 'Christian Davis', 'DMO', '2025-06-10', '2025-06-11', 'sick', 'Pending', NULL, '2025-06-11 06:01:44.000000');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `appraisals`
--
ALTER TABLE `appraisals`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `attendance`
--
ALTER TABLE `attendance`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- Indexes for table `audit_trail`
--
ALTER TABLE `audit_trail`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `hr_policies`
--
ALTER TABLE `hr_policies`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `leave_requests`
--
ALTER TABLE `leave_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `employee_id` (`employee_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `appraisals`
--
ALTER TABLE `appraisals`
  ADD CONSTRAINT `appraisals_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `mod_emp` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
  ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `mod_emp` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `leave_requests`
--
ALTER TABLE `leave_requests`
  ADD CONSTRAINT `leave_requests_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `mod_emp` (`employee_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

