-- New options fields
ALTER TABLE customers ADD COLUMN patron_type_options json AFTER ils_id;
ALTER TABLE customers ADD COLUMN home_library_options json AFTER patron_type_options;
ALTER TABLE custom_fields ADD COLUMN special_logic varchar(64);

-- Email variables
ALTER TABLE customers ADD COLUMN email_variable_values json AFTER home_library_options;
ALTER TABLE customers ADD COLUMN social_media_values json AFTER email_variable_values;

-- Create the emails we want
DELETE FROM emails;
INSERT INTO emails (name) VALUES 
    ('Welcome-1_General'),
    ('Welcome-2_Learn'),
    ('Welcome-3_Connect'),
    ('Welcome-4_KidsAndParents'),
    ('VerifyRegister_Confirmation'),
    ('VerifyRegister_AlreadyHasCard'),
    ('VerifyRegister_NextSteps'),
    ('Renew30Before'),
    ('Renew7Before'),
    ('Renew7After'),
    ('Renew30After'),
    ('RisingAdult'),
    ('Recommends'),
    ('Birthday'),
    ('LibraryCardAnniversary')
;

DROP TABLE IF EXISTS email_variables;
CREATE TABLE email_variables
(
    id serial primary key,
    name varchar(64),
    display_name varchar(64),
    description text,
    is_global boolean default false,
    email_id int,
    created_on timestamp DEFAULT CURRENT_TIMESTAMP,
    created_by int
);

INSERT INTO email_variables (display_name, name, is_global, email_id, description) VALUES 
    ('Customer URL', 'customer_url', 1, null, 'The URL the user is taken to upon clicking the buttons, images, and links in the email'),
    ('Logo Top Alt Text', 'logo_top_alt_text', 1, null, 'Alt text attached to Customer''s logo image'),
    ('Logo Top Image Source', 'logo_top_image_src', 1, null, 'The source URL for the Customer''s logo image'),
    ('Customer URL Button Text', 'customer_url_button_text', 1, null, 'Text on custom button that appears at the bottom of some emails'),
    ('Email Theme Name', 'theme_name', 1, null, 'The name/identifier of the email theme in Mautic, e.g. wcplin_customallinone.'),
    ('Footer Company Name', 'footer_company_name', 1, null, 'The name of the Customer''s company as it will appear in the footer'),
    ('Footer Address (Part 1)', 'footer_company_address_1', 1, null, 'The Customer''s street address'),
    ('Footer Address (Part 2)', 'footer_company_address_2', 1, null, 'The Customer''s city, state, and ZIP code'),
    ('Footer Address (Part 2)', 'footer_company_address_2', 1, null, 'The Customer''s city, state, and ZIP code'),
    ('Footer Phone Number', 'footer_company_phone', 1, null, 'The Customer''s phone number as it will appear in the footer'),

    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-1_General'), 'The source URL for image #1 in the email body'),
    ('Body Image #2 Source', 'body_image_2_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-1_General'), 'The source URL for image #2 in the email body'),
    ('Body Image #3 Source', 'body_image_3_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-1_General'), 'The source URL for image #3 in the email body'),

    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-2_Learn'), 'The source URL for image #1 in the email body'),
    ('Body Image #2 Source', 'body_image_2_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-2_Learn'), 'The source URL for image #2 in the email body'),
    ('Body Image #3 Source', 'body_image_3_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-2_Learn'), 'The source URL for image #3 in the email body'),

    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-3_Connect'), 'The source URL for image #1 in the email body'),
    ('Body Image #2 Source', 'body_image_2_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-3_Connect'), 'The source URL for image #2 in the email body'),
    ('Body Image #3 Source', 'body_image_3_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-3_Connect'), 'The source URL for image #3 in the email body'),

    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-4_KidsAndParents'), 'The source URL for image #1 in the email body'),
    ('Body Image #2 Source', 'body_image_2_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-4_KidsAndParents'), 'The source URL for image #2 in the email body'),
    ('Body Image #3 Source', 'body_image_3_src', 0, (SELECT id FROM emails WHERE name = 'Welcome-4_KidsAndParents'), 'The source URL for image #3 in the email body'),

    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'VerifyRegister_Confirmation'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'VerifyRegister_AlreadyHasCard'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'VerifyRegister_NextSteps'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Renew30Before'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Renew7Before'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Renew7After'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Renew30After'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'RisingAdult'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Recommends'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'Birthday'), 'The source URL for image #1 in the email body'),
    ('Body Image #1 Source', 'body_image_1_src', 0, (SELECT id FROM emails WHERE name = 'LibraryCardAnniversary'), 'The source URL for image #1 in the email body')
