#!/usr/bin/env bash
set -e

# LDAP-Server
LDAP=$(cat <<EOT
CREATE TABLE ldapserver(
  id INT PRIMARY KEY  NOT NULL,
  name  TEXT NOT NULL,
  port INT NOT NULL,
  use_ssl INT NOT NULL,
  search_base TEXT NOT NULL,
  search_filter TEXT NOT NULL,
  attr_uid TEXT NOT NULL,
  attr_cn TEXT NOT NULL,
  bind_password TEXT NOT NULL,
  bind_user TEXT NOT NULL
);
EOT
)

# Provider
PROVIDER=$(cat <<EOT
CREATE TABLE provider(
  id INT PRIMARY KEY  NOT NULL,
  name  TEXT NOT NULL,
  short_name TEXT NOT NULL
);
EOT
)

# Server
SERVER=$(cat <<EOT
CREATE TABLE server(
  id INT PRIMARY KEY  NOT NULL,
  prio INT NOT NULL DEFAULT 10,
  name  TEXT NOT NULL,
  port INT NOT NULL,
  type TEXT NOT NULL,
  socket_type TEXT NOT NULL,
  user_name TEXT NOT NULL,
  authentication TEXT NOT NULL
);
EOT
)

# Domain
DOMAIN=$(cat <<EOT
CREATE TABLE domain(
  id INT PRIMARY KEY  NOT NULL,
  name  TEXT NOT NULL,
  provider_id INT NOT NULL,
  ldapserver_id INT NULL,
  FOREIGN KEY(ldapserver_id) REFERENCES ldapserver(id),
  FOREIGN KEY(provider_id) REFERENCES provider(id)
);
CREATE UNIQUE INDEX domain_name ON domain(name);
EOT
)

# Server-Domain
SERVER_DOMAIN=$(cat <<EOT
CREATE TABLE server_domain(
  server_id INT NOT NULL,
  domain_id INT NOT NULL,
  FOREIGN KEY(server_id) REFERENCES server(id),
  FOREIGN KEY(domain_id) REFERENCES domain(id)
);
EOT
)

## TODO Foreign keys

SQL_CMD=$(cat <<EOT
$LDAP
$PROVIDER
$SERVER
$DOMAIN
$SERVER_DOMAIN
EOT
)

echo -e ${SQL_CMD}

echo -e ${SQL_CMD} | sqlite3 /data/db.sqlite