-- clean the tables in the right order:

DELETE FROM
	generic_lock
;
DELETE FROM
	generic_name
;
DELETE FROM
	generic_text
;

DELETE FROM
	link_open_data_category_conceptual_domain
;
DELETE FROM
	link_closed_data_category_conceptual_domains
;
DELETE FROM
	link_constrained_data_category_conceptual_domains
;
DELETE FROM
	link_constrained_linguistic_section_conceptual_domains
;
DELETE FROM
	link_data_category_selection_users
;
DELETE FROM
	link_group_members
;
DELETE FROM
	link_profile_members
;
DELETE FROM
	link_selected_data_category_picklist
;
DELETE FROM
	link_selected_data_category_profiles
;
DELETE FROM
	link_simple_data_category_super
;
DELETE FROM
	link_value_domain_values
;

DELETE FROM
	core_selected_container_data_category
;
DELETE FROM
	core_selected_complex_data_category
;
DELETE FROM
	core_selected_simple_data_category
;
DELETE FROM
	core_selected_data_category
;

DELETE FROM
	core_data_category_selection
;

DELETE FROM
	core_registration_group
;
DELETE FROM
	core_submission_group
;
DELETE FROM
	core_decision_group
;

DELETE FROM
	core_language_section
;

DELETE FROM
	core_constrained_linguistic_section
;
DELETE FROM
	core_closed_linguistic_section
;
DELETE FROM
	core_linguistic_section
;

DELETE FROM
	core_rule
;

DELETE FROM
	core_profile_value_domain
;
DELETE FROM
	core_value_domain
;
DELETE FROM
	core_conceptual_domain_rule
;
DELETE FROM
	core_open_conceptual_domain
;
DELETE FROM
	core_conceptual_domain
;

DELETE FROM
	core_closed_data_category
;
DELETE FROM
	core_constrained_data_category
;
DELETE FROM
	core_open_data_category
;
DELETE FROM
	core_complex_data_category
;
DELETE FROM
	core_simple_data_category
;
DELETE FROM
	core_container_data_category
;
DELETE FROM
	core_data_category
;

DELETE FROM
	core_profile
;

DELETE FROM
	core_session
;

DELETE FROM
	core_message
;

DELETE FROM
	core_thematic_domain_group
;
DELETE FROM
	core_dcr_board
;
DELETE FROM
	core_group
;
DELETE FROM
	core_owner
;
DELETE FROM
	core_account
;

DELETE FROM
	core_registration_authority
;

DELETE FROM
	core_language
;
DELETE FROM
	core_data_type
;
DELETE FROM
	core_rule_type
;

-- reset sequences

ALTER SEQUENCE core_account_id_seq RESTART WITH 1;

ALTER SEQUENCE core_conceptual_domain_id_seq RESTART WITH 1;

ALTER SEQUENCE core_data_category_id_seq RESTART WITH 1;

ALTER SEQUENCE core_data_category_selection_id_seq RESTART WITH 1;

ALTER SEQUENCE core_data_type_id_seq RESTART WITH 1;

ALTER SEQUENCE core_language_id_seq RESTART WITH 1;

ALTER SEQUENCE core_language_section_id_seq RESTART WITH 1;

ALTER SEQUENCE core_linguistic_section_id_seq RESTART WITH 1;

ALTER SEQUENCE core_message_id_seq RESTART WITH 1;

ALTER SEQUENCE core_profile_id_seq RESTART WITH 1;

ALTER SEQUENCE core_registration_authority_id_seq RESTART WITH 1;

ALTER SEQUENCE core_rule_id_seq RESTART WITH 1;

ALTER SEQUENCE core_rule_type_id_seq RESTART WITH 1;

ALTER SEQUENCE core_selected_data_category_id_seq RESTART WITH 1;

ALTER SEQUENCE core_session_id_seq RESTART WITH 1;

ALTER SEQUENCE generic_lock_id_seq RESTART WITH 1;

ALTER SEQUENCE generic_name_id_seq RESTART WITH 1;

ALTER SEQUENCE generic_text_id_seq RESTART WITH 1;

-- give some tables an initial filling

-- the default languages
-- tag  -> ISO 639-1
-- tags -> (ISO 639-2/B?) ISO 639-2/T&3
INSERT INTO core_language (name, tag, search, tags)            VALUES ('English',       'en', 'english',    'eng');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('French',        'fr', 'french',     'fre fra');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Japanese',      'ja', 'simple',     'jpn');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('German',        'de', 'german',     'ger deu');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Dutch',         'nl', 'dutch',      'dut nld');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Czech',         'cs', 'simple',     'cze ces');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Estonian',      'et', 'simple',     'est');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Finnish',       'fi', 'finnish',    'fin');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Hungarian',     'hu', 'hungarian',  'hun');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Italian',       'it', 'italian',    'ita');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Korean',        'ko', 'simple',     'kor');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Romanian',      'ro', 'romanian',   'rum ron');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Russian',       'ru', 'russian',    'rus');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Swedish',       'sv', 'swedish',    'swe');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Bulgarian',     'bg', 'simple',     'bul');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Croatian',      'hr', 'simple',     'hrv');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Danish',        'da', 'danish',     'dan');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Greek',         'el', 'simple',     'gre ell');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Icelandic',     'is', 'simple',     'ice isl');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Irish',         'ga', 'simple',     'gle');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Hebrew',        'he', 'simple',     'heb');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Latvian',       'lv', 'simple',     'lav');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Lithuanian',    'lt', 'simple',     'lit');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Luxembourgish', 'lb', 'simple',     'ltz');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Maltese',       'mt', 'simple',     'mlt');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Norwegian',     'no', 'norwegian',  'nor');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Polish',        'pl', 'simple',     'pol');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Portuguese',    'pt', 'portuguese', 'por');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Serbian',       'sr', 'simple',     'srp');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Slovene',       'sl', 'simple',     'slv');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Spanish',       'es', 'spanish',    'spa');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Turkish',       'tr', 'turkish',    'tur');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Slovak',        'sk', 'simple',     'slo slk');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Catalan',       'ca', 'simple',     'cat');
INSERT INTO core_language (name, tag, search, tags)            VALUES ('Welsh',         'cy', 'simple',     'wel cym');
INSERT INTO core_language (name, tag, search, tags, direction) VALUES ('Arabic',        'ar', 'simple',     'ara',     'rtl');
INSERT INTO core_language (name, tag, search, tags, direction) VALUES ('Chinese',       'zh', 'simple',     'chi zho', 'ltr');