;

DROP VIEW IF EXISTS vw_grid_email_variables;
CREATE VIEW vw_grid_email_variables AS 
SELECT 
	a.id as row_id,
	a.name as name,
	a.display_name as display_name,
	a.description as description,
    case when a.is_global then 'Yes' else 'No' end as is_global,
	case when a.is_global then '(Global)' else c.name end as parent_email,
	case when a.is_global then 0 else c.id end as parent_email_id,
	a.created_on,
	CONCAT(b.first_name, ' ', b.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Email Variable Details" data-type="email_variable" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM email_variables a 
LEFT JOIN users b
	ON a.created_by = b.id
LEFT JOIN emails c ON a.email_id = c.id
GROUP BY 
	a.id
ORDER BY
	a.name
;

INSERT INTO grids (name) VALUES 
	('email_variables')
;

DELETE FROM grid_columns WHERE grid_id IN (select id from grids where name = 'email_variables');
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	((select id from grids where name = 'email_variables'), 1, 'row_id', 'ID', 'number', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 2, 'display_name', 'Display Name', 'text', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 3, 'name', 'System Name', 'text', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 4, 'description', 'Description', 'text', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 5, 'is_global', 'Global?', 'text', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 6, 'parent_email', 'Parent Email', 'text', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 7, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 8, 'created_by', 'Created By', 'text', 1, 1, 0),
	((select id from grids where name = 'email_variables'), 9, 'actions', 'Actions', 'text', 1, 1, 0)
;


-- Social media platforms
DROP TABLE IF EXISTS social_media_platforms;
CREATE TABLE social_media_platforms
(
    id serial primary key,
    name varchar(64),
    icon_file_name varchar(255),
    created_on timestamp DEFAULT CURRENT_TIMESTAMP,
    created_by int
);

DROP TABLE IF EXISTS customers_to_social_media_platforms;
CREATE TABLE customers_to_social_media_platforms
(
    id serial primary key,
    customer_id int,
    social_media_platform_id int,
    profile_url varchar(255)
);

INSERT INTO social_media_platforms (name, icon_file_name) VALUES 
	('Apple App Store', 'Apple-App-Store.png'),
	('Beanstack', 'Beanstack.png'),
	('Bigspot', 'Bigspot.png'),
	('Discord', 'Discord.png'),
	('Facebook', 'Facebook.png'),
	('Flickr', 'Flickr.png'),
	('Foursquare', 'Foursquare.png'),
	('Goodreads', 'Goodreads.png'),
	('Google Photos', 'Google-Photos.png'),
	('Google Play Store', 'Google-Play-Store.png'),
	('Instagram', 'Instagram.png'),
	('LinkedIn', 'LinkedIn.png'),
	('Meetup', 'Meetup.png'),
	('Mobile-Download', 'Mobile-Download.png'),
	('Newsletter-Signup', 'Newsletter-Signup.png'),
	('Pinterest', 'Pinterest.png'),
	('PodcastLocal', 'PodcastLocal.png'),
	('Snapchat', 'Snapchat.png'),
	('Spotify', 'Spotify.png'),
	('TikTok', 'TikTok.png'),
	('Tumblr', 'Tumblr.png'),
	('Twitter', 'Twitter.png'),
	('WhatsApp', 'WhatsApp.png'),
	('Windows App Store', 'Windows-App-Store.png'),
	('YouTube', 'YouTube.png')
;

-- Add detail management icon
DROP VIEW IF EXISTS vw_grid_customers;
CREATE VIEW vw_grid_customers AS 
SELECT 
	a.id as row_id, 
	a.name as name,
    a.site_code as site_code,
	e.name as ils,
	group_concat(distinct c.name order by c.name separator ', ') as services,
	a.created_on,
	CONCAT(d.first_name, ' ', d.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Customer Details" data-type="customer" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a><a target="_blank" href="/customers/manage/', a.site_code, '"><img src="/images/new-tab.png" style="width: 18px; height: 18px; margin-left: 6px;" class="grid-icon-image"></a>') as actions
FROM customers a
LEFT JOIN customers_to_services b
	ON a.id = b.customer_id
LEFT JOIN services c 
	ON b.service_id = c.id
LEFT JOIN users d 
	ON a.created_by = d.id
LEFT JOIN ils e 
	ON a.ils_id = e.id
GROUP BY
	a.id
ORDER BY 
	a.name
;