-- Creates the tables needed for the MyTunes project

CREATE TABLE [artist] (
	[artist_id] [int] IDENTITY (1, 1) NOT NULL ,
	[artist] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[primary_genre] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[second_genre] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[notes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_artist] PRIMARY KEY  CLUSTERED 
	(
		[artist_id]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


CREATE TABLE [album] (
	[album_id] [int] IDENTITY (1, 1) NOT NULL ,
	[artist_id] [int] NULL ,
	[album] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	CONSTRAINT [PK_album] PRIMARY KEY  CLUSTERED 
	(
		[album_id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_album_artist] FOREIGN KEY 
	(
		[artist_id]
	) REFERENCES [artist] (
		[artist_id]
	)
) ON [PRIMARY]
GO


CREATE TABLE [song] (
	[song_id] [int] IDENTITY (1, 1) NOT NULL ,
	[album_id] [int] NULL ,
	[artist_id] [int] NULL ,
	[title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[filename] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[genre] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[date_added] [datetime] NULL ,
	[date_changed] [datetime] NULL ,
	[date_filemod] [datetime] NULL ,
	[track] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[song_len] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[nplays] [int] NOT NULL CONSTRAINT [DF_song_nplays] DEFAULT (0),
	CONSTRAINT [PK_song] PRIMARY KEY  CLUSTERED 
	(
		[song_id]
	)  ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO


CREATE TABLE [customer] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[first_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_customer_first_name] DEFAULT (''),
	[last_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_customer_last_name] DEFAULT (''),
	[email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_customer_email] DEFAULT (''),
	[password] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_customer_password] DEFAULT (''),
	[expires] [datetime] NULL ,
	[secret] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[secret_expires] [datetime] NULL ,
	[is_admin] [bit] NOT NULL CONSTRAINT [DF_customer_is_admin] DEFAULT (0),
	CONSTRAINT [PK_customer] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO


CREATE TABLE [playlist] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[title] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[cust_id] [int] NULL ,
	CONSTRAINT [PK_playlist] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_playlist_customer] FOREIGN KEY 
	(
		[cust_id]
	) REFERENCES [customer] (
		[id]
	)
) ON [PRIMARY]
GO

CREATE TABLE [playlist_item] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[playlist_id] [int] NULL ,
	[song_id] [int] NULL ,
	CONSTRAINT [PK_playlist_item] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] ,
	CONSTRAINT [FK_playlist_item_playlist] FOREIGN KEY 
	(
		[playlist_id]
	) REFERENCES [playlist] (
		[id]
	),
	CONSTRAINT [FK_playlist_item_song] FOREIGN KEY 
	(
		[song_id]
	) REFERENCES [song] (
		[song_id]
	)
) ON [PRIMARY]
GO




