CREATE TABLE icecast_mount_templates(
  id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  name            VARCHAR(256)  NOT NULL,
  source_username VARCHAR(64),
  source_pass VARCHAR(64),
  max_listeners INTEGER DEFAULT 4,
  max_listener_duration INTEGER DEFAULT 3600,
  dump_file VARCHAR(1024),
  intro VARCHAR(1024),
  fallback_mount VARCHAR(1024),
  fallback_override INTEGER DEFAULT 1,
  fallback_when_full INTEGER DEFAULT 1,
  charset VARCHAR(1024) DEFAULT 'ISO8859-1',
  is_public INTEGER DEFAULT 0,
  stream_name VARCHAR(1024),
  stream_description VARCHAR(10000),
  stream_url  VARCHAR(1024),
  genre VARCHAR(256),
  bitrate INTEGER,
  mime_type VARCHAR(64)  NOT NULL DEFAULT   'audio/mpeg',
  subtype VARCHAR(64),
  burst_size INTEGER,
  mp3_metadata_interval INTEGER,
  hidden INTEGER DEFAULT 1,
  on_connect VARCHAR(1024),
  on_disconnect VARCHAR(1024),
  authentication_id varchar(64) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE icecast_mounts ALTER COLUMN mime_type DROP NOT NULL;
ALTER TABLE icecast_mounts ALTER COLUMN mime_type DROP DEFAULT;
ALTER TABLE icecast_mounts ALTER COLUMN subtype DROP NOT NULL;
ALTER TABLE icecast_mounts ALTER COLUMN subtype DROP DEFAULT;
ALTER TABLE icecast_mounts ADD COLUMN music_session_id VARCHAR(64) REFERENCES music_sessions(id) ON DELETE CASCADE;
ALTER TABLE icecast_mounts ADD COLUMN icecast_server_id VARCHAR(64) NOT NULL REFERENCES icecast_servers(id);
ALTER TABLE icecast_mounts ADD COLUMN icecast_mount_template_id VARCHAR(64) REFERENCES icecast_mount_templates(id);
ALTER TABLE icecast_mounts ADD COLUMN sourced_needs_changing_at TIMESTAMP;
;
CREATE TABLE icecast_server_groups (
    id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
    name VARCHAR(255) UNIQUE NOT NULL
);
-- bootstrap the default icecast group
INSERT INTO icecast_server_groups (id, name) VALUES ('default', 'default');
INSERT INTO icecast_server_groups (id, name) VALUES ('unused', 'unused');

ALTER TABLE users           ADD COLUMN icecast_server_group_id VARCHAR(64) NOT NULL REFERENCES icecast_server_groups(id) DEFAULT 'default';

-- and by default, all servers and users are in this group
ALTER TABLE icecast_servers ADD COLUMN icecast_server_group_id VARCHAR(64) NOT NULL REFERENCES icecast_server_groups(id) DEFAULT 'default';
ALTER TABLE icecast_servers ADD COLUMN mount_template_id VARCHAR(64) REFERENCES icecast_mount_templates(id);


ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_admin_auth_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_admin_auth_id_fkey" FOREIGN KEY (admin_auth_id) REFERENCES icecast_admin_authentications(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_mount_template_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_mount_template_id_fkey" FOREIGN KEY (mount_template_id) REFERENCES icecast_mount_templates(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_directory_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_directory_id_fkey" FOREIGN KEY (directory_id) REFERENCES icecast_directories(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_icecast_server_group_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_icecast_server_group_id_fkey" FOREIGN KEY (icecast_server_group_id) REFERENCES icecast_server_groups(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_limit_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_limit_id_fkey" FOREIGN KEY (limit_id) REFERENCES icecast_limits(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_logging_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_logging_id_fkey" FOREIGN KEY (logging_id) REFERENCES icecast_loggings(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_master_relay_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_master_relay_id_fkey" FOREIGN KEY (master_relay_id) REFERENCES icecast_master_server_relays(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_path_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_path_id_fkey" FOREIGN KEY (path_id) REFERENCES icecast_paths(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_security_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_security_id_fkey" FOREIGN KEY (security_id) REFERENCES icecast_securities(id) ON DELETE SET NULL;
ALTER TABLE icecast_servers DROP  CONSTRAINT "icecast_servers_template_id_fkey";
ALTER TABLE icecast_servers ADD   CONSTRAINT "icecast_servers_template_id_fkey" FOREIGN KEY (template_id) REFERENCES icecast_templates(id) ON DELETE SET NULL;

ALTER TABLE icecast_mounts    DROP  CONSTRAINT "icecast_mounts_icecast_mount_template_id_fkey";
ALTER TABLE icecast_mounts    ADD   CONSTRAINT "icecast_mounts_icecast_mount_template_id_fkey" FOREIGN KEY (icecast_mount_template_id) REFERENCES icecast_mount_templates(id) ON DELETE SET NULL;
ALTER TABLE icecast_mounts    DROP  CONSTRAINT "icecast_mounts_icecast_server_id_fkey";
ALTER TABLE icecast_mounts    ADD   CONSTRAINT "icecast_mounts_icecast_server_id_fkey" FOREIGN KEY (icecast_server_id) REFERENCES icecast_servers(id) ON DELETE SET NULL;

ALTER TABLE icecast_templates DROP  CONSTRAINT "icecast_templates_admin_auth_id_fkey";
ALTER TABLE icecast_templates ADD   CONSTRAINT "icecast_templates_admin_auth_id_fkey" FOREIGN KEY (admin_auth_id) REFERENCES icecast_admin_authentications(id) ON DELETE SET NULL;
ALTER TABLE icecast_templates DROP  CONSTRAINT "icecast_templates_directory_id_fkey";
ALTER TABLE icecast_templates ADD   CONSTRAINT "icecast_templates_directory_id_fkey"  FOREIGN KEY (directory_id) REFERENCES icecast_directories(id) ON DELETE SET NULL;
ALTER TABLE icecast_templates DROP  CONSTRAINT "icecast_templates_limit_id_fkey";
ALTER TABLE icecast_templates ADD   CONSTRAINT "icecast_templates_limit_id_fkey" FOREIGN KEY (limit_id) REFERENCES icecast_limits(id) ON DELETE SET NULL;
ALTER TABLE icecast_templates DROP  CONSTRAINT "icecast_templates_logging_id_fkey";
ALTER TABLE icecast_templates ADD   CONSTRAINT "icecast_templates_logging_id_fkey" FOREIGN KEY (logging_id) REFERENCES icecast_loggings(id) ON DELETE SET NULL;
ALTER TABLE icecast_templates DROP  CONSTRAINT "icecast_templates_master_relay_id_fkey";
ALTER TABLE icecast_templates ADD   CONSTRAINT "icecast_templates_master_relay_id_fkey" FOREIGN KEY (master_relay_id) REFERENCES icecast_master_server_relays(id) ON DELETE SET NULL;
ALTER TABLE icecast_templates DROP  CONSTRAINT "icecast_templates_path_id_fkey";
ALTER TABLE icecast_templates ADD   CONSTRAINT "icecast_templates_path_id_fkey" FOREIGN KEY (path_id) REFERENCES icecast_paths(id) ON DELETE SET NULL;
ALTER TABLE icecast_templates DROP  CONSTRAINT "icecast_templates_security_id_fkey";
ALTER TABLE icecast_templates ADD   CONSTRAINT "icecast_templates_security_id_fkey" FOREIGN KEY (security_id) REFERENCES icecast_securities(id) ON DELETE SET NULL;