CREATE TABLE album (id BIGSERIAL, scan_id BIGINT, name VARCHAR(255) UNIQUE, mtime BIGINT, amazon_flagged SMALLINT DEFAULT 0, meta_flagged SMALLINT DEFAULT 0, folders_flagged SMALLINT DEFAULT 0, has_art SMALLINT DEFAULT 0, PRIMARY KEY(id));
CREATE TABLE artist (id BIGSERIAL, name VARCHAR(255), PRIMARY KEY(id));
CREATE TABLE echonest_properties (id BIGSERIAL, song_id BIGINT, name VARCHAR(255), value VARCHAR(255), PRIMARY KEY(id));
CREATE TABLE genre (id BIGSERIAL, name VARCHAR(255), PRIMARY KEY(id));
CREATE TABLE playlist (id BIGSERIAL, scan_id BIGINT, service_name VARCHAR(255), service_unique_id VARCHAR(255), name VARCHAR(255), mtime BIGINT, PRIMARY KEY(id));
CREATE TABLE playlist_files (id BIGSERIAL, playlist_id BIGINT DEFAULT 0, filename text, orderfield BIGINT, PRIMARY KEY(id));
CREATE TABLE scan (id BIGSERIAL, scan_time VARCHAR(255), scan_type VARCHAR(255), PRIMARY KEY(id));
CREATE TABLE song (id BIGSERIAL, scan_id BIGINT DEFAULT 0, unique_id VARCHAR(255), artist_id BIGINT DEFAULT 0, album_id BIGINT DEFAULT 0, name VARCHAR(255), length VARCHAR(255), accurate_length BIGINT DEFAULT 0, filesize BIGINT DEFAULT 0, bitrate BIGINT DEFAULT 0, yearpublished BIGINT DEFAULT 1969, tracknumber BIGINT DEFAULT 1, label VARCHAR(255), isremix SMALLINT DEFAULT 0, mtime BIGINT, atime BIGINT, filename text, comments text, PRIMARY KEY(id));
CREATE TABLE song_genres (id BIGSERIAL, song_id BIGINT, genre_id BIGINT, PRIMARY KEY(id));
CREATE TABLE sf_guard_group (id SERIAL, name VARCHAR(255) UNIQUE, description VARCHAR(1000), created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(id));
CREATE TABLE sf_guard_group_permission (group_id INT, permission_id INT, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(group_id, permission_id));
CREATE TABLE sf_guard_permission (id SERIAL, name VARCHAR(255) UNIQUE, description VARCHAR(1000), created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(id));
CREATE TABLE sf_guard_remember_key (id SERIAL, user_id INT, remember_key VARCHAR(32), ip_address VARCHAR(50), created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(id, ip_address));
CREATE TABLE sf_guard_user (id SERIAL, username VARCHAR(128) NOT NULL UNIQUE, algorithm VARCHAR(128) DEFAULT 'sha1' NOT NULL, salt VARCHAR(128), password VARCHAR(128), is_active BOOLEAN DEFAULT 'true', is_super_admin BOOLEAN DEFAULT 'false', last_login TIMESTAMP, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(id));
CREATE TABLE sf_guard_user_group (user_id INT, group_id INT, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(user_id, group_id));
CREATE TABLE sf_guard_user_permission (user_id INT, permission_id INT, created_at TIMESTAMP NOT NULL, updated_at TIMESTAMP NOT NULL, PRIMARY KEY(user_id, permission_id));
CREATE INDEX song_index ON echonest_properties (song_id);
CREATE INDEX scan_type_index ON scan (scan_type);
CREATE INDEX unique_id_index ON song (unique_id);
CREATE INDEX tracknumber_index ON song (tracknumber);
CREATE INDEX songyearpublished_index ON song (yearpublished);
CREATE INDEX songname_index ON song (name);
CREATE INDEX songmtime_index ON song (mtime);
CREATE INDEX songlength_index ON song (length);
CREATE INDEX scan_id ON song (scan_id);
CREATE INDEX isremix_index ON song (isremix);
CREATE INDEX artist_index ON song (artist_id);
CREATE INDEX album_index ON song (album_id);
CREATE INDEX song_id_index ON song_genres (song_id);
CREATE INDEX is_active_idx ON sf_guard_user (is_active);
ALTER TABLE sf_guard_group_permission ADD CONSTRAINT sf_guard_group_permission_permission_id_sf_guard_permission_id FOREIGN KEY (permission_id) REFERENCES sf_guard_permission(id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE sf_guard_group_permission ADD CONSTRAINT sf_guard_group_permission_group_id_sf_guard_group_id FOREIGN KEY (group_id) REFERENCES sf_guard_group(id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE sf_guard_remember_key ADD CONSTRAINT sf_guard_remember_key_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE sf_guard_user_group ADD CONSTRAINT sf_guard_user_group_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE sf_guard_user_group ADD CONSTRAINT sf_guard_user_group_group_id_sf_guard_group_id FOREIGN KEY (group_id) REFERENCES sf_guard_group(id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE sf_guard_user_permission ADD CONSTRAINT sf_guard_user_permission_user_id_sf_guard_user_id FOREIGN KEY (user_id) REFERENCES sf_guard_user(id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE sf_guard_user_permission ADD CONSTRAINT sf_guard_user_permission_permission_id_sf_guard_permission_id FOREIGN KEY (permission_id) REFERENCES sf_guard_permission(id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE;
