-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 24, 2024 at 11:52 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `dj_app`
--

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

--
-- Table structure for table `deck_state`
--

CREATE TABLE `deck_state` (
  `id` int(11) NOT NULL,
  `deck_number` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `is_playing` tinyint(1) DEFAULT 0,
  `current_position` float DEFAULT 0,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `deck_state`
--

INSERT INTO `deck_state` (`id`, `deck_number`, `track_id`, `is_playing`, `current_position`, `updated_at`) VALUES
(1, 1, 4, 1, NULL, '2024-10-24 21:43:45'),
(2, 2, 2, 1, NULL, '2024-10-24 21:43:58');

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

--
-- Table structure for table `playlists`
--

CREATE TABLE `playlists` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `playlist_tracks`
--

CREATE TABLE `playlist_tracks` (
  `playlist_id` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `position` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `queue_tracks`
--

CREATE TABLE `queue_tracks` (
  `id` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `deck_number` int(11) NOT NULL,
  `position` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `scheduled_tracks`
--

CREATE TABLE `scheduled_tracks` (
  `id` int(11) NOT NULL,
  `schedule_id` int(11) NOT NULL,
  `track_id` int(11) NOT NULL,
  `scheduled_time` datetime NOT NULL,
  `is_played` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `scheduled_tracks`
--

INSERT INTO `scheduled_tracks` (`id`, `schedule_id`, `track_id`, `scheduled_time`, `is_played`) VALUES
(1, 1, 1, '2024-10-24 15:02:00', 0),
(2, 2, 1, '2024-10-24 15:11:00', 0),
(3, 2, 1, '2024-10-25 16:11:00', 0),
(4, 2, 1, '2024-10-25 15:11:00', 0),
(5, 2, 1, '2024-10-25 14:11:00', 0),
(6, 2, 1, '2024-10-25 13:11:00', 0),
(7, 2, 1, '2024-10-25 12:11:00', 0),
(8, 2, 1, '2024-10-25 11:11:00', 0),
(9, 2, 1, '2024-10-25 10:11:00', 0),
(10, 2, 1, '2024-10-25 09:11:00', 0),
(11, 2, 1, '2024-10-25 08:11:00', 0),
(12, 2, 1, '2024-10-25 07:11:00', 0),
(13, 2, 1, '2024-10-25 06:11:00', 0),
(14, 2, 1, '2024-10-25 05:11:00', 0),
(15, 2, 1, '2024-10-25 04:11:00', 0),
(16, 2, 1, '2024-10-25 03:11:00', 0),
(17, 2, 1, '2024-10-25 02:11:00', 0),
(18, 2, 1, '2024-10-25 01:11:00', 0),
(19, 2, 1, '2024-10-25 00:11:00', 0),
(20, 2, 1, '2024-10-24 23:11:00', 0),
(21, 2, 1, '2024-10-24 22:11:00', 0),
(22, 2, 1, '2024-10-24 21:11:00', 0),
(23, 2, 1, '2024-10-24 20:11:00', 0),
(24, 2, 1, '2024-10-24 19:11:00', 0),
(25, 2, 1, '2024-10-24 18:11:00', 0),
(26, 2, 1, '2024-10-24 17:11:00', 0),
(27, 2, 1, '2024-10-24 16:11:00', 0),
(28, 2, 1, '2024-10-24 15:11:00', 0),
(29, 2, 1, '2024-10-24 14:11:00', 0),
(30, 2, 1, '2024-10-24 13:11:00', 0),
(31, 2, 1, '2024-10-24 12:11:00', 0),
(32, 2, 1, '2024-10-24 11:11:00', 0),
(33, 2, 1, '2024-10-24 10:11:00', 0),
(34, 2, 1, '2024-10-24 09:11:00', 0),
(35, 2, 1, '2024-10-24 08:11:00', 0),
(36, 2, 1, '2024-10-24 07:11:00', 0),
(37, 2, 1, '2024-10-24 06:11:00', 0),
(38, 2, 1, '2024-10-24 05:11:00', 0),
(39, 2, 1, '2024-10-24 04:11:00', 0),
(44, 2, 1, '2024-10-23 23:11:00', 0),
(45, 2, 1, '2024-10-23 22:11:00', 0),
(46, 2, 1, '2024-10-23 21:11:00', 0),
(47, 2, 1, '2024-10-23 20:11:00', 0),
(48, 2, 1, '2024-10-23 19:11:00', 0),
(49, 2, 1, '2024-10-23 18:11:00', 0),
(50, 2, 1, '2024-10-23 17:11:00', 0),
(51, 2, 1, '2024-10-23 16:11:00', 0),
(52, 2, 1, '2024-10-23 15:11:00', 0),
(53, 2, 1, '2024-10-23 14:11:00', 0),
(54, 2, 1, '2024-10-23 13:11:00', 0),
(55, 3, 1, '2024-10-24 15:16:00', 0),
(56, 3, 1, '2024-10-25 16:16:00', 0),
(57, 3, 1, '2024-10-25 15:16:00', 0),
(58, 3, 1, '2024-10-25 14:16:00', 0),
(59, 3, 1, '2024-10-25 13:16:00', 0),
(60, 3, 1, '2024-10-25 12:16:00', 0),
(61, 3, 1, '2024-10-25 11:16:00', 0),
(62, 3, 1, '2024-10-25 10:16:00', 0),
(63, 3, 1, '2024-10-25 09:16:00', 0),
(64, 3, 1, '2024-10-25 08:16:00', 0),
(65, 3, 1, '2024-10-25 07:16:00', 0),
(66, 3, 1, '2024-10-25 06:16:00', 0),
(67, 3, 1, '2024-10-25 05:16:00', 0),
(68, 3, 1, '2024-10-25 04:16:00', 0),
(69, 3, 1, '2024-10-25 03:16:00', 0),
(70, 3, 1, '2024-10-25 02:16:00', 0),
(71, 3, 1, '2024-10-25 01:16:00', 0),
(72, 3, 1, '2024-10-25 00:16:00', 0),
(73, 3, 1, '2024-10-24 23:16:00', 0),
(74, 3, 1, '2024-10-24 22:16:00', 0),
(75, 3, 1, '2024-10-24 21:16:00', 0),
(76, 3, 1, '2024-10-24 20:16:00', 0),
(77, 3, 1, '2024-10-24 19:16:00', 0),
(78, 3, 1, '2024-10-24 18:16:00', 0),
(79, 3, 1, '2024-10-24 17:16:00', 0),
(80, 3, 1, '2024-10-24 16:16:00', 0),
(81, 3, 1, '2024-10-24 15:16:00', 0),
(82, 3, 1, '2024-10-24 14:16:00', 0),
(83, 3, 1, '2024-10-24 13:16:00', 0),
(84, 3, 1, '2024-10-24 12:16:00', 0),
(85, 3, 1, '2024-10-24 11:16:00', 0),
(86, 3, 1, '2024-10-24 10:16:00', 0),
(87, 3, 1, '2024-10-24 09:16:00', 0),
(88, 3, 1, '2024-10-24 08:16:00', 0),
(89, 3, 1, '2024-10-24 07:16:00', 0),
(90, 3, 1, '2024-10-24 06:16:00', 0),
(91, 3, 1, '2024-10-24 05:16:00', 0),
(92, 3, 1, '2024-10-24 04:16:00', 0),
(97, 3, 1, '2024-10-23 23:16:00', 0),
(98, 3, 1, '2024-10-23 22:16:00', 0),
(99, 3, 1, '2024-10-23 21:16:00', 0),
(100, 3, 1, '2024-10-23 20:16:00', 0),
(101, 3, 1, '2024-10-23 19:16:00', 0),
(102, 3, 1, '2024-10-23 18:16:00', 0),
(103, 3, 1, '2024-10-23 17:16:00', 0),
(104, 3, 1, '2024-10-23 16:16:00', 0),
(105, 3, 1, '2024-10-23 15:16:00', 0),
(106, 3, 1, '2024-10-23 14:16:00', 0),
(107, 3, 1, '2024-10-23 13:16:00', 0),
(108, 4, 3, '2024-10-24 12:00:00', 0);

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

--
-- Table structure for table `schedules`
--

CREATE TABLE `schedules` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `schedules`
--

INSERT INTO `schedules` (`id`, `name`, `created_at`) VALUES
(1, 'Schedule for 2024-10-24', '2024-10-24 19:02:26'),
(2, 'Schedule for 2024-10-24', '2024-10-24 19:11:22'),
(3, 'Schedule for 2024-10-24', '2024-10-24 19:12:15'),
(4, 'Schedule for 2024-10-24', '2024-10-24 20:01:00');

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

--
-- Table structure for table `tracks`
--

CREATE TABLE `tracks` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `artist` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `filesize` float NOT NULL,
  `upload_date` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tracks`
--

INSERT INTO `tracks` (`id`, `title`, `artist`, `file_path`, `filesize`, `upload_date`) VALUES
(1, 'as', 'sss', '671a99ad85d41_1729796525.mp3', 1.44, '2024-10-24 19:02:05'),
(2, 'test', 'test', '671a9e78660fe_1729797752.mp3', 1.44, '2024-10-24 19:22:32'),
(3, 'yrtuyb', 'guhkjlk,', '671aa623e38e6_1729799715.mp3', 1.44, '2024-10-24 19:55:15'),
(4, 'wQW', 'QWED', '671aacfb11a9f_1729801467.mp3', 1.44, '2024-10-24 20:24:27');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `deck_state`
--
ALTER TABLE `deck_state`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_deck` (`deck_number`),
  ADD KEY `track_id` (`track_id`);

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

--
-- Indexes for table `playlist_tracks`
--
ALTER TABLE `playlist_tracks`
  ADD PRIMARY KEY (`playlist_id`,`track_id`),
  ADD KEY `track_id` (`track_id`);

--
-- Indexes for table `queue_tracks`
--
ALTER TABLE `queue_tracks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `track_id` (`track_id`),
  ADD KEY `idx_queue_position` (`position`);

--
-- Indexes for table `scheduled_tracks`
--
ALTER TABLE `scheduled_tracks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `schedule_id` (`schedule_id`),
  ADD KEY `track_id` (`track_id`),
  ADD KEY `idx_scheduled_time` (`scheduled_time`);

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

--
-- Indexes for table `tracks`
--
ALTER TABLE `tracks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_upload_date` (`upload_date`);

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `playlists`
--
ALTER TABLE `playlists`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `queue_tracks`
--
ALTER TABLE `queue_tracks`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `deck_state`
--
ALTER TABLE `deck_state`
  ADD CONSTRAINT `deck_state_ibfk_1` FOREIGN KEY (`track_id`) REFERENCES `tracks` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `playlist_tracks`
--
ALTER TABLE `playlist_tracks`
  ADD CONSTRAINT `playlist_tracks_ibfk_1` FOREIGN KEY (`playlist_id`) REFERENCES `playlists` (`id`),
  ADD CONSTRAINT `playlist_tracks_ibfk_2` FOREIGN KEY (`track_id`) REFERENCES `tracks` (`id`);

--
-- Constraints for table `queue_tracks`
--
ALTER TABLE `queue_tracks`
  ADD CONSTRAINT `queue_tracks_ibfk_1` FOREIGN KEY (`track_id`) REFERENCES `tracks` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `scheduled_tracks`
--
ALTER TABLE `scheduled_tracks`
  ADD CONSTRAINT `scheduled_tracks_ibfk_1` FOREIGN KEY (`schedule_id`) REFERENCES `schedules` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `scheduled_tracks_ibfk_2` FOREIGN KEY (`track_id`) REFERENCES `tracks` (`id`) ON DELETE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
