-- --------------------------------------------------------
-- Server OS:                    Win64
-- HeidiSQL Version:             12.12.0.7122
-- --------------------------------------------------------



-- Dumping database structure for planner_ds
CREATE DATABASE IF NOT EXISTS `planner_ds` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;
USE `planner_ds`;

-- Dumping structure for table planner_ds.calendars
CREATE TABLE IF NOT EXISTS `calendars` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `color` varchar(7) NOT NULL DEFAULT '#000000',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `calendars_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=39 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.calendars: ~3 rows (approximately)
INSERT INTO `calendars` (`id`, `user_id`, `name`, `color`) VALUES
	(35, 29, 'calendar first', '#000000'),
	(36, 29, 'shared calendar example', '#000000'),
	(37, 31, 'shared calendar participant example', '#000000'),
	(38, 32, 'calendar reminder test', '#000000');

-- Dumping structure for table planner_ds.calendar_access
CREATE TABLE IF NOT EXISTS `calendar_access` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `calendar_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `can_edit` tinyint(4) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_access` (`calendar_id`,`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.calendar_access: ~3 rows (approximately)
INSERT INTO `calendar_access` (`id`, `calendar_id`, `user_id`, `can_edit`) VALUES
	(13, 36, 30, 0),
	(14, 36, 31, 1),
	(15, 37, 29, 1);

-- Dumping structure for table planner_ds.friends
CREATE TABLE IF NOT EXISTS `friends` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `friend_id` int(11) NOT NULL,
  `can_edit` tinyint(1) DEFAULT 0,
  `calendar_id` int(11) DEFAULT NULL,
  `status` enum('pending','accepted') DEFAULT 'pending',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `friend_id` (`friend_id`),
  CONSTRAINT `friends_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `friends_ibfk_2` FOREIGN KEY (`friend_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.friends: ~5 rows (approximately)
INSERT INTO `friends` (`id`, `user_id`, `friend_id`, `can_edit`, `calendar_id`, `status`) VALUES
	(54, 29, 30, 0, NULL, 'accepted'),
	(55, 29, 31, 0, NULL, 'accepted'),
	(56, 29, 30, 0, 36, 'accepted'),
	(57, 29, 31, 1, 36, 'accepted'),
	(58, 31, 29, 1, 37, 'accepted');

-- Dumping structure for table planner_ds.reminders
CREATE TABLE IF NOT EXISTS `reminders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `task_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `reminder_time` datetime NOT NULL,
  `is_sent` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `task_id` (`task_id`),
  KEY `idx_reminders_time` (`reminder_time`,`is_sent`),
  KEY `idx_reminders_user` (`user_id`),
  CONSTRAINT `reminders_ibfk_1` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE CASCADE,
  CONSTRAINT `reminders_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=119 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.reminders: ~10 rows (approximately)
INSERT INTO `reminders` (`id`, `task_id`, `user_id`, `reminder_time`, `is_sent`, `created_at`) VALUES
	(107, 54, 29, '2026-06-19 11:46:00', 0, '2026-06-05 09:47:01'),
	(108, 54, 29, '2026-06-18 12:46:00', 0, '2026-06-05 09:47:01'),
	(109, 55, 29, '2026-06-26 11:47:00', 0, '2026-06-05 09:47:19'),
	(110, 55, 29, '2026-06-25 12:47:00', 0, '2026-06-05 09:47:19'),
	(111, 56, 29, '2026-06-21 11:47:00', 0, '2026-06-05 09:47:40'),
	(112, 56, 29, '2026-06-20 12:47:00', 0, '2026-06-05 09:47:40'),
	(113, 57, 31, '2026-06-21 11:51:00', 0, '2026-06-05 09:51:39'),
	(114, 57, 31, '2026-06-20 12:51:00', 0, '2026-06-05 09:51:39'),
	(115, 57, 29, '2026-06-21 11:51:00', 0, '2026-06-05 09:51:39'),
	(116, 57, 29, '2026-06-20 12:51:00', 0, '2026-06-05 09:51:39'),
	(117, 58, 32, '2026-06-05 12:54:00', 1, '2026-06-05 09:53:22'),
	(118, 58, 32, '2026-06-04 13:54:00', 1, '2026-06-05 09:53:22');

-- Dumping structure for table planner_ds.tasks
CREATE TABLE IF NOT EXISTS `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `calendar_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `color` varchar(7) NOT NULL DEFAULT '#1E90FF',
  `description` text DEFAULT NULL,
  `start_datetime` datetime NOT NULL,
  `end_datetime` datetime NOT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `assigned_to` (`assigned_to`),
  KEY `idx_tasks_datetime` (`start_datetime`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.tasks: ~4 rows (approximately)
INSERT INTO `tasks` (`id`, `user_id`, `calendar_id`, `title`, `color`, `description`, `start_datetime`, `end_datetime`, `assigned_to`) VALUES
	(54, 29, 35, 'first task', '#59ff00', 'example', '2026-06-19 12:46:00', '2026-06-21 12:46:00', NULL),
	(55, 29, 35, 'chores', '#FF4500', 'example', '2026-06-26 12:47:00', '2026-06-26 12:47:00', NULL),
	(56, 29, 35, 'another task', '#8A2BE2', 'task', '2026-06-21 12:47:00', '2026-06-27 12:47:00', NULL),
	(57, 29, 37, 'participant task', '#FFD700', 'task', '2026-06-21 12:51:00', '2026-06-27 12:51:00', NULL),
	(58, 32, 38, 'task', '#1E90FF', '', '2026-06-05 13:54:00', '2026-06-05 13:54:00', NULL);

-- Dumping structure for table planner_ds.teams
CREATE TABLE IF NOT EXISTS `teams` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `created_by` int(11) NOT NULL,
  `can_edit` tinyint(1) DEFAULT 0,
  `calendar_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `teams_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.teams: ~3 rows (approximately)
INSERT INTO `teams` (`id`, `name`, `created_by`, `can_edit`, `calendar_id`) VALUES
	(11, 'friends team example', 29, 0, NULL),
	(12, 'another team', 29, 0, NULL),
	(13, 'participant team', 30, 0, NULL);

-- Dumping structure for table planner_ds.team_members
CREATE TABLE IF NOT EXISTS `team_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `team_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `can_edit` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `team_id` (`team_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `team_members_ibfk_1` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE,
  CONSTRAINT `team_members_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.team_members: ~4 rows (approximately)
INSERT INTO `team_members` (`id`, `team_id`, `user_id`, `can_edit`) VALUES
	(11, 13, 29, 0),
	(12, 12, 30, 0),
	(13, 12, 31, 0),
	(14, 11, 31, 0);

-- Dumping structure for table planner_ds.user_notification_preferences
CREATE TABLE IF NOT EXISTS `user_notification_preferences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `reminder_1h` tinyint(1) DEFAULT 1,
  `reminder_6h` tinyint(1) DEFAULT 0,
  `reminder_12h` tinyint(1) DEFAULT 0,
  `reminder_24h` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  CONSTRAINT `user_notification_preferences_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table planner_ds.user_notification_preferences: ~3 rows (approximately)
INSERT INTO `user_notification_preferences` (`id`, `user_id`, `reminder_1h`, `reminder_6h`, `reminder_12h`, `reminder_24h`) VALUES
	(29, 29, 1, 0, 0, 1),
	(30, 30, 1, 0, 0, 1),
	(31, 31, 1, 0, 0, 1),
	(32, 32, 1, 0, 0, 1);

