CREATE DATABASE `artgasmator` /*!40100 DEFAULT CHARACTER SET utf8 */;

DROP TABLE IF EXISTS `artgasmator`.`album`;
CREATE TABLE  `artgasmator`.`album` (
  `album_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`album_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
DROP TABLE IF EXISTS `artgasmator`.`artist`;
CREATE TABLE  `artgasmator`.`artist` (
  `artist_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `artist_name` varchar(45) NOT NULL,
  `artist_origin` varchar(45) NOT NULL,
  `artist_years_active` varchar(10) NOT NULL,
  `artist_biography` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`artist_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`artist_gear`;
CREATE TABLE  `artgasmator`.`artist_gear` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`artist_label`;
CREATE TABLE  `artgasmator`.`artist_label` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `artist_id` int(10) unsigned NOT NULL,
  `label_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_artist` (`artist_id`),
  KEY `FK_label` (`label_id`),
  CONSTRAINT `FK_artist` FOREIGN KEY (`artist_id`) REFERENCES `artist` (`artist_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_label` FOREIGN KEY (`label_id`) REFERENCES `label` (`label_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`gear`;
CREATE TABLE  `artgasmator`.`gear` (
  `gear_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`gear_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`genre`;
CREATE TABLE  `artgasmator`.`genre` (
  `genre_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `genre_name` varchar(45) NOT NULL,
  `genre_description` varchar(1000) NOT NULL,
  PRIMARY KEY (`genre_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`label`;
CREATE TABLE  `artgasmator`.`label` (
  `label_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `label_name` varchar(45) NOT NULL,
  `label_description` varchar(1000) NOT NULL,
  `label_location` varchar(45) NOT NULL,
  PRIMARY KEY (`label_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`member`;
CREATE TABLE  `artgasmator`.`member` (
  `member_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `member_name` varchar(45) NOT NULL,
  `member_birth_date` date NOT NULL,
  `member_birth_place` varchar(45) NOT NULL,
  `instruments` varchar(200) NOT NULL,
  `member_occupation` varchar(45) DEFAULT NULL,
  `biography` varchar(4000) DEFAULT NULL,
  `creation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `photo` mediumblob,
  `artist_id` int(11) NOT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`track`;
CREATE TABLE  `artgasmator`.`track` (
  `track_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`track_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`users`;
CREATE TABLE  `artgasmator`.`users` (
  `username` varchar(45) NOT NULL,
  `password` varchar(50) NOT NULL,
  `enabled` tinyint(1) NOT NULL,
  PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `artgasmator`.`authorities`;
CREATE TABLE  `artgasmator`.`authorities` (
  `username` varchar(50) NOT NULL,
  `authority` varchar(50) NOT NULL,
  UNIQUE KEY `ix_auth_username` (`username`,`authority`),
  CONSTRAINT `fk_authorities_users` FOREIGN KEY (`username`) REFERENCES `users` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;