/*
The genres/sub-genres table will be editable for each user as they will all have a selection of genres they are interested into.
*/

/* 
  tables we need here are:
  user, country, affiliation, label, user_affiliation, genre, artist, release, song, subscription, master
*/

create table `user` (
  `id` bigint unsigned auto_increment not null,
  `email` varchar(100) not null,
  `emaillang` varchar(100) not null,
  `name` varchar(30) not null,
  `surname` varchar(40) default null,
  `mobile` varchar(30) default null,
  `address` text default null,
  `zipcode` varchar(10) default null,
  `city` varchar(60) default null,
  `country` smallint not null,
  `feedback` binary default 0,
	`lastlogin` timestamp default '0000-00-00 00:00:00',
  `iplast` varchar(15) default '000.000.000.000',
  `ip` varchar(15) default '000.000.000.000',
  `priority` tinyint default 1,
  `created` timestamp default CURRENT_TIMESTAMP,
  `deleted` binary default 0,
  primary key (`id`),
  unique `user` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `country` (
  `id` int(11) NOT NULL,
  `code` char(2) NOT NULL,
  `name` varchar(200) default NULL,
  `phoneprefix` varchar(50) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `affiliation` (
  `id` bigint unsigned auto_increment not null,
  `jobtitle` varchar(100) not null,
  `company` varchar(100) not null,
  `address` text default null,
  `city` varchar(100) default null,
  `country` smallint default 0,
  `url` varchar(255) default null,
  `phone` varchar(255) default null,
  `created` timestamp default CURRENT_TIMESTAMP,
  `deleted` binary default 0,
  primary key (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `label` (
  `id` bigint unsigned auto_increment not null,
  `name` varchar(100) not null,
  `tagline` varchar(255) default null,
  `address` text default null,
  `country` smallint default 0,
  `url` varchar(255) default null,
  `priority` tinyint default 1,
  `created` timestamp default CURRENT_TIMESTAMP,
  `deleted` binary default 0,
  primary key (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `user_affiliation` (
  `uid` bigint unsigned not null,
  `affid` bigint unsigned not null,
  `created` timestamp default CURRENT_TIMESTAMP,
  unique `affiliation` (`uid`, `affid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `genre` (
  `id` smallint unsigned auto_increment not null,
  `name` varchar(255) not null,
  `description` text default null,
  `parentid` smallint unsigned default 0,
  `deleted` binary default 0,
  primary key (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `genre_map` (
  `genreid` smallint unsigned not null,
  `type` enum('song','release') default 'song',
  `mapid` bigint unsigned not null,
  `order` tinyint default 1,
  unique `map` (`genreid`, `type`, `mapid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `artist` (
  `id` bigint unsigned auto_increment not null,
  `email` varchar(255) not null,
  `password` varchar(60) not null,
  `firstname` varchar(60) default null,
  `lastname` varchar(60) default null,
  `info` text default null,
  `mobile` varchar(15) default null,
  `address` varchar(255) not null,
  `city` varchar(255) not null,
  `zipcode` varchar(255) not null,
  `country` varchar(255) not null,
	`lastlogin` timestamp default '0000-00-00 00:00:00',
  `iplast` varchar(15) default '000.000.000.000',
  `ip` varchar(15) default '000.000.000.000',
  `priority` tinyint default 1,
  `created` timestamp default CURRENT_TIMESTAMP,
  `deleted` binary default 0,
  primary key (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `release` (
  `id` bigint unsigned auto_increment not null,
  `title` varchar(255) not null,
  `utype` enum('artist', 'label') default 'artist',
  `umapid` bigint unsigned not null,
  `artist` varchar(255) not null,
  `label` varchar(255) not null,
  `rcn` varchar(255) default null,
  `ean` varchar(255) default null,
  `rdate` date default '0000-00-00',
  `promodate` date default '0000-00-00',
  `artwork_b` varchar(255) default null,
  `artwork_m` varchar(255) default null,
  `artwork_s` varchar(255) default null,
  `priority` tinyint default 1,
  `released` binary default 0,
  `created` timestamp default CURRENT_TIMESTAMP,
  `deleted` binary default 0,
  primary key (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `song` (
  `id` bigint unsigned auto_increment not null,
  `title` varchar(255) not null,
  `version` varchar(255) default 'Original Mix',
  `artist` varchar(255) not null,
  `label` varchar(255) not null,
  `utype` enum('artist', 'label') default 'artist',
  `umapid` bigint unsigned not null,
  `remixer` varchar(255) default null,
  `isrc` varchar(255) default null,
  `fileflac` varchar(255) default null,
  `filemp3320` varchar(255) default null,
  `filemp3128` varchar(255) default null,
  `preview` varchar(255) default null,
  `waveform` varchar(255) default null,
  `preview_waveform` varchar(255) default null,
  `length` varchar(255) default null,
  `preview_startpt` varchar(255) default '00:00',
  `instrumental` binary default 0,
  `composer` varchar(255) default null,
  `author` varchar(255) default null,
  `publisher` varchar(255) default null,
  `produced` varchar(255) default null,
  `credit` text default null,
  `priority` tinyint default 1,
  `created` timestamp default CURRENT_TIMESTAMP,
  `deleted` binary default 0,
  primary key (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `release_track` (
  `releaseid` smallint unsigned not null,
  `tracknum` tinyint default 1,
  `songid` bigint unsigned not null,
  unique `track` (`releaseid`, `songid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

create table `subscription` (
  `id` bigint unsigned auto_increment not null,
  `type` enum('label', 'artist') default 'artist',
  `mapid` bigint unsigned not null,
  `uid` bigint unsigned not null,
  `newsletter` binary default 1,
  `updates` binary default 1,
  `created` timestamp default CURRENT_TIMESTAMP,
  `deleted` binary default 0,
  primary key (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;

CREATE TABLE `master` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(60) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `email` VARCHAR(80) NOT NULL,
  `iplast` varchar(15) default '000.000.000.000',
  `lastlogin` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted` BINARY(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE(`username`, `email`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE utf8_unicode_ci;
insert into `master` set `username` = 'master', `password` = 'e10adc3949ba59abbe56e057f20f883e', `email` = 'pras.svo@gmail.com';
