-- see http://www.icecast.org/docs/icecast-2.3.3/icecast2_config_file.html#limits
CREATE TABLE icecast_limits (
  id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),

  -- number of listening clients
  clients INTEGER NOT NULL DEFAULT 1000,

  --number of sources include souce clients and relays
  sources INTEGER NOT NULL DEFAULT 50,

  -- maximum size (in bytes) of the stream queue
  queue_size INTEGER NOT NULL DEFAULT 102400,

  -- does not appear to be used
  client_timeout INTEGER DEFAULT 30,

  -- The maximum time (in seconds) to wait for a request to come in once
  -- the client has made a connection to the server.
  -- In general this value should not need to be tweaked.
  header_timeout INTEGER DEFAULT 15,

  -- If a connected source does not send any data within this
  -- timeout period (in seconds), then the source connection
  -- will be removed from the server.
  source_timeout INTEGER DEFAULT 10,

  --  The burst size is the amount of data (in bytes)
  -- to burst to a client at connection time.
  burst_size INTEGER DEFAULT 65536,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

 );


CREATE TABLE  icecast_admin_authentications (
  id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),

  -- The unencrypted password used by sources to connect to icecast2.
  -- The DEFAULT username for all source connections is 'source' but
  -- this option allows to specify a DEFAULT password. This and the username
  -- can be changed in the individual mount sections.
  source_pass VARCHAR(64) NOT NULL,

  -- Used in the master server as part of the authentication when a slave requests
  -- the list of streams to relay. The DEFAULT username is 'relay'
  relay_user VARCHAR(64) NOT NULL,
  relay_pass VARCHAR(64) NOT NULL,

  --The username/password used for all administration functions.
  admin_user VARCHAR(64) NOT NULL,
  admin_pass VARCHAR(64) NOT NULL,

  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );


