-- ==========================================================
-- KUETx Database Schema
-- Charset: utf8mb4 / Collation: utf8mb4_unicode_ci
-- ==========================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- 1. USERS TABLE
CREATE TABLE IF NOT EXISTS `users` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NULL,
    `display_name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(191) NULL UNIQUE,
    `avatar` VARCHAR(255) NULL,
    `bio` VARCHAR(500) NULL,
    `department` VARCHAR(100) NULL,
    `batch_session` VARCHAR(50) NULL,
    `is_anonymous` TINYINT(1) NOT NULL DEFAULT 0,
    `role` ENUM('user', 'moderator', 'admin') NOT NULL DEFAULT 'user',
    `status` ENUM('active', 'suspended', 'deleted') NOT NULL DEFAULT 'active',
    `auth_provider` ENUM('local', 'google') NOT NULL DEFAULT 'local',
    `remember_token` VARCHAR(100) NULL,
    `two_factor_secret` VARCHAR(100) NULL,
    `two_factor_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `two_factor_confirmed_at` DATETIME NULL,
    `last_login_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_username` (`username`),
    INDEX `idx_status` (`status`),
    INDEX `idx_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. OAUTH ACCOUNTS TABLE
CREATE TABLE IF NOT EXISTS `oauth_accounts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `provider` VARCHAR(50) NOT NULL DEFAULT 'google',
    `provider_user_id` VARCHAR(191) NOT NULL,
    `provider_email` VARCHAR(191) NULL,
    `avatar_url` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_provider_uid` (`provider`, `provider_user_id`),
    CONSTRAINT `fk_oauth_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. POSTS TABLE
