-- Adds S3 Regions
DROP TABLE IF EXISTS s3_regions;
CREATE TABLE s3_regions 
(
	id serial primary key,
	system_name varchar(64),
	display_name varchar(128),
	created_on timestamp DEFAULT CURRENT_TIMESTAMP,
	created_by int
);

INSERT INTO s3_regions (system_name, display_name) VALUES 
	('eu-west-2', 'Europe (London)'),
	('us-east-2', 'US East (Ohio)'),
	('ap-southeast-2', 'Asia Pacific (Sydney)'),
	('ca-central-1', 'Canada (Central)')
;

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

DROP VIEW IF EXISTS vw_grid_s3_regions;
CREATE VIEW vw_grid_s3_regions AS 
SELECT 
	a.id as row_id, 
	a.system_name as system_name,
    a.display_name as display_name,
	a.created_on,
	COALESCE(CONCAT(b.first_name, ' ', b.last_name), '(System)') as created_by,
	CONCAT('<a class="open-detail" title="S3 Region Details" data-type="s3_region" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM s3_regions a
LEFT JOIN users b 
	ON a.created_by = b.id
ORDER BY 
	a.system_name
;

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