DROP TABLE IF EXISTS campaigns;
CREATE TABLE campaigns 
(
	id serial primary key,
	name varchar(128),
	description text,
	is_published boolean,
	allow_restart boolean,
	publish_up timestamp null default null,
	publish_down timestamp null default null,
	events text,
	lists text,
	canvas_settings text,
	created_on timestamp DEFAULT CURRENT_TIMESTAMP,
	created_by int
);

DROP TABLE IF EXISTS campaigns_to_services;
CREATE TABLE campaigns_to_services 
(
    id serial primary key,
    campaign_id int,
    service_id int
);

DELETE FROM grids WHERE name = 'campaigns';
INSERT INTO grids (name) VALUES 
	('campaigns')
;

DROP VIEW IF EXISTS vw_grid_campaigns;
CREATE VIEW vw_grid_campaigns AS 
SELECT 
	a.id as row_id,
	a.name as name,
	case when a.allow_restart then 'Yes' else 'No' end as allow_restart,
	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="Campaign Details" data-type="campaign" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM campaigns a 
LEFT JOIN users b
	ON a.created_by = b.id
LEFT JOIN campaigns_to_services c ON a.id = c.campaign_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 = 'campaigns');
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	((select id from grids where name = 'campaigns'), 1, 'row_id', 'Campaign ID', 'number', 1, 1, 0),
	((select id from grids where name = 'campaigns'), 2, 'name', 'Campaign Name', 'text', 1, 1, 0),
	((select id from grids where name = 'campaigns'), 3, 'allow_restart', 'Allow Restart?', 'text', 1, 1, 0),
	((select id from grids where name = 'campaigns'), 4, 'services', 'Services', 'text', 1, 1, 0),
	((select id from grids where name = 'campaigns'), 5, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	((select id from grids where name = 'campaigns'), 6, 'created_by', 'Created By', 'text', 1, 1, 0),
	((select id from grids where name = 'campaigns'), 7, 'actions', 'Actions', 'text', 1, 1, 0)
;