-- Copied from JR's script, maybe we should do this?
-- DROP DATABASE IF EXISTS resourceassignment;
-- CREATE DATABASE resourceassignment
--   WITH OWNER = resourceassignment
--       ENCODING = 'UTF8'
--       TABLESPACE = pg_default
--       LC_COLLATE = 'en_US.UTF-8'
--       LC_CTYPE = 'en_US.UTF-8'
--       CONNECTION LIMIT = -1;

-- psql resourceassignment -U resourceassignment -f create_database.sql -W

BEGIN;

-- only issue >warnings log messages. (only during this transaction)
SET LOCAL client_min_messages=warning;

DROP SCHEMA IF EXISTS virtual_instrument CASCADE;
DROP SCHEMA IF EXISTS resource_monitoring CASCADE;
DROP SCHEMA IF EXISTS resource_allocation CASCADE;

CREATE SCHEMA virtual_instrument;
CREATE SCHEMA resource_monitoring;
CREATE SCHEMA resource_allocation;

-- This is insanity, but works, order needs to be the reverse of the CREATE TABLE statements
DROP VIEW IF EXISTS virtual_instrument.resource_view CASCADE;
DROP VIEW IF EXISTS resource_allocation.task_view CASCADE;
DROP VIEW IF EXISTS resource_allocation.resource_claim_view CASCADE;
DROP VIEW IF EXISTS resource_monitoring.resource_view CASCADE;
DROP TABLE IF EXISTS resource_allocation.config CASCADE;
DROP TABLE IF EXISTS resource_monitoring.resource_group_availability CASCADE;
DROP TABLE IF EXISTS resource_monitoring.resource_availability CASCADE;
DROP TABLE IF EXISTS resource_monitoring.resource_capacity CASCADE;
DROP TABLE IF EXISTS resource_monitoring.resource_usage CASCADE;
DROP TABLE IF EXISTS resource_allocation.resource_claim_property CASCADE;
DROP TABLE IF EXISTS resource_allocation.resource_claim_property_type CASCADE;
DROP TABLE IF EXISTS resource_allocation.resource_claim_property_io_type CASCADE;
DROP TABLE IF EXISTS resource_allocation.sap CASCADE;
DROP TABLE IF EXISTS resource_allocation.conflict_reason CASCADE;
DROP TABLE IF EXISTS resource_allocation.resource_claim_conflict_reason CASCADE;
DROP TABLE IF EXISTS resource_allocation.task_conflict_reason CASCADE;
DROP TABLE IF EXISTS resource_allocation.resource_claim CASCADE;
DROP TABLE IF EXISTS resource_allocation.resource_claim_status CASCADE;
DROP TABLE IF EXISTS resource_allocation.task_predecessor CASCADE;
DROP TABLE IF EXISTS resource_allocation.task CASCADE;
DROP TABLE IF EXISTS resource_allocation.specification CASCADE;
DROP TYPE IF EXISTS antennaset;
DROP TABLE IF EXISTS resource_allocation.observation_specification CASCADE;
DROP TABLE IF EXISTS resource_allocation.task_type CASCADE;
DROP TABLE IF EXISTS resource_allocation.task_status CASCADE;
DROP TABLE IF EXISTS virtual_instrument.resource_group_to_resource_group CASCADE;
DROP TABLE IF EXISTS virtual_instrument.resource_to_resource_group CASCADE;
DROP TABLE IF EXISTS virtual_instrument.resource_group CASCADE;
DROP TABLE IF EXISTS virtual_instrument.resource_group_type CASCADE;
DROP TABLE IF EXISTS virtual_instrument.resource CASCADE;
DROP TABLE IF EXISTS virtual_instrument.resource_type CASCADE;
DROP TABLE IF EXISTS virtual_instrument.unit CASCADE;
-- Would like to use this instead, but I can not get it to do something useful: SET CONSTRAINTS ALL DEFERRED;

