-- $Id$

DROP TABLE IF EXISTS bsc;

DROP TABLE IF EXISTS self;

CREATE TABLE self (
       self_id              SERIAL NOT NULL,
       use_hsm              BOOLEAN,
       PRIMARY KEY          (self_id)
);

DROP TABLE IF EXISTS self_pref;

CREATE TABLE self_pref (
       pref_name            VARCHAR(100),
       pref_value           TEXT,
       self_id              BIGINT unsigned NOT NULL,
       PRIMARY KEY          (self_id, pref_name),
       FOREIGN KEY          (self_id) REFERENCES self
);

CREATE TABLE bsc (
       bsc_id               SERIAL NOT NULL,
       private_key_id       LONGBLOB,
       self_id              BIGINT unsigned NOT NULL,
       public_key           LONGBLOB,
       PRIMARY KEY          (bsc_id),
       FOREIGN KEY          (self_id) REFERENCES self
);

DROP TABLE IF EXISTS bsc_cert;

CREATE TABLE bsc_cert (
       bsc_cert_id          SERIAL NOT NULL,
       cert                 LONGBLOB,
       bsc_id               BIGINT unsigned NOT NULL,
       PRIMARY KEY          (bsc_cert_id),
       FOREIGN KEY          (bsc_id) REFERENCES bsc
);

DROP TABLE IF EXISTS repository;

CREATE TABLE repository (
       repository_id        SERIAL NOT NULL,
       peer_contact_uri     TEXT,
       peer_ta              LONGBLOB,
       bsc_id               BIGINT unsigned NOT NULL,
       self_id              BIGINT unsigned NOT NULL,
       PRIMARY KEY          (repository_id),
       FOREIGN KEY          (self_id) REFERENCES self,
       FOREIGN KEY          (bsc_id) REFERENCES bsc
);

DROP TABLE IF EXISTS parent;

CREATE TABLE parent (
       parent_id            SERIAL NOT NULL,
       peer_ta              LONGBLOB,
       peer_contact_uri     TEXT,
       sia_base             TEXT,
       self_id              BIGINT unsigned NOT NULL,
       bsc_id               BIGINT unsigned NOT NULL,
       repository_id        BIGINT unsigned NOT NULL,
       PRIMARY KEY          (parent_id),
       FOREIGN KEY          (repository_id) REFERENCES repository,
       FOREIGN KEY          (bsc_id) REFERENCES bsc,
       FOREIGN KEY          (self_id) REFERENCES self
);

DROP TABLE IF EXISTS ca;

CREATE TABLE ca (
       ca_id                SERIAL NOT NULL,
       last_crl_sn          BIGINT unsigned,
       last_manifest_sn     BIGINT unsigned,
       next_manifest_update DATETIME,
       next_crl_update      DATETIME,
       last_issued_sn       BIGINT unsigned,
       sia_uri              TEXT,
       parent_id            BIGINT unsigned,
       PRIMARY KEY          (ca_id),
       FOREIGN KEY          (parent_id) REFERENCES parent
);

DROP TABLE IF EXISTS ca_detail;

CREATE TABLE ca_detail (
       ca_detail_id         SERIAL NOT NULL,
       public_key           LONGBLOB,
       private_key_handle   LONGBLOB,
       latest_crl           LONGBLOB,
       latest_ca_cert_over_public_key LONGBLOB,
       manifest_ee_private_key_handle LONGBLOB,
       manifest_ee_public_key LONGBLOB,
       latest_manifest_ee_cert LONGBLOB,
       latest_manifest      LONGBLOB,
       ca_id                BIGINT unsigned NOT NULL,
       PRIMARY KEY          (ca_detail_id),
       FOREIGN KEY          (ca_id) REFERENCES ca
);

DROP TABLE IF EXISTS child;

CREATE TABLE child (
       child_id             SERIAL NOT NULL,
       peer_ta              LONGBLOB,
       self_id              BIGINT unsigned NOT NULL,
       bsc_id               BIGINT unsigned NOT NULL,
       PRIMARY KEY          (child_id),
       FOREIGN KEY          (bsc_id) REFERENCES bsc,
       FOREIGN KEY          (self_id) REFERENCES self
);

DROP TABLE IF EXISTS child_ca_certificate;

CREATE TABLE child_ca_certificate (
       child_id             BIGINT unsigned NOT NULL,
       ca_detail_id         BIGINT unsigned NOT NULL,
       cert                 LONGBLOB NOT NULL,
       PRIMARY KEY          (child_id, ca_detail_id),
       FOREIGN KEY          (ca_detail_id) REFERENCES ca_detail,
       FOREIGN KEY          (child_id) REFERENCES child
);

DROP TABLE IF EXISTS child_ca_link;

CREATE TABLE child_ca_link (
       ca_id                BIGINT unsigned NOT NULL,
       child_id             BIGINT unsigned NOT NULL,
       PRIMARY KEY          (ca_id, child_id),
       FOREIGN KEY          (child_id) REFERENCES child,
       FOREIGN KEY          (ca_id) REFERENCES ca
);

DROP TABLE IF EXISTS route_origin;

CREATE TABLE route_origin (
       route_origin_id      SERIAL NOT NULL,
       as_number            DECIMAL(24,0),
       self_id              BIGINT unsigned NOT NULL,
       PRIMARY KEY          (route_origin_id),
       FOREIGN KEY          (self_id) REFERENCES self
);

DROP TABLE IF EXISTS route_origin_range;

CREATE TABLE route_origin_range (
       start_ip             VARCHAR(40),
       end_ip               VARCHAR(40),
       route_origin_id      BIGINT unsigned NOT NULL,
       PRIMARY KEY          (route_origin_id, start_ip, end_ip),
       FOREIGN KEY          (route_origin_id) REFERENCES route_origin
);

DROP TABLE IF EXISTS roa;

CREATE TABLE roa (
       route_origin_id      BIGINT unsigned NOT NULL,
       ee_cert              LONGBLOB,
       roa                  LONGBLOB NOT NULL,
       ca_detail_id         BIGINT unsigned NOT NULL,
       PRIMARY KEY          (route_origin_id, ca_detail_id),
       FOREIGN KEY          (ca_detail_id) REFERENCES ca_detail,
       FOREIGN KEY          (route_origin_id) REFERENCES route_origin
);