-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `therufts_chatdate`
--

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

--
-- Table structure for table `jars`
--

CREATE TABLE `jars` (
  `id` int(11) NOT NULL,
  `jar_name` enum('A','B') NOT NULL,
  `type` enum('seeker','treasure') NOT NULL,
  `status` enum('active','selected') DEFAULT 'active',
  `assigned_to` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `jars`
--

INSERT INTO `jars` (`id`, `jar_name`, `type`, `status`, `assigned_to`) VALUES
(1, 'A', 'seeker', 'active', NULL),
(3, 'B', 'seeker', 'active', NULL),
(4, 'A', 'seeker', 'active', NULL),
(5, 'A', 'treasure', 'selected', 69),
(6, 'A', 'treasure', 'active', NULL),
(7, 'A', 'treasure', 'active', NULL),
(8, 'B', 'treasure', 'active', NULL),
(10, 'B', 'treasure', 'active', NULL),
(11, 'B', 'seeker', 'active', NULL),
(12, 'B', 'treasure', 'active', NULL),
(13, 'B', 'seeker', 'active', NULL),
(14, 'A', 'seeker', 'active', NULL),
(15, 'A', 'seeker', 'active', NULL);

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

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

CREATE TABLE `messages` (
  `msg_id` int(11) NOT NULL,
  `incoming_msg_id` int(255) NOT NULL,
  `outgoing_msg_id` int(255) NOT NULL,
  `msg` varchar(1000) NOT NULL,
  `timestamp` datetime NOT NULL DEFAULT current_timestamp(),
  `seen` tinyint(1) NOT NULL DEFAULT 0,
  `parent_message_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` (`msg_id`, `incoming_msg_id`, `outgoing_msg_id`, `msg`, `timestamp`, `seen`, `parent_message_id`) VALUES
(1, 66, 69, 'hi', '2024-12-13 09:30:01', 0, NULL),
(2, 66, 69, 'whats happening ', '2024-12-13 09:30:07', 0, NULL),
(3, 66, 69, 'hope this has been resolved now?', '2024-12-13 09:30:17', 0, NULL),
(4, 66, 69, 'ok bye', '2024-12-13 09:30:22', 0, NULL),
(5, 67, 69, 'hi', '2024-12-13 09:34:49', 0, NULL),
(6, 67, 69, 'hi', '2024-12-13 09:40:28', 0, NULL),
(7, 67, 69, 'ok nicw', '2024-12-13 09:41:19', 0, NULL),
(8, 67, 69, 'now much error', '2024-12-13 09:41:26', 0, NULL),
(9, 69, 63, 'hi', '2024-12-13 11:03:51', 0, NULL);

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

--
-- Table structure for table `pickup_lines`
--

CREATE TABLE `pickup_lines` (
  `id` int(11) NOT NULL,
  `slot_number` varchar(10) NOT NULL,
  `pickup_line` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `replied_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `pickup_lines`
--

INSERT INTO `pickup_lines` (`id`, `slot_number`, `pickup_line`, `created_at`, `replied_at`) VALUES
(41, 'M2', 'Are you a magician? Because whenever I look at you, everyone else disappears.\"\n\nIt’s lighthearted and sure to make someone smile!', '2024-12-12 10:07:11', '2024-12-13 08:17:58'),
(42, 'M2', 'No need to flatter but you look really nice', '2024-12-12 10:51:31', NULL),
(43, 'M2', 'Just checking in', '2024-12-12 16:49:57', '2024-12-13 08:01:51');

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

--
-- Table structure for table `player_slots`
--

CREATE TABLE `player_slots` (
  `slot_id` int(11) NOT NULL,
  `player_name` varchar(255) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `slot_number` varchar(3) NOT NULL,
  `status` enum('Available','Occupied') DEFAULT 'Available',
  `picture_path` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `player_slots`
--

INSERT INTO `player_slots` (`slot_id`, `player_name`, `gender`, `slot_number`, `status`, `picture_path`) VALUES
(1, 'Robert King', 'M', 'M1', 'Occupied', 'uploads/maleavater.jpg'),
(2, 'David Carter', 'M', 'M2', 'Occupied', 'uploads/maleavater1.jpg'),
(3, 'James Smith', 'M', 'M3', 'Occupied', 'uploads/maleavater2.jpg'),
(4, '', 'M', 'M4', 'Available', 'uploads/maleavater3.jpg'),
(5, '', 'M', 'M5', 'Available', 'uploads/maleavater4.jpg'),
(6, '', 'M', 'M6', 'Available', 'uploads/maleavater5.jpg'),
(7, 'Mary Atom', 'M', 'M7', 'Occupied', 'uploads/maleavater6.jpg'),
(8, '', 'M', 'M8', 'Available', 'uploads/maleavater7.jpg'),
(9, '', 'M', 'M9', 'Available', 'uploads/maleavater9.jpg'),
(10, '', 'M', 'M10', 'Available', 'uploads/maleavater10.jpg'),
(11, '', 'F', 'F1', 'Available', 'uploads/femaleavater8.jpg'),
(12, '', 'F', 'F2', 'Available', 'uploads/femaleavater7.jpg'),
(13, 'Ene Jacob', 'F', 'F3', 'Occupied', 'uploads/femaleavater6.jpg'),
(14, '', 'F', 'F4', 'Available', 'uploads/femaleavater3.jpg'),
(15, '', 'F', 'F5', 'Available', 'uploads/femaleavater5.jpg'),
(16, 'Catherine Lee', 'F', 'F6', 'Occupied', 'uploads/femaleavater4.jpg'),
(17, '', 'F', 'F7', 'Available', 'uploads/femaleavater.jpg');

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

--
-- Table structure for table `replies`
--

CREATE TABLE `replies` (
  `id` int(11) NOT NULL,
  `pickup_line_id` int(11) NOT NULL,
  `reply` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `slot_number` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `replies`
--

INSERT INTO `replies` (`id`, `pickup_line_id`, `reply`, `created_at`, `slot_number`) VALUES
(58, 41, 'Wow really??', '2024-12-12 10:11:11', 'F3'),
(59, 41, 'Please stop', '2024-12-12 10:47:52', 'F3'),
(60, 43, 'this is nice', '2024-12-13 08:01:51', 'M2'),
(61, 41, 'Boring', '2024-12-13 08:17:58', 'M2');

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

--
-- Table structure for table `selections`
--

CREATE TABLE `selections` (
  `id` int(11) NOT NULL,
  `player_id` int(11) NOT NULL,
  `jar_id` int(11) NOT NULL,
  `selected_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `selections`
--

INSERT INTO `selections` (`id`, `player_id`, `jar_id`, `selected_at`) VALUES
(8, 69, 5, '2024-12-15 09:51:14');

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

--
-- Table structure for table `tv`
--

CREATE TABLE `tv` (
  `id` int(11) NOT NULL,
  `video_link` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `tv`
--

INSERT INTO `tv` (`id`, `video_link`, `created_at`) VALUES
(4, '<iframe width=\"1349\" height=\"480\" src=\"https://www.youtube.com/embed/7FGsmDU2LGc\" title=\"Akpan and Oduma &#39;Shina Rambo&#39;\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen></iframe>', '2024-12-15 14:30:05'),
(5, '<iframe width=\"1349\" height=\"480\" src=\"https://www.youtube.com/embed/ab90EInQbjo\" title=\"BOVI IDENTITY CRISIS LAGOS SPECIAL EDITION\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen></iframe>', '2024-12-15 14:33:51'),
(6, '<iframe width=\"1349\" height=\"480\" src=\"https://www.youtube.com/embed/zLTDnBXjcvI\" title=\"AFRICAN FATHERS VS AFRICAN MOTHERS\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen></iframe>', '2024-12-15 16:33:39');

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

--
-- Table structure for table `useradmin`
--

CREATE TABLE `useradmin` (
  `id` int(11) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `photopath` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') DEFAULT 'inactive',
  `name` varchar(255) DEFAULT NULL,
  `last_login` timestamp NULL DEFAULT NULL,
  `role` text DEFAULT 'Admin'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `useradmin`
--

INSERT INTO `useradmin` (`id`, `email`, `password`, `created_at`, `photopath`, `status`, `name`, `last_login`, `role`) VALUES
(1, 'cojboi@gmail.com', '$2y$10$AEHvu2vbeJm1Xv7vvAuK4.zFRRRGLvEXO.b9cO.GM7yTp6wM6u87O', '2024-12-10 09:50:29', '67587f4d8b7e6_Untitled design.png', 'active', 'COJ', '2024-12-15 16:33:55', 'Admin'),
(2, 'coj@myriad.com.ng', '$2y$10$b.1G.PIrO9YvQgHjGn/uGu1iOQ2flYT5JwawUvVDv/J7DtU7297ii', '2024-12-10 10:50:12', '67587f8ed696f_Screenshot 2024-11-06 205059.png', 'active', 'Onaji', '2024-12-10 17:50:48', 'Admin'),
(4, 'eze@myriad.com', '$2y$10$4mPrNPgwlxUfprqHN.IJi.Xg4Q7U894P55TtxN6cYfbO7j.7o3Lcu', '2024-12-10 19:22:00', '1733859685_femaleavater7.jpg', 'active', 'John Eze', NULL, 'Admin');

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `username` varchar(100) NOT NULL,
  `gender` enum('male','female') NOT NULL,
  `age` int(11) NOT NULL,
  `introduction` text NOT NULL,
  `unique_contribution` text NOT NULL,
  `phone` varchar(15) NOT NULL,
  `email` varchar(255) NOT NULL,
  `photo_path` varchar(255) NOT NULL,
  `video_path` varchar(255) DEFAULT NULL,
  `consent` tinyint(1) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `password` varchar(255) NOT NULL,
  `status` enum('active','inactive','offline','typing') NOT NULL DEFAULT 'offline',
  `slot_number` varchar(10) DEFAULT NULL,
  `last_active` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

INSERT INTO `users` (`id`, `full_name`, `username`, `gender`, `age`, `introduction`, `unique_contribution`, `phone`, `email`, `photo_path`, `video_path`, `consent`, `created_at`, `password`, `status`, `slot_number`, `last_active`) VALUES
(35, 'Robert King', 'robking', 'male', 31, 'Experienced project manager.', 'Led a team to successfully complete a multimillion-dollar project.', '5550002222', 'robert.king@example.com', '../uploads/josephonaji.png', '/videos/robking.mp4', 1, '2024-11-28 11:56:35', '$2y$10$qqNUWGjX1aLREFAOT0aTg.i8ApXUIJ7dQBicBUvmjoA7KVyNg4LKq', 'active', 'M1', NULL),
(36, 'Catherine Lee', 'cathlee', 'female', 29, 'Creative content writer.', 'Wrote a series of articles that boosted SEO rankings by 50%.', '5550003333', 'catherine.lee@example.com', '../uploads/josephonaji.png', NULL, 1, '2024-11-28 11:56:35', '$2y$10$qqNUWGjX1aLREFAOT0aTg.i8ApXUIJ7dQBicBUvmjoA7KVyNg4LKq', 'active', 'F6', NULL),
(37, 'James Smith', 'jamesmith', 'male', 35, 'Software engineer with 10+ years of experience.', 'Developed a scalable e-commerce platform.', '5550004444', 'james.smith@example.com', '../uploads/josephonaji.png', NULL, 1, '2024-11-28 11:56:35', '$2y$10$qqNUWGjX1aLREFAOT0aTg.i8ApXUIJ7dQBicBUvmjoA7KVyNg4LKq', 'active', 'M3', NULL),
(63, 'David Carter', 'dcarter', 'male', 40, 'HR manager dedicated to talent development.', 'Implemented training programs that improved employee performance by 20%.', '5550008888', 'david.carter@example.com', '../uploads/josephonaji.png', NULL, 1, '2024-11-28 11:56:35', '$2y$10$qqNUWGjX1aLREFAOT0aTg.i8ApXUIJ7dQBicBUvmjoA7KVyNg4LKq', 'active', 'M2', '2024-12-13'),
(65, 'Blessing Caroline', 'caro', 'female', 24, 'Thanks thansk', 'rh', '0809117727', 'caroline@gmail.com', '../uploads/josephonaji.png', NULL, 0, '2024-12-05 13:33:00', '$2y$10$61n6ym/G//jKYTsBnNKhXuFe2ECPJX4fzH.YSfOJYCdDXah4xH9F6', 'active', NULL, NULL),
(66, 'v', 'v', 'male', 34, 'xcs', 'dsd', '23456', '32ed@d.v', '../uploads/josephonaji.png', NULL, 0, '2024-12-05 13:51:21', '$2y$10$KJMPXFN/TkouqXPf1HA1euHsXLAkNjeRAmH0JfeJkX3zUDCeYirG6', 'active', NULL, NULL),
(67, 'Jennifer Jacob', 'Jennifer', 'female', 43, 'I am a lover', 'and i am very happy', '089382999', 'jennifer@gmail.com', '../uploads/josephonaji.png', NULL, 0, '2024-12-05 13:52:41', '$2y$10$/Cj8Q.T60SOcChnYnLlo1.YPtpdq9Wvecd9cNOnRcdsV/Q1j0/JfK', 'active', NULL, NULL),
(68, 'Mary Atom', 'atom', 'male', 23, 'All thanks to God Almighty it already 8 months of God\'s faithfulness and blessings with you my dear and beautiful wife ( obim) you are really a great blessing to me and I will forever  love you. Happy 8 months anniversary to us my love.', 'Congratulations to Me 🎊🎊🎊\r\n16m followers on Facebook \r\n2.4m followers on instagram \r\n1.3m subscribers on youtube', '080917778282', 'mary@gmail.com', '../uploads/1695151976709.jpg', NULL, 0, '2024-12-05 15:04:09', '$2y$10$Qc8/ejug1AqR9YUcv..UH.GktRLX8lunmR6eEk7EA3b1j4Wg.oNty', 'active', 'M7', NULL),
(69, 'Ene Jacob', 'Ene', 'female', 23, 'Unique and smart', 'Smart', '08091666887', 'cojboi@gmail.com', '../uploads/Screenshot 2024-11-19 131044.png', NULL, 0, '2024-12-12 10:08:24', '$2y$10$DKKmWjTuTSkvZ3xVXwBikOt/3tWaH3weVFaSFjyseRUmOUX0vRlH2', 'active', 'F3', '2024-12-13');

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `messages`
--
ALTER TABLE `messages`
  ADD PRIMARY KEY (`msg_id`),
  ADD KEY `parent_message_id_index` (`parent_message_id`);

--
-- Indexes for table `pickup_lines`
--
ALTER TABLE `pickup_lines`
  ADD PRIMARY KEY (`id`),
  ADD KEY `slot_number` (`slot_number`);

--
-- Indexes for table `player_slots`
--
ALTER TABLE `player_slots`
  ADD PRIMARY KEY (`slot_id`),
  ADD UNIQUE KEY `idx_slot_number` (`slot_number`);

--
-- Indexes for table `replies`
--
ALTER TABLE `replies`
  ADD PRIMARY KEY (`id`),
  ADD KEY `pickup_line_id` (`pickup_line_id`),
  ADD KEY `slot_number` (`slot_number`);

--
-- Indexes for table `selections`
--
ALTER TABLE `selections`
  ADD PRIMARY KEY (`id`),
  ADD KEY `player_id` (`player_id`),
  ADD KEY `jar_id` (`jar_id`);

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

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

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `fk_slot_number` (`slot_number`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `jars`
--
ALTER TABLE `jars`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `messages`
--
ALTER TABLE `messages`
  MODIFY `msg_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `pickup_lines`
--
ALTER TABLE `pickup_lines`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=44;

--
-- AUTO_INCREMENT for table `player_slots`
--
ALTER TABLE `player_slots`
  MODIFY `slot_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- AUTO_INCREMENT for table `replies`
--
ALTER TABLE `replies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=62;

--
-- AUTO_INCREMENT for table `selections`
--
ALTER TABLE `selections`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

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

--
-- AUTO_INCREMENT for table `useradmin`
--
ALTER TABLE `useradmin`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `pickup_lines`
--
ALTER TABLE `pickup_lines`
  ADD CONSTRAINT `pickup_lines_ibfk_1` FOREIGN KEY (`slot_number`) REFERENCES `users` (`slot_number`) ON DELETE CASCADE;

--
-- Constraints for table `replies`
--
ALTER TABLE `replies`
  ADD CONSTRAINT `replies_ibfk_1` FOREIGN KEY (`pickup_line_id`) REFERENCES `pickup_lines` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `replies_ibfk_2` FOREIGN KEY (`slot_number`) REFERENCES `users` (`slot_number`) ON DELETE CASCADE;

--
-- Constraints for table `selections`
--
ALTER TABLE `selections`
  ADD CONSTRAINT `selections_ibfk_1` FOREIGN KEY (`player_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `selections_ibfk_2` FOREIGN KEY (`jar_id`) REFERENCES `jars` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `users`
--
ALTER TABLE `users`
  ADD CONSTRAINT `fk_slot_number` FOREIGN KEY (`slot_number`) REFERENCES `player_slots` (`slot_number`) ON DELETE SET NULL;
COMMIT;

