-- V3 DEPLOYMENT
-- 1. Run composer require mautic/api-library in prod
-- 2. Run these queries
-- 3. Run code update


-- Create default_value column
ALTER TABLE parameters ADD COLUMN default_value text AFTER implementation_notes;

-- Created Lookup URL column
ALTER TABLE ils ADD COLUMN lookup_url text AFTER name;

CREATE TABLE emails 
(
	id serial primary key,
	name varchar(64),
	subject text,
	from_address text,
	from_name text,
	reply_to_address text,
	bcc_address text,
	is_published boolean,
	publish_up timestamp,
	publish_down timestamp,
	language text,
	read_count int,
	sent_count int,
	revision int,
	custom_html text,
	plain_text text,
	template text,
	email_type text,
	translation_children text,
	translation_parent text,
	variant_sent_count int,
	variant_read_count int,
	variant_children text,
	variant_parent text,
	variant_settings text,
	variant_start_date timestamp,
	category text,
	unsubscribe_form int,
	dynamic_content text,
	lists text,
	created_on timestamp DEFAULT CURRENT_TIMESTAMP,
	created_by int
);

CREATE TABLE custom_fields
(
	id serial primary key,
	label text,
	alias text,
	description text,
	type text,
	`group` text,
	`order` int,
	object text,
	default_value text,
	is_required boolean,
	is_publicly_updatable boolean,
	is_unique_identifier boolean,
	properties text,
	created_on timestamp DEFAULT CURRENT_TIMESTAMP,
	created_by int
);

CREATE TABLE forms
(
	id serial primary key,
	name text,
	description text,
	alias text,
	is_published boolean,
	publish_up timestamp,
	publish_down timestamp,
	cached_html text,
	template text,
	fields text,
	actions text,
	created_on timestamp DEFAULT CURRENT_TIMESTAMP,
	created_by int
);

CREATE TABLE forms_to_services 
(
    id serial primary key,
    form_id int,
    service_id int
);

CREATE TABLE custom_fields_to_services 
(
    id serial primary key,
    custom_field_id int,
    service_id int
);

CREATE TABLE emails_to_services 
(
    id serial primary key,
    email_id int,
    service_id int
);

CREATE TABLE segments_to_services 
(
    id serial primary key,
    segment_id int,
    service_id int
);

CREATE TABLE segments
(
	id serial primary key,
	name text,
	alias text,
	description text,
	is_published boolean,
	is_global boolean,
	filters text,
	created_on timestamp DEFAULT CURRENT_TIMESTAMP,
	created_by int
);


INSERT INTO grids (name) VALUES 
	('ils'),
	('emails'),
	('custom_fields'),
	('forms'),
    ('segments')
;