CREATE TABLE virtual_instrument.unit (
  id serial NOT NULL,
  units text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE virtual_instrument.unit
  OWNER TO resourceassignment;

CREATE TABLE virtual_instrument.resource_type (
  id serial NOT NULL,
  name text NOT NULL,
  unit_id integer NOT NULL REFERENCES virtual_instrument.unit DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE virtual_instrument.resource_type
  OWNER TO resourceassignment;

CREATE TABLE virtual_instrument.resource (
  id serial NOT NULL,
  name text NOT NULL,
  type_id integer NOT NULL REFERENCES virtual_instrument.resource_type ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE virtual_instrument.resource
  OWNER TO resourceassignment;

CREATE TABLE virtual_instrument.resource_group_type (
  id serial NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE virtual_instrument.resource_group_type
  OWNER TO resourceassignment;

CREATE TABLE virtual_instrument.resource_group (
  id serial NOT NULL,
  name text NOT NULL,
  type_id integer NOT NULL REFERENCES virtual_instrument.resource_group_type ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE virtual_instrument.resource_group
  OWNER TO resourceassignment;

CREATE TABLE virtual_instrument.resource_to_resource_group (
  id serial NOT NULL,
  child_id integer NOT NULL REFERENCES virtual_instrument.resource ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  parent_id integer NOT NULL REFERENCES virtual_instrument.resource_group ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE virtual_instrument.resource_to_resource_group
  OWNER TO resourceassignment;

CREATE TABLE virtual_instrument.resource_group_to_resource_group (
  id serial NOT NULL,
  child_id integer NOT NULL REFERENCES virtual_instrument.resource_group ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  parent_id integer REFERENCES virtual_instrument.resource_group ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE virtual_instrument.resource_group_to_resource_group
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.task_status (
  id serial NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.task_status
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.task_type (
  id serial NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.task_type
  OWNER TO resourceassignment;

CREATE TYPE antennaset AS ENUM('HBA Zero', 'HBA One', 'HBA Dual', 'HBA Joined', 'LBA Outer', 'LBA Inner', 'LBA Sparse Even', 'LBA Sparse Odd', 'LBA X', 'LBA Y', 'HBA Zero Inner', 'HBA One Inner', 'HBA Dual Inner', 'HBA Joined Inner');
CREATE TABLE resource_allocation.observation_specification (
  id serial NOT NULL,
  clock integer NOT NULL,
  bitmode integer NOT NULL,
  splitter boolean NOT NULL,
  antenna antennaset NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.observation_specification
  OWNER TO resourceassignment;
-- ALTER TABLE distributors -- Maybe we need a check like this?
-- ADD CONSTRAINT check_values 
-- CHECK (clock = 160 OR clock = 200);

CREATE TABLE resource_allocation.specification (
  id serial NOT NULL,
  starttime timestamp,
  endtime timestamp,
  content text,
  cluster text DEFAULT '',
  observation_id integer REFERENCES resource_allocation.observation_specification  ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.specification
  OWNER TO resourceassignment;

CREATE INDEX specification_cluster_idx
  ON resource_allocation.specification (cluster);

CREATE INDEX specification_starttime_endtime_idx
  ON resource_allocation.specification (starttime DESC, endtime);

-- I think we need this one?
CREATE INDEX specification_observation_id_idx
  ON resource_allocation.specification (observation_id);

CREATE TABLE resource_allocation.task (
  id serial NOT NULL,
  mom_id integer UNIQUE,
  otdb_id integer UNIQUE,
  status_id integer NOT NULL REFERENCES resource_allocation.task_status DEFERRABLE INITIALLY IMMEDIATE,
  type_id integer NOT NULL REFERENCES resource_allocation.task_type DEFERRABLE INITIALLY IMMEDIATE,
  specification_id integer NOT NULL REFERENCES resource_allocation.specification  ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.task
  OWNER TO resourceassignment;

CREATE INDEX task_mom_id_idx
  ON resource_allocation.task (mom_id);

CREATE INDEX task_otdb_id_idx
  ON resource_allocation.task (otdb_id);

CREATE INDEX task_status_id_idx
  ON resource_allocation.task (status_id);

CREATE INDEX task_type_id_idx
  ON resource_allocation.task (type_id);

CREATE TABLE resource_allocation.task_predecessor (
  id serial NOT NULL,
  task_id integer NOT NULL REFERENCES resource_allocation.task(id) ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  predecessor_id integer NOT NULL REFERENCES resource_allocation.task(id) ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id),
  CONSTRAINT task_predecessor_unique UNIQUE (task_id, predecessor_id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.task_predecessor
  OWNER TO resourceassignment;

CREATE INDEX task_predecessor_predecessor_id_idx
  ON resource_allocation.task_predecessor (predecessor_id);

CREATE INDEX task_predecessor_task_id_idx
  ON resource_allocation.task_predecessor (task_id);

CREATE TABLE resource_allocation.resource_claim_status (
  id serial NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.resource_claim_status
  OWNER TO resourceassignment;


CREATE TABLE resource_allocation.resource_claim (
  id serial NOT NULL,
  resource_id integer NOT NULL REFERENCES virtual_instrument.resource ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  task_id integer NOT NULL REFERENCES resource_allocation.task ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  starttime timestamp NOT NULL,
  endtime timestamp NOT NULL,
  status_id integer NOT NULL REFERENCES resource_allocation.resource_claim_status DEFERRABLE INITIALLY IMMEDIATE,
  claim_size bigint NOT NULL,
  username text,
  user_id integer,
  used_rcus bit VARYING(192),
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.resource_claim
  OWNER TO resourceassignment;

CREATE INDEX resource_claim_starttime_endtime_idx
  ON resource_allocation.resource_claim
  USING btree
  (starttime DESC, endtime);

CREATE INDEX resource_claim_task_id_idx
  ON resource_allocation.resource_claim (task_id);

CREATE INDEX resource_claim_resource_id_idx
  ON resource_allocation.resource_claim (resource_id);

CREATE INDEX resource_claim_status_id_idx
  ON resource_allocation.resource_claim (status_id);

CREATE TABLE resource_allocation.conflict_reason (
  id serial NOT NULL,
  reason text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.conflict_reason
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.resource_claim_conflict_reason (
  id serial NOT NULL,
  resource_claim_id integer NOT NULL REFERENCES resource_allocation.resource_claim ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  conflict_reason_id integer NOT NULL REFERENCES resource_allocation.conflict_reason ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.resource_claim_conflict_reason
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.task_conflict_reason (
  id serial NOT NULL,
  task_id integer NOT NULL REFERENCES resource_allocation.task ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  conflict_reason_id integer NOT NULL REFERENCES resource_allocation.conflict_reason ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.task_conflict_reason
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.sap (
  id serial NOT NULL,
  resource_claim_id integer NOT NULL REFERENCES resource_allocation.resource_claim ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  number int NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.sap
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.resource_claim_property_type (
  id serial NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.resource_claim_property_type
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.resource_claim_property_io_type (
  id serial NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.resource_claim_property_io_type
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.resource_claim_property (
  id serial NOT NULL,
  resource_claim_id integer NOT NULL REFERENCES resource_allocation.resource_claim ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  sap_id integer REFERENCES resource_allocation.sap ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  type_id integer NOT NULL REFERENCES resource_allocation.resource_claim_property_type DEFERRABLE INITIALLY IMMEDIATE,
  io_type_id integer NOT NULL DEFAULT 0 REFERENCES resource_allocation.resource_claim_property_io_type DEFERRABLE INITIALLY IMMEDIATE,
  value bigint NOT NULL DEFAULT 1,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.resource_claim_property
  OWNER TO resourceassignment;

CREATE TABLE resource_monitoring.resource_capacity (
  id serial NOT NULL,
  resource_id integer NOT NULL REFERENCES virtual_instrument.resource ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  available bigint NOT NULL,
  total bigint NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_monitoring.resource_capacity
  OWNER TO resourceassignment;

CREATE TABLE resource_monitoring.resource_usage (
  id serial NOT NULL,
  resource_id integer NOT NULL REFERENCES virtual_instrument.resource ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  status_id integer NOT NULL REFERENCES resource_allocation.resource_claim_status ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  as_of_timestamp timestamp UNIQUE NOT NULL,
  usage bigint NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_monitoring.resource_usage
  OWNER TO resourceassignment;
COMMENT ON TABLE resource_monitoring.resource_usage
  IS 'resource_usage is automatically filled (thanks to triggers) whenever claims are inserted/updated/deleted on the resource. It contains a timeseries per resource per status of the used sizes of that resource.';

CREATE INDEX resource_usage_as_of_timestamp_idx
  ON resource_monitoring.resource_usage
  USING btree (as_of_timestamp);

CREATE INDEX resource_usage_resource_id_idx
  ON resource_monitoring.resource_usage (resource_id);

CREATE INDEX resource_usage_status_id_idx
  ON resource_monitoring.resource_usage (status_id);

CREATE TABLE resource_monitoring.resource_availability (
  id serial NOT NULL,
  resource_id integer NOT NULL REFERENCES virtual_instrument.resource ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE,
  available bool NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_monitoring.resource_availability
  OWNER TO resourceassignment;

CREATE TABLE resource_monitoring.resource_group_availability (
  id serial NOT NULL,
  resource_group_id integer NOT NULL REFERENCES virtual_instrument.resource_group DEFERRABLE INITIALLY IMMEDIATE,
  available bool NOT NULL,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_monitoring.resource_group_availability
  OWNER TO resourceassignment;

CREATE TABLE resource_allocation.config (
  id serial NOT NULL,
  name text NOT NULL,
  value text,
  PRIMARY KEY (id)
) WITH (OIDS=FALSE);
ALTER TABLE resource_allocation.config
  OWNER TO resourceassignment;

-- VIEWS ----------------------------------------------

CREATE OR REPLACE VIEW resource_allocation.task_view AS
 SELECT t.id, t.mom_id, t.otdb_id, t.status_id, t.type_id, t.specification_id,
    ts.name AS status, tt.name AS type, s.starttime, s.endtime, extract(epoch from age(s.endtime, s.starttime)) as duration, s.cluster,
    (SELECT array_agg(tp.predecessor_id) FROM resource_allocation.task_predecessor tp where tp.task_id=t.id) as predecessor_ids,
    (SELECT array_agg(tp.task_id) FROM resource_allocation.task_predecessor tp where tp.predecessor_id=t.id) as successor_ids,
    (SELECT DISTINCT ARRAY (
		SELECT _tp.predecessor_id
		FROM resource_allocation.task_predecessor _tp, resource_allocation.task _t
		WHERE t.status_id = 400 AND _tp.task_id = t.id AND _t.id = _tp.predecessor_id AND _t.status_id in (300, 320, 335, 1100, 1150, 1200) ) as array_agg
	) as blocked_by_ids
   FROM resource_allocation.task t
   JOIN resource_allocation.task_status ts ON ts.id = t.status_id
   JOIN resource_allocation.task_type tt ON tt.id = t.type_id
   JOIN resource_allocation.specification s ON s.id = t.specification_id;
ALTER VIEW resource_allocation.task_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_allocation.task_view
  IS 'plain view on task table including task_status.name task_type.name specification.starttime and specification.endtime, duration in seconds, and the task predecessor- and successor ids';


CREATE OR REPLACE VIEW resource_allocation.resource_claim_view AS
 SELECT rc.*,
    rcs.name AS status
   FROM resource_allocation.resource_claim rc
   JOIN resource_allocation.resource_claim_status rcs ON rcs.id = rc.status_id;
ALTER VIEW resource_allocation.resource_claim_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_allocation.resource_claim_view
  IS 'plain view on resource_claim table, including resource_claim_status.name';


CREATE OR REPLACE VIEW virtual_instrument.resource_view AS
  SELECT r.id,
      r.name,
      r.type_id,
      rt.name AS type_name,
      u.id as unit_id,
      u.units as unit
    FROM virtual_instrument.resource r
    JOIN virtual_instrument.resource_type rt ON rt.id = r.type_id
    JOIN virtual_instrument.unit u ON rt.unit_id = u.id;
ALTER VIEW virtual_instrument.resource_view
  OWNER TO resourceassignment;
COMMENT ON VIEW virtual_instrument.resource_view
  IS 'plain view on resource table including task_type.name and units';


CREATE OR REPLACE VIEW resource_allocation.resource_claim_extended_view AS
 SELECT rcv.*, rv.name as resource_name, rv.type_id as resource_type_id, rv.type_name as resource_type_name
   FROM resource_allocation.resource_claim_view rcv
   JOIN virtual_instrument.resource_view rv ON rcv.resource_id = rv.id;
ALTER VIEW resource_allocation.resource_claim_extended_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_allocation.resource_claim_extended_view
  IS 'extended view on resource_claim table, including resource_claim_status.name and the resource itself';

CREATE OR REPLACE VIEW resource_allocation.resource_claim_property_view AS
 SELECT rcp.id, rcp.resource_claim_id, rcp.value, rcp.sap_id,
    rcp.type_id, rcpt.name AS type_name,
    rcp.io_type_id, rcpiot.name AS io_type_name
   FROM resource_allocation.resource_claim_property rcp
   JOIN resource_allocation.resource_claim_property_type rcpt ON rcpt.id = rcp.type_id
   JOIN resource_allocation.resource_claim_property_io_type rcpiot ON rcpiot.id = rcp.io_type_id;
ALTER VIEW resource_allocation.resource_claim_property_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_allocation.resource_claim_property_view
  IS 'plain view on resource_claim_property table, including resource_claim_property_type.name and resource_claim_property_io_type.name';

CREATE OR REPLACE VIEW resource_monitoring.resource_view AS
  SELECT rv.*,
    rc.available AS available_capacity,
    rc.total - rc.available AS used_capacity,
    rc.total AS total_capacity,
    ra.available AS active
  FROM virtual_instrument.resource_view rv
  LEFT JOIN resource_monitoring.resource_capacity rc ON rc.resource_id = rv.id
  LEFT JOIN resource_monitoring.resource_availability ra ON ra.resource_id = rv.id;
ALTER VIEW resource_monitoring.resource_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_monitoring.resource_view
  IS 'view on virtual_instrument.resource_view including availability and capacity';

CREATE OR REPLACE VIEW resource_monitoring.resource_usage_view AS
  SELECT ru.as_of_timestamp, ru.usage, ru.status_id, rcs.name as status_name, rv.*
  FROM resource_monitoring.resource_usage ru
  LEFT JOIN resource_monitoring.resource_view rv ON rv.id = ru.resource_id
  LEFT JOIN resource_allocation.resource_claim_status rcs ON rcs.id = ru.status_id;
ALTER VIEW resource_monitoring.resource_usage_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_monitoring.resource_usage_view
  IS 'combined view on resource_monitoring.resource_view and resource_monitoring.resource_usage';

CREATE OR REPLACE VIEW resource_allocation.resource_claim_conflict_reason_view AS
  SELECT rccr.id, rccr.resource_claim_id, rccr.conflict_reason_id, rc.resource_id, rc.task_id, cr.reason
    FROM resource_allocation.resource_claim_conflict_reason rccr
    JOIN resource_allocation.conflict_reason cr on cr.id = rccr.conflict_reason_id
    JOIN resource_allocation.resource_claim rc on rc.id = rccr.resource_claim_id;
ALTER VIEW resource_allocation.resource_claim_conflict_reason_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_allocation.resource_claim_conflict_reason_view
  IS 'plain view on resource_claim_conflict_reason table including conflict_reason.reason';

CREATE OR REPLACE VIEW resource_allocation.task_conflict_reason_view AS
  SELECT rccr.id, rccr.task_id, rccr.conflict_reason_id, cr.reason
    FROM resource_allocation.task_conflict_reason rccr
    JOIN resource_allocation.conflict_reason cr on cr.id = rccr.conflict_reason_id;
ALTER VIEW resource_allocation.task_conflict_reason_view
  OWNER TO resourceassignment;
COMMENT ON VIEW resource_allocation.task_conflict_reason_view
  IS 'plain view on task_conflict_reason table including conflict_reason.reason';

COMMIT;