<?php

############################################################

array_push($sql_statements,"
create sequence netjuke_groups_seq
	start 1
	increment 1
");

array_push($sql_statements,"
create table netjuke_groups (
    id                   int4 not null
	                          unique
                              primary key
                              default nextval('netjuke_groups_seq'),
    name                 varchar(30) not null unique
)
");

############################################################

array_push($sql_statements,"
create table netjuke_users (
    email                varchar(75) not null
	                                 unique
                                     primary key,
    password             varchar(60) not null,
    name                 varchar(100) not null,
    gr_id                int4 not null
	                          references netjuke_groups (id),
    created              timestamp not null,
    updated              timestamp not null,
    nickname             varchar(30),
    web_site             varchar(255),
    img_src              text,
    last_login           timestamp default null,
    login_cnt            int not null default '0',
    local_music_dir      text
)
");

array_push($sql_statements,"create index netjuke_users_webloginindex on netjuke_users (email, password)");

############################################################

array_push($sql_statements,"
create table netjuke_userprefs (
    us_email             varchar(75) not null
	                                 unique
                                     primary key
									 references netjuke_users (email)
									            on delete cascade
												on update cascade,
    inv_icn              varchar(1) not null,
    icon_set varchar(100) NOT NULL default 'default',
    language char(2) NOT NULL default 'en',
    explore char(1) NOT NULL default 't',
    theme varchar(100) NOT NULL default 'artekopia'
)
");

############################################################

array_push($sql_statements,"
create table netjuke_sessions (
    session_id           varchar(40) not null
	                                 unique
                                     primary key,
    remote_addr          varchar(60) not null,
    created              timestamp not null,
    updated              timestamp not null,
    email                varchar(75),
    name                 varchar(100),
    gr_id                int4,
    nickname             varchar(30),
    default_pl           int4,
    inv_icn              varchar(1) default 'f',
    static		 char(1) default 'f',
    icon_set varchar(100) NOT NULL default 'default',
    language char(2) NOT NULL default 'en',
    explore char(1) NOT NULL default 't',
    theme varchar(100) NOT NULL default 'artekopia'
)
");

############################################################

array_push($sql_statements,"
create sequence netjuke_plists_seq
	start 1
	increment 1
");

array_push($sql_statements,"
create table netjuke_plists (
    id                   int4 not null
	                          unique
                              primary key
                              default nextval('netjuke_plists_seq'),
    us_email             varchar(75) not null
									 references netjuke_users (email)
									            on delete cascade
												on update cascade,
    created              timestamp not null default 'now',
    sequence             int4 not null default 1,
    title                varchar(100) not null,
    comment              varchar(512),
    shared_list          varchar(1) default 'f'
)
");

############################################################

array_push($sql_statements,"
create sequence netjuke_plists_tracks_seq
	start 1
	increment 1
");

array_push($sql_statements,"
create table netjuke_plists_tracks (
    id                   int4 not null
	                          unique
                              primary key
                              default nextval('netjuke_plists_tracks_seq'),
    us_email             varchar(75) not null
									 references netjuke_users (email)
									            on delete cascade
												on update cascade,
    pl_id                int4 not null
							  references netjuke_plists (id)
									     on delete cascade
									     on update cascade,
    tr_id                int4 not null
	                          default 1
							  references netjuke_tracks (id)
									     on delete cascade
									     on update cascade,
    sequence             int4 not null default 1,
    comment              varchar(255)
)
");

############################################################

array_push($sql_statements,"
create sequence netjuke_plists_fav_seq
	start 1
	increment 1
");

array_push($sql_statements,"
create table netjuke_plists_fav (
    id                   int4 not null unique
                              primary key
                              default nextval('netjuke_plists_tracks_seq'),
    us_email             varchar(75) not null
									 references netjuke_users (email)
									            on delete cascade
												on update cascade,
    pl_id                int4 not null
							  references netjuke_plists (id)
									     on delete cascade
									     on update cascade,
    created              timestamp not null default 'now',
    sequence             int4 not null default 1,
    comment              varchar(255)
)
");

############################################################

array_push($sql_statements,"
CREATE TABLE netjuke_themeprefs (
  us_email varchar(75) NOT NULL default '',
  theme varchar(100) NOT NULL default '',
  setting varchar(100) NOT NULL default '',
  value varchar(200) NOT NULL default '',
  PRIMARY KEY  (us_email,theme,setting)
)
");

?>