DROP VIEW IF EXISTS vw_grid_ils;
CREATE VIEW vw_grid_ils AS 
SELECT 
	a.id as row_id, 
	a.name as name,
    a.lookup_url as lookup_url,
	a.created_on,
	CONCAT(b.first_name, ' ', b.last_name) as created_by,
	CONCAT('<a class="open-detail" title="ILS Details" data-type="ils" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM ils a
LEFT JOIN users b 
	ON a.created_by = b.id
ORDER BY 
	a.name
;

DROP VIEW IF EXISTS vw_grid_emails;
CREATE VIEW vw_grid_emails AS 
SELECT 
	a.id as row_id,
	a.name as name,
	a.subject as subject,
	group_concat(d.name separator ', ') as services,
	a.created_on,
	CONCAT(b.first_name, ' ', b.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Email Details" data-type="email" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM emails a 
LEFT JOIN users b
	ON a.created_by = b.id
LEFT JOIN emails_to_services c ON a.id = c.email_id
LEFT JOIN services d ON c.service_id = d.id
GROUP BY 
	a.id
ORDER BY
	a.name
;
	
DROP VIEW IF EXISTS vw_grid_custom_fields;
CREATE VIEW vw_grid_custom_fields AS 
SELECT 
	a.id as row_id,
	a.label as label,
	a.alias as alias,
	group_concat(d.name separator ', ') as services,
	a.created_on,
	CONCAT(b.first_name, ' ', b.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Custom Field Details" data-type="custom_field" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM custom_fields a 
LEFT JOIN users b
	ON a.created_by = b.id
LEFT JOIN custom_fields_to_services c ON a.id = c.custom_field_id
LEFT JOIN services d ON c.service_id = d.id
GROUP BY 
	a.id
ORDER BY
	a.label
;

DROP VIEW IF EXISTS vw_grid_forms;
CREATE VIEW vw_grid_forms AS 
SELECT 
	a.id as row_id,
	a.name as name,
	a.alias as alias,
	group_concat(d.name separator ', ') as services,
	a.created_on,
	CONCAT(b.first_name, ' ', b.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Form Details" data-type="form" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM forms a 
LEFT JOIN users b
	ON a.created_by = b.id
LEFT JOIN forms_to_services c ON a.id = c.form_id
LEFT JOIN services d ON c.service_id = d.id
GROUP BY 
	a.id
ORDER BY
	a.name
;

DROP VIEW IF EXISTS vw_grid_segments;
CREATE VIEW vw_grid_segments AS 
SELECT 
	a.id as row_id,
	a.name as name,
	a.alias as alias,
	group_concat(d.name separator ', ') as services,
	a.created_on,
	CONCAT(b.first_name, ' ', b.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Segment Details" data-type="segment" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM segments a 
LEFT JOIN users b
	ON a.created_by = b.id
LEFT JOIN segments_to_services c ON a.id = c.segment_id
LEFT JOIN services d ON c.service_id = d.id
GROUP BY 
	a.id
ORDER BY
	a.name
;

DELETE FROM grid_columns WHERE grid_id IN (select id from grids where name = 'ils');
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	((select id from grids where name = 'ils'), 1, 'row_id', 'ILS ID', 'number', 1, 1, 0),
	((select id from grids where name = 'ils'), 2, 'name', 'ILS Name', 'text', 1, 1, 0),
	((select id from grids where name = 'ils'), 3, 'lookup_url', 'Lookup URL', 'text', 1, 1, 0),
	((select id from grids where name = 'ils'), 4, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	((select id from grids where name = 'ils'), 5, 'created_by', 'Created By', 'text', 1, 1, 0),
	((select id from grids where name = 'ils'), 6, 'actions', 'Actions', 'text', 1, 1, 0)
;

DELETE FROM grid_columns WHERE grid_id IN (select id from grids where name = 'emails');
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	((select id from grids where name = 'emails'), 1, 'row_id', 'ID', 'number', 1, 1, 0),
	((select id from grids where name = 'emails'), 2, 'name', 'Name', 'text', 1, 1, 0),
	((select id from grids where name = 'emails'), 3, 'subject', 'Subject', 'text', 1, 1, 0),
	((select id from grids where name = 'emails'), 4, 'services', 'Services', 'text', 1, 1, 0),
	((select id from grids where name = 'emails'), 5, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	((select id from grids where name = 'emails'), 6, 'created_by', 'Created By', 'text', 1, 1, 0),
	((select id from grids where name = 'emails'), 7, 'actions', 'Actions', 'text', 1, 1, 0)
;

DELETE FROM grid_columns WHERE grid_id IN (select id from grids where name = 'custom_fields');
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	((select id from grids where name = 'custom_fields'), 1, 'row_id', 'ID', 'number', 1, 1, 0),
	((select id from grids where name = 'custom_fields'), 2, 'label', 'Name', 'text', 1, 1, 0),
	((select id from grids where name = 'custom_fields'), 3, 'alias', 'Alias', 'text', 1, 1, 0),
	((select id from grids where name = 'custom_fields'), 4, 'services', 'Services', 'text', 1, 1, 0),
	((select id from grids where name = 'custom_fields'), 5, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	((select id from grids where name = 'custom_fields'), 6, 'created_by', 'Created By', 'text', 1, 1, 0),
	((select id from grids where name = 'custom_fields'), 7, 'actions', 'Actions', 'text', 1, 1, 0)
;

DELETE FROM grid_columns WHERE grid_id IN (select id from grids where name = 'forms');
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	((select id from grids where name = 'forms'), 1, 'row_id', 'ID', 'number', 1, 1, 0),
	((select id from grids where name = 'forms'), 2, 'name', 'Name', 'text', 1, 1, 0),
	((select id from grids where name = 'forms'), 3, 'alias', 'Alias', 'text', 1, 1, 0),
	((select id from grids where name = 'forms'), 4, 'services', 'Services', 'text', 1, 1, 0),
	((select id from grids where name = 'forms'), 5, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	((select id from grids where name = 'forms'), 6, 'created_by', 'Created By', 'text', 1, 1, 0),
	((select id from grids where name = 'forms'), 7, 'actions', 'Actions', 'text', 1, 1, 0)
;

DELETE FROM grid_columns WHERE grid_id IN (select id from grids where name = 'segments');
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	((select id from grids where name = 'segments'), 1, 'row_id', 'ID', 'number', 1, 1, 0),
	((select id from grids where name = 'segments'), 2, 'name', 'Name', 'text', 1, 1, 0),
	((select id from grids where name = 'segments'), 3, 'alias', 'Alias', 'text', 1, 1, 0),
	((select id from grids where name = 'segments'), 4, 'services', 'Services', 'text', 1, 1, 0),
	((select id from grids where name = 'segments'), 5, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	((select id from grids where name = 'segments'), 6, 'created_by', 'Created By', 'text', 1, 1, 0),
	((select id from grids where name = 'segments'), 7, 'actions', 'Actions', 'text', 1, 1, 0)
;

-- Set all the endpoints 
UPDATE ils SET lookup_url = concat('https://6l0gq7uf04.execute-api.us-east-2.amazonaws.com/Prod/fetch/ils/', lower(name), '/client') WHERE 1=1;