------------------------------------------
------------- DROP TRIGGERS --------------
------------------------------------------
DROP TRIGGER IF EXISTS template_deleted ON templates;
DROP TRIGGER IF EXISTS screen_details_modified ON screen_details;
DROP TRIGGER IF EXISTS group_added ON groups;
DROP TRIGGER IF EXISTS group_modified ON groups;

------------------------------------------
------------ DROP FUNCTIONS --------------
------------------------------------------
DROP FUNCTION IF EXISTS template_delete();
DROP FUNCTION IF EXISTS screen_update();
DROP FUNCTION IF EXISTS group_insert();
DROP FUNCTION IF EXISTS group_update();

------------------------------------------
-------------- DROP TABLES ---------------
------------------------------------------
DROP TABLE shops;
DROP TABLE screen_events;
DROP TABLE screen_details;
DROP TABLE template_sports;
--DROP TABLE template_markets;
DROP TABLE templates;
DROP TABLE screens;
DROP TABLE groups;
DROP TABLE country_services;
DROP TABLE countries;

------------------------------------------
------------- CREATE TABLES --------------
------------------------------------------
CREATE TABLE templates
(
  id serial NOT NULL,
  name character varying(50),
  code character varying(5),
  type character(1) NOT NULL,
  max_events integer NOT NULL,
  image character varying,
  html character varying,
  css character varying[],
  resources character varying[],
  countries character varying[],
  markets character varying[],
  modified timestamp without time zone,
  CONSTRAINT templates_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE templates
  OWNER TO postgres;
----------------------------------------
CREATE TABLE template_sports
(
  id serial NOT NULL,
  template_id integer NOT NULL,
  sport_id integer NOT NULL,
  "order" integer NOT NULL,
  CONSTRAINT template_sports_pkey PRIMARY KEY (id),
  CONSTRAINT template_id_fkey FOREIGN KEY (template_id)
      REFERENCES templates (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE template_sports
  OWNER TO postgres;
----------------------------------------
/*CREATE TABLE template_markets
(
  id serial NOT NULL,
  template_id integer NOT NULL,
  markettype_id integer NOT NULL,
  description character varying(100),
  "order" integer NOT NULL,
  CONSTRAINT template_markets_pkey PRIMARY KEY (id),
  CONSTRAINT templates_id_fkey FOREIGN KEY (template_id)
      REFERENCES templates (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE template_markets_pkey
  OWNER TO postgres;*/
-----------------------------------------
CREATE TABLE countries
(
  id serial NOT NULL,
  name character varying(64),
  code character varying(2),
  default_language character varying(4),
  published boolean NOT NULL DEFAULT false,
  CONSTRAINT countries_pkey PRIMARY KEY (id),
  CONSTRAINT "UNIQUE_country_code" UNIQUE (code)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE countries
  OWNER TO postgres;
-----------------------------------------
CREATE TABLE country_services
(
  id serial NOT NULL,
  country_id integer NOT NULL,
  content_service character varying,
  publish_services character varying[],
  publish_resources character varying[],
  CONSTRAINT country_services_pkey PRIMARY KEY (id),
  CONSTRAINT countries_id_fkey FOREIGN KEY (country_id)
	REFERENCES countries (id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE country_services
  OWNER TO postgres;
-----------------------------------------
CREATE TABLE groups
(
  id serial NOT NULL,
  country_id integer NOT NULL,
  code integer NOT NULL,
  name character varying(64) NOT NULL,
  max_screens integer NOT NULL,
  published boolean NOT NULL DEFAULT false,
  CONSTRAINT groups_pkey PRIMARY KEY (id),
  CONSTRAINT "UNIQUE_group_code" UNIQUE (country_id, code),
  CONSTRAINT countries_id_fkey FOREIGN KEY (country_id)
	REFERENCES countries (id) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
  
)
WITH (
  OIDS=FALSE
);
ALTER TABLE groups
  OWNER TO postgres;
-----------------------------------------
CREATE TABLE shops
(
  id serial NOT NULL,
  group_id integer NOT NULL,
  ctd integer NOT NULL,
  language character varying(3) NOT NULL,
  published boolean NOT NULL DEFAULT false,
  CONSTRAINT shops_pkey PRIMARY KEY (id),
  CONSTRAINT groups_id_fkey FOREIGN KEY (group_id)
      REFERENCES groups (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE shops
  OWNER TO postgres;
-----------------------------------------
CREATE TABLE screens
(
  id serial NOT NULL,
  group_id integer NOT NULL,
  "order" integer NOT NULL,
  is_published boolean DEFAULT false,
  CONSTRAINT screens_pkey PRIMARY KEY (id),
  CONSTRAINT groups_id_fkey FOREIGN KEY (group_id)
      REFERENCES groups (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE screens
  OWNER TO postgres;
-----------------------------------------
CREATE TABLE screen_details
(
  id serial NOT NULL,
  parent_id uuid NOT NULL,
  screen_id integer NOT NULL,
  template_id integer NOT NULL,
  "order" integer NOT NULL,
  rotation_time integer NOT NULL DEFAULT 20,
  startDate timestamp without time zone DEFAULT now(),
  endDate timestamp without time zone DEFAULT now(),
  modified timestamp without time zone DEFAULT now(),
  CONSTRAINT screen_details_pkey PRIMARY KEY (id),
  CONSTRAINT screens_id_fkey FOREIGN KEY (screen_id)
      REFERENCES screens (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT templates_id_fkey FOREIGN KEY (template_id)
      REFERENCES templates (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE screen_details
  OWNER TO postgres;
-----------------------------------------
CREATE TABLE screen_events
(
  id serial NOT NULL,
  screen_detail_id integer NOT NULL,
  event_id integer NOT NULL,
  event_name character varying,
  sport_name character varying,
  "group" integer NOT NULL,
  "order" integer NOT NULL,
  modified timestamp without time zone,
  CONSTRAINT screen_events_pkey PRIMARY KEY (id),
  CONSTRAINT screen_detail_id_fkey FOREIGN KEY (screen_detail_id)
      REFERENCES screen_details (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE screen_events
  OWNER TO postgres;

------------------------------------------  

------------------------------------------
------------ CREATE FUNCTIONS ------------
------------------------------------------

-- Function : template_delete
CREATE OR REPLACE FUNCTION template_delete()
  RETURNS trigger AS
$BODY$
DECLARE
      selRecord   RECORD;
BEGIN
        FOR selRecord IN SELECT * FROM screen_details WHERE template_id = OLD.id
		LOOP
			DELETE FROM screen_events WHERE screen_detail_id IN (SELECT id FROM screen_details WHERE id = selRecord.id);
			RAISE NOTICE 'SCREEN EVENTS DELETED';
			DELETE FROM screen_details WHERE id = selRecord.id;
			RAISE NOTICE 'SCREEN DETAILS DELETED';
		END LOOP;
	RETURN OLD;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION template_delete()
  OWNER TO postgres;
  
-- Function : screen_update
CREATE OR REPLACE FUNCTION screen_update()
  RETURNS trigger AS
$BODY$
BEGIN
	UPDATE screens SET is_published = false WHERE id = NEW.screen_id;
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION screen_update()
  OWNER TO postgres;
  
-- Function : group_insert
CREATE OR REPLACE FUNCTION group_insert()
  RETURNS trigger AS
$BODY$
BEGIN
	FOR i IN 1..NEW.max_screens LOOP
	    INSERT INTO screens (group_id, "order") values (NEW.id, i);
	END LOOP;
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION group_insert()
  OWNER TO postgres;
  
-- Function : group_update  
CREATE OR REPLACE FUNCTION group_update()
  RETURNS trigger AS
$BODY$
DECLARE
	thisgroup RECORD;
	forstart INTEGER;
BEGIN
	RAISE NOTICE 'OLD is %',OLD.max_screens;
	RAISE NOTICE 'NEW is %',NEW.max_screens;
	IF OLD.max_screens > NEW.max_screens THEN
		DELETE FROM screen_events WHERE screen_detail_id IN (SELECT id FROM screen_details WHERE screen_id IN ( SELECT id FROM screens WHERE "order" > NEW.max_screens AND group_id = new.id));
		DELETE FROM screen_details WHERE screen_id IN ( SELECT id FROM screens WHERE "order" > NEW.max_screens AND group_id = new.id);
		DELETE FROM screens WHERE  "order" > NEW.max_screens AND group_id = new.id;
	ELSE
		forstart = OLD.max_screens+1;
		FOR i IN forstart..NEW.max_screens LOOP
		    INSERT INTO screens (group_id, "order") values (NEW.id, i);
		END LOOP;
	END IF;
	RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION group_update()
  OWNER TO postgres;

------------------------------------------
------------ CREATE TRIGGERS -------------
------------------------------------------
-- Trigger: template_deleted on templates
CREATE TRIGGER template_deleted
  BEFORE DELETE
  ON templates
  FOR EACH ROW
  EXECUTE PROCEDURE template_delete();
  
-- Trigger: screen_details_modified on screen_details
CREATE TRIGGER screen_details_modified
  AFTER INSERT OR UPDATE
  ON screen_details
  FOR EACH ROW
  EXECUTE PROCEDURE screen_update();
  
-- Trigger: group_added on groups
CREATE TRIGGER group_added
  AFTER INSERT
  ON groups
  FOR EACH ROW
  EXECUTE PROCEDURE group_insert();
  
-- Trigger: group_modified on groups
CREATE TRIGGER group_modified
  AFTER UPDATE
  ON groups
  FOR EACH ROW
  EXECUTE PROCEDURE group_update(); 