/*
 Navicat Premium Data Transfer

 Source Server         : MariaDB
 Source Server Type    : MariaDB
 Source Server Version : 120302
 Source Host           : localhost:3308
 Source Schema         : website_tracker

 Target Server Type    : MariaDB
 Target Server Version : 120302
 File Encoding         : 65001

 Date: 14/08/2026 01:37:15
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for companies
-- ----------------------------
DROP TABLE IF EXISTS `companies`;
CREATE TABLE `companies`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of companies
-- ----------------------------
INSERT INTO `companies` VALUES (1, 'Dolphin Manufacturing LLC', 'DML', 1, '2026-08-14 01:27:16', '2026-08-14 01:27:16');

-- ----------------------------
-- Table structure for content_reminders
-- ----------------------------
DROP TABLE IF EXISTS `content_reminders`;
CREATE TABLE `content_reminders`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `website_id` int(10) UNSIGNED NOT NULL,
  `hod_user_id` int(10) UNSIGNED NOT NULL,
  `due_date` date NOT NULL,
  `sent_at` datetime NULL DEFAULT NULL,
  `status` enum('Pending','Responded','No Response') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Pending',
  `response_type` enum('Has Updates','No Updates') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `response_notes` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `responded_at` datetime NULL DEFAULT NULL,
  `generated_request_id` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_reminder_website`(`website_id`) USING BTREE,
  INDEX `fk_reminder_hod`(`hod_user_id`) USING BTREE,
  INDEX `fk_reminder_request`(`generated_request_id`) USING BTREE,
  INDEX `idx_reminder_status`(`status`) USING BTREE,
  INDEX `idx_reminder_due`(`due_date`) USING BTREE,
  CONSTRAINT `fk_reminder_hod` FOREIGN KEY (`hod_user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_reminder_request` FOREIGN KEY (`generated_request_id`) REFERENCES `website_requests` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `fk_reminder_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of content_reminders
-- ----------------------------

-- ----------------------------
-- Table structure for notifications
-- ----------------------------
DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) UNSIGNED NOT NULL,
  `title` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `link_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_notification_user_read`(`user_id`, `is_read`) USING BTREE,
  CONSTRAINT `fk_notification_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of notifications
-- ----------------------------
INSERT INTO `notifications` VALUES (1, 1, 'New website request', 'WEB-202608-0001 - Add upcoming exhibition details', 'request-view.php?id=1', 1, '2026-08-14 01:31:53');

-- ----------------------------
-- Table structure for request_attachments
-- ----------------------------
DROP TABLE IF EXISTS `request_attachments`;
CREATE TABLE `request_attachments`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `uploaded_by` int(10) UNSIGNED NOT NULL,
  `original_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `stored_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `mime_type` varchar(120) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `file_size` bigint(20) UNSIGNED NULL DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_attachments_request`(`request_id`) USING BTREE,
  INDEX `fk_attachments_user`(`uploaded_by`) USING BTREE,
  CONSTRAINT `fk_attachments_request` FOREIGN KEY (`request_id`) REFERENCES `website_requests` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_attachments_user` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of request_attachments
-- ----------------------------

-- ----------------------------
-- Table structure for request_comments
-- ----------------------------
DROP TABLE IF EXISTS `request_comments`;
CREATE TABLE `request_comments`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `user_id` int(10) UNSIGNED NOT NULL,
  `comment` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_internal` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_comments_request`(`request_id`) USING BTREE,
  INDEX `fk_comments_user`(`user_id`) USING BTREE,
  CONSTRAINT `fk_comments_request` FOREIGN KEY (`request_id`) REFERENCES `website_requests` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_comments_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of request_comments
-- ----------------------------

-- ----------------------------
-- Table structure for request_status_history
-- ----------------------------
DROP TABLE IF EXISTS `request_status_history`;
CREATE TABLE `request_status_history`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `request_id` bigint(20) UNSIGNED NOT NULL,
  `changed_by` int(10) UNSIGNED NOT NULL,
  `old_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `new_status` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `note` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `fk_status_request`(`request_id`) USING BTREE,
  INDEX `fk_status_user`(`changed_by`) USING BTREE,
  CONSTRAINT `fk_status_request` FOREIGN KEY (`request_id`) REFERENCES `website_requests` (`id`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `fk_status_user` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of request_status_history
-- ----------------------------
INSERT INTO `request_status_history` VALUES (1, 1, 1, NULL, 'Submitted', 'Request created', '2026-08-14 01:31:53');

-- ----------------------------
-- Table structure for request_types
-- ----------------------------
DROP TABLE IF EXISTS `request_types`;
CREATE TABLE `request_types`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of request_types
-- ----------------------------
INSERT INTO `request_types` VALUES (1, 'New Page / Section', 'Create a new website page or section', 1);
INSERT INTO `request_types` VALUES (2, 'Existing Page Change', 'Change text, images, layout or other existing page content', 1);
INSERT INTO `request_types` VALUES (3, 'New Product Page', 'Add a new product page', 1);
INSERT INTO `request_types` VALUES (4, 'Product Technical Details', 'Add or revise product technical data/specifications', 1);
INSERT INTO `request_types` VALUES (5, 'Blog / News', 'Publish a blog, news item or article', 1);
INSERT INTO `request_types` VALUES (6, 'Banner / Campaign', 'Add or revise a website banner or campaign', 1);
INSERT INTO `request_types` VALUES (7, 'Image / Document', 'Upload or replace website images, brochures, PDFs or documents', 1);
INSERT INTO `request_types` VALUES (8, 'SEO / Meta Update', 'Update title, description, keywords or SEO-related content', 1);
INSERT INTO `request_types` VALUES (9, 'Remove / Deactivate Content', 'Remove or deactivate existing content', 1);
INSERT INTO `request_types` VALUES (10, 'Other', 'Other website request', 1);

-- ----------------------------
-- Table structure for roles
-- ----------------------------
DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `name`(`name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of roles
-- ----------------------------
INSERT INTO `roles` VALUES (1, 'Admin');
INSERT INTO `roles` VALUES (3, 'HOD');
INSERT INTO `roles` VALUES (2, 'IT Team');

-- ----------------------------
-- Table structure for settings
-- ----------------------------
DROP TABLE IF EXISTS `settings`;
CREATE TABLE `settings`  (
  `setting_key` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `setting_value` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  PRIMARY KEY (`setting_key`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of settings
-- ----------------------------
INSERT INTO `settings` VALUES ('default_due_days', '7');
INSERT INTO `settings` VALUES ('group_name', 'Dolphin Group');
INSERT INTO `settings` VALUES ('reminder_grace_days', '7');

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `role_id` int(10) UNSIGNED NOT NULL,
  `company_id` int(10) UNSIGNED NULL DEFAULT NULL,
  `full_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `password_hash` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `must_change_password` tinyint(1) NOT NULL DEFAULT 0,
  `last_login_at` datetime NULL DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `email`(`email`) USING BTREE,
  INDEX `fk_users_role`(`role_id`) USING BTREE,
  INDEX `fk_users_company`(`company_id`) USING BTREE,
  CONSTRAINT `fk_users_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES (1, 1, NULL, 'Sammar', 'itstaff1@dolrad.ae', '$2y$10$12EW6.6pfpo8bWkGQL4ys.5ztc9QpVni/KOwXpY4kLJti6fAdgt0i', NULL, 1, 0, '2026-08-14 01:25:31', '2026-08-14 01:17:41', '2026-08-14 01:25:31');
INSERT INTO `users` VALUES (2, 3, 1, 'Abdul', 'abdul@dolphinml.com', '$2y$10$pR9hyBhwAUhw5UNzGhQv/.wfWKb/z48s5whX4bfHS2L48IOKHttYC', NULL, 1, 1, '2026-08-14 01:33:54', '2026-08-14 01:29:32', '2026-08-14 01:33:54');

-- ----------------------------
-- Table structure for website_requests
-- ----------------------------
DROP TABLE IF EXISTS `website_requests`;
CREATE TABLE `website_requests`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_no` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `website_id` int(10) UNSIGNED NOT NULL,
  `request_type_id` int(10) UNSIGNED NOT NULL,
  `requested_by` int(10) UNSIGNED NOT NULL,
  `assigned_to` int(10) UNSIGNED NULL DEFAULT NULL,
  `title` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `page_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `priority` enum('Low','Normal','High','Urgent') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Normal',
  `status` enum('Draft','Submitted','IT Review','In Progress','Waiting for HOD','Completed','Closed','Rejected','Correction Required') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Submitted',
  `due_date` date NULL DEFAULT NULL,
  `hod_approval_note` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL,
  `completed_at` datetime NULL DEFAULT NULL,
  `closed_at` datetime NULL DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `ticket_no`(`ticket_no`) USING BTREE,
  INDEX `fk_requests_type`(`request_type_id`) USING BTREE,
  INDEX `idx_requests_status`(`status`) USING BTREE,
  INDEX `idx_requests_website`(`website_id`) USING BTREE,
  INDEX `idx_requests_requested_by`(`requested_by`) USING BTREE,
  INDEX `idx_requests_assigned_to`(`assigned_to`) USING BTREE,
  CONSTRAINT `fk_requests_assignee` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT,
  CONSTRAINT `fk_requests_requester` FOREIGN KEY (`requested_by`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_requests_type` FOREIGN KEY (`request_type_id`) REFERENCES `request_types` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_requests_website` FOREIGN KEY (`website_id`) REFERENCES `websites` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of website_requests
-- ----------------------------
INSERT INTO `website_requests` VALUES (1, 'WEB-202608-0001', 1, 3, 1, NULL, 'Add upcoming exhibition details', 'Add each exhibition details', 'https://dolphinml.com/news-blogs/', 'Normal', 'Submitted', '2026-08-21', NULL, NULL, NULL, '2026-08-14 01:31:53', '2026-08-14 01:31:53');

-- ----------------------------
-- Table structure for websites
-- ----------------------------
DROP TABLE IF EXISTS `websites`;
CREATE TABLE `websites`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `company_id` int(10) UNSIGNED NOT NULL,
  `website_name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `domain` varchar(190) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `primary_hod_user_id` int(10) UNSIGNED NULL DEFAULT NULL,
  `reminder_enabled` tinyint(1) NOT NULL DEFAULT 1,
  `reminder_interval_days` int(11) NOT NULL DEFAULT 30,
  `last_content_review_date` date NULL DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `uq_websites_domain`(`domain`) USING BTREE,
  INDEX `fk_websites_company`(`company_id`) USING BTREE,
  INDEX `fk_websites_hod`(`primary_hod_user_id`) USING BTREE,
  CONSTRAINT `fk_websites_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk_websites_hod` FOREIGN KEY (`primary_hod_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of websites
-- ----------------------------
INSERT INTO `websites` VALUES (1, 1, 'Dolphin Manufacturing LLC', 'www.dolphinml.com', NULL, 1, 30, NULL, 1, '2026-08-14 01:28:25', '2026-08-14 01:28:25');

SET FOREIGN_KEY_CHECKS = 1;