-- Add System Name column. Create nice "Display Name" values.
ALTER TABLE services ADD COLUMN system_name varchar(64) AFTER name;
UPDATE services SET system_name = name;
UPDATE services SET name = CONCAT(UCASE(LEFT(name, 1)), SUBSTRING(name, 2));
UPDATE services SET name = 'Email Validation' WHERE name = 'Validation';

-- Create services grid as spec'd
DROP VIEW IF EXISTS vw_grid_services;
CREATE VIEW vw_grid_services AS 
SELECT 
	a.id as row_id, 
	a.name as display_name,
	a.system_name as system_name,
	a.created_on,
	CONCAT(b.first_name, ' ', b.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Service Details" data-type="service" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM services a
LEFT JOIN users b 
	ON a.created_by = b.id
GROUP BY
	a.id
ORDER BY 
	a.id
;

-- Structure the service columns
DELETE FROM grid_columns WHERE grid_id = 2;
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	(2, 1, 'row_id', 'Service ID', 'number', 1, 1, 0),
	(2, 2, 'display_name', 'Display Name', 'text', 1, 1, 0),
	(2, 3, 'system_name', ' System Name', 'text', 1, 1, 0),
	(2, 4, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	(2, 5, 'created_by', 'Created By', 'text', 1, 1, 0),
	(2, 6, 'actions', 'Actions', 'text', 1, 1, 0)
;

-- This just updates the is_global field to show "Global" vs "Service" instead of Yes / No
DROP VIEW IF EXISTS vw_grid_parameters;
CREATE VIEW vw_grid_parameters AS 
SELECT 
	a.id as row_id, 
	a.name as name,
    a.description as description,
	group_concat(distinct c.name order by c.name separator ', ') as services,
	case when a.is_global then 'Global' else 'Service' end as is_global,
	a.created_on,
	group_concat(distinct f.name order by f.name separator ', ') as ils,
	CONCAT(d.first_name, ' ', d.last_name) as created_by,
	CONCAT('<a class="open-detail" title="Parameter Details" data-type="parameter" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM parameters a
LEFT JOIN parameters_to_services b
	ON a.id = b.parameter_id
LEFT JOIN services c 
	ON b.service_id = c.id
LEFT JOIN users d 
	ON a.created_by = d.id
LEFT JOIN ils_to_parameters e 
	ON a.id = e.parameter_id 
LEFT JOIN ils f
	ON e.ils_id = f.id
GROUP BY
	a.id
ORDER BY 
	a.name
;

-- Change the display name to "Scope" in Parameter table
UPDATE grid_columns SET display_name = 'Scope' WHERE field = 'is_global' and grid_id = 3;

-- Add two new columns
ALTER TABLE parameters ADD COLUMN is_json boolean DEFAULT false AFTER data_type;
ALTER TABLE parameters ADD COLUMN implementation_notes text AFTER created_on;