START TRANSACTION;


CREATE DATABASE IF NOT EXISTS `musicplayer` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci;
USE `musicplayer`;

CREATE TABLE `albums` (
  `id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `artist` int(11) NOT NULL,
  `genre` int(11) NOT NULL,
  `artworkPath` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `albums` (`id`, `title`, `artist`, `genre`, `artworkPath`) VALUES
(1, 'Born to Die', 1, 2, 'assets/images/artwork/borntodie.png'),
(2, 'Kamikaze', 5, 1, 'assets/images/artwork/Kamikaze.jpg'),
(3, 'Evolve', 3, 2, 'assets/images/artwork/Evolve.jpg'),
(4, 'My Dear Melancholy', 4, 3, 'assets/images/artwork/MyDearMelancholy.jpg'),
(5, 'Native', 2, 2, 'assets/images/artwork/Native.jpg');

CREATE TABLE `artists` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `artists` (`id`, `name`) VALUES
(1, 'Lana Del Rey'),
(2, 'one republic'),
(3, 'Imagine Dragons'),
(4, 'The Weeknd'),
(5, 'Eminem');

CREATE TABLE `genres` (
  `id` int(11) NOT NULL,
  `name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `genres` (`id`, `name`) VALUES
(1, 'Rap'),
(2, 'Pop'),
(3, 'R&B/Soul');

CREATE TABLE `songs` (
  `id` int(11) NOT NULL,
  `title` varchar(250) NOT NULL,
  `artist` int(11) NOT NULL,
  `album` int(11) NOT NULL,
  `genre` int(11) NOT NULL,
  `duration` varchar(8) NOT NULL,
  `path` varchar(500) NOT NULL,
  `albumOrder` int(11) NOT NULL,
  `plays` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `songs` (`id`, `title`, `artist`, `album`, `genre`, `duration`, `path`, `albumOrder`, `plays`) VALUES
(1, 'Call Out My Name', 4, 4, 3, '3:48', 'assets/music/CallOutMyName.mp3', 1, 33),
(2, 'Believer', 3, 3, 2, '3:24', 'assets/music/Believer.mp3', 3, 30),
(3, 'Born to Die', 1, 1, 2, '4:46', 'assets/music/BorntoDie.mp3', 1, 81),
(4, 'Love Runs Out', 2, 5, 2, '4:04', 'assets/music/LoveRunsOut.mp3', 1, 34),
(5, 'The Ringer', 5, 2, 1, '5:37', 'assets/music/TheRinger.mp3', 1, 27),
(6, 'Dark Paradise', 1, 1, 2, '4:03', 'assets/music/DarkParadise.mp3', 2, 66),
(7, 'Carmen.mp3', 1, 1, 2, '4:08', 'assets/music/Carmen.mp3', 3, 44);


ALTER TABLE `albums`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `artists`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `genres`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `songs`
  ADD PRIMARY KEY (`id`);


ALTER TABLE `albums`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `artists`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

ALTER TABLE `genres`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

ALTER TABLE `songs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;

