-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Aug 10, 2025 at 02:46 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

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

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

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

CREATE TABLE `activities` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `activity_type` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `related_id` int(11) DEFAULT NULL,
  `related_type` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `activities` (`id`, `user_id`, `activity_type`, `description`, `ip_address`, `user_agent`, `related_id`, `related_type`, `created_at`) VALUES
(1, 2, 'login', 'Admin login successful', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/138.0.0.0 Safari/537.36', NULL, NULL, '2025-08-08 06:44:26'),
(2, 2, 'database_backup', 'Admin System Administrator created a database backup', NULL, NULL, NULL, NULL, '2025-08-08 06:57:55'),
(3, 2, 'admin_created', 'Admin System Administrator created admin account for Odoch Kenneth', NULL, NULL, NULL, NULL, '2025-08-08 08:04:36'),
(4, 2, 'payment_type_updated', 'Admin System Administrator updated payment type: Application Fee', NULL, NULL, NULL, NULL, '2025-08-08 10:13:05'),
(5, 2, 'profile_updated', 'Admin System Administrator updated profile for System Administrator', NULL, NULL, NULL, NULL, '2025-08-08 11:29:32'),
(6, 2, 'student_status_updated', 'Admin System Administrator updated status for Odoch Kenneth to active', NULL, NULL, NULL, NULL, '2025-08-08 11:33:04'),
(7, 2, 'message_sent', 'Admin System Administrator sent message to Odoch Kenneth', NULL, NULL, NULL, NULL, '2025-08-08 13:30:00'),
(8, 1, 'message_sent', 'Student Odoch Kenneth sent message to admin 2', NULL, NULL, NULL, NULL, '2025-08-08 13:46:07'),
(9, 1, 'message_sent', 'Student Odoch Kenneth sent message to admin 2', NULL, NULL, NULL, NULL, '2025-08-08 13:47:50'),
(15, 2, 'university_created', 'Created university: dfghjkh', NULL, NULL, NULL, NULL, '2025-08-09 03:45:58'),
(16, 2, 'university_updated', 'Admin System Administrator updated university: dfghjkh', NULL, NULL, NULL, NULL, '2025-08-09 03:58:36'),
(17, 2, 'university_updated', 'Admin System Administrator updated university: dfghjkh', NULL, NULL, NULL, NULL, '2025-08-09 03:59:48'),
(18, 2, 'university_updated', 'Admin System Administrator updated university: dfghjkh', NULL, NULL, NULL, NULL, '2025-08-09 04:04:08'),
(19, 2, 'university_updated', 'Admin System Administrator updated university: dfghjkh', NULL, NULL, NULL, NULL, '2025-08-09 04:11:31'),
(20, 2, 'university_updated', 'Admin System Administrator updated university: dfghjkh', NULL, NULL, NULL, NULL, '2025-08-09 04:12:18'),
(21, 2, 'university_updated', 'Admin System Administrator updated university: dfghjkh', NULL, NULL, NULL, NULL, '2025-08-09 04:12:32'),
(22, 2, 'blog_post_status_changed', 'Admin System Administrator changed post status to draft', NULL, NULL, NULL, NULL, '2025-08-09 16:37:48'),
(23, 2, 'blog_post_status_changed', 'Admin System Administrator changed post status to published', NULL, NULL, NULL, NULL, '2025-08-09 16:38:20'),
(24, 2, 'blog_post_updated', 'Admin System Administrator updated blog post: How to Secure Full Scholarships for US Universities in 2025', NULL, NULL, NULL, NULL, '2025-08-09 16:44:02'),
(25, 2, 'blog_post_updated', 'Admin System Administrator updated blog post: How to Secure Full Scholarships for US Universities in 2025', NULL, NULL, NULL, NULL, '2025-08-09 16:53:03'),
(26, 2, 'blog_post_created', 'Admin System Administrator created blog post: adfgdhfjgh', NULL, NULL, NULL, NULL, '2025-08-09 16:57:35'),
(27, 2, 'blog_post_deleted', 'Admin System Administrator deleted blog post ID: 1', NULL, NULL, NULL, NULL, '2025-08-09 16:58:28'),
(28, 2, 'university_created', 'Created university: gggh with logo with banner image', NULL, NULL, NULL, NULL, '2025-08-09 18:09:34'),
(29, 2, 'university_created', 'Created university: Odoch Kenneth with logo with banner image with country flag', NULL, NULL, NULL, NULL, '2025-08-09 20:21:23'),
(30, 2, 'counselor_created', 'Admin System Administrator created counselor account for Odoch Kenneth', NULL, NULL, NULL, NULL, '2025-08-09 21:52:23'),
(31, 3, 'login', 'Admin login successful', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/138.0.0.0 Safari/537.36', NULL, NULL, '2025-08-09 22:50:01'),
(32, 3, 'student_assigned', 'Admin Odoch Kenneth assigned Odoch Kenneth to counselor Odoch Kenneth', NULL, NULL, NULL, NULL, '2025-08-09 22:50:56'),
(33, 4, 'message_sent', 'Counselor sent a message to student 1', NULL, NULL, NULL, NULL, '2025-08-09 22:56:08'),
(34, 3, 'login', 'Admin login successful', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/138.0.0.0 Safari/537.36', NULL, NULL, '2025-08-09 23:01:43'),
(35, 3, 'login', 'Admin login successful', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/138.0.0.0 Safari/537.36', NULL, NULL, '2025-08-10 06:28:59'),
(36, 3, 'document_verified', 'Admin Odoch Kenneth approved document #2', NULL, NULL, NULL, NULL, '2025-08-10 07:10:27'),
(37, 3, 'program_created', 'Admin Odoch Kenneth created program test product for university 1', NULL, NULL, NULL, NULL, '2025-08-10 07:29:41');

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

--
-- Table structure for table `admins`
--

CREATE TABLE `admins` (
  `id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `profile_photo` varchar(255) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `position` varchar(100) DEFAULT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`permissions`)),
  `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 `admins`
--

INSERT INTO `admins` (`id`, `first_name`, `last_name`, `phone`, `profile_photo`, `department`, `position`, `permissions`, `created_at`, `updated_at`) VALUES
(2, 'System', 'Administrator', '', NULL, 'administration', '', '[]', '2025-08-08 06:43:37', '2025-08-08 11:29:32'),
(3, 'Odoch', 'Kenneth', '0785961086', 'uploads/admins/3_admin_1754640276.jpg', 'administration', 'Admin', '[\"user_management\",\"application_management\",\"financial_management\",\"content_management\",\"system_management\"]', '2025-08-08 08:04:36', '2025-08-08 08:04:36');

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

--
-- Table structure for table `api_keys`
--

CREATE TABLE `api_keys` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `key_hash` varchar(255) NOT NULL,
  `permissions` text DEFAULT NULL,
  `status` enum('active','revoked','suspended') DEFAULT 'active',
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `applications`
--

CREATE TABLE `applications` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `program_id` int(11) NOT NULL,
  `counselor_id` int(11) DEFAULT NULL,
  `application_status` enum('draft','submitted','under_review','interview_scheduled','accepted','conditionally_accepted','rejected','withdrawn') DEFAULT 'draft',
  `submission_date` date DEFAULT NULL,
  `decision_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `application_fee_paid` tinyint(1) DEFAULT 0,
  `fee_payment_date` date DEFAULT NULL,
  `fee_amount` decimal(10,2) DEFAULT NULL,
  `scholarship_awarded` decimal(10,2) 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;

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

--
-- Table structure for table `application_documents`
--

CREATE TABLE `application_documents` (
  `id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `document_type` enum('transcript','recommendation_letter','sop','cv','passport','language_test','portfolio','other') NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `review_notes` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime 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;

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

--
-- Table structure for table `application_notes`
--

CREATE TABLE `application_notes` (
  `id` int(11) NOT NULL,
  `application_id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  `note_type` enum('general','interview','follow_up','internal') NOT NULL,
  `content` text NOT NULL,
  `is_private` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `appointments`
--

CREATE TABLE `appointments` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `counselor_id` int(11) NOT NULL,
  `appointment_type` enum('consultation','document_review','interview_prep','visa_guidance','other') NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `duration_minutes` int(11) DEFAULT 30,
  `status` enum('scheduled','completed','cancelled','no_show') DEFAULT 'scheduled',
  `notes` text DEFAULT NULL,
  `google_meet_link` varchar(255) DEFAULT NULL,
  `reminder_sent` tinyint(1) DEFAULT 0,
  `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;

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

--
-- Table structure for table `blog_posts`
--

CREATE TABLE `blog_posts` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `excerpt` text DEFAULT NULL,
  `content` text NOT NULL,
  `category_id` int(11) NOT NULL,
  `author_id` int(11) NOT NULL,
  `featured_image` varchar(255) DEFAULT NULL,
  `status` enum('draft','published','scheduled') DEFAULT 'draft',
  `is_featured` tinyint(1) DEFAULT 0,
  `view_count` int(11) DEFAULT 0,
  `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 `blog_posts`
--

INSERT INTO `blog_posts` (`id`, `title`, `slug`, `excerpt`, `content`, `category_id`, `author_id`, `featured_image`, `status`, `is_featured`, `view_count`, `created_at`, `updated_at`) VALUES
(2, 'adfgdhfjgh', 'how-to-secure-full-scholarships-for-us-universities-in-2025', 'dertrytuio7iuyrhteghjtkuyliiutyrewetyu57i', 'aewrt4y65u76i87o978i6hegehtjrykulp797oiyrtegwgry46u57i68o', 5, 2, 'uploads/blog/post_2_1754758655.jpg', 'published', 0, 1, '2025-08-09 16:57:35', '2025-08-09 16:57:55');

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

--
-- Table structure for table `blog_tags`
--

CREATE TABLE `blog_tags` (
  `id` int(11) NOT NULL,
  `tag` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `blog_tags`
--

INSERT INTO `blog_tags` (`id`, `tag`, `created_at`) VALUES
(1, 'ertyui7okyjthdgsfghtrjtkuyliiuyt', '2025-08-09 16:57:35');

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

--
-- Table structure for table `categories`
--

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `categories`
--

INSERT INTO `categories` (`id`, `name`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Scholarships', 'Articles about scholarships and financial aid', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(2, 'Visa', 'Updates and guides about student visas', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(3, 'UK', 'Information about studying in the United Kingdom', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(4, 'USA', 'Information about studying in the United States', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(5, 'Canada', 'Information about studying in Canada', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(6, 'Germany', 'Information about studying in Germany', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(7, 'Test Preparation', 'Tips for IELTS, TOEFL, GRE, GMAT, etc.', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(8, 'Guides', 'Comprehensive guides for studying abroad', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(9, 'Tips', 'General tips and advice', '2025-08-09 11:01:04', '2025-08-09 11:01:04'),
(10, 'Student Experience', 'Student experiences and testimonials', '2025-08-09 11:01:04', '2025-08-09 11:01:04');

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

--
-- Table structure for table `counselors`
--

CREATE TABLE `counselors` (
  `id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `profile_photo` varchar(255) DEFAULT NULL,
  `specialization` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`specialization`)),
  `max_students` int(11) DEFAULT 30,
  `current_students` int(11) DEFAULT 0,
  `performance_rating` decimal(3,2) 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 `counselors`
--

INSERT INTO `counselors` (`id`, `first_name`, `last_name`, `phone`, `profile_photo`, `specialization`, `max_students`, `current_students`, `performance_rating`, `created_at`, `updated_at`) VALUES
(4, 'Odoch', 'Kenneth', '0785961086', NULL, '[\"UK Universities\",\"USA Universities\",\"Canada Universities\",\"Australia Universities\",\"STEM Programs\",\"Business Programs\",\"Arts & Humanities\"]', 30, 0, 0.00, '2025-08-09 21:52:23', '2025-08-09 21:52:23');

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

--
-- Table structure for table `countries`
--

CREATE TABLE `countries` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `code` varchar(3) NOT NULL,
  `flag_path` varchar(255) DEFAULT NULL,
  `visa_requirements` text DEFAULT NULL,
  `living_cost_estimate` decimal(10,2) DEFAULT NULL,
  `popularity_rank` int(11) DEFAULT NULL,
  `image_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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `countries`
--

INSERT INTO `countries` (`id`, `name`, `code`, `flag_path`, `visa_requirements`, `living_cost_estimate`, `popularity_rank`, `image_path`, `created_at`, `updated_at`) VALUES
(5, 'Uganda', '+25', NULL, 'asdfdgfhg', 5000.00, 3, 'uploads/countries/country-uganda-6896b8ac57332.jpg', '2025-08-08 18:40:01', '2025-08-09 02:55:40');

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

--
-- Table structure for table `feedback`
--

CREATE TABLE `feedback` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `counselor_id` int(11) NOT NULL,
  `rating` tinyint(4) NOT NULL CHECK (`rating` between 1 and 5),
  `comments` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `due_date` date NOT NULL,
  `status` enum('unpaid','paid','overdue','cancelled') DEFAULT 'unpaid',
  `payment_id` int(11) DEFAULT NULL,
  `description` 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=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int(11) NOT NULL,
  `sender_id` int(11) NOT NULL,
  `receiver_id` int(11) NOT NULL,
  `subject` varchar(255) DEFAULT NULL,
  `content` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `read_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` (`id`, `sender_id`, `receiver_id`, `subject`, `content`, `is_read`, `read_at`, `created_at`) VALUES
(1, 2, 1, NULL, 'hi', 1, '2025-08-08 09:54:33', '2025-08-08 06:53:10'),
(2, 2, 1, 'Help', 'hello', 1, '2025-08-08 16:30:00', '2025-08-08 13:30:00'),
(3, 1, 2, 'yes', 'whats up', 0, NULL, '2025-08-08 13:46:07'),
(4, 1, 2, 'yes', 'hello', 0, NULL, '2025-08-08 13:47:50'),
(5, 4, 1, NULL, 'hello', 1, '2025-08-10 01:56:46', '2025-08-09 22:56:08');

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

--
-- Table structure for table `message_attachments`
--

CREATE TABLE `message_attachments` (
  `id` int(11) NOT NULL,
  `message_id` int(11) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `message_attachments`
--

INSERT INTO `message_attachments` (`id`, `message_id`, `file_path`, `file_name`, `file_size`, `created_at`) VALUES
(1, 4, 'uploads/messages/4_attachment_1754660870_0.docx', 'CURRICULUM VITAE.docx', 16272, '2025-08-08 13:47:50');

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `notification_type` enum('system','application','payment','document','message') NOT NULL,
  `related_id` int(11) DEFAULT NULL,
  `related_type` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `read_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `user_id`, `title`, `message`, `is_read`, `notification_type`, `related_id`, `related_type`, `created_at`, `read_at`) VALUES
(1, 3, 'Welcome to JV Overseas Education', 'Your admin account has been created. You can now log in with your email and password.', 0, 'system', NULL, NULL, '2025-08-08 08:04:36', NULL),
(2, 1, 'Your Status Has Been Updated', 'Your student status has been updated to: active. ', 0, '', NULL, NULL, '2025-08-08 11:33:04', NULL),
(3, 1, 'New Message from Admin', 'You have received a new message from System Administrator: Help', 1, 'message', 2, 'message', '2025-08-08 13:30:00', NULL),
(4, 2, 'New Message from Student', 'You have received a new message from Odoch Kenneth: yes', 0, 'message', 3, 'message', '2025-08-08 13:46:07', NULL),
(5, 2, 'New Message from Student', 'You have received a new message from Odoch Kenneth: yes', 0, 'message', 4, 'message', '2025-08-08 13:47:50', NULL),
(6, 4, 'Welcome to JV Overseas Education', 'Your counselor account has been created. You can now log in with your email and password.', 0, 'system', NULL, NULL, '2025-08-09 21:52:23', NULL),
(7, 1, 'You\'ve Been Assigned a Counselor', 'You have been assigned to counselor Odoch Kenneth', 0, '', NULL, NULL, '2025-08-09 22:50:56', NULL),
(8, 4, 'New Student Assigned', 'Student Odoch Kenneth has been assigned to you', 0, '', NULL, NULL, '2025-08-09 22:50:56', NULL),
(9, 1, 'New Message from Counselor', 'You have received a new message from your counselor.', 1, 'message', NULL, NULL, '2025-08-09 22:56:08', NULL),
(10, 4, 'New Document Uploaded', 'Student Odoch Kenneth has uploaded a new Academic Transcript', 0, 'document', NULL, NULL, '2025-08-09 23:33:00', NULL),
(11, 1, 'Document approved', 'Your document has been approved by your counselor with notes: Document approved by counselor', 0, 'document', 1, 'student_document', '2025-08-09 23:54:02', NULL),
(12, 3, 'New Document Uploaded', 'Student Odoch Kenneth has uploaded a new Curriculum Vitae', 0, 'document', NULL, NULL, '2025-08-10 07:09:38', NULL),
(13, 1, 'Document Approved', 'Your document has been approved. Notes: tested', 0, 'document', NULL, NULL, '2025-08-10 07:10:27', NULL);

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

--
-- Table structure for table `password_resets`
--

CREATE TABLE `password_resets` (
  `email` varchar(255) NOT NULL,
  `token` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `payment_type_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) DEFAULT 'USD',
  `payment_date` date NOT NULL,
  `payment_method` enum('credit_card','bank_transfer','paypal','other') NOT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `status` enum('pending','completed','failed','refunded') DEFAULT 'pending',
  `related_application_id` int(11) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `receipt_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=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `payment_types`
--

CREATE TABLE `payment_types` (
  `id` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `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()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payment_types`
--

INSERT INTO `payment_types` (`id`, `name`, `description`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'Application Fee', 'University application processing fee', 1, '2025-08-05 13:49:12', '2025-08-08 10:13:05'),
(2, 'Visa Fee', 'Visa application processing fee', 1, '2025-08-05 13:49:12', '2025-08-05 13:49:12'),
(3, 'Service Fee', 'Consultation service fee', 1, '2025-08-05 13:49:12', '2025-08-05 13:49:12'),
(4, 'Other', 'Miscellaneous payment', 1, '2025-08-05 13:49:12', '2025-08-05 13:49:12');

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

--
-- Table structure for table `post_tags`
--

CREATE TABLE `post_tags` (
  `id` int(11) NOT NULL,
  `post_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `post_tags`
--

INSERT INTO `post_tags` (`id`, `post_id`, `tag_id`) VALUES
(1, 2, 1);

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

--
-- Table structure for table `programs`
--

CREATE TABLE `programs` (
  `id` int(11) NOT NULL,
  `university_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `degree_level` enum('diploma','bachelors','masters','phd','certificate') DEFAULT NULL,
  `duration_months` int(11) DEFAULT NULL,
  `tuition_fee` decimal(10,2) DEFAULT NULL,
  `tuition_fee_currency` varchar(3) DEFAULT 'USD',
  `intake_months` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`intake_months`)),
  `language_requirements` text DEFAULT NULL,
  `academic_requirements` text DEFAULT NULL,
  `program_description` text DEFAULT NULL,
  `admission_deadline` date DEFAULT NULL,
  `application_fee` decimal(10,2) 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 `programs`
--

INSERT INTO `programs` (`id`, `university_id`, `name`, `degree_level`, `duration_months`, `tuition_fee`, `tuition_fee_currency`, `intake_months`, `language_requirements`, `academic_requirements`, `program_description`, `admission_deadline`, `application_fee`, `created_at`, `updated_at`) VALUES
(1, 1, 'test product', 'bachelors', 8, 44444.00, 'USD', '[\"August\"]', 'IRLTOS', 'High score 80%', 'ghfsasghfhmcnxfgzdfxngchjvgcxfd', '2025-08-23', 55.00, '2025-08-10 07:29:41', '2025-08-10 07:29:41');

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

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int(11) NOT NULL,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text DEFAULT NULL,
  `setting_group` varchar(50) DEFAULT NULL,
  `is_public` tinyint(1) DEFAULT 0,
  `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 `settings`
--

INSERT INTO `settings` (`id`, `setting_key`, `setting_value`, `setting_group`, `is_public`, `created_at`, `updated_at`) VALUES
(1, 'contact_address', 'Kampala, Uganda', 'website', 1, '2025-08-08 06:56:07', '2025-08-08 06:56:07');

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

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(11) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `date_of_birth` date DEFAULT NULL,
  `gender` enum('male','female','other') DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `country` varchar(100) DEFAULT NULL,
  `postal_code` varchar(20) DEFAULT NULL,
  `profile_photo` varchar(255) DEFAULT NULL,
  `education_level` varchar(100) DEFAULT NULL,
  `current_institution` varchar(255) DEFAULT NULL,
  `graduation_year` int(11) DEFAULT NULL,
  `gpa` decimal(3,2) DEFAULT NULL,
  `preferred_countries` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`preferred_countries`)),
  `preferred_courses` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`preferred_courses`)),
  `budget` decimal(10,2) DEFAULT NULL,
  `assigned_counselor_id` int(11) DEFAULT NULL,
  `status` enum('prospective','active','admitted','rejected','alumni') DEFAULT NULL,
  `notes` 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `first_name`, `last_name`, `date_of_birth`, `gender`, `phone`, `address`, `city`, `country`, `postal_code`, `profile_photo`, `education_level`, `current_institution`, `graduation_year`, `gpa`, `preferred_countries`, `preferred_courses`, `budget`, `assigned_counselor_id`, `status`, `notes`, `created_at`, `updated_at`) VALUES
(1, 'Odoch', 'Kenneth', '2022-09-07', 'male', '0785961086', NULL, NULL, NULL, NULL, 'uploads/profiles/1_profile_1754592835.png', 'Diploma', 'Bugolobi', NULL, 3.50, NULL, NULL, 5000.00, 4, 'active', '', '2025-08-07 10:25:54', '2025-08-09 22:50:56');

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

--
-- Table structure for table `student_documents`
--

CREATE TABLE `student_documents` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `document_type` enum('passport','transcript','diploma','recommendation_letter','sop','cv','language_test','financial_statement','other') NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `status` enum('pending','uploaded','approved','rejected') DEFAULT 'pending',
  `notes` text DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `review_notes` text DEFAULT NULL,
  `reviewed_by` int(11) DEFAULT NULL,
  `reviewed_at` datetime 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 `student_documents`
--

INSERT INTO `student_documents` (`id`, `student_id`, `document_type`, `file_path`, `status`, `notes`, `expiry_date`, `review_notes`, `reviewed_by`, `reviewed_at`, `created_at`, `updated_at`) VALUES
(1, 1, 'transcript', 'uploads/documents/1_1754782380_284492e0a7760333.docx', 'approved', 'jhghfhgjhkhjg', NULL, 'Document approved by counselor', 4, '2025-08-10 02:54:02', '2025-08-09 23:33:00', '2025-08-09 23:54:02'),
(2, 1, 'cv', 'uploads/documents/1_1754809778_36e5f29db811277f.docx', 'approved', 'test', NULL, 'tested', NULL, '2025-08-10 10:10:27', '2025-08-10 07:09:38', '2025-08-10 07:10:27');

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

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `assigned_to` int(11) NOT NULL,
  `assigned_by` int(11) NOT NULL,
  `related_student_id` int(11) DEFAULT NULL,
  `related_application_id` int(11) DEFAULT NULL,
  `priority` enum('low','medium','high') DEFAULT 'medium',
  `status` enum('pending','in_progress','completed','cancelled') DEFAULT 'pending',
  `deadline` datetime DEFAULT NULL,
  `completed_at` datetime 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;

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

--
-- Table structure for table `testimonials`
--

CREATE TABLE `testimonials` (
  `id` int(11) NOT NULL,
  `student_name` varchar(255) NOT NULL,
  `program` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `rating` tinyint(4) NOT NULL DEFAULT 5,
  `content` text NOT NULL,
  `is_featured` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `universities`
--

CREATE TABLE `universities` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `country_id` int(11) NOT NULL,
  `city` varchar(100) DEFAULT NULL,
  `ranking` int(11) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `banner_image` varchar(255) DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `partnership_level` enum('basic','preferred','premium') DEFAULT NULL,
  `contact_name` varchar(100) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(20) DEFAULT NULL,
  `application_fee` decimal(10,2) DEFAULT NULL,
  `application_deadlines` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`application_deadlines`)),
  `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 `universities`
--

INSERT INTO `universities` (`id`, `name`, `country_id`, `city`, `ranking`, `logo`, `banner_image`, `website`, `description`, `partnership_level`, `contact_name`, `contact_email`, `contact_phone`, `application_fee`, `application_deadlines`, `created_at`, `updated_at`) VALUES
(1, 'dfghjkh', 5, 'vdghjky', 5, NULL, NULL, 'https://www.unversity.ude', 'fghjykuilkytrhwefqegwtruytiuyoikujhfd', 'preferred', 'dewfrteerh', 'donkenix@gmail.com', '0785961086', 5000.00, '[\"August\"]', '2025-08-09 03:45:58', '2025-08-09 04:12:32'),
(2, 'gggh', 5, 'Kampal', 50, 'uploads/universities/university_logo_2_1754762973.jpg', 'uploads/universities/university_banner_2_1754762974.jpg', 'https://www.unversity.ude', 'zfdgxfhjkjfgesrdtfygukh', 'basic', 'Odoch Kenneth', 'donkenix@gmail.com', '0785961086', NULL, '[\"August\"]', '2025-08-09 18:09:33', '2025-08-09 18:09:34'),
(3, 'Odoch Kenneth', 5, 'Kampal', 5, 'uploads/universities/university_logo_3_1754770883.png', 'uploads/universities/university_banner_3_1754770883.jpg', 'https://www.unversity.ude', 'fghjulyuytretuyiluykujh', 'preferred', 'Odoch Kenneth', 'donkenix@gmail.com', '0785961086', 4444.00, '[\"August\"]', '2025-08-09 20:21:23', '2025-08-09 20:21:23');

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `user_type` enum('student','counselor','admin') NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `last_login` datetime 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 `users`
--

INSERT INTO `users` (`id`, `email`, `password_hash`, `user_type`, `is_active`, `last_login`, `created_at`, `updated_at`) VALUES
(1, 'odochkenneth72@gmail.com', '$2y$10$bn5.Ucn5y.zZFIipFMd2CeNy4Ql5qAienrWHvOa2iYUnTsdcx.emK', 'student', 1, '2025-08-08 09:54:27', '2025-08-07 10:25:54', '2025-08-08 06:54:27'),
(2, 'admin@jvoverseas.com', '$2y$10$yqafJkYNdmkc2ghWrcCOculCPTJl.RuZ4nWY0hyLnqwTpI7lx57m6', 'admin', 1, '2025-08-08 09:44:26', '2025-08-08 06:43:37', '2025-08-08 06:44:26'),
(3, 'donkenix@gmail.com', '$2y$10$p2KSoXw5TTjGkvJQi6w/W.Nhs2GGL/t1JXUnjUfZ6P7dOP3aH8dki', 'admin', 1, '2025-08-10 09:28:59', '2025-08-08 08:04:36', '2025-08-10 06:28:59'),
(4, 'odochkenneth7@gmail.com', '$2y$10$iRPPLkJa1ebVc/URrEgvce01G6jqA26lWG3RuvUO3TMGjH51D7JJW', 'counselor', 1, '2025-08-10 02:34:33', '2025-08-09 21:52:23', '2025-08-09 23:34:33');

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

--
-- Table structure for table `visa_applications`
--

CREATE TABLE `visa_applications` (
  `id` int(11) NOT NULL,
  `student_id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `application_date` date DEFAULT NULL,
  `status` enum('not_started','document_collection','application_submitted','biometrics_completed','under_review','approved','rejected') DEFAULT 'not_started',
  `expected_decision_date` date DEFAULT NULL,
  `decision_date` date DEFAULT NULL,
  `visa_type` varchar(100) DEFAULT NULL,
  `visa_number` varchar(100) DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `counselor_id` 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;

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

--
-- Table structure for table `visa_checklist`
--

CREATE TABLE `visa_checklist` (
  `id` int(11) NOT NULL,
  `country_id` int(11) NOT NULL,
  `document_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `is_required` tinyint(1) DEFAULT 1,
  `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;

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

--
-- Table structure for table `visa_documents`
--

CREATE TABLE `visa_documents` (
  `id` int(11) NOT NULL,
  `visa_application_id` int(11) NOT NULL,
  `document_type` varchar(100) NOT NULL,
  `status` enum('pending','uploaded','approved','rejected') DEFAULT 'pending',
  `file_path` varchar(255) DEFAULT NULL,
  `notes` 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=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activities`
--
ALTER TABLE `activities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_activities_user` (`user_id`);

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

--
-- Indexes for table `api_keys`
--
ALTER TABLE `api_keys`
  ADD PRIMARY KEY (`id`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `applications`
--
ALTER TABLE `applications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `counselor_id` (`counselor_id`),
  ADD KEY `idx_applications_student` (`student_id`),
  ADD KEY `idx_applications_program` (`program_id`),
  ADD KEY `idx_applications_status` (`application_status`);

--
-- Indexes for table `application_documents`
--
ALTER TABLE `application_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `application_id` (`application_id`),
  ADD KEY `reviewed_by` (`reviewed_by`);

--
-- Indexes for table `application_notes`
--
ALTER TABLE `application_notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `application_id` (`application_id`),
  ADD KEY `author_id` (`author_id`);

--
-- Indexes for table `appointments`
--
ALTER TABLE `appointments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `counselor_id` (`counselor_id`);

--
-- Indexes for table `blog_posts`
--
ALTER TABLE `blog_posts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `author_id` (`author_id`);

--
-- Indexes for table `blog_tags`
--
ALTER TABLE `blog_tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `tag` (`tag`);

--
-- Indexes for table `categories`
--
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

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

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

--
-- Indexes for table `feedback`
--
ALTER TABLE `feedback`
  ADD PRIMARY KEY (`id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `counselor_id` (`counselor_id`);

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `payment_id` (`payment_id`);

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sender_id` (`sender_id`),
  ADD KEY `receiver_id` (`receiver_id`);

--
-- Indexes for table `message_attachments`
--
ALTER TABLE `message_attachments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `message_id` (`message_id`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_notifications_user` (`user_id`);

--
-- Indexes for table `password_resets`
--
ALTER TABLE `password_resets`
  ADD KEY `email` (`email`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `payment_type_id` (`payment_type_id`),
  ADD KEY `related_application_id` (`related_application_id`),
  ADD KEY `idx_payments_student` (`student_id`),
  ADD KEY `idx_payments_status` (`status`);

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

--
-- Indexes for table `post_tags`
--
ALTER TABLE `post_tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_post_tag` (`post_id`,`tag_id`),
  ADD KEY `tag_id` (`tag_id`);

--
-- Indexes for table `programs`
--
ALTER TABLE `programs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `university_id` (`university_id`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `setting_key` (`setting_key`);

--
-- Indexes for table `students`
--
ALTER TABLE `students`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_students_assigned_counselor` (`assigned_counselor_id`);

--
-- Indexes for table `student_documents`
--
ALTER TABLE `student_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `fk_reviewed_by` (`reviewed_by`);

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `assigned_by` (`assigned_by`),
  ADD KEY `related_student_id` (`related_student_id`),
  ADD KEY `related_application_id` (`related_application_id`),
  ADD KEY `idx_tasks_assigned` (`assigned_to`),
  ADD KEY `idx_tasks_status` (`status`);

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

--
-- Indexes for table `universities`
--
ALTER TABLE `universities`
  ADD PRIMARY KEY (`id`),
  ADD KEY `country_id` (`country_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`);

--
-- Indexes for table `visa_applications`
--
ALTER TABLE `visa_applications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `student_id` (`student_id`),
  ADD KEY `country_id` (`country_id`),
  ADD KEY `counselor_id` (`counselor_id`);

--
-- Indexes for table `visa_checklist`
--
ALTER TABLE `visa_checklist`
  ADD PRIMARY KEY (`id`),
  ADD KEY `country_id` (`country_id`);

--
-- Indexes for table `visa_documents`
--
ALTER TABLE `visa_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `visa_application_id` (`visa_application_id`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activities`
--
ALTER TABLE `activities`
  ADD CONSTRAINT `activities_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `admins`
--
ALTER TABLE `admins`
  ADD CONSTRAINT `admins_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `api_keys`
--
ALTER TABLE `api_keys`
  ADD CONSTRAINT `api_keys_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `applications`
--
ALTER TABLE `applications`
  ADD CONSTRAINT `applications_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `applications_ibfk_2` FOREIGN KEY (`program_id`) REFERENCES `programs` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `applications_ibfk_3` FOREIGN KEY (`counselor_id`) REFERENCES `counselors` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `application_documents`
--
ALTER TABLE `application_documents`
  ADD CONSTRAINT `application_documents_ibfk_1` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `application_documents_ibfk_2` FOREIGN KEY (`reviewed_by`) REFERENCES `admins` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `application_notes`
--
ALTER TABLE `application_notes`
  ADD CONSTRAINT `application_notes_ibfk_1` FOREIGN KEY (`application_id`) REFERENCES `applications` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `application_notes_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `appointments`
--
ALTER TABLE `appointments`
  ADD CONSTRAINT `appointments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `appointments_ibfk_2` FOREIGN KEY (`counselor_id`) REFERENCES `counselors` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `blog_posts`
--
ALTER TABLE `blog_posts`
  ADD CONSTRAINT `blog_posts_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `blog_posts_ibfk_2` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `counselors`
--
ALTER TABLE `counselors`
  ADD CONSTRAINT `counselors_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `feedback`
--
ALTER TABLE `feedback`
  ADD CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `feedback_ibfk_2` FOREIGN KEY (`counselor_id`) REFERENCES `counselors` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `invoices`
--
ALTER TABLE `invoices`
  ADD CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`payment_id`) REFERENCES `payments` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `message_attachments`
--
ALTER TABLE `message_attachments`
  ADD CONSTRAINT `message_attachments_ibfk_1` FOREIGN KEY (`message_id`) REFERENCES `messages` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`payment_type_id`) REFERENCES `payment_types` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `payments_ibfk_3` FOREIGN KEY (`related_application_id`) REFERENCES `applications` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `post_tags`
--
ALTER TABLE `post_tags`
  ADD CONSTRAINT `post_tags_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `blog_posts` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `post_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `blog_tags` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `programs`
--
ALTER TABLE `programs`
  ADD CONSTRAINT `programs_ibfk_1` FOREIGN KEY (`university_id`) REFERENCES `universities` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `students`
--
ALTER TABLE `students`
  ADD CONSTRAINT `students_ibfk_1` FOREIGN KEY (`id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `students_ibfk_2` FOREIGN KEY (`assigned_counselor_id`) REFERENCES `counselors` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `student_documents`
--
ALTER TABLE `student_documents`
  ADD CONSTRAINT `fk_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `student_documents_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tasks`
--
ALTER TABLE `tasks`
  ADD CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`assigned_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`related_student_id`) REFERENCES `students` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `tasks_ibfk_4` FOREIGN KEY (`related_application_id`) REFERENCES `applications` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `universities`
--
ALTER TABLE `universities`
  ADD CONSTRAINT `universities_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `visa_applications`
--
ALTER TABLE `visa_applications`
  ADD CONSTRAINT `visa_applications_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `visa_applications_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `visa_applications_ibfk_3` FOREIGN KEY (`counselor_id`) REFERENCES `counselors` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `visa_checklist`
--
ALTER TABLE `visa_checklist`
  ADD CONSTRAINT `visa_checklist_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `visa_documents`
--
ALTER TABLE `visa_documents`
  ADD CONSTRAINT `visa_documents_ibfk_1` FOREIGN KEY (`visa_application_id`) REFERENCES `visa_applications` (`id`) ON DELETE CASCADE;
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 */;
