-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Nov 08, 2025 at 11:46 AM
-- Server version: 10.4.28-MariaDB
-- PHP Version: 8.2.4

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: `gad_system`
--

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

--
-- Table structure for table `activities`
--

CREATE TABLE `activities` (
  `activity_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `venue` varchar(255) DEFAULT NULL,
  `activity_date` date NOT NULL,
  `start_time` time NOT NULL,
  `end_time` time NOT NULL,
  `registration_deadline` datetime DEFAULT NULL,
  `attendance_cutoff_time` time DEFAULT NULL COMMENT 'Time after which attendance is marked as late',
  `zoom_link` varchar(500) DEFAULT NULL,
  `training_hours` decimal(4,2) DEFAULT NULL,
  `category` enum('Gender Sensitivity','Women Empowerment','LGBTQ+ Rights','Anti-VAWC','Other') NOT NULL,
  `level` enum('Basic','Intermediate','Advanced') DEFAULT 'Basic',
  `target_participants` int(11) DEFAULT NULL,
  `budget` decimal(12,2) DEFAULT NULL,
  `quarter` enum('Q1','Q2','Q3','Q4') DEFAULT NULL,
  `year` int(11) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `requires_pre_test` tinyint(1) DEFAULT 1,
  `requires_post_test` tinyint(1) DEFAULT 1,
  `requires_evaluation` tinyint(1) DEFAULT 1,
  `created_by` int(11) 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=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `activities`
--

INSERT INTO `activities` (`activity_id`, `title`, `description`, `venue`, `activity_date`, `start_time`, `end_time`, `registration_deadline`, `attendance_cutoff_time`, `zoom_link`, `training_hours`, `category`, `level`, `target_participants`, `budget`, `quarter`, `year`, `is_active`, `requires_pre_test`, `requires_post_test`, `requires_evaluation`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 'Gender Sensitivity Training', 'A comprehensive training on gender sensitivity and awareness in the workplace.', 'PSAU Conference Hall', '2025-11-15', '09:00:00', '17:00:00', NULL, NULL, NULL, NULL, 'Gender Sensitivity', 'Basic', 100, 50000.00, 'Q4', 2025, 1, 1, 1, 1, 1, '2025-11-03 13:16:07', '2025-11-03 13:16:07'),
(2, 'Women Empowerment Seminar', 'Seminar focused on empowering women in leadership roles.', 'PSAU Auditorium', '2025-11-20', '13:00:00', '16:00:00', NULL, NULL, NULL, NULL, 'Women Empowerment', 'Basic', 50, 30000.00, 'Q4', 2025, 1, 1, 1, 1, 1, '2025-11-03 13:16:07', '2025-11-03 13:16:07'),
(3, 'GAD Planning Workshop', 'Workshop for planning GAD activities for the next academic year.', 'PSAU Board Room', '2025-11-25', '08:00:00', '12:00:00', NULL, NULL, NULL, NULL, 'Other', 'Basic', 30, 20000.00, 'Q4', 2025, 1, 1, 1, 1, 1, '2025-11-03 13:16:07', '2025-11-03 13:16:07');

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

--
-- Table structure for table `activity_attendance`
--

CREATE TABLE `activity_attendance` (
  `attendance_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `registration_id` int(11) DEFAULT NULL,
  `attendance_type` enum('qr_scan','walk_in') NOT NULL,
  `check_in_time` timestamp NOT NULL DEFAULT current_timestamp(),
  `status` enum('present','late','absent') DEFAULT 'present',
  `scanned_by` int(11) DEFAULT NULL COMMENT 'User who scanned the QR or registered walk-in',
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `activity_attendance`
--

INSERT INTO `activity_attendance` (`attendance_id`, `activity_id`, `user_id`, `registration_id`, `attendance_type`, `check_in_time`, `status`, `scanned_by`, `notes`) VALUES
(1, 2, 2, 5, 'qr_scan', '2025-11-04 09:56:24', 'present', 1, NULL);

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

--
-- Table structure for table `activity_pre_assignments`
--

CREATE TABLE `activity_pre_assignments` (
  `assignment_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `file_url` varchar(500) DEFAULT NULL,
  `due_date` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `activity_registrations`
--

CREATE TABLE `activity_registrations` (
  `registration_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `registration_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `is_approved` tinyint(1) DEFAULT 0,
  `approved_by` int(11) DEFAULT NULL,
  `approved_at` timestamp NULL DEFAULT NULL,
  `qr_code` varchar(255) DEFAULT NULL,
  `qr_code_generated_at` timestamp NULL DEFAULT NULL,
  `status` enum('pending','approved','rejected','cancelled') DEFAULT 'pending',
  `rejection_reason` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `activity_registrations`
--

INSERT INTO `activity_registrations` (`registration_id`, `activity_id`, `user_id`, `registration_date`, `is_approved`, `approved_by`, `approved_at`, `qr_code`, `qr_code_generated_at`, `status`, `rejection_reason`) VALUES
(1, 1, 2, '2025-11-03 13:25:29', 0, NULL, NULL, 'qr_1_1762176329.png', '2025-11-03 13:25:31', 'pending', NULL),
(2, 2, 3, '2025-11-03 13:25:58', 0, NULL, NULL, NULL, NULL, 'pending', NULL),
(3, 3, 4, '2025-11-03 13:25:58', 0, NULL, NULL, NULL, NULL, 'pending', NULL),
(4, 1, 3, '2025-11-03 13:25:58', 0, NULL, NULL, NULL, NULL, 'pending', NULL),
(5, 2, 2, '2025-11-04 08:46:27', 1, 1, '2025-11-04 08:58:02', 'qr_5_1762246682.png', '2025-11-04 08:58:04', 'approved', NULL);

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

--
-- Table structure for table `activity_target_groups`
--

CREATE TABLE `activity_target_groups` (
  `target_group_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `category` enum('Faculty','Non-Teaching','Service Personnel','All') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `assessment_questions`
--

CREATE TABLE `assessment_questions` (
  `question_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `question_text` text NOT NULL,
  `question_type` enum('multiple_choice','true_false','rating','text') NOT NULL,
  `options` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'For multiple choice questions' CHECK (json_valid(`options`)),
  `correct_answer` varchar(255) DEFAULT NULL COMMENT 'For pre/post tests',
  `order_number` int(11) DEFAULT NULL,
  `points` int(11) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `assessment_responses`
--

CREATE TABLE `assessment_responses` (
  `response_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `answer` text DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `assessment_results`
--

CREATE TABLE `assessment_results` (
  `result_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `total_score` decimal(5,2) DEFAULT NULL,
  `percentage` decimal(5,2) DEFAULT NULL,
  `passed` tinyint(1) DEFAULT NULL,
  `time_taken` int(11) DEFAULT NULL COMMENT 'Time taken in minutes',
  `completed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `assessment_templates`
--

CREATE TABLE `assessment_templates` (
  `template_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `assessment_type` enum('pre_test','post_test','evaluation') NOT NULL,
  `title` varchar(255) NOT NULL,
  `instructions` text DEFAULT NULL,
  `passing_score` int(11) DEFAULT 70,
  `time_limit` int(11) DEFAULT NULL COMMENT 'Time limit in minutes',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `certificates`
--

CREATE TABLE `certificates` (
  `certificate_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `certificate_number` varchar(100) NOT NULL,
  `issued_date` date NOT NULL,
  `certificate_file` varchar(255) DEFAULT NULL,
  `is_downloaded` tinyint(1) DEFAULT 0,
  `download_count` int(11) DEFAULT 0,
  `first_download_at` timestamp NULL DEFAULT NULL,
  `last_download_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `contact_messages`
--

CREATE TABLE `contact_messages` (
  `message_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `name` varchar(150) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `message` text NOT NULL,
  `status` enum('unread','read','replied') DEFAULT 'unread',
  `replied_at` timestamp NULL DEFAULT NULL,
  `replied_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `email_notifications`
--

CREATE TABLE `email_notifications` (
  `notification_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `email_type` enum('registration_confirmation','approval','pre_test','qr_code','post_test','certificate','reminder') NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `is_sent` tinyint(1) DEFAULT 0,
  `error_message` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `employee_profiles`
--

CREATE TABLE `employee_profiles` (
  `profile_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `employee_number` varchar(50) DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `middle_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) NOT NULL,
  `suffix` varchar(10) DEFAULT NULL,
  `gender` enum('Male','Female','Other') NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `position` varchar(150) DEFAULT NULL,
  `category` enum('Faculty','Non-Teaching','Service Personnel','Other') NOT NULL,
  `department` varchar(150) DEFAULT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `profile_image` 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=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `employee_profiles`
--

INSERT INTO `employee_profiles` (`profile_id`, `user_id`, `employee_number`, `first_name`, `middle_name`, `last_name`, `suffix`, `gender`, `date_of_birth`, `age`, `position`, `category`, `department`, `contact_number`, `address`, `profile_image`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 'Admin', NULL, 'User', NULL, 'Male', NULL, NULL, NULL, 'Faculty', NULL, NULL, NULL, NULL, '2025-11-03 13:15:11', '2025-11-03 13:15:11'),
(2, 2, NULL, 'John', NULL, 'Doe', NULL, 'Male', NULL, NULL, 'Professor', 'Faculty', NULL, '09171234567', NULL, NULL, '2025-11-03 13:15:34', '2025-11-03 13:15:34'),
(3, 3, NULL, 'Jane', NULL, 'Smith', NULL, 'Female', NULL, NULL, 'Administrative Officer', 'Non-Teaching', NULL, '09181234567', NULL, NULL, '2025-11-03 13:15:34', '2025-11-03 13:15:34'),
(4, 4, NULL, 'Maria', NULL, 'Garcia', NULL, 'Female', NULL, NULL, 'Associate Professor', 'Faculty', NULL, '09191234567', NULL, NULL, '2025-11-03 13:15:34', '2025-11-03 13:15:34'),
(5, 8, NULL, 'GAD', NULL, 'Secretariat', NULL, 'Female', NULL, NULL, NULL, 'Non-Teaching', NULL, NULL, NULL, NULL, '2025-11-03 13:30:31', '2025-11-03 13:30:31'),
(6, 9, NULL, 'Maria', 'Cruz', 'Santos', NULL, 'Female', NULL, NULL, NULL, 'Non-Teaching', NULL, '09171234567', NULL, NULL, '2025-11-03 13:43:46', '2025-11-03 13:43:46'),
(7, 10, NULL, 'Roberto', 'Dela Cruz', 'Reyes', NULL, 'Male', NULL, NULL, NULL, 'Non-Teaching', NULL, '09181234567', NULL, NULL, '2025-11-03 13:43:46', '2025-11-03 13:43:46'),
(8, 11, NULL, 'Li', 'M', 'Sevidal', NULL, 'Male', NULL, NULL, NULL, 'Service Personnel', NULL, NULL, NULL, NULL, '2025-11-04 09:11:56', '2025-11-04 09:11:56');

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

--
-- Table structure for table `evaluation_answers`
--

CREATE TABLE `evaluation_answers` (
  `answer_id` int(11) NOT NULL,
  `response_id` int(11) NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer_value` text DEFAULT NULL,
  `rating_value` int(11) DEFAULT NULL COMMENT 'For rating questions (1-5)',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `evaluation_forms`
--

CREATE TABLE `evaluation_forms` (
  `form_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `instructions` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `evaluation_forms`
--

INSERT INTO `evaluation_forms` (`form_id`, `activity_id`, `title`, `instructions`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 'Gender Sensitivity Training Evaluation', 'Please take a moment to evaluate this training. Your feedback helps us improve future programs.', 1, 1, '2025-11-04 08:39:42', '2025-11-04 08:39:42'),
(2, 2, 'Women Empowerment Workshop Evaluation', 'Your feedback is valuable! Please share your thoughts about the workshop.', 1, 1, '2025-11-04 08:39:42', '2025-11-04 08:39:42'),
(3, 3, 'GAD Planning and Budgeting Training Evaluation', 'Help us improve! Please provide your honest feedback about this training.', 1, 1, '2025-11-04 08:39:42', '2025-11-04 08:39:42'),
(4, 3, 'Test1', 'enter', 1, 1, '2025-11-04 09:15:08', '2025-11-04 09:15:08');

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

--
-- Table structure for table `evaluation_questions`
--

CREATE TABLE `evaluation_questions` (
  `question_id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `question_text` text NOT NULL,
  `question_type` enum('rating','multiple_choice','text','yes_no') NOT NULL DEFAULT 'rating',
  `options` text DEFAULT NULL COMMENT 'JSON array of options for multiple choice',
  `is_required` tinyint(1) DEFAULT 1,
  `order_num` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `evaluation_questions`
--

INSERT INTO `evaluation_questions` (`question_id`, `form_id`, `question_text`, `question_type`, `options`, `is_required`, `order_num`, `created_at`) VALUES
(1, 1, 'How would you rate the overall quality of this training?', 'rating', NULL, 1, 1, '2025-11-04 08:39:42'),
(2, 1, 'The training objectives were clearly defined and met.', 'rating', NULL, 1, 2, '2025-11-04 08:39:42'),
(3, 1, 'The facilitator was knowledgeable and engaging.', 'rating', NULL, 1, 3, '2025-11-04 08:39:42'),
(4, 1, 'The training materials were helpful and relevant.', 'rating', NULL, 1, 4, '2025-11-04 08:39:42'),
(5, 1, 'The training duration was appropriate.', 'rating', NULL, 1, 5, '2025-11-04 08:39:42'),
(6, 1, 'Would you recommend this training to others?', 'yes_no', NULL, 1, 6, '2025-11-04 08:39:42'),
(7, 1, 'What did you find most valuable about this training?', 'text', NULL, 1, 7, '2025-11-04 08:39:42'),
(8, 1, 'What suggestions do you have for improving this training?', 'text', NULL, 0, 8, '2025-11-04 08:39:42'),
(9, 1, 'How will you apply what you learned in your work?', 'text', NULL, 1, 9, '2025-11-04 08:39:42'),
(10, 2, 'Overall, how would you rate this workshop?', 'rating', NULL, 1, 1, '2025-11-04 08:39:42'),
(11, 2, 'The workshop content was relevant to my needs.', 'rating', NULL, 1, 2, '2025-11-04 08:39:42'),
(12, 2, 'The facilitator effectively engaged participants.', 'rating', NULL, 1, 3, '2025-11-04 08:39:42'),
(13, 2, 'The workshop venue and facilities were adequate.', 'rating', NULL, 1, 4, '2025-11-04 08:39:42'),
(14, 2, 'I gained new knowledge and skills from this workshop.', 'rating', NULL, 1, 5, '2025-11-04 08:39:42'),
(15, 2, 'Did the workshop meet your expectations?', 'yes_no', NULL, 1, 6, '2025-11-04 08:39:42'),
(16, 2, 'What was the most impactful part of the workshop?', 'text', NULL, 1, 7, '2025-11-04 08:39:42'),
(17, 2, 'What topics would you like to see in future workshops?', 'text', NULL, 0, 8, '2025-11-04 08:39:42'),
(18, 3, 'How would you rate the overall training experience?', 'rating', NULL, 1, 1, '2025-11-04 08:39:42'),
(19, 3, 'The training objectives were clearly communicated.', 'rating', NULL, 1, 2, '2025-11-04 08:39:42'),
(20, 3, 'The content was well-organized and easy to follow.', 'rating', NULL, 1, 3, '2025-11-04 08:39:42'),
(21, 3, 'The facilitator demonstrated expertise in the subject.', 'rating', NULL, 1, 4, '2025-11-04 08:39:42'),
(22, 3, 'The training materials and resources were useful.', 'rating', NULL, 1, 5, '2025-11-04 08:39:42'),
(23, 3, 'The training schedule and timing were appropriate.', 'rating', NULL, 1, 6, '2025-11-04 08:39:42'),
(24, 3, 'I feel confident applying GAD planning and budgeting concepts.', 'rating', NULL, 1, 7, '2025-11-04 08:39:42'),
(25, 3, 'Would you attend similar trainings in the future?', 'yes_no', NULL, 1, 8, '2025-11-04 08:39:42'),
(26, 3, 'What specific skills or knowledge did you gain?', 'text', NULL, 1, 9, '2025-11-04 08:39:42'),
(27, 3, 'How can we improve future GAD trainings?', 'text', NULL, 0, 10, '2025-11-04 08:39:42');

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

--
-- Table structure for table `evaluation_responses`
--

CREATE TABLE `evaluation_responses` (
  `response_id` int(11) NOT NULL,
  `form_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `registration_id` int(11) DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `gpb_forms`
--

CREATE TABLE `gpb_forms` (
  `gpb_id` int(11) NOT NULL,
  `year` int(11) NOT NULL,
  `activity_title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `target_date` date DEFAULT NULL,
  `estimated_budget` decimal(12,2) DEFAULT NULL,
  `target_participants` int(11) DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `created_by` int(11) 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=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `monitoring_forms`
--

CREATE TABLE `monitoring_forms` (
  `monitoring_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `gpb_item` varchar(255) DEFAULT NULL,
  `actual_date` date DEFAULT NULL,
  `actual_budget` decimal(12,2) DEFAULT NULL,
  `actual_participants` int(11) DEFAULT NULL,
  `accomplishments` text DEFAULT NULL,
  `challenges` text DEFAULT NULL,
  `recommendations` text DEFAULT NULL,
  `submitted_by` int(11) DEFAULT NULL,
  `submitted_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `post_tests`
--

CREATE TABLE `post_tests` (
  `test_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `instructions` text DEFAULT NULL,
  `passing_score` int(11) DEFAULT 60,
  `time_limit` int(11) DEFAULT 30 COMMENT 'Time limit in minutes',
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `post_tests`
--

INSERT INTO `post_tests` (`test_id`, `activity_id`, `title`, `instructions`, `passing_score`, `time_limit`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 'Gender Sensitivity Post-Test', 'This test will assess what you have learned from the Gender Sensitivity Training. Please answer all questions based on what you learned during the activity.', 70, 30, 1, 1, '2025-11-04 08:29:51', '2025-11-04 08:29:51'),
(2, 2, 'Women Empowerment Post-Assessment', 'This assessment will measure your understanding of women empowerment concepts after the workshop.', 70, 20, 1, 1, '2025-11-04 08:29:51', '2025-11-04 08:29:51'),
(3, 3, 'GAD Planning and Budgeting Post-Test', 'This test evaluates your understanding of GAD planning and budgeting after the training.', 75, 25, 1, 1, '2025-11-04 08:29:51', '2025-11-04 08:29:51');

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

--
-- Table structure for table `post_test_responses`
--

CREATE TABLE `post_test_responses` (
  `response_id` int(11) NOT NULL,
  `test_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `registration_id` int(11) DEFAULT NULL,
  `started_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `submitted_at` timestamp NULL DEFAULT NULL,
  `score` decimal(5,2) DEFAULT 0.00,
  `total_points` int(11) DEFAULT 0,
  `percentage` decimal(5,2) DEFAULT 0.00,
  `passed` tinyint(1) DEFAULT 0,
  `time_taken` int(11) DEFAULT NULL COMMENT 'Time taken in seconds'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `post_test_responses`
--

INSERT INTO `post_test_responses` (`response_id`, `test_id`, `user_id`, `activity_id`, `registration_id`, `started_at`, `submitted_at`, `score`, `total_points`, `percentage`, `passed`, `time_taken`) VALUES
(1, 1, 2, 1, NULL, '2025-11-04 09:02:43', '2024-03-15 07:30:00', 8.00, 9, 89.00, 1, 1400),
(2, 1, 3, 1, NULL, '2025-11-04 09:02:43', '2024-03-15 08:00:00', 9.00, 9, 100.00, 1, 1100),
(3, 1, 4, 1, NULL, '2025-11-04 09:02:43', '2024-03-15 08:30:00', 7.00, 9, 78.00, 1, 1600);

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

--
-- Table structure for table `pre_tests`
--

CREATE TABLE `pre_tests` (
  `test_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `instructions` text DEFAULT NULL,
  `passing_score` int(11) DEFAULT 60,
  `time_limit` int(11) DEFAULT 30 COMMENT 'Time limit in minutes',
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pre_tests`
--

INSERT INTO `pre_tests` (`test_id`, `activity_id`, `title`, `instructions`, `passing_score`, `time_limit`, `is_active`, `created_by`, `created_at`, `updated_at`) VALUES
(1, 1, 'Gender Sensitivity Pre-Test', 'This test will assess your current knowledge about gender sensitivity and equality. Please answer all questions honestly. You have 30 minutes to complete this test.', 60, 30, 1, 1, '2025-11-04 08:24:05', '2025-11-04 08:24:05'),
(2, 2, 'Women Empowerment Pre-Assessment', 'This assessment will help us understand your current knowledge about women empowerment and rights. Please answer honestly.', 60, 20, 1, 1, '2025-11-04 08:24:05', '2025-11-04 08:24:05'),
(3, 3, 'GAD Planning and Budgeting Pre-Test', 'This test assesses your understanding of GAD planning and budgeting concepts.', 70, 25, 1, 1, '2025-11-04 08:24:05', '2025-11-04 08:24:05');

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

--
-- Table structure for table `pre_test_responses`
--

CREATE TABLE `pre_test_responses` (
  `response_id` int(11) NOT NULL,
  `test_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_id` int(11) NOT NULL,
  `registration_id` int(11) DEFAULT NULL,
  `started_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `submitted_at` timestamp NULL DEFAULT NULL,
  `score` decimal(5,2) DEFAULT 0.00,
  `total_points` int(11) DEFAULT 0,
  `percentage` decimal(5,2) DEFAULT 0.00,
  `passed` tinyint(1) DEFAULT 0,
  `time_taken` int(11) DEFAULT NULL COMMENT 'Time taken in seconds'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `pre_test_responses`
--

INSERT INTO `pre_test_responses` (`response_id`, `test_id`, `user_id`, `activity_id`, `registration_id`, `started_at`, `submitted_at`, `score`, `total_points`, `percentage`, `passed`, `time_taken`) VALUES
(1, 1, 2, 1, NULL, '2025-11-04 09:02:43', '2024-03-10 02:30:00', 6.00, 7, 86.00, 1, 1200),
(2, 1, 3, 1, NULL, '2025-11-04 09:02:43', '2024-03-10 03:15:00', 7.00, 7, 100.00, 1, 900),
(3, 1, 4, 1, NULL, '2025-11-04 09:02:43', '2024-03-10 06:20:00', 3.00, 7, 43.00, 0, 1500);

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

--
-- Table structure for table `resource_library`
--

CREATE TABLE `resource_library` (
  `resource_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `resource_type` enum('manual','poster','video') NOT NULL,
  `file_url` varchar(500) DEFAULT NULL,
  `thumbnail_url` varchar(500) DEFAULT NULL,
  `file_size` bigint(20) DEFAULT NULL,
  `uploaded_by` int(11) DEFAULT NULL,
  `upload_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `setting_id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `system_settings`
--

INSERT INTO `system_settings` (`setting_id`, `setting_key`, `setting_value`, `description`, `updated_by`, `updated_at`) VALUES
(1, 'site_name', 'PSAU GAD Information System', 'System name', NULL, '2025-11-03 13:13:38'),
(2, 'site_email', 'gad@psau.edu.ph', 'System email address', NULL, '2025-11-03 13:13:38'),
(3, 'attendance_grace_period', '20', 'Grace period for attendance in minutes', NULL, '2025-11-03 13:13:38'),
(4, 'auto_approve_psau', '1', 'Auto-approve PSAU accounts', NULL, '2025-11-03 13:13:38'),
(5, 'certificate_template', 'default', 'Certificate template to use', NULL, '2025-11-03 13:13:38'),
(6, 'smtp_host', '', 'SMTP host for email', NULL, '2025-11-03 13:13:38'),
(7, 'smtp_port', '587', 'SMTP port', NULL, '2025-11-03 13:13:38'),
(8, 'smtp_username', '', 'SMTP username', NULL, '2025-11-03 13:13:38'),
(9, 'smtp_password', '', 'SMTP password', NULL, '2025-11-03 13:13:38');

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

--
-- Table structure for table `test_answers`
--

CREATE TABLE `test_answers` (
  `answer_id` int(11) NOT NULL,
  `response_id` int(11) NOT NULL,
  `test_type` enum('pre_test','post_test') NOT NULL,
  `question_id` int(11) NOT NULL,
  `answer_text` text DEFAULT NULL,
  `is_correct` tinyint(1) DEFAULT 0,
  `points_earned` decimal(5,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `test_answers`
--

INSERT INTO `test_answers` (`answer_id`, `response_id`, `test_type`, `question_id`, `answer_text`, `is_correct`, `points_earned`, `created_at`) VALUES
(1, 1, 'pre_test', 1, 'A', 1, 1.00, '2025-11-04 09:02:43'),
(2, 1, 'pre_test', 2, 'True', 1, 1.00, '2025-11-04 09:02:43'),
(3, 1, 'pre_test', 3, 'C', 1, 1.00, '2025-11-04 09:02:43'),
(4, 1, 'pre_test', 4, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(5, 1, 'pre_test', 5, 'False', 1, 1.00, '2025-11-04 09:02:43'),
(6, 1, 'pre_test', 6, 'A', 0, 0.00, '2025-11-04 09:02:43'),
(7, 1, 'pre_test', 7, 'D', 1, 1.00, '2025-11-04 09:02:43'),
(8, 2, 'pre_test', 1, 'A', 1, 1.00, '2025-11-04 09:02:43'),
(9, 2, 'pre_test', 2, 'True', 1, 1.00, '2025-11-04 09:02:43'),
(10, 2, 'pre_test', 3, 'C', 1, 1.00, '2025-11-04 09:02:43'),
(11, 2, 'pre_test', 4, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(12, 2, 'pre_test', 5, 'False', 1, 1.00, '2025-11-04 09:02:43'),
(13, 2, 'pre_test', 6, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(14, 2, 'pre_test', 7, 'D', 1, 1.00, '2025-11-04 09:02:43'),
(15, 3, 'pre_test', 1, 'B', 0, 0.00, '2025-11-04 09:02:43'),
(16, 3, 'pre_test', 2, 'False', 0, 0.00, '2025-11-04 09:02:43'),
(17, 3, 'pre_test', 3, 'C', 1, 1.00, '2025-11-04 09:02:43'),
(18, 3, 'pre_test', 4, 'A', 0, 0.00, '2025-11-04 09:02:43'),
(19, 3, 'pre_test', 5, 'False', 1, 1.00, '2025-11-04 09:02:43'),
(20, 3, 'pre_test', 6, 'A', 0, 0.00, '2025-11-04 09:02:43'),
(21, 3, 'pre_test', 7, 'D', 1, 1.00, '2025-11-04 09:02:43'),
(22, 1, 'post_test', 8, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(23, 1, 'post_test', 9, 'True', 1, 1.00, '2025-11-04 09:02:43'),
(24, 1, 'post_test', 10, 'C', 1, 1.00, '2025-11-04 09:02:43'),
(25, 1, 'post_test', 11, 'A', 1, 1.00, '2025-11-04 09:02:43'),
(26, 1, 'post_test', 12, 'False', 1, 1.00, '2025-11-04 09:02:43'),
(27, 1, 'post_test', 13, 'D', 1, 1.00, '2025-11-04 09:02:43'),
(28, 1, 'post_test', 14, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(29, 1, 'post_test', 15, 'True', 1, 1.00, '2025-11-04 09:02:43'),
(30, 1, 'post_test', 16, 'A', 0, 0.00, '2025-11-04 09:02:43'),
(31, 2, 'post_test', 8, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(32, 2, 'post_test', 9, 'True', 1, 1.00, '2025-11-04 09:02:43'),
(33, 2, 'post_test', 10, 'C', 1, 1.00, '2025-11-04 09:02:43'),
(34, 2, 'post_test', 11, 'A', 1, 1.00, '2025-11-04 09:02:43'),
(35, 2, 'post_test', 12, 'False', 1, 1.00, '2025-11-04 09:02:43'),
(36, 2, 'post_test', 13, 'D', 1, 1.00, '2025-11-04 09:02:43'),
(37, 2, 'post_test', 14, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(38, 2, 'post_test', 15, 'True', 1, 1.00, '2025-11-04 09:02:43'),
(39, 2, 'post_test', 16, 'A', 1, 1.00, '2025-11-04 09:02:43'),
(40, 3, 'post_test', 8, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(41, 3, 'post_test', 9, 'True', 1, 1.00, '2025-11-04 09:02:43'),
(42, 3, 'post_test', 10, 'C', 1, 1.00, '2025-11-04 09:02:43'),
(43, 3, 'post_test', 11, 'B', 0, 0.00, '2025-11-04 09:02:43'),
(44, 3, 'post_test', 12, 'False', 1, 1.00, '2025-11-04 09:02:43'),
(45, 3, 'post_test', 13, 'D', 1, 1.00, '2025-11-04 09:02:43'),
(46, 3, 'post_test', 14, 'B', 1, 1.00, '2025-11-04 09:02:43'),
(47, 3, 'post_test', 15, 'False', 0, 0.00, '2025-11-04 09:02:43'),
(48, 3, 'post_test', 16, 'A', 1, 1.00, '2025-11-04 09:02:43');

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

--
-- Table structure for table `test_questions`
--

CREATE TABLE `test_questions` (
  `question_id` int(11) NOT NULL,
  `test_id` int(11) NOT NULL,
  `test_type` enum('pre_test','post_test') NOT NULL,
  `question_text` text NOT NULL,
  `question_type` enum('multiple_choice','true_false','essay') NOT NULL DEFAULT 'multiple_choice',
  `option_a` varchar(500) DEFAULT NULL,
  `option_b` varchar(500) DEFAULT NULL,
  `option_c` varchar(500) DEFAULT NULL,
  `option_d` varchar(500) DEFAULT NULL,
  `correct_answer` varchar(10) DEFAULT NULL COMMENT 'A, B, C, D, or TRUE/FALSE',
  `points` int(11) DEFAULT 1,
  `order_num` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `test_questions`
--

INSERT INTO `test_questions` (`question_id`, `test_id`, `test_type`, `question_text`, `question_type`, `option_a`, `option_b`, `option_c`, `option_d`, `correct_answer`, `points`, `order_num`, `created_at`) VALUES
(1, 1, 'pre_test', 'What is gender?', 'multiple_choice', 'Biological differences between males and females', 'Social and cultural roles, behaviors, and attributes that society considers appropriate for men and women', 'The same as sex', 'None of the above', 'B', 1, 1, '2025-11-04 08:24:05'),
(2, 1, 'pre_test', 'Gender equality means treating everyone exactly the same regardless of their gender.', 'true_false', NULL, NULL, NULL, NULL, 'FALSE', 1, 2, '2025-11-04 08:24:05'),
(3, 1, 'pre_test', 'Which of the following is an example of gender stereotyping?', 'multiple_choice', 'Assuming women are naturally better at caregiving', 'Believing men should not show emotions', 'Thinking certain jobs are only for men or women', 'All of the above', 'D', 1, 3, '2025-11-04 08:24:05'),
(4, 1, 'pre_test', 'GAD stands for:', 'multiple_choice', 'Gender and Development', 'Gender Against Discrimination', 'General Administrative Department', 'Gender Awareness Day', 'A', 1, 4, '2025-11-04 08:24:05'),
(5, 1, 'pre_test', 'Gender-based violence only affects women.', 'true_false', NULL, NULL, NULL, NULL, 'FALSE', 1, 5, '2025-11-04 08:24:05'),
(6, 1, 'pre_test', 'What is the main goal of gender mainstreaming?', 'multiple_choice', 'To promote women only', 'To integrate gender perspectives into all policies and programs', 'To create separate programs for men and women', 'To eliminate all gender differences', 'B', 1, 6, '2025-11-04 08:24:05'),
(7, 1, 'pre_test', 'The Philippines has a Magna Carta of Women.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 7, '2025-11-04 08:24:05'),
(8, 1, 'pre_test', 'Which is NOT a dimension of gender equality?', 'multiple_choice', 'Equal access to resources', 'Equal opportunities', 'Equal outcomes', 'Equal physical strength', 'D', 1, 8, '2025-11-04 08:24:05'),
(9, 1, 'pre_test', 'Gender sensitivity means being aware of and responsive to gender issues.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 9, '2025-11-04 08:24:05'),
(10, 1, 'pre_test', 'Explain in your own words why gender sensitivity is important in the workplace.', 'essay', NULL, NULL, NULL, NULL, NULL, 2, 10, '2025-11-04 08:24:05'),
(11, 2, 'pre_test', 'Women empowerment refers to:', 'multiple_choice', 'Giving women power over men', 'Increasing women\'s ability to make choices and transform those choices into desired actions', 'Making women work harder', 'Separating women from men', 'B', 1, 1, '2025-11-04 08:24:05'),
(12, 2, 'pre_test', 'Economic empowerment of women includes access to financial resources and decision-making.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 2, '2025-11-04 08:24:05'),
(13, 2, 'pre_test', 'Which is a key area of women empowerment?', 'multiple_choice', 'Economic participation', 'Political participation', 'Education and health', 'All of the above', 'D', 1, 3, '2025-11-04 08:24:05'),
(14, 2, 'pre_test', 'The glass ceiling refers to:', 'multiple_choice', 'A type of building material', 'Invisible barriers preventing women from advancing to top positions', 'A women\'s organization', 'A government policy', 'B', 1, 4, '2025-11-04 08:24:05'),
(15, 2, 'pre_test', 'Women\'s rights are human rights.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 5, '2025-11-04 08:24:05'),
(16, 3, 'pre_test', 'GAD Budget refers to:', 'multiple_choice', 'A separate budget for women only', 'At least 5% of total agency budget for GAD programs', 'Money given to GAD focal persons', 'Optional budget allocation', 'B', 1, 1, '2025-11-04 08:24:05'),
(17, 3, 'pre_test', 'All government agencies are required to have a GAD Plan and Budget.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 2, '2025-11-04 08:24:05'),
(18, 3, 'pre_test', 'The GAD Plan and Budget should be:', 'multiple_choice', 'Integrated into the agency\'s overall plans and budget', 'Kept separate from regular planning', 'Only for women employees', 'Optional for agencies', 'A', 1, 3, '2025-11-04 08:24:05'),
(19, 3, 'pre_test', 'PCW stands for:', 'multiple_choice', 'Philippine Commission on Women', 'Philippine Council for Women', 'Philippine Committee on Women', 'Philippine Center for Women', 'A', 1, 4, '2025-11-04 08:24:05'),
(20, 3, 'pre_test', 'Gender-responsive budgeting means allocating equal amounts to men and women.', 'true_false', NULL, NULL, NULL, NULL, 'FALSE', 1, 5, '2025-11-04 08:24:05'),
(21, 3, 'pre_test', 'What is the purpose of the GAD Focal Point System?', 'multiple_choice', 'To monitor and evaluate GAD programs', 'To ensure gender mainstreaming in the agency', 'To coordinate GAD activities', 'All of the above', 'D', 1, 6, '2025-11-04 08:24:05'),
(22, 1, 'post_test', 'Gender refers to:', 'multiple_choice', 'Biological sex', 'Socially constructed roles and behaviors', 'Physical characteristics', 'Genetic makeup', 'B', 1, 1, '2025-11-04 08:29:51'),
(23, 1, 'post_test', 'Gender mainstreaming means integrating gender perspectives into all policies and programs.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 2, '2025-11-04 08:29:51'),
(24, 1, 'post_test', 'Which of the following promotes gender equality in the workplace?', 'multiple_choice', 'Equal pay for equal work', 'Equal opportunities for promotion', 'Zero tolerance for harassment', 'All of the above', 'D', 1, 3, '2025-11-04 08:29:51'),
(25, 1, 'post_test', 'The Magna Carta of Women was enacted in what year?', 'multiple_choice', '2007', '2008', '2009', '2010', 'C', 1, 4, '2025-11-04 08:29:51'),
(26, 1, 'post_test', 'Gender-based violence can happen to anyone regardless of gender.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 5, '2025-11-04 08:29:51'),
(27, 1, 'post_test', 'What is the primary goal of GAD programs?', 'multiple_choice', 'To favor women over men', 'To achieve gender equality and equity', 'To create separate programs', 'To eliminate gender differences', 'B', 1, 6, '2025-11-04 08:29:51'),
(28, 1, 'post_test', 'Gender sensitivity training is only important for women.', 'true_false', NULL, NULL, NULL, NULL, 'FALSE', 1, 7, '2025-11-04 08:29:51'),
(29, 1, 'post_test', 'Which is a key principle of gender equality?', 'multiple_choice', 'Equal access to resources', 'Equal participation in decision-making', 'Equal benefits from development', 'All of the above', 'D', 1, 8, '2025-11-04 08:29:51'),
(30, 1, 'post_test', 'Stereotyping limits opportunities for both men and women.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 9, '2025-11-04 08:29:51'),
(31, 1, 'post_test', 'Based on what you learned, how can you promote gender sensitivity in your workplace? Provide specific examples.', 'essay', NULL, NULL, NULL, NULL, NULL, 2, 10, '2025-11-04 08:29:51'),
(32, 2, 'post_test', 'Women empowerment is about:', 'multiple_choice', 'Giving women control over men', 'Enabling women to make strategic life choices', 'Making women independent from family', 'Promoting women only', 'B', 1, 1, '2025-11-04 08:29:51'),
(33, 2, 'post_test', 'Economic empowerment includes access to credit, property rights, and income-generating activities.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 2, '2025-11-04 08:29:51'),
(34, 2, 'post_test', 'The five dimensions of women empowerment include:', 'multiple_choice', 'Economic, social, political, legal, and psychological', 'Only economic and social', 'Only political and legal', 'Only psychological', 'A', 1, 3, '2025-11-04 08:29:51'),
(35, 2, 'post_test', 'What does the \"glass ceiling\" represent?', 'multiple_choice', 'A physical barrier in buildings', 'Invisible barriers preventing women from reaching top positions', 'A type of discrimination law', 'A women\'s support group', 'B', 1, 4, '2025-11-04 08:29:51'),
(36, 2, 'post_test', 'Women\'s empowerment benefits only women.', 'true_false', NULL, NULL, NULL, NULL, 'FALSE', 1, 5, '2025-11-04 08:29:51'),
(37, 2, 'post_test', 'Which is NOT a strategy for women empowerment?', 'multiple_choice', 'Education and skills training', 'Access to leadership positions', 'Limiting women to traditional roles', 'Promoting women\'s rights', 'C', 1, 6, '2025-11-04 08:29:51'),
(38, 2, 'post_test', 'Describe two ways you can support women empowerment in your community.', 'essay', NULL, NULL, NULL, NULL, NULL, 2, 7, '2025-11-04 08:29:51'),
(39, 3, 'post_test', 'The minimum GAD budget allocation for government agencies is:', 'multiple_choice', '3% of total budget', '5% of total budget', '10% of total budget', '15% of total budget', 'B', 1, 1, '2025-11-04 08:29:51'),
(40, 3, 'post_test', 'GAD Plan and Budget must be submitted annually to PCW.', 'true_false', NULL, NULL, NULL, NULL, 'TRUE', 1, 2, '2025-11-04 08:29:51'),
(41, 3, 'post_test', 'The GAD Plan and Budget should address:', 'multiple_choice', 'Client-focused programs', 'Organization-focused programs', 'Both client and organization-focused programs', 'Neither client nor organization programs', 'C', 1, 3, '2025-11-04 08:29:51'),
(42, 3, 'post_test', 'PCW stands for:', 'multiple_choice', 'Philippine Commission on Women', 'Philippine Council for Women', 'Philippine Committee on Women', 'Philippine Center for Women', 'A', 1, 4, '2025-11-04 08:29:51'),
(43, 3, 'post_test', 'Gender-responsive budgeting means allocating exactly 50% to men and 50% to women.', 'true_false', NULL, NULL, NULL, NULL, 'FALSE', 1, 5, '2025-11-04 08:29:51'),
(44, 3, 'post_test', 'The GAD Focal Point System is responsible for:', 'multiple_choice', 'Monitoring GAD implementation', 'Coordinating GAD activities', 'Ensuring gender mainstreaming', 'All of the above', 'D', 1, 6, '2025-11-04 08:29:51'),
(45, 3, 'post_test', 'Gender analysis is important in planning because:', 'multiple_choice', 'It identifies different needs of men and women', 'It ensures programs benefit everyone equitably', 'It helps allocate resources effectively', 'All of the above', 'D', 1, 7, '2025-11-04 08:29:51'),
(46, 3, 'post_test', 'The GAD budget can be used for any program as long as it mentions gender.', 'true_false', NULL, NULL, NULL, NULL, 'FALSE', 1, 8, '2025-11-04 08:29:51');

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

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

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('admin','employee','secretariat') NOT NULL,
  `is_psau_account` tinyint(1) DEFAULT 0,
  `is_approved` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_login` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `users` (`user_id`, `email`, `password_hash`, `role`, `is_psau_account`, `is_approved`, `is_active`, `created_at`, `updated_at`, `last_login`) VALUES
(1, 'admin@psau.edu.ph', '$2y$10$hBhiQKyr1emtbFBYAg3ZdOByZU2ppwyho4P.gcq21ivNQOlMyOC8i', 'admin', 1, 1, 1, '2025-11-03 13:15:11', '2025-11-04 09:55:47', '2025-11-04 09:55:47'),
(2, 'john.doe@psau.edu.ph', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'employee', 1, 1, 1, '2025-11-03 13:15:34', '2025-11-04 09:55:22', '2025-11-04 09:55:22'),
(3, 'jane.smith@psau.edu.ph', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'employee', 1, 1, 1, '2025-11-03 13:15:34', '2025-11-03 13:15:34', NULL),
(4, 'maria.garcia@psau.edu.ph', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'employee', 1, 1, 1, '2025-11-03 13:15:34', '2025-11-03 13:15:34', NULL),
(8, 'secretariat@psau.edu.ph', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'secretariat', 0, 1, 1, '2025-11-03 13:30:31', '2025-11-04 08:14:13', '2025-11-04 08:14:13'),
(9, 'secretariat2@psau.edu.ph', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'secretariat', 0, 1, 1, '2025-11-03 13:43:46', '2025-11-03 13:43:46', NULL),
(10, 'secretariat3@psau.edu.ph', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'secretariat', 0, 1, 1, '2025-11-03 13:43:46', '2025-11-03 13:43:46', NULL),
(11, 'lisevidal@psau.edu.ph', '$2y$10$m9ar2IvxYJossKq9jqouzOs8oDxLkh/tFUI51fOf/4JhP7chiZcjW', 'employee', 1, 1, 1, '2025-11-04 09:11:56', '2025-11-04 09:12:03', '2025-11-04 09:12:03');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activities`
--
ALTER TABLE `activities`
  ADD PRIMARY KEY (`activity_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_date` (`activity_date`),
  ADD KEY `idx_category` (`category`),
  ADD KEY `idx_active` (`is_active`);

--
-- Indexes for table `activity_attendance`
--
ALTER TABLE `activity_attendance`
  ADD PRIMARY KEY (`attendance_id`),
  ADD UNIQUE KEY `unique_attendance` (`activity_id`,`user_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `registration_id` (`registration_id`),
  ADD KEY `scanned_by` (`scanned_by`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `activity_pre_assignments`
--
ALTER TABLE `activity_pre_assignments`
  ADD PRIMARY KEY (`assignment_id`),
  ADD KEY `activity_id` (`activity_id`);

--
-- Indexes for table `activity_registrations`
--
ALTER TABLE `activity_registrations`
  ADD PRIMARY KEY (`registration_id`),
  ADD UNIQUE KEY `unique_registration` (`activity_id`,`user_id`),
  ADD UNIQUE KEY `qr_code` (`qr_code`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `approved_by` (`approved_by`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `activity_target_groups`
--
ALTER TABLE `activity_target_groups`
  ADD PRIMARY KEY (`target_group_id`),
  ADD KEY `activity_id` (`activity_id`);

--
-- Indexes for table `assessment_questions`
--
ALTER TABLE `assessment_questions`
  ADD PRIMARY KEY (`question_id`),
  ADD KEY `template_id` (`template_id`);

--
-- Indexes for table `assessment_responses`
--
ALTER TABLE `assessment_responses`
  ADD PRIMARY KEY (`response_id`),
  ADD KEY `template_id` (`template_id`),
  ADD KEY `question_id` (`question_id`),
  ADD KEY `activity_id` (`activity_id`),
  ADD KEY `idx_user_activity` (`user_id`,`activity_id`);

--
-- Indexes for table `assessment_results`
--
ALTER TABLE `assessment_results`
  ADD PRIMARY KEY (`result_id`),
  ADD UNIQUE KEY `unique_result` (`template_id`,`user_id`,`activity_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `activity_id` (`activity_id`);

--
-- Indexes for table `assessment_templates`
--
ALTER TABLE `assessment_templates`
  ADD PRIMARY KEY (`template_id`),
  ADD KEY `activity_id` (`activity_id`);

--
-- Indexes for table `certificates`
--
ALTER TABLE `certificates`
  ADD PRIMARY KEY (`certificate_id`),
  ADD UNIQUE KEY `certificate_number` (`certificate_number`),
  ADD UNIQUE KEY `unique_certificate` (`user_id`,`activity_id`),
  ADD KEY `activity_id` (`activity_id`);

--
-- Indexes for table `contact_messages`
--
ALTER TABLE `contact_messages`
  ADD PRIMARY KEY (`message_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `replied_by` (`replied_by`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `email_notifications`
--
ALTER TABLE `email_notifications`
  ADD PRIMARY KEY (`notification_id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `employee_profiles`
--
ALTER TABLE `employee_profiles`
  ADD PRIMARY KEY (`profile_id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD UNIQUE KEY `employee_number` (`employee_number`),
  ADD KEY `idx_category` (`category`),
  ADD KEY `idx_gender` (`gender`);

--
-- Indexes for table `evaluation_answers`
--
ALTER TABLE `evaluation_answers`
  ADD PRIMARY KEY (`answer_id`),
  ADD KEY `response_id` (`response_id`),
  ADD KEY `question_id` (`question_id`);

--
-- Indexes for table `evaluation_forms`
--
ALTER TABLE `evaluation_forms`
  ADD PRIMARY KEY (`form_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_eval_form_activity` (`activity_id`);

--
-- Indexes for table `evaluation_questions`
--
ALTER TABLE `evaluation_questions`
  ADD PRIMARY KEY (`question_id`),
  ADD KEY `form_id` (`form_id`);

--
-- Indexes for table `evaluation_responses`
--
ALTER TABLE `evaluation_responses`
  ADD PRIMARY KEY (`response_id`),
  ADD UNIQUE KEY `unique_user_form` (`form_id`,`user_id`),
  ADD KEY `activity_id` (`activity_id`),
  ADD KEY `registration_id` (`registration_id`),
  ADD KEY `idx_eval_response_user` (`user_id`,`activity_id`);

--
-- Indexes for table `gpb_forms`
--
ALTER TABLE `gpb_forms`
  ADD PRIMARY KEY (`gpb_id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `monitoring_forms`
--
ALTER TABLE `monitoring_forms`
  ADD PRIMARY KEY (`monitoring_id`),
  ADD KEY `activity_id` (`activity_id`),
  ADD KEY `submitted_by` (`submitted_by`);

--
-- Indexes for table `post_tests`
--
ALTER TABLE `post_tests`
  ADD PRIMARY KEY (`test_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_post_test_activity` (`activity_id`);

--
-- Indexes for table `post_test_responses`
--
ALTER TABLE `post_test_responses`
  ADD PRIMARY KEY (`response_id`),
  ADD UNIQUE KEY `unique_user_test` (`test_id`,`user_id`),
  ADD KEY `activity_id` (`activity_id`),
  ADD KEY `registration_id` (`registration_id`),
  ADD KEY `idx_post_response_user` (`user_id`,`activity_id`);

--
-- Indexes for table `pre_tests`
--
ALTER TABLE `pre_tests`
  ADD PRIMARY KEY (`test_id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_pre_test_activity` (`activity_id`);

--
-- Indexes for table `pre_test_responses`
--
ALTER TABLE `pre_test_responses`
  ADD PRIMARY KEY (`response_id`),
  ADD UNIQUE KEY `unique_user_test` (`test_id`,`user_id`),
  ADD KEY `activity_id` (`activity_id`),
  ADD KEY `registration_id` (`registration_id`),
  ADD KEY `idx_pre_response_user` (`user_id`,`activity_id`);

--
-- Indexes for table `resource_library`
--
ALTER TABLE `resource_library`
  ADD PRIMARY KEY (`resource_id`),
  ADD KEY `uploaded_by` (`uploaded_by`),
  ADD KEY `idx_type` (`resource_type`);

--
-- Indexes for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD PRIMARY KEY (`setting_id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`),
  ADD KEY `updated_by` (`updated_by`);

--
-- Indexes for table `test_answers`
--
ALTER TABLE `test_answers`
  ADD PRIMARY KEY (`answer_id`),
  ADD KEY `idx_response` (`response_id`,`test_type`);

--
-- Indexes for table `test_questions`
--
ALTER TABLE `test_questions`
  ADD PRIMARY KEY (`question_id`),
  ADD KEY `idx_test` (`test_id`,`test_type`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_email` (`email`),
  ADD KEY `idx_role` (`role`),
  ADD KEY `idx_approved` (`is_approved`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activities`
--
ALTER TABLE `activities`
  MODIFY `activity_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

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

--
-- AUTO_INCREMENT for table `activity_pre_assignments`
--
ALTER TABLE `activity_pre_assignments`
  MODIFY `assignment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `activity_registrations`
--
ALTER TABLE `activity_registrations`
  MODIFY `registration_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `activity_target_groups`
--
ALTER TABLE `activity_target_groups`
  MODIFY `target_group_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `assessment_questions`
--
ALTER TABLE `assessment_questions`
  MODIFY `question_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `assessment_responses`
--
ALTER TABLE `assessment_responses`
  MODIFY `response_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `assessment_results`
--
ALTER TABLE `assessment_results`
  MODIFY `result_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `assessment_templates`
--
ALTER TABLE `assessment_templates`
  MODIFY `template_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `certificates`
--
ALTER TABLE `certificates`
  MODIFY `certificate_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contact_messages`
--
ALTER TABLE `contact_messages`
  MODIFY `message_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `email_notifications`
--
ALTER TABLE `email_notifications`
  MODIFY `notification_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `employee_profiles`
--
ALTER TABLE `employee_profiles`
  MODIFY `profile_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `evaluation_answers`
--
ALTER TABLE `evaluation_answers`
  MODIFY `answer_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `evaluation_forms`
--
ALTER TABLE `evaluation_forms`
  MODIFY `form_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `evaluation_questions`
--
ALTER TABLE `evaluation_questions`
  MODIFY `question_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=28;

--
-- AUTO_INCREMENT for table `evaluation_responses`
--
ALTER TABLE `evaluation_responses`
  MODIFY `response_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `gpb_forms`
--
ALTER TABLE `gpb_forms`
  MODIFY `gpb_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `monitoring_forms`
--
ALTER TABLE `monitoring_forms`
  MODIFY `monitoring_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `post_tests`
--
ALTER TABLE `post_tests`
  MODIFY `test_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `post_test_responses`
--
ALTER TABLE `post_test_responses`
  MODIFY `response_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `pre_tests`
--
ALTER TABLE `pre_tests`
  MODIFY `test_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `pre_test_responses`
--
ALTER TABLE `pre_test_responses`
  MODIFY `response_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `resource_library`
--
ALTER TABLE `resource_library`
  MODIFY `resource_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `system_settings`
--
ALTER TABLE `system_settings`
  MODIFY `setting_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `test_answers`
--
ALTER TABLE `test_answers`
  MODIFY `answer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=49;

--
-- AUTO_INCREMENT for table `test_questions`
--
ALTER TABLE `test_questions`
  MODIFY `question_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=47;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activities`
--
ALTER TABLE `activities`
  ADD CONSTRAINT `activities_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `activity_attendance`
--
ALTER TABLE `activity_attendance`
  ADD CONSTRAINT `activity_attendance_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `activity_attendance_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `activity_attendance_ibfk_3` FOREIGN KEY (`registration_id`) REFERENCES `activity_registrations` (`registration_id`),
  ADD CONSTRAINT `activity_attendance_ibfk_4` FOREIGN KEY (`scanned_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `activity_pre_assignments`
--
ALTER TABLE `activity_pre_assignments`
  ADD CONSTRAINT `activity_pre_assignments_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE;

--
-- Constraints for table `activity_registrations`
--
ALTER TABLE `activity_registrations`
  ADD CONSTRAINT `activity_registrations_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `activity_registrations_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `activity_registrations_ibfk_3` FOREIGN KEY (`approved_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `activity_target_groups`
--
ALTER TABLE `activity_target_groups`
  ADD CONSTRAINT `activity_target_groups_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE;

--
-- Constraints for table `assessment_questions`
--
ALTER TABLE `assessment_questions`
  ADD CONSTRAINT `assessment_questions_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `assessment_templates` (`template_id`) ON DELETE CASCADE;

--
-- Constraints for table `assessment_responses`
--
ALTER TABLE `assessment_responses`
  ADD CONSTRAINT `assessment_responses_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `assessment_templates` (`template_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assessment_responses_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `assessment_questions` (`question_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assessment_responses_ibfk_3` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assessment_responses_ibfk_4` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE;

--
-- Constraints for table `assessment_results`
--
ALTER TABLE `assessment_results`
  ADD CONSTRAINT `assessment_results_ibfk_1` FOREIGN KEY (`template_id`) REFERENCES `assessment_templates` (`template_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assessment_results_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `assessment_results_ibfk_3` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE;

--
-- Constraints for table `assessment_templates`
--
ALTER TABLE `assessment_templates`
  ADD CONSTRAINT `assessment_templates_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE;

--
-- Constraints for table `certificates`
--
ALTER TABLE `certificates`
  ADD CONSTRAINT `certificates_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `certificates_ibfk_2` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE;

--
-- Constraints for table `contact_messages`
--
ALTER TABLE `contact_messages`
  ADD CONSTRAINT `contact_messages_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`),
  ADD CONSTRAINT `contact_messages_ibfk_2` FOREIGN KEY (`replied_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `email_notifications`
--
ALTER TABLE `email_notifications`
  ADD CONSTRAINT `email_notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

--
-- Constraints for table `employee_profiles`
--
ALTER TABLE `employee_profiles`
  ADD CONSTRAINT `employee_profiles_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

--
-- Constraints for table `evaluation_answers`
--
ALTER TABLE `evaluation_answers`
  ADD CONSTRAINT `evaluation_answers_ibfk_1` FOREIGN KEY (`response_id`) REFERENCES `evaluation_responses` (`response_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `evaluation_answers_ibfk_2` FOREIGN KEY (`question_id`) REFERENCES `evaluation_questions` (`question_id`) ON DELETE CASCADE;

--
-- Constraints for table `evaluation_forms`
--
ALTER TABLE `evaluation_forms`
  ADD CONSTRAINT `evaluation_forms_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `evaluation_forms_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `evaluation_questions`
--
ALTER TABLE `evaluation_questions`
  ADD CONSTRAINT `evaluation_questions_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `evaluation_forms` (`form_id`) ON DELETE CASCADE;

--
-- Constraints for table `evaluation_responses`
--
ALTER TABLE `evaluation_responses`
  ADD CONSTRAINT `evaluation_responses_ibfk_1` FOREIGN KEY (`form_id`) REFERENCES `evaluation_forms` (`form_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `evaluation_responses_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `evaluation_responses_ibfk_3` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `evaluation_responses_ibfk_4` FOREIGN KEY (`registration_id`) REFERENCES `activity_registrations` (`registration_id`) ON DELETE SET NULL;

--
-- Constraints for table `gpb_forms`
--
ALTER TABLE `gpb_forms`
  ADD CONSTRAINT `gpb_forms_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `monitoring_forms`
--
ALTER TABLE `monitoring_forms`
  ADD CONSTRAINT `monitoring_forms_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `monitoring_forms_ibfk_2` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `post_tests`
--
ALTER TABLE `post_tests`
  ADD CONSTRAINT `post_tests_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post_tests_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `post_test_responses`
--
ALTER TABLE `post_test_responses`
  ADD CONSTRAINT `post_test_responses_ibfk_1` FOREIGN KEY (`test_id`) REFERENCES `post_tests` (`test_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post_test_responses_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post_test_responses_ibfk_3` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post_test_responses_ibfk_4` FOREIGN KEY (`registration_id`) REFERENCES `activity_registrations` (`registration_id`) ON DELETE SET NULL;

--
-- Constraints for table `pre_tests`
--
ALTER TABLE `pre_tests`
  ADD CONSTRAINT `pre_tests_ibfk_1` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `pre_tests_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `pre_test_responses`
--
ALTER TABLE `pre_test_responses`
  ADD CONSTRAINT `pre_test_responses_ibfk_1` FOREIGN KEY (`test_id`) REFERENCES `pre_tests` (`test_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `pre_test_responses_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `pre_test_responses_ibfk_3` FOREIGN KEY (`activity_id`) REFERENCES `activities` (`activity_id`) ON DELETE CASCADE,
  ADD CONSTRAINT `pre_test_responses_ibfk_4` FOREIGN KEY (`registration_id`) REFERENCES `activity_registrations` (`registration_id`) ON DELETE SET NULL;

--
-- Constraints for table `resource_library`
--
ALTER TABLE `resource_library`
  ADD CONSTRAINT `resource_library_ibfk_1` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`user_id`);

--
-- Constraints for table `system_settings`
--
ALTER TABLE `system_settings`
  ADD CONSTRAINT `system_settings_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `users` (`user_id`);
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 */;
