-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               8.4.3 - MySQL Community Server - GPL
-- Server OS:                    Win64
-- HeidiSQL Version:             12.8.0.6908
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for intranet_db
CREATE DATABASE IF NOT EXISTS `intranet_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `intranet_db`;

-- Dumping structure for table intranet_db.announcements
CREATE TABLE IF NOT EXISTS `announcements` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_by` int DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `content` text,
  `author` varchar(50) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_announcements_created_by` (`created_by`),
  KEY `updated_by` (`updated_by`),
  CONSTRAINT `announcements_ibfk_1` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_announcements_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table intranet_db.announcements: ~6 rows (approximately)
INSERT INTO `announcements` (`id`, `created_by`, `title`, `content`, `author`, `created_at`, `updated_at`, `updated_by`) VALUES
	(1, 1, 'Important: Annual Staff Meeting', 'The annual staff meeting will be held on December 15th at 10 AM in the main conference hall. All staff are required to attend.', 'Admin User', '2025-12-11 19:39:10', NULL, NULL),
	(2, 1, 'New Policy on Remote Work', 'Please review the updated remote work policy available in the Document Repository. It outlines new guidelines and procedures for working from home.', 'HR Department', '2025-12-11 19:39:10', NULL, NULL),
	(3, 1, 'Holiday Party Invitation', 'Join us for our annual holiday party on December 20th at The Grand Ballroom. RSVP by December 10th.', 'Social Committee', '2025-12-11 19:39:10', NULL, NULL),
	(4, 1, 'Test new Announcement', '<p>Dear LSC members, kind reminder that this CNY dinner organized by Main Committee is Member self pay. LSC is giving support to Main Committee and trying to form 10 tables. Section will give a mystery gift to those attending under Marketing Section. Remember the theme of the night, "<strong style="color: rgb(153, 51, 255);">Shanghai Grand</strong>". Thank you.</p>', 'LSC Admin', '2026-01-28 10:54:54', NULL, NULL),
	(5, 3, 'This announcement is posted by sales123', '<p>Let\'s test if marketing123 can edit it, yes i can</p>', 'sales123', '2026-01-30 10:07:52', '2026-02-06 11:32:12', 3),
	(6, 2, 'marketing123', '<p>Posted by marketing123, test edit date</p>', 'marketing123', '2026-01-30 10:08:41', '2026-01-30 10:56:29', 1);

-- Dumping structure for table intranet_db.doctor_leave
CREATE TABLE IF NOT EXISTS `doctor_leave` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_by` int DEFAULT NULL,
  `doctor_name` varchar(100) NOT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_doctor_leave_created_by` (`created_by`),
  CONSTRAINT `fk_doctor_leave_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table intranet_db.doctor_leave: ~4 rows (approximately)
INSERT INTO `doctor_leave` (`id`, `created_by`, `doctor_name`, `reason`, `start_date`, `end_date`) VALUES
	(1, 1, 'Dr. Alice Smith', 'Annual Leave', '2025-12-13', '2025-12-13'),
	(2, 1, 'Dr. Bob Johnson', 'Conference Attendance', '2025-12-16', '2026-01-29'),
	(3, 1, 'Dr. Charlie Brown', 'Sick Leave', '2025-12-12', '2025-12-12'),
	(4, 1, 'Dr. Alice Smith', 'Annual Leave', '2025-12-14', '2025-12-14');