-- the default data types

INSERT INTO core_data_type (id, data_type, super) VALUES ( 2,'anyType',NULL);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 3,'anySimpleType',2);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 4,'duration',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 5,'dateTime',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 6,'time',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 7,'date',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 8,'gYearMonth',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 9,'gYear',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (10,'gMonthDay',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (11,'gDay',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (12,'gMonth',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (13,'boolean',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (14,'base64Binary',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (15,'hexBinary',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (16,'float',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (17,'double',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (18,'anyURI',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (19,'QName',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (20,'NOTATION',3);
INSERT INTO core_data_type (id, data_type, super) VALUES ( 1,'string',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (21,'normalizedString',1);
INSERT INTO core_data_type (id, data_type, super) VALUES (22,'token',21);
INSERT INTO core_data_type (id, data_type, super) VALUES (23,'language',22);
INSERT INTO core_data_type (id, data_type, super) VALUES (24,'Name',22);
INSERT INTO core_data_type (id, data_type, super) VALUES (25,'NMTOKEN',22);
INSERT INTO core_data_type (id, data_type, super) VALUES (26,'NCName',24);
INSERT INTO core_data_type (id, data_type, super) VALUES (27,'ID',26);
INSERT INTO core_data_type (id, data_type, super) VALUES (28,'IDREF',26);
INSERT INTO core_data_type (id, data_type, super) VALUES (29,'ENTITY',26);
INSERT INTO core_data_type (id, data_type, super) VALUES (30,'decimal',3);
INSERT INTO core_data_type (id, data_type, super) VALUES (31,'integer',30);
INSERT INTO core_data_type (id, data_type, super) VALUES (32,'nonPositiveInteger',31);
INSERT INTO core_data_type (id, data_type, super) VALUES (33,'long',31);
INSERT INTO core_data_type (id, data_type, super) VALUES (34,'nonNegativeInteger',31);
INSERT INTO core_data_type (id, data_type, super) VALUES (35,'negativeInteger',32);
INSERT INTO core_data_type (id, data_type, super) VALUES (36,'int',33);
INSERT INTO core_data_type (id, data_type, super) VALUES (37,'short',36);
INSERT INTO core_data_type (id, data_type, super) VALUES (38,'byte',37);
INSERT INTO core_data_type (id, data_type, super) VALUES (39,'unsignedLong',34);
INSERT INTO core_data_type (id, data_type, super) VALUES (40,'positiveInteger',34);
INSERT INTO core_data_type (id, data_type, super) VALUES (41,'unsignedInt',39);
INSERT INTO core_data_type (id, data_type, super) VALUES (42,'unsignedShort',41);
INSERT INTO core_data_type (id, data_type, super) VALUES (43,'unsignedByte',42);

-- the default rule languages

INSERT INTO core_rule_type (rule_type, xml) VALUES('XML Schema facet',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('XML Schema regular expression',false);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Relax NG datatype parameter',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Schematron rule',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Object Constraint Language',false);
INSERT INTO core_rule_type (rule_type, xml) VALUES('Semantic Web Rule Language',true);
INSERT INTO core_rule_type (rule_type, xml) VALUES('SCHEMAcat reference',true);

-- the default registration authority
-- CHANGE: rename to the proper name of the Registration Authority

INSERT INTO core_registration_authority (id, registration_authority) VALUES (0, 'Private');
INSERT INTO core_registration_authority (    registration_authority) VALUES (   'Example Registration Authority');

-- the default guest user (password: guest)

INSERT INTO core_account(id) VALUES (1);
INSERT INTO core_owner(id) VALUES (1);

-- the default isocat admin (no password, internal use only)

INSERT INTO core_account(id) VALUES (2);
INSERT INTO core_owner(id) VALUES (2);

-- the default DCR Board chair (password: password)
-- CHANGE: replace by the proper details of the DCR Board chair

INSERT INTO core_account(id) VALUES (3);
INSERT INTO core_owner(id) VALUES (3);

-- the DCR Board

INSERT INTO core_account(id) VALUES (4);
INSERT INTO core_group (id) VALUES (4);
INSERT INTO core_dcr_board (id) VALUES (4);
INSERT INTO link_group_members("group", member, "role", status) VALUES (4,3,'chair','accepted');

-- the default profile

INSERT INTO core_profile (id, name) VALUES (-1, 'not available');
INSERT INTO core_profile (id, name) VALUES (0, 'undecided');

-- an example TDG
INSERT INTO core_account(id) VALUES (5);
INSERT INTO core_owner(id) VALUES (5);
INSERT INTO core_group (id) VALUES (5);
INSERT INTO core_thematic_domain_group (id,"name",description,"order",status,activity) VALUES (5,'Example','This is an example TDG and profile. To rename them the PostgreSQL database needs to be updated.',1,'example','example');
INSERT INTO link_group_members("group", member, "role", status) VALUES (5,3,'chair','accepted');

-- an example TDG profile

INSERT INTO core_profile (name,tdg) VALUES ('Example',5);