--contains all the settings for listing a stream on any of the Icecast2 YP Directory servers.
-- Multiple occurances of this section can be specified in order to be listed on multiple directory servers.
CREATE TABLE  icecast_directories (
   id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
   yp_url_timeout INTEGER NOT NULL DEFAULT 15,
   yp_url VARCHAR(1024) NOT NULL,
   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE icecast_listen_sockets (
    id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),

    -- The TCP port that will be used to accept client connections.
    port INTEGER NOT NULL DEFAULT  8001,

    -- An optional IP address that can be used to bind to a specific network card.
    -- If not supplied, then it will bind to all interfaces.
    bind_address VARCHAR(1024),

    shoutcast_mount VARCHAR(1024),
    shoutcast_compat INTEGER,

   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE icecast_relays (
   id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),

   -- ip address of server we are relaying from and port number
   server VARCHAR(1024) NOT NULL,
   port INTEGER NOT NULL DEFAULT  8001,

   -- mount at server. eg /example.ogg
   mount VARCHAR(1024)  NOT NULL,
   -- eg /different.ogg
   local_mount VARCHAR(1024),
   -- eg joe. could be null
   relay_username VARCHAR(64),
   -- user password
   relay_pass VARCHAR(64),
   relay_shoutcast_metadata INTEGER DEFAULT 0,
   --- relay only if we have someone wanting to listen
   on_demand INTEGER DEFAULT 1,

   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE UNLOGGED TABLE icecast_user_authentications(
    id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
    --"htpasswd or url"
    -- real name is type
    authentication_type VARCHAR(16)  DEFAULT  'url',
    -- these are for httpasswd
    filename  VARCHAR(1024),
    allow_duplicate_users INTEGER,

    -- these options are for url
    -- eg value="http://myauthserver.com/stream_start.php"
    mount_add VARCHAR(1024),
    --value="http://myauthserver.com/stream_end.php"
    mount_remove VARCHAR(1024),
    --value="http://myauthserver.com/listener_joined.php"
    listener_add VARCHAR(1024),
    --value="http://myauthserver.com/listener_left.php"
    listener_remove VARCHAR(1024),
    -- value="user"
    unused_username VARCHAR(64),
    -- value="pass"
    unused_pass VARCHAR(64),
    -- value="icecast-auth-user: 1"
    auth_header VARCHAR(64)   DEFAULT 'icecast-auth-user: 1',
    -- value="icecast-auth-timelimit:"
    timelimit_header VARCHAR(64)   DEFAULT 'icecast-auth-timelimit:',

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE UNLOGGED TABLE icecast_mounts (
    id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
    -- eg/example-complex.ogg
    name VARCHAR(1024) UNIQUE NOT NULL,
    source_username VARCHAR(64),
    source_pass VARCHAR(64),
    max_listeners INTEGER DEFAULT 4,
    max_listener_duration INTEGER DEFAULT 3600,
    --  dump of the stream coming through on this mountpoint.
    -- eg /tmp/dump-example1.ogg
    dump_file VARCHAR(1024),
     -- intro music to play
    -- This optional value specifies a mountpoint that clients are automatically moved to
    -- if the source shuts down or is not streaming at the time a listener connects.
    intro VARCHAR(1024),
    fallback_mount VARCHAR(1024),
    -- When enabled, this allows a connecting source client or relay on this mountpoint
    -- to move listening clients back from the fallback mount.
    fallback_override INTEGER DEFAULT 1,

    -- When set to 1, this will cause new listeners, when the max listener count for the mountpoint
    -- has been reached, to move to the fallback mount if there is one specified.
    fallback_when_full INTEGER DEFAULT 1,

    --For non-Ogg streams like MP3, the metadata that is inserted into the stream often
    -- has no defined character set.
    charset VARCHAR(1024) DEFAULT 'ISO8859-1',
    -- possible values are -1, 0, 1
    -- real name is public but this is reserved word in ruby
    is_public INTEGER DEFAULT 0,

    stream_name VARCHAR(1024),
    stream_description VARCHAR(10000),
    -- direct to user page
    stream_url  VARCHAR(1024),
    -- get this from the session info
    genre VARCHAR(256),
    bitrate INTEGER,
    -- real name is type but this is reserved name in ruby
    mime_type VARCHAR(64)  NOT NULL DEFAULT   'audio/ogg' ,
    subtype VARCHAR(64)  NOT NULL DEFAULT  'vorbis',

    -- This optional setting allows for providing a burst size which overrides the
    -- DEFAULT burst size as defined in limits. The value is in bytes.
    burst_size INTEGER,
    mp3_metadata_interval INTEGER,

    -- Enable this to prevent this mount from being shown on the xsl pages.
    -- This is mainly for cases where a local relay is configured and you do
    -- not want the source of the local relay to be shown
    hidden INTEGER DEFAULT 1,

    --called when the source  connects or disconnects. The scripts are called with the name of the mount
    on_connect VARCHAR(1024),
    on_disconnect VARCHAR(1024),

    -- references icecast_user_authentications(id)
    authentication_id varchar(64) DEFAULT NULL,

    ------stats------
    listeners INTEGER NOT NULL DEFAULT 0,
    sourced   BOOLEAN NOT NULL DEFAULT FALSE,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE icecast_paths (
    id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
    base_dir VARCHAR(1024) NOT NULL DEFAULT  './',
    log_dir VARCHAR(1024)  NOT NULL DEFAULT  './logs',
    pid_file VARCHAR(1024) DEFAULT  './icecast.pid',
    web_root VARCHAR(1024)  NOT NULL DEFAULT  './web',
    admin_root VARCHAR(1024) NOT NULL DEFAULT './admin',
    allow_ip VARCHAR(1024),
    deny_ip VARCHAR(1024),
    alias_source VARCHAR(1024),
    alias_dest VARCHAR(1024),

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE icecast_loggings (
    id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
    access_log VARCHAR(1024)  NOT NULL DEFAULT 'access.log',
    error_log VARCHAR(1024)  NOT NULL DEFAULT 'error.log',
    playlist_log VARCHAR(1024),
    -- 4 Debug, 3 Info, 2 Warn, 1 Error
    log_level INTEGER NOT NULL DEFAULT 3 ,
    log_archive INTEGER,
    -- 10 meg log file by default
    log_size INTEGER DEFAULT 10000,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE icecast_securities (
    id VARCHAR(64) PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
    chroot INTEGER NOT NULL  DEFAULT 0,
    change_owner_user VARCHAR(64),
    change_owner_group VARCHAR(64),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE icecast_master_server_relays(
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),

     -- ip address of the master icecast server and port number
    master_server VARCHAR(1024) NOT NULL,
    master_server_port INTEGER NOT NULL DEFAULT 8001,
    --The interval (in seconds) that the Relay Server will poll the Master Server for any new mountpoints to relay.
    master_update_interval INTEGER NOT NULL DEFAULT 120,
    -- This is the relay username on the master server. It is used to query the server for a list of
    -- mountpoints to relay. If not specified then 'relay' is used
    master_username VARCHAR(64) NOT NULL,
    master_pass VARCHAR(64) NOT NULL,

    --Global on-demand setting for relays. Because you do not have individual relay options when
    -- using a master server relay, you still may want those relays to only pull the stream when
    -- there is at least one listener on the slave. The typical case here is to avoid surplus
    -- bandwidth costs when no one is listening.
    relays_on_demand INTEGER NOT NULL DEFAULT 1,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE icecast_templates (
      id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),

      limit_id        VARCHAR(64) REFERENCES icecast_limits(id),
      admin_auth_id   VARCHAR(64) REFERENCES icecast_admin_authentications(id),
      directory_id    VARCHAR(64) REFERENCES icecast_directories(id),
      master_relay_id VARCHAR(64) REFERENCES icecast_master_server_relays(id),
      path_id         VARCHAR(64) REFERENCES icecast_paths(id),
      logging_id      VARCHAR(64) REFERENCES icecast_loggings(id),
      security_id     VARCHAR(64) REFERENCES icecast_securities(id),

      location        VARCHAR(1024) NOT NULL,
      name            VARCHAR(256)  NOT NULL,
      admin_email     VARCHAR(1024) NOT NULL DEFAULT 'admin@jamkazam.com',
      fileserve       INTEGER       NOT NULL DEFAULT 1,

      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE icecast_servers (
      id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
      --use this to mark the server configuration as needing to be regenerated
      config_changed  INTEGER DEFAULT 0,
      limit_id        VARCHAR(64) REFERENCES icecast_limits(id),
      admin_auth_id   VARCHAR(64) REFERENCES icecast_admin_authentications(id),
      directory_id    VARCHAR(64) REFERENCES icecast_directories(id),
      master_relay_id VARCHAR(64) REFERENCES icecast_master_server_relays(id),
      path_id         VARCHAR(64) REFERENCES icecast_paths(id),
      logging_id      VARCHAR(64) REFERENCES icecast_loggings(id),
      security_id     VARCHAR(64) REFERENCES icecast_securities(id),
      template_id     VARCHAR(64) NOT NULL REFERENCES icecast_templates(id),

      -- This is the DNS name or IP address that will be used for the stream directory lookups or possibily
      -- the playlist generation if a Host header is not provided. While localhost is shown as an example,
      -- in fact you will want something that your listeners can use.
      hostname VARCHAR(1024) NOT NULL,
      server_id  VARCHAR(1024) UNIQUE NOT NULL,
      --This sets the location string for this icecast instance. It will be shown e.g in the web interface.
      location VARCHAR(1024),
      --This should contain contact details for getting in touch with the server administrator.
      admin_email VARCHAR(1024),
      -- This flag turns on the icecast2 fileserver from which static files can be served.
      -- All files are served relative to the path specified in the <paths><webroot> configuration
      -- setting. By DEFAULT the setting is enabled so that requests for the images
      -- on the status page are retrievable.
      fileserve INTEGER,
      -- This optional setting allows for the administrator of the server to override the
      -- DEFAULT server identification. The DEFAULT is icecast followed by a version number
      -- and most will not care to change it however this setting will change that.

      created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

);

CREATE TABLE icecast_server_mounts (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    --REFERENCES icecast_mounts(id) ON DELETE CASCADE,
    icecast_mount_id VARCHAR(64),
    icecast_server_id VARCHAR(64) REFERENCES icecast_servers(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE icecast_server_mounts ADD CONSTRAINT server_mount_uniqkey UNIQUE (icecast_mount_id, icecast_server_id);

CREATE TABLE icecast_server_relays (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    icecast_relay_id VARCHAR(64) REFERENCES icecast_relays(id) ON DELETE CASCADE,
    icecast_server_id VARCHAR(64) REFERENCES icecast_servers(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE icecast_server_relays ADD CONSTRAINT server_relay_uniqkey UNIQUE (icecast_relay_id, icecast_server_id);

CREATE TABLE icecast_server_sockets (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    icecast_listen_socket_id VARCHAR(64) REFERENCES icecast_listen_sockets(id) ON DELETE CASCADE,
    icecast_server_id VARCHAR(64) REFERENCES icecast_servers(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE icecast_server_sockets ADD CONSTRAINT server_socket_uniqkey UNIQUE (icecast_listen_socket_id, icecast_server_id);

CREATE TABLE icecast_template_sockets (
    id VARCHAR(64) PRIMARY KEY DEFAULT uuid_generate_v4(),
    icecast_listen_socket_id VARCHAR(64) REFERENCES icecast_listen_sockets(id) ON DELETE CASCADE,
    icecast_template_id VARCHAR(64) REFERENCES icecast_templates(id) ON DELETE CASCADE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE icecast_template_sockets ADD CONSTRAINT template_socket_uniqkey UNIQUE (icecast_listen_socket_id, icecast_template_id);