-- Dumping structure for table intranet_db.documents
CREATE TABLE IF NOT EXISTS `documents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_by` int DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `description` text,
  `filename` varchar(255) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `file_type` varchar(50) DEFAULT 'document',
  `album_id` int DEFAULT NULL,
  `retention_days` int DEFAULT NULL,
  `uploaded_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `album_id` (`album_id`),
  KEY `fk_documents_created_by` (`created_by`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `photo_albums` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_documents_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table intranet_db.documents: ~10 rows (approximately)
INSERT INTO `documents` (`id`, `created_by`, `title`, `description`, `filename`, `file_path`, `file_type`, `album_id`, `retention_days`, `uploaded_at`) VALUES
	(1, 1, 'Company Handbook 2026', 'The latest version of the company handbook, valid for the year 2025.', 'Company_Handbook_2025.pdf', 'uploads/Company_Handbook_2025.pdf', 'document', NULL, NULL, '2025-12-11 19:39:10'),
	(2, 1, 'Q3 Performance Report', 'Summary of company performance for the third quarter.', 'Q3_Performance_Report.xlsx', 'uploads/Q3_Performance_Report.xlsx', 'document', NULL, 30, '2025-12-11 19:39:10'),
	(3, 1, 'Marketing Strategy Draft', 'Draft document for the upcoming marketing campaign.', 'Marketing_Strategy_Draft.docx', 'uploads/Marketing_Strategy_Draft.docx', 'document', NULL, 7, '2025-12-11 19:39:10'),
	(6, 1, 'Album Photo 1-1: Morning Briefing', 'Team discussing daily tasks.', 'album1_photo1.jpg', 'uploads/photos/album1_photo1.jpg', 'image', 1, NULL, '2025-12-11 19:39:15'),
	(7, 1, 'Album Photo 1-2: Lunch Break', 'Colleagues enjoying lunch.', 'album1_photo2.jpg', 'uploads/photos/album1_photo2.jpg', 'image', 1, NULL, '2025-12-11 19:39:15'),
	(8, 1, 'Album Photo 2-1: Award Ceremony', 'Celebrating achievements.', 'album2_photo1.jpg', 'uploads/photos/album2_photo1.jpg', 'image', 2, NULL, '2025-12-11 19:39:15'),
	(9, 1, 'Album Photo 2-2: Group Photo', 'Team photo at the event.', 'album2_photo2.jpg', 'uploads/photos/album2_photo2.jpg', 'image', 2, NULL, '2025-12-11 19:39:15'),
	(10, 1, 'Dummy Photo 1: Scenic View', 'A beautiful landscape.', 'individual_photo1.jpg', 'uploads/photos/individual_photo1.jpg', 'image', NULL, NULL, '2025-12-11 19:39:15'),
	(13, 1, 'Test', 'test', 'Chinese New Year.jpg', 'uploads/photos/6979754bab5d3.jpg', 'image', NULL, NULL, '2026-01-28 10:32:43'),
	(14, 1, 'test 6mb', '', 'Gemini_Generated_Image_8vgdc18vgdc18vgd.png', 'uploads/photos/697c0ac296810.png', 'image', NULL, NULL, '2026-01-30 09:34:58');

-- Dumping structure for table intranet_db.photo_albums
CREATE TABLE IF NOT EXISTS `photo_albums` (
  `id` int NOT NULL AUTO_INCREMENT,
  `created_by` int DEFAULT NULL,
  `album_name` varchar(255) NOT NULL,
  `description` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `album_name` (`album_name`),
  KEY `fk_photo_albums_created_by` (`created_by`),
  CONSTRAINT `fk_photo_albums_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table intranet_db.photo_albums: ~2 rows (approximately)
INSERT INTO `photo_albums` (`id`, `created_by`, `album_name`, `description`, `created_at`) VALUES
	(1, 1, 'Dummy Album 1: Office Life', 'Candid shots from around the office.', '2025-12-11 19:39:15'),
	(2, 1, 'Dummy Album 2: Company Event', 'Photos from our annual company gathering.', '2025-12-11 19:39:15');

-- Dumping structure for table intranet_db.settings
CREATE TABLE IF NOT EXISTS `settings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `setting_name` varchar(255) NOT NULL,
  `setting_value` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_name` (`setting_name`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table intranet_db.settings: ~4 rows (approximately)
INSERT INTO `settings` (`id`, `setting_name`, `setting_value`, `created_at`, `updated_at`) VALUES
	(1, 'current_theme', 'malaysia_national_day', '2025-12-11 12:38:58', '2026-01-23 05:47:12'),
	(2, 'max_photo_size', '5', '2026-01-22 07:49:37', '2026-01-30 02:06:11'),
	(3, 'home_announcement_limit', '4', '2026-01-28 02:57:22', '2026-01-28 02:57:22'),
	(4, 'home_document_limit', '2', '2026-01-28 02:57:22', '2026-01-28 02:57:34');

-- Dumping structure for table intranet_db.users
CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('admin','visitor') DEFAULT 'visitor',
  `full_name` varchar(100) DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table intranet_db.users: ~3 rows (approximately)
INSERT INTO `users` (`id`, `username`, `password`, `role`, `full_name`, `created_at`) VALUES
	(1, 'admin', '$2y$10$dok0lqRrGZAAqqOi5eieLeIedexzo/KIiqI/UT.86gyvyxWWL5mZa', 'admin', 'LSC Admin', '2025-12-11 19:38:51'),
	(2, 'marketing', '$2y$12$XYhpd50D0Gl10yXWkLqbY.bTuJ4I1X.nOdSu.dO8BDod6BHLAmcmK', 'visitor', 'marketing123', '2026-01-22 15:39:55'),
	(3, 'Sales', '$2y$12$CR9Y1HSfk7aaFYHnP5Bi5.1fd0fxW3Rc/BLuF47K0LZiUm.Q6/Fnu', 'visitor', 'sales123', '2026-01-30 10:06:47');

-- Dumping structure for table intranet_db.user_permissions
CREATE TABLE IF NOT EXISTS `user_permissions` (
  `user_id` int NOT NULL,
  `module_name` varchar(50) NOT NULL,
  `can_edit` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`user_id`,`module_name`),
  CONSTRAINT `user_permissions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table intranet_db.user_permissions: ~12 rows (approximately)
INSERT INTO `user_permissions` (`user_id`, `module_name`, `can_edit`) VALUES
	(1, 'announcements', 1),
	(1, 'doctor_leave', 1),
	(1, 'documents', 1),
	(1, 'photos', 1),
	(2, 'announcements', 1),
	(2, 'doctor_leave', 0),
	(2, 'documents', 1),
	(2, 'photos', 0),
	(3, 'announcements', 1),
	(3, 'doctor_leave', 0),
	(3, 'documents', 1),
	(3, 'photos', 0);

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;