CREATE TABLE IF NOT EXISTS `posts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `content` TEXT NOT NULL,
    `visibility` ENUM('public', 'private') NOT NULL DEFAULT 'public',
    `status` ENUM('pending', 'approved', 'rejected', 'deleted') NOT NULL DEFAULT 'pending',
    `moderation_note` TEXT NULL,
    `moderated_by` BIGINT UNSIGNED NULL,
    `moderated_at` DATETIME NULL,
    `reactions_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `comments_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `shares_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_posts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_posts_moderator` FOREIGN KEY (`moderated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    INDEX `idx_posts_status_created` (`status`, `created_at`),
    INDEX `idx_posts_user_status` (`user_id`, `status`),
    FULLTEXT KEY `ft_posts_content` (`content`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. POST IMAGES TABLE
CREATE TABLE IF NOT EXISTS `post_images` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `post_id` BIGINT UNSIGNED NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `thumb_path` VARCHAR(255) NOT NULL,
    `file_size` INT UNSIGNED NOT NULL,
    `mime_type` VARCHAR(50) NOT NULL,
    `display_order` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_post_images_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    INDEX `idx_post_images_order` (`post_id`, `display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. POST REACTIONS TABLE
CREATE TABLE IF NOT EXISTS `post_reactions` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `post_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `reaction_type` ENUM('spotted', 'caught', 'lol', 'bruh', 'wtf', 'respect') NOT NULL DEFAULT 'spotted',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_post_user_reaction` (`post_id`, `user_id`),
    CONSTRAINT `fk_reactions_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_reactions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_reaction_type` (`post_id`, `reaction_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. COMMENTS TABLE
CREATE TABLE IF NOT EXISTS `comments` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `post_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `content` VARCHAR(1000) NOT NULL,
    `status` ENUM('active', 'hidden', 'deleted') NOT NULL DEFAULT 'active',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_comments_post` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_comments_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_comments_post_created` (`post_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. SHARES TABLE
CREATE TABLE IF NOT EXISTS `shares` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `original_post_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `caption` VARCHAR(500) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_shares_post` FOREIGN KEY (`original_post_id`) REFERENCES `posts` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_shares_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_shares_user` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. TEACHERS TABLE
CREATE TABLE IF NOT EXISTS `teachers` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(150) NOT NULL,
    `designation` VARCHAR(100) NOT NULL,
    `department` VARCHAR(100) NOT NULL,
    `email` VARCHAR(191) NULL,
    `phone` VARCHAR(50) NULL,
    `office` VARCHAR(150) NULL,
    `photo` VARCHAR(255) NULL,
    `bio` TEXT NULL,
    `official_url` VARCHAR(500) NULL,
    `rating_avg` DECIMAL(3, 2) NOT NULL DEFAULT 0.00,
    `rating_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_teacher_dept` (`department`),
    INDEX `idx_teacher_rating` (`rating_avg`),
    FULLTEXT KEY `ft_teacher_search` (`name`, `department`, `designation`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. TEACHER REVIEWS TABLE
CREATE TABLE IF NOT EXISTS `teacher_reviews` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `teacher_id` BIGINT UNSIGNED NOT NULL,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `rating` TINYINT UNSIGNED NOT NULL CHECK (`rating` BETWEEN 1 AND 5),
    `comment` TEXT NULL,
    `status` ENUM('pending', 'approved', 'rejected', 'hidden') NOT NULL DEFAULT 'pending',
    `moderation_note` VARCHAR(255) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_teacher_user_review` (`teacher_id`, `user_id`),
    CONSTRAINT `fk_reviews_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_reviews_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_reviews_teacher_status` (`teacher_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. ADS TABLE
CREATE TABLE IF NOT EXISTS `ads` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `ad_size` ENUM('468x60', '300x250') NOT NULL,
    `ad_code` MEDIUMTEXT NOT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `weight` INT NOT NULL DEFAULT 1,
    `views_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `clicks_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. REPORTS TABLE
CREATE TABLE IF NOT EXISTS `reports` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `reporter_id` BIGINT UNSIGNED NOT NULL,
    `target_type` ENUM('post', 'comment', 'teacher_review', 'user') NOT NULL,
    `target_id` BIGINT UNSIGNED NOT NULL,
    `reason` ENUM('Spam', 'Harassment', 'Offensive content', 'Fake information', 'Inappropriate image', 'Other') NOT NULL,
    `details` TEXT NULL,
    `status` ENUM('pending', 'investigating', 'resolved', 'dismissed') NOT NULL DEFAULT 'pending',
    `admin_notes` TEXT NULL,
    `resolved_by` BIGINT UNSIGNED NULL,
    `resolved_at` DATETIME NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_reports_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_reports_resolver` FOREIGN KEY (`resolved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
    INDEX `idx_reports_status` (`status`),
    INDEX `idx_reports_target` (`target_type`, `target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. NOTIFICATIONS TABLE
CREATE TABLE IF NOT EXISTS `notifications` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `type` VARCHAR(50) NOT NULL,
    `title` VARCHAR(255) NOT NULL,
    `message` TEXT NOT NULL,
    `link` VARCHAR(255) NULL,
    `is_read` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_notif_user_read` (`user_id`, `is_read`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. SITE SETTINGS TABLE
CREATE TABLE IF NOT EXISTS `site_settings` (
    `setting_key` VARCHAR(100) PRIMARY KEY,
    `setting_value` MEDIUMTEXT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. ADMIN LOGS TABLE
CREATE TABLE IF NOT EXISTS `admin_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `admin_id` BIGINT UNSIGNED NOT NULL,
    `action` VARCHAR(100) NOT NULL,
    `target_type` VARCHAR(50) NULL,
    `target_id` BIGINT UNSIGNED NULL,
    `description` TEXT NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` VARCHAR(255) NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_admin_logs_admin` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
    INDEX `idx_admin_logs_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
-- ==========================================================
-- KUETx Demo & Initial Seeds
-- ==========================================================

SET NAMES utf8mb4;

-- SITE SETTINGS
INSERT INTO `site_settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'KUETx'),
('site_tagline', 'KUET, Unfiltered.'),
('site_description', 'The independent student community platform for Khulna University of Engineering & Technology.'),
('footer_disclaimer', 'KUETx is an independent student community platform and is not officially affiliated with KUET.'),
('primary_color', '#4f46e5'),
('dark_mode_default', '0'),
('max_post_length', '5000'),
('max_comment_length', '1000'),
('max_images_per_post', '5'),
('max_image_size_mb', '10'),
('ad_frequency_min', '5'),
('ad_frequency_max', '10'),
('ad_placement_enabled', '1'),
('post_moderation_enabled', '1'),
('review_moderation_enabled', '1'),
('maintenance_mode', '0')
ON DUPLICATE KEY UPDATE `setting_value` = VALUES(`setting_value`);

-- DEMO USERS (Admin password: Admin@123456, User password: User@123456)
INSERT INTO `users` (`id`, `username`, `password_hash`, `display_name`, `email`, `avatar`, `bio`, `department`, `batch_session`, `is_anonymous`, `role`, `status`, `auth_provider`, `created_at`) VALUES
(1, 'admin', '$2y$10$0melTj.17RePjFjyJ39MFeIB183PCD522PMIY/atDFw5WuRS.Eiam', 'KUETx Admin', 'admin@kuetx.com', NULL, 'Official platform administrator & moderation team.', 'Computer Science & Engineering', '2k18', 0, 'admin', 'active', 'local', NOW()),
(2, 'nabil_hasan', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Nabil Hasan', 'nabil@example.com', NULL, 'Passionate coder, coffee drinker, and CSE enthusiast.', 'Computer Science & Engineering', '2k20', 0, 'user', 'active', 'local', NOW()),
(3, 'sadia_afrin', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Sadia Afrin', 'sadia@example.com', NULL, 'Circuit lover, robotics club member, KUETian.', 'Electrical & Electronic Engineering', '2k21', 0, 'user', 'active', 'local', NOW()),
(4, 'tanvir_ahmed', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Tanvir Ahmed', 'tanvir@example.com', NULL, 'Automotive & thermodynamics. Ready for graduation!', 'Mechanical Engineering', '2k19', 0, 'user', 'active', 'local', NOW()),
(5, 'kuetstudent24', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Anonymous', 'anon1@example.com', NULL, 'Observing campus life from the shadows.', 'Civil Engineering', '2k21', 1, 'user', 'active', 'local', NOW()),
(6, 'kuet_insider', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Anonymous', 'anon2@example.com', NULL, 'Spotted updates & whispers around the lakes.', 'Computer Science & Engineering', '2k22', 1, 'user', 'active', 'local', NOW()),
(7, 'mehedi_hasan', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Mehedi Hasan', 'mehedi@example.com', NULL, 'Supply chain & lean operations enthusiast.', 'Industrial Engineering & Management', '2k22', 0, 'user', 'active', 'local', NOW()),
(8, 'faiza_rahman', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Faiza Rahman', 'faiza@example.com', NULL, 'Signals, telecommunication, and photography.', 'Electronics & Communication Engineering', '2k21', 0, 'user', 'active', 'local', NOW()),
(9, 'arif_chowdhury', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Arif Chowdhury', 'arif@example.com', NULL, 'Polymer science & sustainable footwear technology.', 'Leather Engineering', '2k20', 0, 'user', 'active', 'local', NOW()),
(10, 'rokon_uzzaman', '$2y$10$9JZGT4.qotZOAw0KluO49eunNlR4Qbga.FfUHMAZNU6QzluPh9ZCu', 'Rokon Uzzaman', 'rokon@example.com', NULL, 'Bio-instrumentation & medical diagnostics.', 'Biomedical Engineering', '2k23', 0, 'user', 'active', 'local', NOW())
ON DUPLICATE KEY UPDATE `username` = VALUES(`username`);

-- 10 REALISTIC KUET TEACHERS
INSERT INTO `teachers` (`id`, `name`, `designation`, `department`, `email`, `phone`, `office`, `photo`, `bio`, `official_url`, `rating_avg`, `rating_count`, `created_at`) VALUES
(1, 'Prof. Dr. K. M. Azharul Hasan', 'Professor', 'Computer Science & Engineering', 'azharul@cse.kuet.ac.bd', '+880-41-769468 Ext. 400', 'New Academic Building, CSE Dept', NULL, 'Specialization: Data Mining, Database Systems, Cloud Computing, Parallel Processing.', 'https://kuet.ac.bd/cse/azharul', 4.80, 15, NOW()),
(2, 'Prof. Dr. M. M. A. Hashem', 'Professor', 'Computer Science & Engineering', 'hashem@cse.kuet.ac.bd', '+880-41-769468 Ext. 401', 'CSE Department, Room 302', NULL, 'Specialization: Artificial Intelligence, Soft Computing, Neural Networks, Pattern Recognition.', 'https://kuet.ac.bd/cse/hashem', 4.65, 20, NOW()),
(3, 'Prof. Dr. Kazi Md. Rokibul Alam', 'Professor', 'Computer Science & Engineering', 'rokibul@cse.kuet.ac.bd', '+880-41-769468 Ext. 405', 'CSE Department, Room 304', NULL, 'Specialization: Machine Learning, Bio-informatics, Natural Language Processing.', 'https://kuet.ac.bd/cse/rokibul', 4.50, 12, NOW()),
(4, 'Prof. Dr. Md. Rafiqul Islam', 'Professor', 'Electrical & Electronic Engineering', 'rafiqul@eee.kuet.ac.bd', '+880-41-769468 Ext. 300', 'EEE Department, Room 201', NULL, 'Specialization: Power System Analysis, Renewable Energy Systems, Smart Grid.', 'https://kuet.ac.bd/eee/rafiqul', 4.70, 14, NOW()),
(5, 'Prof. Dr. Mohammad Shaifur Rahman', 'Professor', 'Electrical & Electronic Engineering', 'shaifur@eee.kuet.ac.bd', '+880-41-769468 Ext. 305', 'EEE Department, Room 204', NULL, 'Specialization: VLSI Design, Semiconductor Devices, Nanotechnology.', 'https://kuet.ac.bd/eee/shaifur', 4.30, 9, NOW()),
(6, 'Prof. Dr. Mohammad Ariful Islam', 'Professor', 'Mechanical Engineering', 'ariful@me.kuet.ac.bd', '+880-41-769468 Ext. 200', 'Mechanical Engineering Building', NULL, 'Specialization: Fluid Mechanics, CFD, Aerodynamics, Thermal Systems.', 'https://kuet.ac.bd/me/ariful', 4.40, 11, NOW()),
(7, 'Prof. Dr. Quazi Hamidul Bari', 'Professor', 'Civil Engineering', 'qhbari@ce.kuet.ac.bd', '+880-41-769468 Ext. 100', 'Civil Engineering Building, Room 105', NULL, 'Specialization: Environmental Engineering, Water & Wastewater Treatment, Arsenic Mitigation.', 'https://kuet.ac.bd/ce/qhbari', 4.85, 18, NOW()),
(8, 'Prof. Dr. Md. Mostafizur Rahman', 'Professor', 'Electronics & Communication Engineering', 'mostafizur@ece.kuet.ac.bd', '+880-41-769468 Ext. 500', 'ECE Building, Room 203', NULL, 'Specialization: Wireless Communications, RF Engineering, Optical Fiber Networks.', 'https://kuet.ac.bd/ece/mostafizur', 4.25, 8, NOW()),
(9, 'Prof. Dr. Subrata Saha', 'Professor', 'Industrial Engineering & Management', 'subrata@iem.kuet.ac.bd', '+880-41-769468 Ext. 600', 'IEM Building, Room 102', NULL, 'Specialization: Operations Research, Supply Chain Optimization, Production Systems.', 'https://kuet.ac.bd/iem/subrata', 4.60, 10, NOW()),
(10, 'Dr. Md. Abul Hashem', 'Professor', 'Leather Engineering', 'hashem@le.kuet.ac.bd', '+880-41-769468 Ext. 700', 'Leather Engineering Building', NULL, 'Specialization: Leather Processing Technology, Solid Waste Management, Environmental Impact.', 'https://kuet.ac.bd/le/hashem', 4.55, 7, NOW())
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- 20 POSTS (Mix of approved, pending, rejected)
INSERT INTO `posts` (`id`, `user_id`, `content`, `status`, `moderation_note`, `moderated_by`, `moderated_at`, `reactions_count`, `comments_count`, `shares_count`, `created_at`) VALUES
-- Approved posts
(1, 2, 'Welcome to KUETx! The long-awaited unfiltered student network is finally live. Share your campus moments, rate teachers fairly, and stay connected with the entire KUET family. 🚀✨', 'approved', 'Welcome announcement approved.', 1, NOW(), 28, 6, 4, DATE_SUB(NOW(), INTERVAL 5 HOUR)),
(2, 3, 'Spotted a family of ducks peacefully swimming near the Central Mosque pond this morning before the 8:00 AM class. KUET campus mornings just hit different! 🦆🌿', 'approved', 'Wholesome campus post.', 1, NOW(), 42, 5, 2, DATE_SUB(NOW(), INTERVAL 4 HOUR)),
(3, 5, 'To the legend who left an extra umbrella outside the CSE library during today’s sudden thunderstorm: you literally saved my laptop from drowning. Respect! 🫡🌧️', 'approved', 'Approved wholesome post.', 1, NOW(), 35, 3, 1, DATE_SUB(NOW(), INTERVAL 3 HOUR)),
(4, 4, 'Friendly reminder that the KUET Inter-Department Football Tournament starts next Friday! Mechanical squad is gearing up for the trophy. Which department are you backing this year? ⚽🏆', 'approved', 'Sports announcement.', 1, NOW(), 19, 8, 3, DATE_SUB(NOW(), INTERVAL 3 HOUR)),
(5, 7, 'Has anyone else noticed how serene the Shahid Minar area looks at sunset? Best place on campus to clear your mind after back-to-back lab sessions. 🌅', 'approved', 'Campus appreciation.', 1, NOW(), 22, 2, 0, DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(6, 6, 'Overheard at the cafeteria: "Bro, thermodynamic cycles are just spicy water heating up and cooling down." Whoever said that, please tutor me before term finals! 😂☕', 'approved', 'Student banter.', 1, NOW(), 51, 9, 7, DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(7, 8, 'Telecommunications lab report submission deadline is in 24 hours. Coffee consumption has officially reached critical levels across all residential halls. ☕⚡', 'approved', 'Student life relatable.', 1, NOW(), 16, 4, 1, DATE_SUB(NOW(), INTERVAL 1 HOUR)),
(8, 9, 'Leather Engineering department has organized a mini seminar on sustainable leather tanning and zero-waste discharges. Very insightful session by Dr. Hashem sir! 🌿👞', 'approved', 'Academic highlight.', 1, NOW(), 12, 1, 0, DATE_SUB(NOW(), INTERVAL 1 HOUR)),
(9, 2, 'Algorithm contest practice this Saturday at 3:00 PM in Lab 1. Open for all batches (2k20 to 2k23). Let’s solve some interesting graph and dynamic programming problems together! 💻🔥', 'approved', 'Coding event.', 1, NOW(), 24, 7, 5, DATE_SUB(NOW(), INTERVAL 45 MINUTE)),
(10, 10, 'Biomedical engineering senior project showcase is happening next month. Can’t wait to show what we have been building with biosensors and microcontrollers! 🩺🔬', 'approved', 'Showcase announcement.', 1, NOW(), 15, 2, 1, DATE_SUB(NOW(), INTERVAL 30 MINUTE)),
(11, 3, 'Power Electronics assignment is finally submitted! Taking a well-deserved walk around the lake. The winter breeze is starting to kick in. ❄️🚶‍♀️', 'approved', 'Approved.', 1, NOW(), 18, 3, 0, DATE_SUB(NOW(), INTERVAL 20 MINUTE)),
(12, 5, 'Pro tip for new freshers: always check the lab notice board on Sunday mornings instead of relying on group chat screenshots. Trust me on this one. 👀💡', 'approved', 'Helpful tip.', 1, NOW(), 29, 4, 3, DATE_SUB(NOW(), INTERVAL 10 MINUTE)),

-- Pending posts (for moderation queue testing)
(13, 4, 'Lost my Casio fx-991EX calculator in the ME drawing room (desk row 3) today around 2 PM. If anyone spotted it, please reply here or return to Hall office! 🙏', 'pending', NULL, NULL, NULL, 0, 0, 0, DATE_SUB(NOW(), INTERVAL 8 MINUTE)),
(14, 6, 'Anyone selling their second-hand bicycle? Preferably one with working brakes and a front basket. Commuting from Rokeya Hall is tiring on foot! 🚲', 'pending', NULL, NULL, NULL, 0, 0, 0, DATE_SUB(NOW(), INTERVAL 6 MINUTE)),
(15, 7, 'Are the library group study rooms open during the weekend this month? Need a quiet spot for final year design project discussions.', 'pending', NULL, NULL, NULL, 0, 0, 0, DATE_SUB(NOW(), INTERVAL 5 MINUTE)),
(16, 8, 'Found a student ID card near the auditorium gate belonging to someone from CE 2k22 batch. Left it with the security desk.', 'pending', NULL, NULL, NULL, 0, 0, 0, DATE_SUB(NOW(), INTERVAL 3 MINUTE)),
(17, 2, 'Looking for teammates for the upcoming National Hackathon. Looking for one frontend dev and one ML enthusiast. DM or ping me!', 'pending', NULL, NULL, NULL, 0, 0, 0, DATE_SUB(NOW(), INTERVAL 1 MINUTE)),

-- Rejected posts
(18, 4, 'Buy cheap essay writing services and assignment shortcuts! Click this sketchy link now bit.ly/fake-link-12345', 'rejected', 'Blatant spam and commercial solicitation.', 1, NOW(), 0, 0, 0, DATE_SUB(NOW(), INTERVAL 6 HOUR)),
(19, 6, 'Targeting and badmouthing specific student roommate in room 402 with personal phone numbers.', 'rejected', 'Violation of harassment & doxxing community guidelines.', 1, NOW(), 0, 0, 0, DATE_SUB(NOW(), INTERVAL 5 HOUR)),
(20, 5, 'Gambling website promotional code get 500 bonus now.', 'rejected', 'Illegal / promotional gambling link.', 1, NOW(), 0, 0, 0, DATE_SUB(NOW(), INTERVAL 4 HOUR))
ON DUPLICATE KEY UPDATE `content` = VALUES(`content`);

-- POST REACTIONS DEMO
INSERT INTO `post_reactions` (`post_id`, `user_id`, `reaction_type`, `created_at`) VALUES
(1, 2, 'spotted', NOW()),
(1, 3, 'respect', NOW()),
(1, 4, 'spotted', NOW()),
(1, 5, 'caught', NOW()),
(2, 2, 'spotted', NOW()),
(2, 4, 'spotted', NOW()),
(2, 5, 'lol', NOW()),
(3, 2, 'respect', NOW()),
(3, 3, 'respect', NOW()),
(6, 2, 'lol', NOW()),
(6, 3, 'bruh', NOW()),
(6, 4, 'wtf', NOW()),
(6, 5, 'lol', NOW())
ON DUPLICATE KEY UPDATE `reaction_type` = VALUES(`reaction_type`);

-- COMMENTS DEMO
INSERT INTO `comments` (`post_id`, `user_id`, `content`, `status`, `created_at`) VALUES
(1, 3, 'Super excited for KUETx! Clean UI and feels great on mobile.', 'active', DATE_SUB(NOW(), INTERVAL 4 HOUR)),
(1, 4, 'Finally a dedicated place for KUETians. Great job!', 'active', DATE_SUB(NOW(), INTERVAL 4 HOUR)),
(1, 5, 'Love the anonymous mode feature. Keep it up dev team!', 'active', DATE_SUB(NOW(), INTERVAL 3 HOUR)),
(2, 2, 'The campus ducks are the true faculty of KUET. 😂', 'active', DATE_SUB(NOW(), INTERVAL 3 HOUR)),
(2, 5, 'I was there! One of them was literally chasing bread crumbs.', 'active', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(6, 4, 'As an ME student, I feel attacked by this thermodynamic definition. 😂', 'active', DATE_SUB(NOW(), INTERVAL 1 HOUR)),
(6, 2, 'Can confirm, that was probably Tanvir shouting in the cafeteria!', 'active', DATE_SUB(NOW(), INTERVAL 1 HOUR))
ON DUPLICATE KEY UPDATE `content` = VALUES(`content`);

-- TEACHER REVIEWS DEMO
INSERT INTO `teacher_reviews` (`id`, `teacher_id`, `user_id`, `rating`, `comment`, `status`, `created_at`) VALUES
(1, 1, 2, 5, 'Excellent teacher! His Database Management Systems and Cloud Computing lectures are crystal clear and very practical.', 'approved', DATE_SUB(NOW(), INTERVAL 2 DAY)),
(2, 1, 3, 5, 'Very supportive during lab evaluations. Always encourages independent thinking and quality research.', 'approved', DATE_SUB(NOW(), INTERVAL 1 DAY)),
(3, 2, 2, 5, 'A pioneer in AI and soft computing. His insights into neural networks are legendary.', 'approved', DATE_SUB(NOW(), INTERVAL 3 DAY)),
(4, 2, 5, 4, 'Challenging exams, but you learn so much from his courses. Definitely recommended!', 'approved', DATE_SUB(NOW(), INTERVAL 2 DAY)),
(5, 7, 5, 5, 'Bari sir is an authority in environmental engineering. Extremely humble and deeply knowledgeable.', 'approved', DATE_SUB(NOW(), INTERVAL 4 DAY)),
(6, 4, 3, 5, 'Makes electrical power systems feel intuitive with practical grid examples.', 'approved', DATE_SUB(NOW(), INTERVAL 1 DAY)),
(7, 3, 2, 4, 'Rokibul sir’s machine learning assignments are tough but extremely rewarding for your portfolio.', 'approved', DATE_SUB(NOW(), INTERVAL 12 HOUR)),
-- Pending reviews for moderation test
(8, 6, 4, 5, 'Ariful sir’s fluid dynamics explanations with CFD simulations are awesome!', 'pending', DATE_SUB(NOW(), INTERVAL 2 HOUR)),
(9, 9, 7, 5, 'Great supply chain case studies that prepare you for industry operations.', 'pending', DATE_SUB(NOW(), INTERVAL 1 HOUR))
ON DUPLICATE KEY UPDATE `comment` = VALUES(`comment`);

-- DEMO ADS
INSERT INTO `ads` (`id`, `name`, `ad_size`, `ad_code`, `is_active`, `weight`, `views_count`, `clicks_count`, `created_at`) VALUES
(1, 'KUET Tech Fest 2026 Banner', '468x60', '<div class="kuetx-ad-banner" style="background: linear-gradient(135deg, #1e1b4b 0%, #312e81 100%); color: #ffffff; padding: 12px 18px; border-radius: 12px; display: flex; align-items: center; justify-content: space-between; border: 1px solid rgba(99,102,241,0.3);"><div style="display:flex; align-items:center; gap:12px;"><span style="font-size:24px;">🚀</span><div><strong style="font-size:14px; display:block; color:#e0e7ff;">KUET National Tech Fest 2026</strong><span style="font-size:12px; color:#a5b4fc;">Registration now open for Project Showcasing & Hackathon!</span></div></div><a href="https://kuet.ac.bd" target="_blank" rel="noopener" style="background:#4f46e5; color:#fff; text-decoration:none; padding:6px 14px; border-radius:8px; font-size:12px; font-weight:600; white-space:nowrap;">Register Now →</a></div>', 1, 10, 150, 24, NOW()),
(2, 'KUET Career Club Workshop', '300x250', '<div class="kuetx-ad-box" style="background: linear-gradient(145deg, #0f172a, #1e293b); color: #ffffff; padding: 18px; border-radius: 14px; text-align: center; border: 1px solid rgba(56,189,248,0.2);"><div style="font-size:32px; margin-bottom:8px;">💼</div><h4 style="margin:0 0 6px 0; font-size:16px; color:#38bdf8;">KUET Career Club</h4><p style="font-size:12px; color:#94a3b8; line-height:1.4; margin-bottom:14px;">Master resume building, mock tech interviews, and global scholarship applications.</p><a href="https://kuet.ac.bd" target="_blank" rel="noopener" style="display:inline-block; background:linear-gradient(135deg, #0284c7, #2563eb); color:#fff; text-decoration:none; padding:8px 18px; border-radius:8px; font-size:13px; font-weight:600;">Join Free Workshop</a></div>', 1, 10, 120, 18, NOW())
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- DEMO NOTIFICATIONS
INSERT INTO `notifications` (`user_id`, `type`, `title`, `message`, `link`, `is_read`, `created_at`) VALUES
(2, 'system', 'Welcome to KUETx!', 'Your account has been activated. Start exploring the campus feed and teacher directory.', '/', 1, DATE_SUB(NOW(), INTERVAL 5 HOUR)),
(2, 'post_approved', 'Post Approved', 'Your post regarding Algorithm contest practice has been approved by admin.', '/post/9', 0, DATE_SUB(NOW(), INTERVAL 45 MINUTE)),
(3, 'comment', 'New Comment', 'Nabil Hasan commented on your post about campus ducks.', '/post/2', 0, DATE_SUB(NOW(), INTERVAL 2 HOUR));

-- DEMO ADMIN LOGS
INSERT INTO `admin_logs` (`admin_id`, `action`, `target_type`, `target_id`, `description`, `ip_address`, `created_at`) VALUES
(1, 'APPROVE_POST', 'post', 1, 'Admin approved post #1 (Welcome to KUETx)', '127.0.0.1', DATE_SUB(NOW(), INTERVAL 5 HOUR)),
(1, 'APPROVE_POST', 'post', 2, 'Admin approved post #2 (Pond ducks)', '127.0.0.1', DATE_SUB(NOW(), INTERVAL 4 HOUR)),
(1, 'REJECT_POST', 'post', 18, 'Admin rejected post #18 (Spam essay writing service)', '127.0.0.1', DATE_SUB(NOW(), INTERVAL 6 HOUR)),
(1, 'APPROVE_REVIEW', 'teacher_review', 1, 'Admin approved review #1 for Prof. Dr. K. M. Azharul Hasan', '127.0.0.1', DATE_SUB(NOW(), INTERVAL 2 DAY));