-- 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');

--
-- 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`);

--
-- 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;

--
-- 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;
COMMIT;

