\c gss

-- /!\ Certificates are not deleted by script to avoid destroying CAs. They must be deleted manually.

-- Clear TLS Connections and test instances
TRUNCATE TABLE tls_connection,tls_connection_data_item,tls_connection_data_item_aliases_item, tls_connection_tls_connection_data_item,tls_test_instance,tls_test_suite_instance_tls_test_instance;
SELECT setval('tls_connection_id_seq', 1);
SELECT setval('tls_connection_data_item_id_seq', 1);
SELECT setval('tls_connection_data_item_aliases_item_id_seq', 1);
SELECT setval('tls_test_instance_id_seq', 1);

-- Clear proxy messages
--Following table does not exist : pxy_abstract_message_attributesforcommandset
TRUNCATE TABLE pxy_connection, pxy_abstract_message;
SELECT setval('pxy_message_id_seq', 1);

-- Delete ATNA Questionnaires except the template
DELETE FROM atna_audited_event where atna_questionnaire_id != (select id from atna_questionnaire where system = 'TEMPLATE');
DELETE FROM atna_network_communication where atna_questionnaire_id != (select id from atna_questionnaire where system = 'TEMPLATE');
DELETE FROM atna_tls_test where atna_questionnaire_id != (select id from atna_questionnaire where system = 'TEMPLATE');
DELETE FROM atna_questionnaire where system != 'TEMPLATE';
DELETE FROM public.atna_instruction_instance where testing_session_id in (SELECT id FROM atna_testing_session where description != 'Demonstration');
DELETE FROM atna_testing_session where description != 'Demonstration';

TRUNCATE TABLE xua_service_provider_test_instance;
SELECT setval('xua_service_provider_test_instance_id_seq', 1);


-- Delete SyslogCollector data
TRUNCATE TABLE syslog_message ;
SELECT setval('syslog_message_id_seq', 1);

-- Delete certificate requests
CREATE TABLE certificate_to_keep (
    id integer NOT NULL
);
INSERT INTO certificate_to_keep (id) VALUES (22), (23), (24), (25), (26), (27), (28), (29), (30), (31), (32), (33), (34), (35), (72), (94), (100), (101), (146), (147), (149), (167), (234), (238), (239), (333), (334), (335), (336), (337), (338), (348), (349), (333), (333);

BEGIN;
alter table pki_certificate DROP CONSTRAINT fk7524c0535694cbcf;
alter table pki_certificate_request DROP CONSTRAINT fk447356a3e45e63b9;
alter table pki_certificate DROP CONSTRAINT fk7524c053e45e63b9;
DELETE FROM pki_certificate_request where id NOT IN (SELECT request_id FROM pki_certificate WHERE id IN (SELECT id FROM certificate_to_keep));
DELETE FROM pki_certificate where id NOT IN (SELECT id FROM certificate_to_keep);
DELETE FROM pki_certificate_key where id NOT IN (
SELECT privatekey_id as combine FROM pki_certificate WHERE id IN (SELECT id FROM certificate_to_keep) UNION ALL
SELECT publickey_id FROM pki_certificate WHERE id IN (SELECT id FROM certificate_to_keep) UNION ALL
SELECT privatekey_id FROM pki_certificate_request WHERE id IN (SELECT request_id FROM pki_certificate WHERE id IN (SELECT id FROM certificate_to_keep)) UNION ALL
SELECT publickey_id FROM pki_certificate_request WHERE id IN (SELECT request_id FROM pki_certificate WHERE id IN (SELECT id FROM certificate_to_keep)));

DELETE FROM pki_certificate_x509 where id NOT IN (SELECT certificatex509_id FROM pki_certificate WHERE id IN (SELECT id FROM certificate_to_keep));
alter table pki_certificate ADD CONSTRAINT fk7524c0535694cbcf FOREIGN KEY (request_id) REFERENCES  pki_certificate_request(id);
alter table pki_certificate_request ADD CONSTRAINT fk447356a3e45e63b9 FOREIGN KEY (certificateauthority_id) REFERENCES pki_certificate(id);
alter table pki_certificate ADD CONSTRAINT fk7524c053e45e63b9 FOREIGN KEY (certificateauthority_id) REFERENCES pki_certificate(id);

COMMIT;

DROP TABLE certificate_to_keep;

