

CREATE TABLE users 
(
	id serial PRIMARY KEY,
	username varchar(255) UNIQUE,
	password varchar(512),
	first_name varchar(64),
	last_name varchar(64),
	active boolean default true,
	user_type_id int not null,
	created_by int default 0,
	created_on timestamp DEFAULT CURRENT_TIMESTAMP,
	last_logged_in timestamp
);

CREATE TABLE user_types 
(
	id serial PRIMARY KEY,
	name varchar(255) UNIQUE,
	display_text varchar(512),
	description text,
	created_on timestamp DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO user_types (name, display_text, description) VALUES
	('admin', 'Administrator', 'Administrator of the platform'),
	('user', 'User', 'Basic user of the platform')
;

-- Password "admin"
INSERT INTO users (username, password, user_type_id, first_name, last_name) VALUES 
	('ted@patronpoint.com', '$2y$12$DmrWOshatkBzier7RigThOi3pE9FKJePLnwq5UnhDsMErKhKA2weW', 1, 'Ted', 'Fons'),
	('349000@gmail.com', '$2y$12$DmrWOshatkBzier7RigThOi3pE9FKJePLnwq5UnhDsMErKhKA2weW', 1, 'Steven', 'Hinkle')
;

CREATE TABLE grids 
(
	id serial primary key,
	name varchar(64)
);

INSERT INTO grids (name) VALUES 
	('customers'),
	('services'),
	('parameters'),
	('users')
;

CREATE TABLE ils 
(
	id serial primary key,
	name varchar(64),
	created_on timestamp default current_timestamp,
	created_by int
);

INSERT INTO ils (name, created_by) VALUES
	('Apollo', 1),
	('Atriuum', 1),
	('Carl.X', 1),
	('ESS', 1),
	('Evergreen', 1),
	('FOLIO', 1),
	('Horizon', 1),
	('Infor', 1),
	('Koha', 1),
	('Library.Solution', 1),
	('Polaris', 1),
	('Sierra', 1),
	('Spydus', 1),
	('Symphony', 1),
	('Verso', 1)
;

CREATE TABLE customers 
(
	id serial primary key,
	name varchar(64),
	site_code varchar(64),
	ils_id int,
	created_on timestamp default current_timestamp,
	created_by int
);

INSERT INTO customers (created_by, name, ils_id, site_code) VALUES
	(1, 'Adrian District Library',(SELECT id FROM ils WHERE name = 'Atriuum'),'adlmi'),
	(1, 'Ajax Public Library',(SELECT id FROM ils WHERE name = 'Koha'),'aplon'),
	(1, 'Alachua County Library District',(SELECT id FROM ils WHERE name = 'Polaris'),'acldfl'),
	(1, 'Alvin Sherman Library',(SELECT id FROM ils WHERE name = 'Sierra'),'aslfl'),
	(1, 'Anaheim Public Library',(SELECT id FROM ils WHERE name = 'Horizon'),'apl'),
	(1, 'Anderson County Library System',(SELECT id FROM ils WHERE name = 'Evergreen'),'aclssc'),
	(1, 'Arlington Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'apltx'),
	(1, 'Auburn Hills Public Library',(SELECT id FROM ils WHERE name = 'Carl.X'),'ahplmi'),
	(1, 'Aurora Public Library District',(SELECT id FROM ils WHERE name = 'Polaris'),'apldil'),
	(1, 'Barrie Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'baplon'),
	(1, 'Barrington Area Library',(SELECT id FROM ils WHERE name = 'Polaris'),'balil'),
	(1, 'Bayport-Blue Point Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'bbpplny'),
	(1, 'Bellwood Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'bplil'),
	(1, 'Benbrook Public Library',(SELECT id FROM ils WHERE name = 'Koha'),'bpltx'),
	(1, 'Blount County Public Library',(SELECT id FROM ils WHERE name = 'Atriuum'),'bcpltn'),
	(1, 'Boise Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'bplid'),
	(1, 'Boone County Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'bocplky'),
	(1, 'Bossier Parish Libraries',(SELECT id FROM ils WHERE name = 'Koha'),'bplla'),
	(1, 'Brazoria County Library System',(SELECT id FROM ils WHERE name = 'Polaris'),'bclstx'),
	(1, 'Brooklyn Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'bplny'),
	(1, 'Broward County Library',(SELECT id FROM ils WHERE name = 'Symphony'),'bclfl'),
	(1, 'Brown County Library',(SELECT id FROM ils WHERE name = 'Symphony'),'bclwi'),
	(1, 'Buckeye Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'bplaz'),
	(1, 'Bullitt County Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'bcplky'),
	(1, 'Burlington Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'bplon'),
	(1, 'Burwood Council Library Service',(SELECT id FROM ils WHERE name = 'Spydus'),'blnsw'),
	(1, 'Calcasieu Parish Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'cpplla'),
	(1, 'Capital Area District Libraries',(SELECT id FROM ils WHERE name = 'Sierra'),'cadl'),
	(1, 'Carrollton Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'cpltx'),
	(1, 'Cass District Library',(SELECT id FROM ils WHERE name = 'Horizon'),'cdlmi'),
	(1, 'Cedar Rapids Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'crplia'),
	(1, 'Cedarburg Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'cplwi'),
	(1, 'Champaign Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'cplil'),
	(1, 'Chatham-Kent Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'ckplon'),
	(1, 'Chattanooga Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'cpltn'),
	(1, 'Chemeketa Cooperative Regional Library Service',(SELECT id FROM ils WHERE name = 'Symphony'),'ccrls'),
	(1, 'Cincinnati and Hamilton County Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'chcoh'),
	(1, 'Clarksville-Montgomery County Public Library',(SELECT id FROM ils WHERE name = 'Library.Solution'),'cmcpl'),
	(1, 'Clearview Library District',(SELECT id FROM ils WHERE name = 'Polaris'),'cldco'),
	(1, 'Clermont County Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'ccploh'),
	(1, 'Cleveland Law Library',(SELECT id FROM ils WHERE name = 'Symphony'),'clev-cll'),
	(1, 'CLEVNET',(SELECT id FROM ils WHERE name = 'Symphony'),'clevnet'),
	(1, 'Cockburn Libraries',(SELECT id FROM ils WHERE name = 'Spydus'),'clwa'),
	(1, 'Columbus Metropolitan Library',(SELECT id FROM ils WHERE name = 'Polaris'),'cmloh'),
	(1, 'Comsewogue Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'cplny'),
	(1, 'Cuyahoga County Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'cuyaoh'),
	(1, 'Danbury Library',(SELECT id FROM ils WHERE name = 'Sierra'),'dlct'),
	(1, 'Danville Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'danplil'),
	(1, 'Dauphin County Library System',(SELECT id FROM ils WHERE name = 'Sierra'),'dcls'),
	(1, 'Davis County Library',(SELECT id FROM ils WHERE name = 'Polaris'),'davisut'),
	(1, 'Decatur Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'dplil'),
	(1, 'Delaware County District Library',(SELECT id FROM ils WHERE name = 'Polaris'),'dcdl'),
	(1, 'Dothan Houston County Library System',(SELECT id FROM ils WHERE name = 'Koha'),'dhcal'),
	(1, 'Douglas County Libraries',(SELECT id FROM ils WHERE name = 'Horizon'),'dclco'),
	(1, 'Eagle Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'eplid'),
	(1, 'East Lansing Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'elplmi'),
	(1, 'Emma S. Clark Memorial Library',(SELECT id FROM ils WHERE name = 'Sierra'),'ecmlny'),
	(1, 'Fairfield City Council Libraries',(SELECT id FROM ils WHERE name = 'Spydus'),'fcnsw'),
	(1, 'Fairport Harbor Public Library',(SELECT id FROM ils WHERE name = ''),'clev-fh'),
	(1, 'Fountaindale Public Library District',(SELECT id FROM ils WHERE name = 'Polaris'),'fpldil'),
	(1, 'Frederick County Public Libraries',(SELECT id FROM ils WHERE name = 'Carl.X'),'fcplmd'),
	(1, 'Genesee District Library',(SELECT id FROM ils WHERE name = 'Sierra'),'gdlmi'),
	(1, 'Grapevine Public Library',(SELECT id FROM ils WHERE name = 'Koha'),'gpltx'),
	(1, 'Greenwich Leisure Limited',(SELECT id FROM ils WHERE name = 'ESS'),'glluk'),
	(1, 'Greenwood Public Library',(SELECT id FROM ils WHERE name = 'Evergreen'),'gplin'),
	(1, 'Hamilton City Libraries',(SELECT id FROM ils WHERE name = 'Symphony'),'hclnz'),
	(1, 'Harford County Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'hcmd'),
	(1, 'Hauppauge Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'hplny'),
	(1, 'Helen Plum Library',(SELECT id FROM ils WHERE name = 'Sierra'),'hplil'),
	(1, 'Henderson Libraries',(SELECT id FROM ils WHERE name = 'Polaris'),'hlnv'),
	(1, 'Hoboken Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'hplnj'),
	(1, 'Hosting Solutions & Library Consulting',(SELECT id FROM ils WHERE name = 'Verso'),'powerlib'),
	(1, 'Indianapolis-Marion County Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'iplin'),
	(1, 'Jackson County Library Services',(SELECT id FROM ils WHERE name = 'Koha'),'jclsor'),
	(1, 'Jefferson Township Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'jtplnj'),
	(1, 'Jennings County Public Library',(SELECT id FROM ils WHERE name = 'Evergreen'),'jcplin'),
	(1, 'Jericho Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'jplny'),
	(1, 'Jersey City Free Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'jcfplnj'),
	(1, 'Jessamine County Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'jcplky'),
	(1, 'Johnson County Library',(SELECT id FROM ils WHERE name = 'Symphony'),'jclks'),
	(1, 'Kent District Library',(SELECT id FROM ils WHERE name = 'Symphony'),'kdlmi'),
	(1, 'Kershaw County Library',(SELECT id FROM ils WHERE name = 'Evergreen'),'kclsc'),
	(1, 'Kitchener Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'kpl'),
	(1, 'Kitsap Regional Library',(SELECT id FROM ils WHERE name = 'Polaris'),'krlwa'),
	(1, 'La Porte County Public Library',(SELECT id FROM ils WHERE name = 'Library.Solution'),'lpcplin'),
	(1, 'Lakeland Library Cooperative',(SELECT id FROM ils WHERE name = 'Sierra'),'llxmi'),
	(1, 'Lansing Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'lplil'),
	(1, 'Las Vegas-Clark County Library District',(SELECT id FROM ils WHERE name = 'Sierra'),'lvccld'),
	(1, 'Lehi City Library',(SELECT id FROM ils WHERE name = 'Symphony'),'lehiut'),
	(1, 'Lexington Public Library',(SELECT id FROM ils WHERE name = 'Carl.X'),'lplky'),
	(1, 'Libraries of Middlesex Automation Consortium',(SELECT id FROM ils WHERE name = 'Symphony'),'lmacnj'),
	(1, 'Lindenhurst Memorial Library',(SELECT id FROM ils WHERE name = 'Sierra'),'lmlny'),
	(1, 'Lorain Public Library System',(SELECT id FROM ils WHERE name = 'Symphony'),'clev-lor'),
	(1, 'Los Angeles Public Library',(SELECT id FROM ils WHERE name = 'Carl.X'),'laplca'),
	(1, 'Mansfield-Richland County Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'mrcpl'),
	(1, 'Maribyrnong Library Service',(SELECT id FROM ils WHERE name = 'Spydus'),'mlsvic'),
	(1, 'Mason Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'mploh'),
	(1, 'Memphis Public Libraries',(SELECT id FROM ils WHERE name = 'Symphony'),'mpltn'),
	(1, 'Middle Country Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'mcplny'),
	(1, 'MidPointe Library System',(SELECT id FROM ils WHERE name = 'Sierra'),'mplsoh'),
	(1, 'Milton Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'mplon'),
	(1, 'Milwaukee County Federated Library System',(SELECT id FROM ils WHERE name = 'Sierra'),'mcfwi'),
	(1, 'Moonee Valley Library System',(SELECT id FROM ils WHERE name = 'Symphony'),'mvlvic'),
	(1, 'Napa County Library',(SELECT id FROM ils WHERE name = 'Polaris'),'nclca'),
	(1, 'Naperville Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'nplil'),
	(1, 'Nashville Public Library',(SELECT id FROM ils WHERE name = 'Carl.X'),'npltn'),
	(1, 'Nassau Library System',(SELECT id FROM ils WHERE name = 'Sierra'),'nlsny'),
	(1, 'New Castle-Henry County Public Library',(SELECT id FROM ils WHERE name = 'Evergreen'),'nchcplin'),
	(1, 'North Las Vegas Library District',(SELECT id FROM ils WHERE name = 'Sierra'),'nlvldnv'),
	(1, 'Novi Public Library',(SELECT id FROM ils WHERE name = 'Carl.X'),'nplmi'),
	(1, 'O''Fallon Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'oplil'),
	(1, 'OC Public Libraries',(SELECT id FROM ils WHERE name = 'Symphony'),'ocplca'),
	(1, 'Okanagan Regional Library',(SELECT id FROM ils WHERE name = 'Polaris'),'orlbc'),
	(1, 'Omaha Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'oplne'),
	(1, 'Ottawa Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'oplon'),
	(1, 'Owen Sound & North Grey Union Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'osnguon'),
	(1, 'Palatine Public Library District',(SELECT id FROM ils WHERE name = 'Polaris'),'ppldil'),
	(1, 'Palmerston North City Library',(SELECT id FROM ils WHERE name = 'Symphony'),'pnclnz'),
	(1, 'Perry Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'clev-per'),
	(1, 'Peterborough Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'pplon'),
	(1, 'Poughkeepsie Public Library District',(SELECT id FROM ils WHERE name = 'Sierra'),'ppldny'),
	(1, 'Princeton Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'pplnj'),
	(1, 'Queenstown Lakes District Council',(SELECT id FROM ils WHERE name = 'Symphony'),'qldcnz'),
	(1, 'Quincy Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'qplil'),
	(1, 'Rapid City Public Library',(SELECT id FROM ils WHERE name = 'Koha'),'rcplsd'),
	(1, 'Regina Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'rplsk'),
	(1, 'Sacramento Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'splca'),
	(1, 'Salina Public Library',(SELECT id FROM ils WHERE name = 'Koha'),'splks'),
	(1, 'Saline County Library',(SELECT id FROM ils WHERE name = 'Symphony'),'sclar'),
	(1, 'Saline District Library',(SELECT id FROM ils WHERE name = 'Polaris'),'sdlmi'),
	(1, 'Santa Clara County Library District',(SELECT id FROM ils WHERE name = 'Horizon'),'sccld'),
	(1, 'Sarasota County Library',(SELECT id FROM ils WHERE name = 'Sierra'),'sclfl'),
	(1, 'Saskatoon Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'splsk'),
	(1, 'SEO (Serving Every Ohioan)',(SELECT id FROM ils WHERE name = 'Symphony'),'seooh'),
	(1, 'Sequoyah Regional Library System',(SELECT id FROM ils WHERE name = 'Library.Solution'),'srlsga'),
	(1, 'Shorewood-Troy Public Library District',(SELECT id FROM ils WHERE name = 'Polaris'),'stpldil'),
	(1, 'Sioux City Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'scplia'),
	(1, 'Siskiyou County Library System',(SELECT id FROM ils WHERE name = 'Koha'),'scplca'),
	(1, 'Skokie Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'splil'),
	(1, 'Sno-Isle Libraries',(SELECT id FROM ils WHERE name = 'Polaris'),'silwa'),
	(1, 'Spokane Public Library',(SELECT id FROM ils WHERE name = 'FOLIO'),'splwa'),
	(1, 'St. Albert Public Library',(SELECT id FROM ils WHERE name = 'Horizon'),'saplab'),
	(1, 'St. Joe County Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'sjcin'),
	(1, 'St. Tammany Parish Library',(SELECT id FROM ils WHERE name = 'Polaris'),'stplla'),
	(1, 'Suffolk Libraries',(SELECT id FROM ils WHERE name = 'Spydus'),'slsfk'),
	(1, 'Syosset Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'splny'),
	(1, 'The Ferguson Library',(SELECT id FROM ils WHERE name = 'Sierra'),'ferguson'),
	(1, 'Thunder Bay Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'tbplon'),
	(1, 'Tinley Park Public Library',(SELECT id FROM ils WHERE name = 'Symphony'),'tpplil'),
	(1, 'Toowoomba Region Libraries',(SELECT id FROM ils WHERE name = 'Spydus'),'trlqld'),
	(1, 'Troy Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'tplmi'),
	(1, 'Tulsa City-County Library',(SELECT id FROM ils WHERE name = 'Sierra'),'tcclok'),
	(1, 'Victoria Public Library',(SELECT id FROM ils WHERE name = 'Apollo'),'vpltx'),
	(1, 'Volusia County Public Library',(SELECT id FROM ils WHERE name = 'Infor'),'vcfl'),
	(1, 'Warwick Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'wplri'),
	(1, 'Waterloo Public Library',(SELECT id FROM ils WHERE name = 'Sierra'),'wpl'),
	(1, 'Weber County Library System',(SELECT id FROM ils WHERE name = 'Sierra'),'wclsut'),
	(1, 'Wellington City Libraries',(SELECT id FROM ils WHERE name = 'Carl.X'),'wclnz'),
	(1, 'West Gippsland Regional Library Corporation',(SELECT id FROM ils WHERE name = 'Symphony'),'wgrlvic'),
	(1, 'West Vancouver Memorial Library',(SELECT id FROM ils WHERE name = 'Sierra'),'wvmlbc'),
	(1, 'Whatcom County Library System',(SELECT id FROM ils WHERE name = 'Polaris'),'wclswa'),
	(1, 'Wheaton Public Library',(SELECT id FROM ils WHERE name = 'Polaris'),'wplil'),
	(1, 'Yolo County Library',(SELECT id FROM ils WHERE name = 'Sierra'),'yclca')
;

CREATE TABLE services 
(
	id serial primary key,
	name varchar(64),
	created_on timestamp default current_timestamp,
	created_by int
);

INSERT INTO services (name, created_by) VALUES
	('autorenew', 1),
	('monthly', 1),
	('notices', 1),
	('recommend', 1),
	('register', 1),
	('renew', 1),
	('validation', 1),
	('welcome', 1)
;

CREATE TABLE parameters 
(
	id serial primary key,
	name varchar(64),
	description text,
	data_type varchar(255),
	is_global boolean default false,
	created_on timestamp default current_timestamp,
	created_by int
);

INSERT INTO parameters (name, is_global, data_type, description, created_by) VALUES
	('block_by_date', 0, 'String', 'The parameter which we use to do thing A', 1),
	('sierra_api_url', 1, 'String', 'The parameter which we use to do thing B', 1),
	('sierra_oauth_password', 1, 'String', 'The parameter which we use to do thing C', 1),
	('sierra_oauth_username', 1, 'String', 'The global parameter 1', 1),
	('include_patron_types', 0, 'StringList', 'The global parameter 2', 1),
	('include_homelibrary_codes', 0, 'StringList', 'The parameter which we use to do thing C', 1),
	('minutes_interval', 0, 'String', 'The global parameter 1', 1),
	('fieldmap', 0, 'String', 'The global parameter 2', 1),
	('live_segment_id', 0, 'String', 'The global parameter 2', 1)
;

CREATE TABLE ils_to_parameters(
	id serial primary key,
	ils_id int,
	parameter_id int
);

INSERT INTO ils_to_parameters (ils_id, parameter_id) 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'carlx' and lower(b.name) = 'block_by_date' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'evergreen' and lower(b.name) = 'block_by_date' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'horizon' and lower(b.name) = 'block_by_date' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'koha' and lower(b.name) = 'block_by_date' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'polaris' and lower(b.name) = 'block_by_date' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'block_by_date' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'spydus' and lower(b.name) = 'block_by_date' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'symphony' and lower(b.name) = 'block_by_date' UNION 

	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'sierra_api_url' UNION 

	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'sierra_oauth_password' UNION 

	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'sierra_oauth_username' UNION 

	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'carlx' and lower(b.name) = 'include_patron_types' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'evergreen' and lower(b.name) = 'include_patron_types' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'horizon' and lower(b.name) = 'include_patron_types' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'koha' and lower(b.name) = 'include_patron_types' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'polaris' and lower(b.name) = 'include_patron_types' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'include_patron_types' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'spydus' and lower(b.name) = 'include_patron_types' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'symphony' and lower(b.name) = 'include_patron_types' UNION 

	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'include_homelibrary_codes' UNION 

	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'carlx' and lower(b.name) = 'live_segment_id' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'evergreen' and lower(b.name) = 'live_segment_id' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'horizon' and lower(b.name) = 'live_segment_id' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'koha' and lower(b.name) = 'live_segment_id' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'polaris' and lower(b.name) = 'live_segment_id' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'live_segment_id' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'spydus' and lower(b.name) = 'live_segment_id' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'symphony' and lower(b.name) = 'live_segment_id' UNION 
	
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'koha' and lower(b.name) = 'minutes_interval' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'polaris' and lower(b.name) = 'minutes_interval' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'minutes_interval' UNION 

	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'carlx' and lower(b.name) = 'fieldmap' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'evergreen' and lower(b.name) = 'fieldmap' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'horizon' and lower(b.name) = 'fieldmap' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'koha' and lower(b.name) = 'fieldmap' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'polaris' and lower(b.name) = 'fieldmap' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'sierra' and lower(b.name) = 'fieldmap' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'spydus' and lower(b.name) = 'fieldmap' UNION 
	SELECT a.id, b.id from ils a, parameters b WHERE lower(a.name) = 'symphony' and lower(b.name) = 'fieldmap'
;

CREATE TABLE customers_to_services(
	id serial primary key,
	customer_id int,
	service_id int
);

INSERT INTO customers_to_services (customer_id, service_id) 
	SELECT a.id, b.id from customers a, services b
;

CREATE TABLE parameters_to_services(
	id serial primary key,
	service_id int,
	parameter_id int
);

INSERT INTO parameters_to_services (service_id, parameter_id) 
	SELECT a.id, b.id from services a, parameters b WHERE not b.is_global
;

CREATE TABLE grid_columns  
(
	id serial primary key,
	grid_id int,
	field varchar(64),
	display_name varchar(64),
    data_type varchar(64),
    col_order int,
    allowed bit,
    visible bit,
    editable bit
);

-- Customers
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	(1, 1, 'row_id', 'Customer ID', 'number', 1, 1, 0),
	(1, 2, 'name', 'Customer Name', 'text', 1, 1, 0),
	(1, 3, 'site_code', 'Site Code', 'text', 1, 1, 0),
	(1, 4, 'ils', 'ILS', 'text', 1, 1, 0),
	(1, 5, 'services', 'Services', 'text', 1, 1, 0),
	(1, 6, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	(1, 7, 'created_by', 'Created By', 'text', 1, 1, 0),
	(1, 8, 'actions', 'Actions', 'text', 1, 1, 0)
;

-- Services
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, 'name', 'Service Name', 'text', 1, 1, 0),
	(2, 3, 'parameters', 'Parameters', '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)
;

-- Parameters
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	(3, 1, 'row_id', 'Parameter ID', 'number', 1, 1, 0),
	(3, 2, 'name', 'Parameter Name', 'text', 1, 1, 0),
	(3, 3, 'is_global', 'Global?', 'text', 1, 1, 0),
	(3, 4, 'services', 'Services', 'text', 1, 1, 0),
	(3, 5, 'ils', 'Supported ILS', 'text', 1, 1, 0),
	(3, 6, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	(3, 7, 'created_by', 'Created By', 'text', 1, 1, 0),
	(3, 8, 'actions', 'Actions', 'text', 1, 1, 0)
;

-- Users
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	(4, 1, 'row_id', 'User ID', 'number', 1, 1, 0),
	(4, 2, 'username', 'Email Address', 'text', 1, 1, 0),
	(4, 3, 'name', 'Name', 'text', 1, 1, 0),
	(4, 4, 'user_type', 'User Type', 'text', 1, 1, 0),
	(4, 5, 'active', 'Active?', 'text', 1, 1, 0),
	(4, 6, 'created_by', 'Created By', 'text', 1, 1, 0),
	(4, 7, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	(4, 8, 'actions', 'Actions', 'text', 1, 1, 0)
;

-- ILS
INSERT INTO grid_columns (grid_id, col_order, field, display_name, data_type, allowed, visible, editable) VALUES 
	(5, 1, 'row_id', 'ILS ID', 'number', 1, 1, 0),
	(5, 2, 'name', 'Email Address', 'text', 1, 1, 0),
	(5, 3, 'created_by', 'Created By', 'text', 1, 1, 0),
	(5, 4, 'created_on', 'Created On', 'timestamp', 1, 1, 0),
	(5, 5, 'actions', 'Actions', 'text', 1, 1, 0)
;

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>') 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
;

DROP VIEW IF EXISTS vw_grid_services;
CREATE VIEW vw_grid_services AS 
SELECT 
	a.id as row_id, 
	a.name as name,
	group_concat(c.name separator ', ') as parameters,
	a.created_on,
	CONCAT(d.first_name, ' ', d.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 parameters_to_services b
	ON a.id = b.service_id
LEFT JOIN parameters c 
	ON (b.parameter_id = c.id or c.is_global)
LEFT JOIN users d 
	ON a.created_by = d.id
GROUP BY
	a.id
ORDER BY 
	a.name
;

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 'Yes' else 'No' 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
;

DROP VIEW IF EXISTS vw_grid_users;
CREATE VIEW vw_grid_users AS 
SELECT 
	a.id as row_id, 
	a.username as username,
	CONCAT(a.first_name, ' ', a.last_name) as name,
	c.display_text as user_type,
	case when a.active then 'Yes' else 'No' end as active,
	a.created_on,
	coalesce(CONCAT(b.first_name, ' ', b.last_name), '(System)') as created_by,
	CONCAT('<a class="open-detail" title="User Details" data-type="user" data-id="', a.id, '"><img src="/images/details.png" class="grid-icon-image"></a>') as actions
FROM users a
LEFT JOIN users b 
	ON a.created_by = b.id
LEFT JOIN user_types c 
	ON a.user_type_id = c.id
ORDER BY 
	a.id
;

DROP VIEW IF EXISTS vw_grid_ils;
CREATE VIEW vw_grid_ils AS 
SELECT 
	a.id as row_id, 
	a.name as name,
	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
;