BEGIN;
CREATE TABLE appstore  (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL default '1',
--HELP_EN_INFO:company
--HELP_EN_LONG:The company this appstore belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:Het bedrijf waar deze appstore bij hoort.

 name varchar(255) NOT NULL default '',
--HELP_EN_INFO:appstore name
--HELP_EN_LONG:The name of this appstore.
--HELP_NL_INFO:naam
--HELP_NL_LONG:De naam van deze appstore.

 status varchar(8) NOT NULL default 'active'
   CHECK (status IN ('active', 'deleted')),
--HELP_EN_INFO:state
--HELP_EN_LONG:Appstore status.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van deze appstore.

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON appstore  TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON appstore_id_seq TO astium;

INSERT INTO appstore (name, company) VALUES ('empty', 1);

CREATE TABLE application  (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL default '1',
--HELP_EN_INFO:company
--HELP_EN_LONG:The company this application belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:Het bedrijf waar deze applicatie bij hoort.

 name varchar(255) NOT NULL default '',
--HELP_EN_INFO:application name
--HELP_EN_LONG:The name of this application.
--HELP_NL_INFO:naam
--HELP_NL_LONG:De naam van deze applicatie.

 filename varchar(255) NOT NULL default '',
--HELP_EN_INFO:filename
--HELP_EN_LONG:The name of the file of this application.
--HELP_NL_INFO:bestandsnaam
--HELP_NL_LONG:De naam van het bestand van deze applicatie.

 appstore integer NOT NULL default '1',
--HELP_EN_INFO:appstore
--HELP_EN_LONG:The appstore of this application.
--HELP_NL_INFO:appstore
--HELP_NL_LONG:De appstore van deze applicatie.

 version integer NOT NULL default '1',
--HELP_EN_INFO:version
--HELP_EN_LONG:The version of this application.
--HELP_NL_INFO:versie
--HELP_NL_LONG:De versie van deze applicatie.

 date timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:date
--HELP_EN_LONG:The date of this application.
--HELP_NL_INFO:datum
--HELP_NL_LONG:De datum van deze applicatie.

 status varchar(8) NOT NULL default 'active'
   CHECK (status IN ('active', 'deleted')),
--HELP_EN_INFO:state
--HELP_EN_LONG:Applicatie status.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van deze applicatie.

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON application  TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON application_id_seq TO astium;

INSERT INTO application (name, company, appstore) VALUES ('empty', 1, 1);

CREATE TABLE competence  (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL default '1',
--HELP_EN_INFO:company
--HELP_EN_LONG:The company this competence belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:Het bedrijf waar deze kwalificatie bij hoort.

 name varchar(255) NOT NULL default '',
--HELP_EN_INFO:competence name
--HELP_EN_LONG:The name of this competence.
--HELP_NL_INFO:naam
--HELP_NL_LONG:De naam van deze kwalificatie.

 description varchar(256) NOT NULL default '',
--HELP_EN_INFO:Description
--HELP_EN_LONG:The description for this competence.
--HELP_NL_INFO:Omschrijving
--HELP_NL_LONG:Een omschrijving voor deze kwalificatie.

 status varchar(8) NOT NULL default 'active'
   CHECK (status IN ('active', 'deleted')),
--HELP_EN_INFO:state
--HELP_EN_LONG:Competence status.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van deze kwalificatie.

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON competence  TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON competence_id_seq TO astium;

INSERT INTO competence (name, company, description) VALUES ('empty', 1, 'empty');

CREATE TABLE notificationrole (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 company integer NOT NULL default '1',
--HELP_EN_INFO:company
--HELP_EN_LONG:The company this notification role belongs to.
--HELP_NL_INFO:bedrijf
--HELP_NL_LONG:Het bedrijf waar deze rol bij hoort.

 name varchar(255) NOT NULL default '',
--HELP_EN_INFO:notification role name
--HELP_EN_LONG:The name of this notification role.
--HELP_NL_INFO:naam
--HELP_NL_LONG:De naam van deze notificatie rol.

 description varchar(256) NOT NULL default '',
--HELP_EN_INFO:Description
--HELP_EN_LONG:The description for this notification role.
--HELP_NL_INFO:Omschrijving
--HELP_NL_LONG:Een omschrijving voor deze notificatie rol.

 status varchar(8) NOT NULL default 'active'
   CHECK (status IN ('active', 'deleted')),
--HELP_EN_INFO:state
--HELP_EN_LONG:Role status.
--HELP_NL_INFO:status
--HELP_NL_LONG:De status van deze notificatie rol.

 PRIMARY KEY  (id)
);

GRANT UPDATE, SELECT, INSERT, DELETE ON notificationrole  TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON notificationrole_id_seq TO astium;

INSERT INTO notificationrole (name, company, description) VALUES ('empty', 1, 'empty');
INSERT INTO notificationrole (name, company, description) VALUES ('empty', 2, 'empty');
INSERT INTO notificationrole (name, company, description) VALUES ('Default', 3, 'Default Role');

CREATE TABLE contact_competence (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 contact integer NOT NULL default '1',
--HELP_EN_INFO:contact
--HELP_EN_LONG:The agent for this competence.
--HELP_NL_INFO:persoon
--HELP_NL_LONG:De medewerker voor deze kwalificatie.

 competence integer NOT NULL default '1',
--HELP_EN_INFO:competence
--HELP_EN_LONG:The competence for this agent.
--HELP_NL_INFO:kwalificatie
--HELP_NL_LONG:De kwalificatie voor deze medewerker.

 PRIMARY KEY  (id)
);

CREATE INDEX contact_competence_contact_index ON contact_competence (contact);
CREATE INDEX contact_competence_competence_index ON contact_competence (competence);

GRANT UPDATE, SELECT, INSERT, DELETE ON contact_competence TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON contact_competence_id_seq TO astium;
INSERT INTO contact_competence (contact, competence) VALUES ('1', '1');

CREATE TABLE contact_notificationrole (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 contact integer NOT NULL default '1',
--HELP_EN_INFO:contact
--HELP_EN_LONG:The agent for this notification role.
--HELP_NL_INFO:persoon
--HELP_NL_LONG:De medewerker voor deze notificatie rol.

 notificationrole integer NOT NULL default '1',
--HELP_EN_INFO:notification role
--HELP_EN_LONG:The notification role for this agent.
--HELP_NL_INFO:notificatie rol
--HELP_NL_LONG:De notificatie rol voor deze medewerker.

 PRIMARY KEY  (id)
);

CREATE INDEX contact_notificationrole_contact_index ON contact_notificationrole (contact);
CREATE INDEX contact_notificationrole_notificationrole_index ON contact_notificationrole (notificationrole);

GRANT UPDATE, SELECT, INSERT, DELETE ON contact_notificationrole TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON contact_notificationrole_id_seq TO astium;
INSERT INTO contact_notificationrole (contact, notificationrole) VALUES ('1', '1');

CREATE TABLE contact_role (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 contact integer NOT NULL default '1',
--HELP_EN_INFO:contact
--HELP_EN_LONG:The agent for this role.
--HELP_NL_INFO:persoon
--HELP_NL_LONG:De medewerker voor deze rol.

 role integer NOT NULL default '1',
--HELP_EN_INFO:role
--HELP_EN_LONG:The role for this agent.
--HELP_NL_INFO:rol
--HELP_NL_LONG:De rol voor deze medewerker.

 PRIMARY KEY  (id)
);

CREATE INDEX contact_role_contact_index ON contact_role (contact);
CREATE INDEX contact_role_role_index ON contact_role (role);

GRANT UPDATE, SELECT, INSERT, DELETE ON contact_role TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON contact_role_id_seq TO astium;
INSERT INTO contact_role (contact, role) VALUES ('1', '1');

CREATE TABLE role_application (
 id serial,

 created timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Creation Date
--HELP_EN_LONG:The date and time this record was created.
--HELP_NL_INFO:Datum aanmaak
--HELP_NL_LONG:De tijd en datum waarop dit is aangemaakt.

 lastupdate timestamp with time zone not null default CURRENT_TIMESTAMP,
--HELP_EN_INFO:Last Update
--HELP_EN_LONG:The date and time this record was updated.
--HELP_NL_INFO:Laatste wijziging
--HELP_NL_LONG:De tijd en datum van laatste wijziging.

 role integer NOT NULL default '1',
--HELP_EN_INFO:role
--HELP_EN_LONG:The role for this application.
--HELP_NL_INFO:rol
--HELP_NL_LONG:De rol voor deze applicatie.

 application integer NOT NULL default '1',
--HELP_EN_INFO:application
--HELP_EN_LONG:The application for this role.
--HELP_NL_INFO:applicatie
--HELP_NL_LONG:De applicatie voor deze rol.

 PRIMARY KEY  (id)
);

CREATE INDEX role_application_role_index ON role_application (role);
CREATE INDEX role_application_application_index ON role_application (application);

GRANT UPDATE, SELECT, INSERT, DELETE ON role_application TO astium;
GRANT UPDATE, SELECT, INSERT, DELETE ON role_application_id_seq TO astium;
INSERT INTO role_application (role, application) VALUES ('1', '1');

-- appstore
ALTER TABLE appstore ADD CONSTRAINT appstore_company_constraint FOREIGN KEY (company) REFERENCES company (id);

-- application
ALTER TABLE application ADD CONSTRAINT application_company_constraint FOREIGN KEY (company) REFERENCES company (id);
ALTER TABLE application ADD CONSTRAINT application_appstore_constraint FOREIGN KEY (appstore) REFERENCES appstore (id);

-- competence
ALTER TABLE competence ADD CONSTRAINT competence_company_constraint FOREIGN KEY (company) REFERENCES company (id);

-- contact_notificationrole
ALTER TABLE contact_notificationrole ADD CONSTRAINT contact_notificationrole_notificationrole_constraint FOREIGN KEY (notificationrole) REFERENCES notificationrole (id);
ALTER TABLE contact_notificationrole ADD CONSTRAINT contact_notificationrole_contact_constraint FOREIGN KEY (contact) REFERENCES contact (id);

-- contact_competence
ALTER TABLE contact_competence ADD CONSTRAINT contact_competence_competence_constraint FOREIGN KEY (competence) REFERENCES competence (id);
ALTER TABLE contact_competence ADD CONSTRAINT contact_competence_contact_constraint FOREIGN KEY (contact) REFERENCES contact (id);

-- contact_role
ALTER TABLE contact_role ADD CONSTRAINT contact_role_role_constraint FOREIGN KEY (role) REFERENCES role (id);
ALTER TABLE contact_role ADD CONSTRAINT contact_role_contact_constraint FOREIGN KEY (contact) REFERENCES contact (id);

-- notificationrole
ALTER TABLE notificationrole ADD CONSTRAINT notificationrole_company_constraint FOREIGN KEY (company) REFERENCES company (id);

-- role_application
ALTER TABLE role_application ADD CONSTRAINT role_application_role_constraint FOREIGN KEY (role) REFERENCES role (id);
ALTER TABLE role_application ADD CONSTRAINT role_application_application_constraint FOREIGN KEY (application) REFERENCES application (id);

COMMIT;