CREATE TABLE  notification_destinations  (
   destination_id  int NOT NULL,
   address  varchar(250)  NOT NULL,
   type_id  varchar(50)  NOT NULL,
   pirate_id  varchar(250)  NOT NULL,
   PRIMARY KEY (destination_id)   
);
 

INSERT INTO  notification_destinations  ( destination_id ,  address ,  type_id ,  pirate_id ) VALUES
(1, '33333333333', 'fb', 'marek.krejpsky'),
(2, 'marek.fffwefwer', 'sms', 'marek.krejpsky'),
(3, '434444444444', 'email', 'marek.krejpsky'),
(6, '222222222222222222', 'zulip', 'marek.krejpsky'),
(7, 'fferer.krejpsky', 'signal', 'marek.krejpsky');


CREATE TABLE  notification_sources  (
   source_id  int NOT NULL,
   name  varchar(100)  NOT NULL,
   sender_id  varchar(250)  NOT NULL,
   group_id  varchar(250)  NOT NULL,
   PRIMARY KEY (source_id) 
);

CREATE UNIQUE INDEX sender_to_group_idx ON notification_sources (sender_id,group_id);

--

INSERT INTO  notification_sources  ( source_id ,  name ,  sender_id ,  group_id ) VALUES
(1, 'Zprávy od Helios pro PKS Vysocina', 'HELIOS_VOTING_MODULE', 'deadbeef-babe-f002-000000000112'),
(2, 'Zprávy Forum pro PKS Vysocina', 'FORUM_PHPBB_NEWTHREAD_MODULE', 'deadbeef-babe-f002-000000000112'),
(3, 'Zprávy Piratsky Kalendar pro PKS Vysocina', 'EVENT_SCHEDULER_PICALENDAR', 'deadbeef-babe-f002-000000000112'),
(4, 'Zpravy Helios pro KS Vysocina', 'HELIOS_VOTING_MODULE', 'deadbeef-babe-f002-000000000037'),
(5, 'Zpravy Forum pro KS Vysocina', 'FORUM_PHPBB_NEWTHREAD_MODULE', 'deadbeef-babe-f002-000000000037');


CREATE TABLE  notification_types  (
   type_id  varchar(50)  NOT NULL,
   script  varchar(250)  NOT NULL,
   name  varchar(250)  NOT NULL,
   PRIMARY KEY (type_id) 
);

INSERT INTO  notification_types  ( type_id ,  script ,  name ) VALUES
('email', '/usr/local/pinotice/pinotice_send_mail.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'Email zpráva'),
('fb', '/usr/local/pinotice/pinotice_send_FB.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'zprava na Facebook '),
('signal', '/usr/local/pinotice/pinotice_send_SIGNAL.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'SIGNAL zprava'),
('sms', '/usr/local/pinotice/pinotice_send_SMS.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'SMS zpráva'),
('zulip', '/usr/local/pinotice/pinotice_send_ZULIP.sh \"%email\" \"%from\" \"%to\" \"%subject\" \"%text\"', 'Zulip oznámení');


CREATE TABLE  pirates  (
   pirate_id  varchar(250)  NOT NULL,
   name  varchar(250)  NOT NULL,
   comment  text  NOT NULL,
   email  varchar(250)  NOT NULL,
   PRIMARY KEY (pirate_id) 
);


CREATE TABLE  pirate_in_group  (
   pirate_id  varchar(250)  NOT NULL,
   group_id  varchar(250)  NOT NULL,
   PRIMARY KEY (pirate_id,group_id) 
);



CREATE TABLE  pirate_receives  (
   destination_id  int NOT NULL,
   source_id  int NOT NULL,
   subject_pattern  varchar(250)  NOT NULL
);

CREATE INDEX src_idx ON pirate_receives (source_id);
CREATE INDEX dst_idx ON pirate_receives (destination_id);


CREATE TABLE  target_groups  (
   group_id  varchar(250)  NOT NULL,
   email_alias  varchar(250)  NOT NULL,
   name  text  NOT NULL,
   about  text  NOT NULL,
   PRIMARY KEY (group_id)    
);