-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 08, 2025 at 11:35 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12
drop Database obgyn_research_platform;
create Database obgyn_research_platform;
USE obgyn_research_platform;
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: `obgyn_research_platform`
--

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

--
-- Table structure for table `analytics_data`
--

CREATE TABLE `analytics_data` (
  `id` int(11) UNSIGNED NOT NULL,
  `metric_name` varchar(100) NOT NULL,
  `metric_value` decimal(15,2) NOT NULL,
  `dimension` varchar(100) DEFAULT NULL,
  `date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `announcements`
--

CREATE TABLE `announcements` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `type` enum('info','warning','success','danger') DEFAULT 'info',
  `target_role` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `created_by` int(11) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `table_name` varchar(100) DEFAULT NULL,
  `record_id` int(11) UNSIGNED DEFAULT NULL,
  `old_values` text DEFAULT NULL,
  `new_values` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_logs`
--

INSERT INTO `audit_logs` (`id`, `user_id`, `action`, `table_name`, `record_id`, `old_values`, `new_values`, `ip_address`, `user_agent`, `created_at`) VALUES
(1, NULL, 'user_registered', 'users', 1, NULL, NULL, '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/140.0.0.0 Safari/537.36', '2025-10-08 19:21:12'),
(2, 2, 'user_login', 'users', 2, NULL, NULL, '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/140.0.0.0 Safari/537.36', '2025-10-08 21:20:09'),
(3, 2, 'cms_contact_updated', 'settings', NULL, NULL, '{\"company_name\":\"GREC\",\"tagline\":\"GANNA Research Center\",\"email_primary\":\"info@GREC.com\",\"email_support\":\"support@GREC.com\",\"phone_primary\":\"+20 11 12220298\",\"phone_secondary\":\"+20 10 23288826\",\"address_line1\":\"Cairo - Egypt\",\"address_line2\":\"Gannah Hospital\",\"city\":\"\",\"state\":\"\",\"country\":\"\",\"postal_code\":\"\",\"working_hours\":\"Saturday - thursday 09:00 AM - 06:00 PM\",\"map_latitude\":\"\",\"map_longitude\":\"\"}', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/140.0.0.0 Safari/537.36', '2025-10-08 21:22:50'),
(4, 2, 'cms_contact_updated', 'settings', NULL, NULL, '{\"company_name\":\"GREC\",\"tagline\":\"GANNA Research Center\",\"email_primary\":\"info@GREC.com\",\"email_support\":\"support@GREC.com\",\"phone_primary\":\"+20 11 12220298\",\"phone_secondary\":\"+20 10 23288826\",\"address_line1\":\"Cairo - Egypt\",\"address_line2\":\"Gannah Hospital\",\"city\":\"\",\"state\":\"\",\"country\":\"\",\"postal_code\":\"\",\"working_hours\":\"Saturday - thursday 09:00 AM - 06:00 PM\",\"map_latitude\":\"40.171\",\"map_longitude\":\"-74.280\"}', '::1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/140.0.0.0 Safari/537.36', '2025-10-08 21:24:09');

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

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

CREATE TABLE `categories` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `order_num` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `chat_participants`
--

CREATE TABLE `chat_participants` (
  `id` int(11) UNSIGNED NOT NULL,
  `room_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `role` enum('admin','member') DEFAULT 'member',
  `joined_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `last_read_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `chat_rooms`
--

CREATE TABLE `chat_rooms` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `type` enum('direct','group','study') NOT NULL DEFAULT 'direct',
  `study_id` int(11) UNSIGNED DEFAULT NULL,
  `created_by` int(11) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `cms_features`
--

CREATE TABLE `cms_features` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `icon_type` enum('fontawesome','image','emoji') DEFAULT 'fontawesome',
  `icon_image` varchar(500) DEFAULT NULL,
  `link` varchar(500) DEFAULT NULL,
  `order_num` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `cms_hero_sections`
--

CREATE TABLE `cms_hero_sections` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `subtitle` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `background_image` varchar(500) DEFAULT NULL,
  `background_video` varchar(500) DEFAULT NULL,
  `background_type` enum('image','video','color') DEFAULT 'image',
  `background_color` varchar(20) DEFAULT NULL,
  `overlay_opacity` decimal(3,2) DEFAULT 0.50,
  `button_text` varchar(100) DEFAULT NULL,
  `button_link` varchar(500) DEFAULT NULL,
  `button_style` varchar(50) DEFAULT 'primary',
  `secondary_button_text` varchar(100) DEFAULT NULL,
  `secondary_button_link` varchar(500) DEFAULT NULL,
  `text_alignment` enum('left','center','right') DEFAULT 'center',
  `height` varchar(20) DEFAULT 'auto',
  `order_num` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `cms_media`
--

CREATE TABLE `cms_media` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `file_size` int(11) NOT NULL,
  `width` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  `thumbnail_path` varchar(500) DEFAULT NULL,
  `alt_text` varchar(255) DEFAULT NULL,
  `caption` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `folder` varchar(255) DEFAULT NULL,
  `uploaded_by` int(11) UNSIGNED NOT NULL,
  `usage_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_unicode_ci;

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

--
-- Table structure for table `cms_menus`
--

CREATE TABLE `cms_menus` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `location` enum('header','footer','sidebar','custom') NOT NULL DEFAULT 'header',
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_by` int(11) UNSIGNED NOT 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 `cms_menu_items`
--

CREATE TABLE `cms_menu_items` (
  `id` int(11) UNSIGNED NOT NULL,
  `menu_id` int(11) UNSIGNED NOT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `url` varchar(500) DEFAULT NULL,
  `page_id` int(11) UNSIGNED DEFAULT NULL,
  `link_type` enum('internal','external','page','custom') DEFAULT 'internal',
  `target` enum('_self','_blank') DEFAULT '_self',
  `icon` varchar(100) DEFAULT NULL,
  `css_class` varchar(100) DEFAULT NULL,
  `order_num` int(11) DEFAULT 0,
  `visibility` enum('public','logged_in','logged_out','admin') DEFAULT 'public',
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `cms_pages`
--

CREATE TABLE `cms_pages` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `content` longtext DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `meta_keywords` text DEFAULT NULL,
  `og_title` varchar(255) DEFAULT NULL,
  `og_description` text DEFAULT NULL,
  `og_image` varchar(500) DEFAULT NULL,
  `twitter_card` varchar(50) DEFAULT 'summary',
  `canonical_url` varchar(500) DEFAULT NULL,
  `robots` varchar(100) DEFAULT 'index,follow',
  `status` enum('draft','published') DEFAULT 'draft',
  `language` varchar(10) DEFAULT 'en',
  `created_by` int(11) UNSIGNED NOT 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 `cms_posts`
--

CREATE TABLE `cms_posts` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `excerpt` text DEFAULT NULL,
  `content` longtext DEFAULT NULL,
  `meta_title` varchar(255) DEFAULT NULL,
  `meta_description` text DEFAULT NULL,
  `meta_keywords` text DEFAULT NULL,
  `og_title` varchar(255) DEFAULT NULL,
  `og_description` text DEFAULT NULL,
  `og_image` varchar(500) DEFAULT NULL,
  `twitter_card` varchar(50) DEFAULT 'summary',
  `canonical_url` varchar(500) DEFAULT NULL,
  `robots` varchar(100) DEFAULT 'index,follow',
  `featured_image` varchar(500) DEFAULT NULL,
  `category_id` int(11) UNSIGNED DEFAULT NULL,
  `author_id` int(11) UNSIGNED NOT NULL,
  `status` enum('draft','published') DEFAULT 'draft',
  `language` varchar(10) DEFAULT 'en',
  `views_count` int(11) DEFAULT 0,
  `published_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_unicode_ci;

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

--
-- Table structure for table `cms_sliders`
--

CREATE TABLE `cms_sliders` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `location` varchar(100) DEFAULT 'homepage',
  `status` enum('active','inactive') DEFAULT 'active',
  `autoplay` tinyint(1) DEFAULT 1,
  `autoplay_speed` int(11) DEFAULT 5000,
  `transition_effect` varchar(50) DEFAULT 'fade',
  `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 `cms_slider_items`
--

CREATE TABLE `cms_slider_items` (
  `id` int(11) UNSIGNED NOT NULL,
  `slider_id` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `image` varchar(500) NOT NULL,
  `link` varchar(500) DEFAULT NULL,
  `button_text` varchar(100) DEFAULT NULL,
  `order_num` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `cms_stats`
--

CREATE TABLE `cms_stats` (
  `id` int(11) UNSIGNED NOT NULL,
  `label` varchar(255) NOT NULL,
  `value` varchar(50) NOT NULL,
  `suffix` varchar(20) DEFAULT NULL,
  `prefix` varchar(20) DEFAULT NULL,
  `icon` varchar(100) DEFAULT NULL,
  `color` varchar(20) DEFAULT NULL,
  `order_num` int(11) DEFAULT 0,
  `animate` tinyint(1) DEFAULT 1,
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `cms_team_members`
--

CREATE TABLE `cms_team_members` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `department` varchar(255) DEFAULT NULL,
  `photo` varchar(500) DEFAULT NULL,
  `bio` text DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `facebook` varchar(255) DEFAULT NULL,
  `twitter` varchar(255) DEFAULT NULL,
  `linkedin` varchar(255) DEFAULT NULL,
  `order_num` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `cms_testimonials`
--

CREATE TABLE `cms_testimonials` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `photo` varchar(500) DEFAULT NULL,
  `testimonial` text NOT NULL,
  `rating` tinyint(1) DEFAULT 5,
  `order_num` int(11) DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `cms_widgets`
--

CREATE TABLE `cms_widgets` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `widget_type` enum('text','html','menu','recent_posts','categories','tags','social','custom') NOT NULL,
  `content` longtext DEFAULT NULL,
  `location` enum('sidebar_left','sidebar_right','footer_1','footer_2','footer_3','footer_4','custom') NOT NULL,
  `order_num` int(11) DEFAULT 0,
  `settings` text DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_by` int(11) UNSIGNED NOT 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 `connections`
--

CREATE TABLE `connections` (
  `id` int(11) UNSIGNED NOT NULL,
  `requester_id` int(11) UNSIGNED NOT NULL,
  `receiver_id` int(11) UNSIGNED NOT NULL,
  `status` enum('pending','accepted','declined','blocked') DEFAULT 'pending',
  `message` 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_unicode_ci;

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

--
-- Table structure for table `contact_approvals`
--

CREATE TABLE `contact_approvals` (
  `id` int(11) UNSIGNED NOT NULL,
  `requester_id` int(11) UNSIGNED NOT NULL,
  `receiver_id` int(11) UNSIGNED NOT NULL,
  `status` enum('pending','approved','declined') DEFAULT 'pending',
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `responded_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `email_templates`
--

CREATE TABLE `email_templates` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` longtext NOT NULL,
  `variables` text DEFAULT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(50) DEFAULT 'general',
  `status` enum('active','inactive') DEFAULT 'active',
  `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 `email_templates`
--

INSERT INTO `email_templates` (`id`, `name`, `slug`, `subject`, `body`, `variables`, `description`, `category`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Welcome Email', 'welcome', 'Welcome to {{site_name}}!', '<h2>Welcome {{user_name}}!</h2>\r\n<p>Thank you for joining {{site_name}}. We\'re excited to have you as part of our research community.</p>\r\n<p>Your account has been successfully created. You can now:</p>\r\n<ul>\r\n  <li>Create and manage research studies</li>\r\n  <li>Connect with other researchers</li>\r\n  <li>Share publications and findings</li>\r\n  <li>Collaborate on projects</li>\r\n</ul>\r\n<p>Get started by completing your profile and exploring the platform.</p>\r\n<p><a href=\"{{site_url}}/researcher/dashboard.php\" style=\"background-color: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;\">Go to Dashboard</a></p>\r\n<p>Best regards,<br>The {{site_name}} Team</p>', '{{user_name}}, {{site_name}}, {{site_url}}', 'Sent when a new user registers', 'user', 'active', '2025-10-08 21:08:29', '2025-10-08 21:08:29'),
(2, 'Email Verification', 'email_verification', 'Verify Your Email - {{site_name}}', '<h2>Email Verification</h2>\r\n<p>Hello {{user_name}},</p>\r\n<p>Please verify your email address by clicking the button below:</p>\r\n<p><a href=\"{{verification_link}}\" style=\"background-color: #28a745; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;\">Verify Email</a></p>\r\n<p>Or copy and paste this link into your browser:</p>\r\n<p>{{verification_link}}</p>\r\n<p>This link will expire in 24 hours.</p>\r\n<p>If you didn\'t create an account, please ignore this email.</p>\r\n<p>Best regards,<br>The {{site_name}} Team</p>', '{{user_name}}, {{verification_link}}, {{site_name}}', 'Email verification link', 'user', 'active', '2025-10-08 21:08:29', '2025-10-08 21:08:29'),
(3, 'Password Reset', 'password_reset', 'Reset Your Password - {{site_name}}', '<h2>Password Reset Request</h2>\r\n<p>Hello {{user_name}},</p>\r\n<p>We received a request to reset your password. Click the button below to create a new password:</p>\r\n<p><a href=\"{{reset_link}}\" style=\"background-color: #dc3545; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;\">Reset Password</a></p>\r\n<p>Or copy and paste this link into your browser:</p>\r\n<p>{{reset_link}}</p>\r\n<p>This link will expire in 1 hour.</p>\r\n<p>If you didn\'t request a password reset, please ignore this email or contact support if you have concerns.</p>\r\n<p>Best regards,<br>The {{site_name}} Team</p>', '{{user_name}}, {{reset_link}}, {{site_name}}', 'Password reset link', 'user', 'active', '2025-10-08 21:08:29', '2025-10-08 21:08:29'),
(4, 'New Connection Request', 'connection_request', 'New Connection Request - {{site_name}}', '<h2>New Connection Request</h2>\r\n<p>Hello {{receiver_name}},</p>\r\n<p><strong>{{requester_name}}</strong> wants to connect with you on {{site_name}}.</p>\r\n<p>{{requester_title}}<br>{{requester_institution}}</p>\r\n<p>Message: {{message}}</p>\r\n<p><a href=\"{{site_url}}/researcher/connections.php\" style=\"background-color: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;\">View Request</a></p>\r\n<p>Best regards,<br>The {{site_name}} Team</p>', '{{receiver_name}}, {{requester_name}}, {{requester_title}}, {{requester_institution}}, {{message}}, {{site_url}}, {{site_name}}', 'Connection request notification', 'notification', 'active', '2025-10-08 21:08:29', '2025-10-08 21:08:29'),
(5, 'Study Invitation', 'study_invitation', 'Study Collaboration Invitation - {{site_name}}', '<h2>Study Collaboration Invitation</h2>\r\n<p>Hello {{user_name}},</p>\r\n<p>You have been invited to collaborate on a research study:</p>\r\n<p><strong>{{study_title}}</strong></p>\r\n<p>Role: {{role}}<br>Invited by: {{inviter_name}}</p>\r\n<p>{{study_description}}</p>\r\n<p><a href=\"{{study_link}}\" style=\"background-color: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;\">View Study</a></p>\r\n<p>Best regards,<br>The {{site_name}} Team</p>', '{{user_name}}, {{study_title}}, {{role}}, {{inviter_name}}, {{study_description}}, {{study_link}}, {{site_name}}', 'Study collaboration invitation', 'notification', 'active', '2025-10-08 21:08:29', '2025-10-08 21:08:29'),
(6, 'New Message', 'new_message', 'New Message from {{sender_name}} - {{site_name}}', '<h2>New Message</h2>\r\n<p>Hello {{receiver_name}},</p>\r\n<p>You have received a new message from <strong>{{sender_name}}</strong>:</p>\r\n<blockquote style=\"border-left: 4px solid #007bff; padding-left: 15px; margin: 20px 0;\">{{message_preview}}</blockquote>\r\n<p><a href=\"{{message_link}}\" style=\"background-color: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px; display: inline-block;\">Read Message</a></p>\r\n<p>Best regards,<br>The {{site_name}} Team</p>', '{{receiver_name}}, {{sender_name}}, {{message_preview}}, {{message_link}}, {{site_name}}', 'New message notification', 'notification', 'active', '2025-10-08 21:08:29', '2025-10-08 21:08:29');

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

--
-- Table structure for table `followers`
--

CREATE TABLE `followers` (
  `id` int(11) UNSIGNED NOT NULL,
  `follower_id` int(11) UNSIGNED NOT NULL,
  `following_id` int(11) UNSIGNED NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `institutions`
--

CREATE TABLE `institutions` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('university','hospital','research_center','clinic','other') NOT NULL,
  `country` varchar(100) NOT NULL,
  `city` varchar(100) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `website` varchar(255) DEFAULT NULL,
  `logo` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `admin_user_id` int(11) UNSIGNED DEFAULT NULL,
  `status` enum('active','inactive','pending') DEFAULT 'pending',
  `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 `institution_members`
--

CREATE TABLE `institution_members` (
  `id` int(11) UNSIGNED NOT NULL,
  `institution_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `role` varchar(100) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'active',
  `joined_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `id` int(11) UNSIGNED NOT NULL,
  `email` varchar(255) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `success` tinyint(1) DEFAULT 0,
  `attempted_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `login_attempts`
--

INSERT INTO `login_attempts` (`id`, `email`, `ip_address`, `success`, `attempted_at`) VALUES
(1, 'admin@obgynresearch.com', '::1', 0, '2025-10-08 21:18:10'),
(2, 'admin@obgynresearch.com', '::1', 1, '2025-10-08 21:20:09');

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

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

CREATE TABLE `messages` (
  `id` int(11) UNSIGNED NOT NULL,
  `room_id` int(11) UNSIGNED NOT NULL,
  `sender_id` int(11) UNSIGNED NOT NULL,
  `message` text NOT NULL,
  `message_type` enum('text','file','image','system') DEFAULT 'text',
  `file_path` varchar(500) DEFAULT NULL,
  `file_name` varchar(255) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `notifications` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `type` varchar(50) NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `link` varchar(500) DEFAULT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `module` varchar(50) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `publications`
--

CREATE TABLE `publications` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `study_id` int(11) UNSIGNED DEFAULT NULL,
  `title` varchar(500) NOT NULL,
  `authors` text NOT NULL,
  `journal` varchar(255) DEFAULT NULL,
  `publication_date` date DEFAULT NULL,
  `doi` varchar(255) DEFAULT NULL,
  `pubmed_id` varchar(50) DEFAULT NULL,
  `external_link` varchar(500) DEFAULT NULL,
  `abstract` text DEFAULT NULL,
  `keywords` text DEFAULT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `status` enum('draft','published','pending_approval') DEFAULT 'draft',
  `approval_status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) UNSIGNED DEFAULT NULL,
  `views_count` int(11) DEFAULT 0,
  `downloads_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_unicode_ci;

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

--
-- Table structure for table `publication_comments`
--

CREATE TABLE `publication_comments` (
  `id` int(11) UNSIGNED NOT NULL,
  `publication_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `comment` text NOT NULL,
  `status` enum('pending','approved','rejected') DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `researcher_profiles`
--

CREATE TABLE `researcher_profiles` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `photo` varchar(255) DEFAULT NULL,
  `title` varchar(100) DEFAULT NULL,
  `specialization` varchar(255) DEFAULT NULL,
  `institution_id` int(11) UNSIGNED DEFAULT NULL,
  `department` varchar(255) DEFAULT NULL,
  `degrees` text DEFAULT NULL,
  `bio` text DEFAULT NULL,
  `research_interests` text DEFAULT NULL,
  `orcid_id` varchar(50) DEFAULT NULL,
  `google_scholar` varchar(255) DEFAULT NULL,
  `researchgate` varchar(255) DEFAULT NULL,
  `linkedin` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `phone_visible` tinyint(1) DEFAULT 0,
  `country` varchar(100) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `publications_count` int(11) DEFAULT 0,
  `studies_count` int(11) DEFAULT 0,
  `collaborations_count` int(11) DEFAULT 0,
  `profile_views` int(11) DEFAULT 0,
  `verified_badge` 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_unicode_ci;

--
-- Dumping data for table `researcher_profiles`
--

INSERT INTO `researcher_profiles` (`id`, `user_id`, `photo`, `title`, `specialization`, `institution_id`, `department`, `degrees`, `bio`, `research_interests`, `orcid_id`, `google_scholar`, `researchgate`, `linkedin`, `phone`, `phone_visible`, `country`, `city`, `publications_count`, `studies_count`, `collaborations_count`, `profile_views`, `verified_badge`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0, 0, 0, 0, 0, '2025-10-08 19:21:10', '2025-10-08 19:21:10'),
(2, 2, NULL, 'Platform Administrator', 'System Administration', NULL, NULL, NULL, 'Super administrator with full access to all platform features.', NULL, NULL, NULL, NULL, NULL, NULL, 0, NULL, NULL, 0, 0, 0, 0, 1, '2025-10-08 21:19:40', '2025-10-08 21:19:40');

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

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`, `description`, `created_at`) VALUES
(1, 'super_admin', 'Platform Super Administrator - Full control', '2025-10-08 18:21:12'),
(2, 'institution_admin', 'Institution Administrator - Manage local teams', '2025-10-08 18:21:12'),
(3, 'researcher', 'Researcher/Doctor - Main user role', '2025-10-08 18:21:12'),
(4, 'guest', 'Guest/Visitor - Limited access', '2025-10-08 18:21:12');

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

--
-- Table structure for table `role_permissions`
--

CREATE TABLE `role_permissions` (
  `role_id` int(11) UNSIGNED NOT NULL,
  `permission_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `settings` (
  `id` int(11) UNSIGNED NOT NULL,
  `key_name` varchar(100) NOT NULL,
  `value` text DEFAULT NULL,
  `type` varchar(50) DEFAULT 'string',
  `group_name` varchar(50) DEFAULT 'general',
  `category` varchar(50) DEFAULT 'general',
  `description` text 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 `settings`
--

INSERT INTO `settings` (`id`, `key_name`, `value`, `type`, `group_name`, `category`, `description`, `updated_at`) VALUES
(1, 'site_name', 'OBGYN Research Platform', 'string', 'general', 'general', 'Website name', '2025-10-08 18:21:13'),
(2, 'site_email', 'admin@obgynresearch.com', 'string', 'general', 'general', 'Contact email', '2025-10-08 18:21:13'),
(3, 'site_language', 'en', 'string', 'general', 'general', 'Default language', '2025-10-08 18:21:13'),
(4, 'registration_enabled', '1', 'boolean', 'users', 'general', 'Allow new registrations', '2025-10-08 18:21:13'),
(5, 'email_verification_required', '1', 'boolean', 'users', 'general', 'Require email verification', '2025-10-08 18:21:13'),
(6, 'max_upload_size', '10485760', 'integer', 'files', 'general', 'Max file upload size in bytes (10MB)', '2025-10-08 18:21:13'),
(7, 'allowed_file_types', 'pdf,doc,docx,xls,xlsx,jpg,jpeg,png', 'string', 'files', 'general', 'Allowed file extensions', '2025-10-08 18:21:13'),
(8, 'site_logo', '', 'string', 'branding', 'cms', 'Website logo path', '2025-10-08 21:08:29'),
(9, 'site_favicon', '', 'string', 'branding', 'cms', 'Website favicon path', '2025-10-08 21:08:29'),
(10, 'site_tagline', 'Advancing OBGYN Research Together', 'string', 'branding', 'cms', 'Website tagline/slogan', '2025-10-08 21:08:29'),
(11, 'brand_primary_color', '#007bff', 'string', 'branding', 'cms', 'Primary brand color', '2025-10-08 21:08:29'),
(12, 'brand_secondary_color', '#6c757d', 'string', 'branding', 'cms', 'Secondary brand color', '2025-10-08 21:08:29'),
(13, 'posts_per_page', '10', 'integer', 'blog', 'cms', 'Number of blog posts per page', '2025-10-08 21:08:29'),
(14, 'enable_comments', '1', 'boolean', 'blog', 'cms', 'Enable comments on blog posts', '2025-10-08 21:08:29'),
(15, 'comment_moderation', '1', 'boolean', 'blog', 'cms', 'Require comment approval', '2025-10-08 21:08:29'),
(16, 'enable_gallery', '1', 'boolean', 'media', 'cms', 'Enable photo gallery', '2025-10-08 21:08:29'),
(17, 'max_image_size', '5242880', 'integer', 'media', 'cms', 'Max image upload size in bytes (5MB)', '2025-10-08 21:08:29'),
(18, 'image_quality', '85', 'integer', 'media', 'cms', 'Image compression quality (1-100)', '2025-10-08 21:08:29'),
(19, 'thumbnail_width', '300', 'integer', 'media', 'cms', 'Thumbnail width in pixels', '2025-10-08 21:08:29'),
(20, 'thumbnail_height', '300', 'integer', 'media', 'cms', 'Thumbnail height in pixels', '2025-10-08 21:08:29'),
(21, 'enable_seo', '1', 'boolean', 'seo', 'cms', 'Enable SEO features', '2025-10-08 21:08:29'),
(22, 'google_analytics_id', '', 'string', 'seo', 'cms', 'Google Analytics tracking ID', '2025-10-08 21:08:29'),
(23, 'google_site_verification', '', 'string', 'seo', 'cms', 'Google site verification code', '2025-10-08 21:08:29'),
(24, 'facebook_app_id', '', 'string', 'social', 'cms', 'Facebook App ID for Open Graph', '2025-10-08 21:08:29'),
(25, 'company_name', 'GREC', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(26, 'tagline', 'GANNA Research Center', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(27, 'email_primary', 'info@GREC.com', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(28, 'email_support', 'support@GREC.com', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(29, 'phone_primary', '+20 11 12220298', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(30, 'phone_secondary', '+20 10 23288826', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(31, 'address_line1', 'Cairo - Egypt', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(32, 'address_line2', 'Gannah Hospital', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(33, 'city', '', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(34, 'state', '', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(35, 'country', '', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(36, 'postal_code', '', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(37, 'working_hours', 'Saturday - thursday 09:00 AM - 06:00 PM', 'string', 'general', 'contact', NULL, '2025-10-08 21:22:50'),
(38, 'map_latitude', '40.171', 'string', 'general', 'contact', NULL, '2025-10-08 21:24:09'),
(39, 'map_longitude', '-74.280', 'string', 'general', 'contact', NULL, '2025-10-08 21:24:09'),
(40, 'facebook', 'https://www.facebook.com/ahmed.sherif.908670', 'string', 'general', 'social_media', NULL, '2025-10-08 21:22:50'),
(41, 'twitter', 'https://www.facebook.com/ahmed.sherif.908670', 'string', 'general', 'social_media', NULL, '2025-10-08 21:22:50'),
(42, 'linkedin', 'https://www.facebook.com/ahmed.sherif.908670', 'string', 'general', 'social_media', NULL, '2025-10-08 21:22:50'),
(43, 'instagram', 'https://www.facebook.com/ahmed.sherif.908670', 'string', 'general', 'social_media', NULL, '2025-10-08 21:22:50'),
(44, 'youtube', 'https://www.facebook.com/ahmed.sherif.908670', 'string', 'general', 'social_media', NULL, '2025-10-08 21:22:50'),
(45, 'researchgate', 'https://www.facebook.com/ahmed.sherif.908670', 'string', 'general', 'social_media', NULL, '2025-10-08 21:22:50'),
(46, 'orcid', 'https://www.facebook.com/ahmed.sherif.908670', 'string', 'general', 'social_media', NULL, '2025-10-08 21:22:50');

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

--
-- Table structure for table `studies`
--

CREATE TABLE `studies` (
  `id` int(11) UNSIGNED NOT NULL,
  `title` varchar(500) NOT NULL,
  `slug` varchar(500) NOT NULL,
  `creator_id` int(11) UNSIGNED NOT NULL,
  `institution_id` int(11) UNSIGNED DEFAULT NULL,
  `category_id` int(11) UNSIGNED DEFAULT NULL,
  `type` enum('clinical','surgical','fertility','urogynecology','menopause','other') NOT NULL,
  `status` enum('draft','active','completed','archived','pending_approval') DEFAULT 'draft',
  `summary` text DEFAULT NULL,
  `objectives` text DEFAULT NULL,
  `methodology` text DEFAULT NULL,
  `outcomes` text DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `is_collaborative` tinyint(1) DEFAULT 0,
  `is_public` tinyint(1) DEFAULT 1,
  `approval_status` enum('pending','approved','rejected') DEFAULT 'pending',
  `approved_by` int(11) UNSIGNED DEFAULT NULL,
  `approved_at` datetime DEFAULT NULL,
  `views_count` int(11) DEFAULT 0,
  `downloads_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_unicode_ci;

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

--
-- Table structure for table `study_collaborators`
--

CREATE TABLE `study_collaborators` (
  `id` int(11) UNSIGNED NOT NULL,
  `study_id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `role` enum('lead','co_investigator','statistician','contributor','reviewer') NOT NULL,
  `status` enum('pending','active','declined','removed') DEFAULT 'pending',
  `invited_by` int(11) UNSIGNED DEFAULT NULL,
  `joined_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `study_documents`
--

CREATE TABLE `study_documents` (
  `id` int(11) UNSIGNED NOT NULL,
  `study_id` int(11) UNSIGNED NOT NULL,
  `uploaded_by` int(11) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_type` varchar(50) NOT NULL,
  `file_size` int(11) NOT NULL,
  `version` int(11) DEFAULT 1,
  `parent_document_id` int(11) UNSIGNED DEFAULT NULL,
  `downloads_count` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `study_tags`
--

CREATE TABLE `study_tags` (
  `study_id` int(11) UNSIGNED NOT NULL,
  `tag_id` int(11) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `subscriptions`
--

CREATE TABLE `subscriptions` (
  `id` int(11) UNSIGNED NOT NULL,
  `user_id` int(11) UNSIGNED NOT NULL,
  `plan_name` varchar(100) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(10) DEFAULT 'USD',
  `status` enum('active','expired','cancelled') DEFAULT 'active',
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `tags`
--

CREATE TABLE `tags` (
  `id` int(11) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `slug` varchar(100) NOT NULL,
  `usage_count` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `users` (
  `id` int(11) UNSIGNED NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `role_id` int(11) UNSIGNED NOT NULL DEFAULT 4,
  `status` enum('pending','active','suspended','inactive') DEFAULT 'pending',
  `email_verified` tinyint(1) DEFAULT 0,
  `verification_token` varchar(255) DEFAULT NULL,
  `reset_token` varchar(255) DEFAULT NULL,
  `reset_token_expiry` datetime DEFAULT NULL,
  `two_factor_enabled` tinyint(1) DEFAULT 0,
  `two_factor_secret` varchar(255) DEFAULT NULL,
  `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_unicode_ci;

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

INSERT INTO `users` (`id`, `email`, `password`, `first_name`, `last_name`, `role_id`, `status`, `email_verified`, `verification_token`, `reset_token`, `reset_token_expiry`, `two_factor_enabled`, `two_factor_secret`, `last_login`, `created_at`, `updated_at`) VALUES
(1, 'ahmed.sherif.abdelfattah@gmail.com', '$2y$12$mZW3PkUfnYoKrhFUJLgO..t6CoBXqkstOrVA8n7L/4K0AAwZ7unBW', 'Ahmed', 'Sherif', 3, 'active', 0, 'a1b47674235b9f8184e1bcb6d49f740cc327cbf9be833d01013d604c5055ec2f', NULL, NULL, 0, NULL, NULL, '2025-10-08 19:21:10', '2025-10-08 21:20:18'),
(2, 'admin@obgynresearch.com', '$2y$12$KYRcVkVYSkIGGb93mc952OaOD9Z9HsvOI2ZV/Qi.7NHUNd/5AKilG', 'Super', 'Admin', 1, 'active', 1, NULL, NULL, NULL, 0, NULL, '2025-10-09 00:20:09', '2025-10-08 21:19:40', '2025-10-08 21:20:09');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `analytics_data`
--
ALTER TABLE `analytics_data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `metric_name` (`metric_name`),
  ADD KEY `date` (`date`);

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

--
-- Indexes for table `audit_logs`
--
ALTER TABLE `audit_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `action` (`action`),
  ADD KEY `table_name` (`table_name`),
  ADD KEY `created_at` (`created_at`);

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

--
-- Indexes for table `chat_participants`
--
ALTER TABLE `chat_participants`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `room_user` (`room_id`,`user_id`),
  ADD KEY `user_id` (`user_id`);

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

--
-- Indexes for table `cms_features`
--
ALTER TABLE `cms_features`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_num` (`order_num`),
  ADD KEY `status` (`status`);

--
-- Indexes for table `cms_hero_sections`
--
ALTER TABLE `cms_hero_sections`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_num` (`order_num`),
  ADD KEY `status` (`status`);

--
-- Indexes for table `cms_media`
--
ALTER TABLE `cms_media`
  ADD PRIMARY KEY (`id`),
  ADD KEY `uploaded_by` (`uploaded_by`),
  ADD KEY `file_type` (`file_type`),
  ADD KEY `folder` (`folder`),
  ADD KEY `idx_type_folder` (`file_type`,`folder`);
ALTER TABLE `cms_media` ADD FULLTEXT KEY `search` (`title`,`alt_text`,`description`);

--
-- Indexes for table `cms_menus`
--
ALTER TABLE `cms_menus`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `location` (`location`),
  ADD KEY `status` (`status`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `cms_menu_items`
--
ALTER TABLE `cms_menu_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `menu_id` (`menu_id`),
  ADD KEY `parent_id` (`parent_id`),
  ADD KEY `page_id` (`page_id`),
  ADD KEY `order_num` (`order_num`),
  ADD KEY `idx_menu_parent_order` (`menu_id`,`parent_id`,`order_num`);

--
-- Indexes for table `cms_pages`
--
ALTER TABLE `cms_pages`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug_language` (`slug`,`language`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `idx_slug_status` (`slug`,`status`);

--
-- Indexes for table `cms_posts`
--
ALTER TABLE `cms_posts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug_language` (`slug`,`language`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `author_id` (`author_id`),
  ADD KEY `idx_status_published` (`status`,`published_at`);

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

--
-- Indexes for table `cms_slider_items`
--
ALTER TABLE `cms_slider_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `slider_id` (`slider_id`),
  ADD KEY `order_num` (`order_num`);

--
-- Indexes for table `cms_stats`
--
ALTER TABLE `cms_stats`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_num` (`order_num`),
  ADD KEY `status` (`status`);

--
-- Indexes for table `cms_team_members`
--
ALTER TABLE `cms_team_members`
  ADD PRIMARY KEY (`id`),
  ADD KEY `department` (`department`),
  ADD KEY `order_num` (`order_num`),
  ADD KEY `status` (`status`);

--
-- Indexes for table `cms_testimonials`
--
ALTER TABLE `cms_testimonials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `order_num` (`order_num`),
  ADD KEY `status` (`status`);

--
-- Indexes for table `cms_widgets`
--
ALTER TABLE `cms_widgets`
  ADD PRIMARY KEY (`id`),
  ADD KEY `location` (`location`),
  ADD KEY `order_num` (`order_num`),
  ADD KEY `status` (`status`),
  ADD KEY `created_by` (`created_by`);

--
-- Indexes for table `connections`
--
ALTER TABLE `connections`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `connection_pair` (`requester_id`,`receiver_id`),
  ADD KEY `receiver_id` (`receiver_id`),
  ADD KEY `status` (`status`);

--
-- Indexes for table `contact_approvals`
--
ALTER TABLE `contact_approvals`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `contact_pair` (`requester_id`,`receiver_id`),
  ADD KEY `receiver_id` (`receiver_id`);

--
-- Indexes for table `email_templates`
--
ALTER TABLE `email_templates`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `category` (`category`);

--
-- Indexes for table `followers`
--
ALTER TABLE `followers`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `follow_pair` (`follower_id`,`following_id`),
  ADD KEY `following_id` (`following_id`);

--
-- Indexes for table `institutions`
--
ALTER TABLE `institutions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `admin_user_id` (`admin_user_id`),
  ADD KEY `country` (`country`);

--
-- Indexes for table `institution_members`
--
ALTER TABLE `institution_members`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `institution_user` (`institution_id`,`user_id`),
  ADD KEY `user_id` (`user_id`);

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

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

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

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

--
-- Indexes for table `publications`
--
ALTER TABLE `publications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `study_id` (`study_id`);
ALTER TABLE `publications` ADD FULLTEXT KEY `search` (`title`,`abstract`,`keywords`);

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

--
-- Indexes for table `researcher_profiles`
--
ALTER TABLE `researcher_profiles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `user_id` (`user_id`),
  ADD KEY `institution_id` (`institution_id`),
  ADD KEY `country` (`country`);

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

--
-- Indexes for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD PRIMARY KEY (`role_id`,`permission_id`),
  ADD KEY `permission_id` (`permission_id`);

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

--
-- Indexes for table `studies`
--
ALTER TABLE `studies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `slug` (`slug`),
  ADD KEY `creator_id` (`creator_id`),
  ADD KEY `institution_id` (`institution_id`),
  ADD KEY `category_id` (`category_id`),
  ADD KEY `status` (`status`),
  ADD KEY `type` (`type`),
  ADD KEY `idx_status_public` (`status`,`is_public`);
ALTER TABLE `studies` ADD FULLTEXT KEY `search` (`title`,`summary`,`objectives`);

--
-- Indexes for table `study_collaborators`
--
ALTER TABLE `study_collaborators`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `study_user` (`study_id`,`user_id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `study_documents`
--
ALTER TABLE `study_documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `study_id` (`study_id`),
  ADD KEY `uploaded_by` (`uploaded_by`),
  ADD KEY `parent_document_id` (`parent_document_id`);

--
-- Indexes for table `study_tags`
--
ALTER TABLE `study_tags`
  ADD PRIMARY KEY (`study_id`,`tag_id`),
  ADD KEY `tag_id` (`tag_id`);

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

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

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

--
-- Constraints for table `chat_participants`
--
ALTER TABLE `chat_participants`
  ADD CONSTRAINT `chat_participants_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `chat_rooms` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `chat_participants_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `chat_rooms`
--
ALTER TABLE `chat_rooms`
  ADD CONSTRAINT `chat_rooms_ibfk_1` FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `chat_rooms_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

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

--
-- Constraints for table `cms_menu_items`
--
ALTER TABLE `cms_menu_items`
  ADD CONSTRAINT `cms_menu_items_ibfk_1` FOREIGN KEY (`menu_id`) REFERENCES `cms_menus` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `cms_menu_items_ibfk_2` FOREIGN KEY (`parent_id`) REFERENCES `cms_menu_items` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `cms_menu_items_ibfk_3` FOREIGN KEY (`page_id`) REFERENCES `cms_pages` (`id`) ON DELETE SET NULL;

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

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

--
-- Constraints for table `cms_slider_items`
--
ALTER TABLE `cms_slider_items`
  ADD CONSTRAINT `cms_slider_items_ibfk_1` FOREIGN KEY (`slider_id`) REFERENCES `cms_sliders` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `connections`
--
ALTER TABLE `connections`
  ADD CONSTRAINT `connections_ibfk_1` FOREIGN KEY (`requester_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `connections_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `contact_approvals`
--
ALTER TABLE `contact_approvals`
  ADD CONSTRAINT `contact_approvals_ibfk_1` FOREIGN KEY (`requester_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `contact_approvals_ibfk_2` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `followers`
--
ALTER TABLE `followers`
  ADD CONSTRAINT `followers_ibfk_1` FOREIGN KEY (`follower_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `followers_ibfk_2` FOREIGN KEY (`following_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `institutions`
--
ALTER TABLE `institutions`
  ADD CONSTRAINT `institutions_ibfk_1` FOREIGN KEY (`admin_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `institution_members`
--
ALTER TABLE `institution_members`
  ADD CONSTRAINT `institution_members_ibfk_1` FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `institution_members_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `messages`
--
ALTER TABLE `messages`
  ADD CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `chat_rooms` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `messages_ibfk_2` FOREIGN KEY (`sender_id`) REFERENCES `users` (`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 `publications`
--
ALTER TABLE `publications`
  ADD CONSTRAINT `publications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `publications_ibfk_2` FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `publication_comments`
--
ALTER TABLE `publication_comments`
  ADD CONSTRAINT `publication_comments_ibfk_1` FOREIGN KEY (`publication_id`) REFERENCES `publications` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `publication_comments_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

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

--
-- Constraints for table `role_permissions`
--
ALTER TABLE `role_permissions`
  ADD CONSTRAINT `role_permissions_ibfk_1` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `role_permissions_ibfk_2` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `studies`
--
ALTER TABLE `studies`
  ADD CONSTRAINT `studies_ibfk_1` FOREIGN KEY (`creator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `studies_ibfk_2` FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `study_collaborators`
--
ALTER TABLE `study_collaborators`
  ADD CONSTRAINT `study_collaborators_ibfk_1` FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `study_collaborators_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `study_documents`
--
ALTER TABLE `study_documents`
  ADD CONSTRAINT `study_documents_ibfk_1` FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `study_documents_ibfk_2` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `study_documents_ibfk_3` FOREIGN KEY (`parent_document_id`) REFERENCES `study_documents` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `study_tags`
--
ALTER TABLE `study_tags`
  ADD CONSTRAINT `study_tags_ibfk_1` FOREIGN KEY (`study_id`) REFERENCES `studies` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `study_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE;

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


ALTER TABLE audit_logs ADD COLUMN description TEXT DEFAULT NULL AFTER record_id;