
DROP VIEW rob_hist_inventory;
DROP VIEW roc_hist_inventory;
DROP VIEW dcs_hist_inventory;
DROP VIEW ori_hist_inventory;
DROP VIEW sm_hist_inventory;

DROP VIEW roc_positions;
DROP VIEW roc_types;
DROP VIEW rob_positions;
DROP VIEW rob_type_pos;
DROP VIEW rob_types;
DROP VIEW dcs_positions;
DROP VIEW ori_positions;
DROP VIEW sm_positions;



DROP VIEW inventory;
DROP VIEW ori_inventory;
DROP VIEW dcs_inventory;
DROP VIEW rob_inventory;
DROP VIEW roc_inventory;
DROP VIEW sm_inventory;

DROP VIEW ori_comment;
DROP VIEW dcs_comment;
DROP VIEW rob_comment;
DROP VIEW roc_comment;
DROP VIEW sm_comment;


CREATE VIEW roc_comment AS
	SELECT  roc_type, roc_serial, timestamp, person, title, body, 
		attachment0, attachment1, attachment2, attachment3, attachment4
	FROM roc_cmnt_link JOIN comment USING (cmnt_id);
	
CREATE VIEW ori_comment AS
	SELECT  ori_id, timestamp, person, title, body, 
		attachment0, attachment1, attachment2, attachment3, attachment4
	FROM ori_cmnt_link JOIN comment USING (cmnt_id);
	
CREATE VIEW dcs_comment AS
	SELECT  dcs_id, timestamp, person, title, body, 
		attachment0, attachment1, attachment2, attachment3, attachment4
	FROM dcs_cmnt_link JOIN comment USING (cmnt_id);
	
CREATE VIEW rob_comment AS
	SELECT  rob_id, timestamp, person, title, body, 
		attachment0, attachment1, attachment2, attachment3, attachment4
	FROM rob_cmnt_link JOIN comment USING (cmnt_id);
	
CREATE VIEW sm_comment AS
	SELECT  sm_id, timestamp, person, title, body, 
		attachment0, attachment1, attachment2, attachment3, attachment4
	FROM sm_cmnt_link JOIN comment USING (cmnt_id);
	






CREATE VIEW sm_inventory(name,type,subtype,id,status,person,loc,nrobs,ncmts) AS 
    SELECT 'SM_' || to_char(sm_id,'FMRN'),
	text 'sm', CAST(status AS VARCHAR(10)), sm_id, 
	CAST(NULL AS VARCHAR(100)), person,
	coalesce(
	    (SELECT name FROM loc WHERE sm.loc_id=loc.loc_id),
	    'TRD Slot'||lpad(trd_pos,2,'0')),
	( SELECT count(1) FROM roc 
	  WHERE roc.sm_id=sm.sm_id),
	( SELECT count(1) FROM sm_comment 
	  WHERE sm_comment.sm_id=sm.sm_id )
    FROM sm;

CREATE VIEW roc_inventory(name,type,subtype,id,status,person,loc,nrobs,ncmts) 
    AS 
    SELECT roc_type||'_'||lpad(roc_serial,3,'0'),
	text 'roc', roc_type, roc_serial, CAST(status AS VARCHAR(100)), person,
	coalesce(
	    (SELECT name FROM loc WHERE roc.loc_id=loc.loc_id),
	    'SM'||lpad(sm_id,2,'0')||':L'||sm_layer||':S'||sm_stack),
	( SELECT count(1) FROM rob 
	  WHERE rob.roc_serial=roc.roc_serial AND rob.roc_type=roc.roc_type ),
	( SELECT count(1) FROM roc_comment 
	  WHERE roc_comment.roc_serial=roc.roc_serial 
		AND roc_comment.roc_type=roc.roc_type )
    FROM roc;

CREATE VIEW rob_inventory(name,type,subtype,id,status,person,loc,ncmts) AS 
    SELECT 'F'||form||'_T'||type||'_V'||ver||'_N'||lpad(rob_id,3,'0'),
	text 'rob', type, rob_id,  
	(SELECT description FROM rob_status WHERE rob.status=rob_status.status), 
	person,
	coalesce(
	    (SELECT name FROM loc WHERE rob.loc_id=loc.loc_id),
	    roc_type||'_'||lpad(roc_serial,3,'0')||':'||roc_pos),
	( SELECT count(1) FROM rob_comment 
	  WHERE rob_comment.rob_id=rob.rob_id )
    FROM rob
    WHERE rob_id<10000
    UNION ALL
    SELECT type||'_'||lpad(rob_id,9,'0'),
	text 'rob', type, rob_id,  
	(SELECT description FROM rob_status WHERE rob.status=rob_status.status), 
	person,
	coalesce(
	    (SELECT name FROM loc WHERE rob.loc_id=loc.loc_id),
	    roc_type||'_'||lpad(roc_serial,3,'0')||':'||roc_pos),
	( SELECT count(1) FROM rob_comment 
	  WHERE rob_comment.rob_id=rob.rob_id )
    FROM rob
    WHERE rob_id>=10000;


CREATE VIEW dcs_inventory(name,type,subtype,id,status,person,loc,ncmts) AS 
	SELECT 'DCS'||lpad(dcs_id,4,'0'), text 'dcs', type, dcs_id, 
	(SELECT description FROM dcs_status WHERE dcs_status.status=dcs.status), 
	person,
	coalesce(
	    (SELECT name FROM loc WHERE dcs.loc_id=loc.loc_id),
	    (SELECT name FROM rob_inventory WHERE rob_inventory.id=dcs.rob_id)),
	( SELECT count(1) FROM dcs_comment 
	  WHERE dcs_comment.dcs_id=dcs.dcs_id )
	FROM dcs;

CREATE VIEW ori_inventory(name,type,subtype,id,status,person,loc,ncmts) AS 
	SELECT 	'ORI_V' || version || '_N' ||lpad(ori_id,4,'0'), 
		text 'ori', cast(NULL as text), ori_id, 
		(SELECT description FROM ori_status 
		 WHERE ori_status.status=ori.status), 
		person,
		coalesce(
	    	(SELECT name FROM loc WHERE ori.loc_id=loc.loc_id),
	    	(SELECT name FROM rob_inventory 
		 WHERE rob_inventory.id=ori.rob_id)),
	( SELECT count(1) FROM ori_comment 
	  WHERE ori_comment.ori_id=ori.ori_id )
	FROM ori;


CREATE VIEW inventory(name,type,subtype,id,status,person,loc,remarks,ncmts) AS 
	SELECT	name,type,subtype,id,status,person,loc,
		CASE WHEN cast(substring(subtype from 4 for 1) AS int)*2+6=nrobs 
		     THEN 'full' 
		     ELSE nrobs || ' ROBs' 
		END,
		ncmts
	FROM roc_inventory 
	UNION ALL
	SELECT name,type,subtype,id,status,person,loc,cast(NULL AS text),ncmts
	FROM rob_inventory 
	UNION ALL
	SELECT name,type,subtype,id,status,person,loc,cast(NULL AS text),ncmts
	FROM dcs_inventory
	UNION ALL
	SELECT name,type,subtype,id,status,person,loc,cast(NULL AS text),ncmts
	FROM ori_inventory
	UNION ALL
	SELECT name,type,subtype,id,status,person,loc,cast(NULL AS text),ncmts
	FROM sm_inventory;


CREATE VIEW rob_hist_inventory( name,type,subtype,id,status,person,loc,
				begin_time,end_time) AS 
    SELECT 'F'||form||'_T'||type||'_V'||ver||'_N'||lpad(rob_id,4,'0'),
	text 'rob', type, rob_id, status, person,
	coalesce(
	    (SELECT name FROM loc WHERE rob_hist.loc_id=loc.loc_id),
	    roc_type||'_'||lpad(roc_serial,3,'0')||':'||roc_pos),
	date_trunc('second',begin_time),date_trunc('second',end_time)
    FROM rob_hist
    WHERE rob_id<10000
    UNION ALL
    SELECT type||'_'||lpad(rob_id,9,'0'),
	text 'rob', type, rob_id, status, person,
	coalesce(
	    (SELECT name FROM loc WHERE rob_hist.loc_id=loc.loc_id),
	    roc_type||'_'||lpad(roc_serial,3,'0')||':'||roc_pos),
	date_trunc('second',begin_time),date_trunc('second',end_time)
    FROM rob_hist
    WHERE rob_id>=10000;


CREATE VIEW roc_hist_inventory( name,type,subtype,id,status,person,loc,
				begin_time,end_time) AS 
    SELECT roc_type||'_'||lpad(roc_serial,3,'0'),
	text 'roc', roc_type, roc_serial, status, person,
	coalesce(
	    (SELECT name FROM loc WHERE roc_hist.loc_id=loc.loc_id),
	    'SM'||lpad(sm_id,2,'0')||':L'||sm_layer||':S'||sm_stack),
	date_trunc('second',begin_time),date_trunc('second',end_time)
    FROM roc_hist;

CREATE VIEW dcs_hist_inventory( name,type,subtype,id,status,person,loc,
				begin_time,end_time) AS 
    SELECT 'DCS'||lpad(dcs_id,4,'0'), text 'dcs', type, dcs_id, 
	(SELECT description FROM dcs_status WHERE dcs_status.status=dcs_hist.status),
	person,
	coalesce(
	    (SELECT name FROM loc WHERE dcs_hist.loc_id=loc.loc_id),
	    (SELECT name FROM rob_inventory 
	     WHERE rob_inventory.id=dcs_hist.rob_id)),
	date_trunc('second',begin_time),date_trunc('second',end_time)
    FROM dcs_hist;



CREATE VIEW ori_hist_inventory( name,type,subtype,id,status,person,loc,
				begin_time,end_time) AS 
    SELECT 'ORI_V' || version || '_N' ||lpad(ori_id,4,'0'), 
	text 'ori', cast(NULL as text), ori_id, 
	(SELECT description FROM ori_status WHERE ori_status.status=ori_hist.status),
	person,
	coalesce(
	    (SELECT name FROM loc WHERE ori_hist.loc_id=loc.loc_id),
	    (SELECT name FROM rob_inventory 
	     WHERE rob_inventory.id=ori_hist.rob_id)),
	date_trunc('second',begin_time),date_trunc('second',end_time)
    FROM ori_hist;



CREATE VIEW sm_hist_inventory(name,type,subtype,id,status,person,loc,
			      begin_time,end_time) AS 
    SELECT 'SM_' || to_char(sm_id,'FMRN'),
	text 'sm', CAST(NULL AS VARCHAR(10)), sm_id, 
	CAST(status AS VARCHAR(100)), person,
	coalesce(
	    (SELECT name FROM loc WHERE sm_hist.loc_id=loc.loc_id),
	    'TRD Slot '||lpad(trd_pos,2,'0')),
	date_trunc('second',begin_time),date_trunc('second',end_time)
    FROM sm_hist;



DROP VIEW roc_summary;
DROP VIEW roc_dcs;
CREATE VIEW roc_dcs(roc_type,roc_serial,dcs_id,loc_id,sm_id,sm_stack,sm_layer) AS 
	SELECT  dcsrob.*,
		loc_id,sm_id,sm_stack,sm_layer
	FROM roc JOIN 
	      ( SELECT roc_type,roc_serial,dcs_id 
		FROM dcs JOIN rob USING(rob_id)) AS dcsrob 
			 USING(roc_type,roc_serial);

CREATE VIEW roc_summary(roc_type, roc_serial,
			dcs_id, ori_a_id, ori_b_id,
			loc, sm_id, sm_stack, sm_layer, sm_slot) AS

	SELECT  roc_type,roc_serial,

		( SELECT dcs_id 
		  FROM dcs 
		  WHERE rob_id=(SELECT rob_id 
				FROM rob 
				WHERE rob.roc_pos=3 AND 
				      rob.roc_type=roc.roc_type AND
				      rob.roc_serial=roc.roc_serial)),

		( SELECT ori_id 
		  FROM ori 
		  WHERE rob_id=(SELECT rob_id 
				FROM rob 
				WHERE rob.roc_pos=4 AND 
				      rob.roc_type=roc.roc_type AND
				      rob.roc_serial=roc.roc_serial)),

		( SELECT ori_id 
		  FROM ori 
		  WHERE rob_id=(SELECT rob_id 
				FROM rob 
				WHERE rob.roc_pos=5 AND 
				      rob.roc_type=roc.roc_type AND
				      rob.roc_serial=roc.roc_serial)),


		(SELECT name FROM loc WHERE loc.loc_id=roc.loc_id),
		sm_id,sm_stack,sm_layer,
		(SELECT trd_pos FROM sm WHERE sm.sm_id=roc.sm_id)
	FROM roc;
	--	FROM roc_dcs;





CREATE VIEW dcs_positions(name,loc_id,rob_id,dcs_id) AS
	SELECT  (SELECT name FROM rob_inventory WHERE id=rob_id), 
		CAST(NULL as INTEGER),rob.rob_id,
		(SELECT dcs_id FROM dcs WHERE dcs.rob_id=rob.rob_id)
	FROM	rob
	WHERE	type='2B'
	UNION ALL
	SELECT	name,loc.loc_id,CAST(NULL as INTEGER),dcs_id
	FROM	loc JOIN dcs USING(loc_id)
	UNION ALL
	SELECT	name,loc.loc_id,CAST(NULL as INTEGER),CAST(NULL as INTEGER)
	FROM	loc;
	

CREATE VIEW ori_positions(name,loc_id,rob_id,ori_id) AS
	SELECT  (SELECT name FROM rob_inventory WHERE id=rob_id), 
		CAST(NULL as INTEGER),rob.rob_id,
		(SELECT ori_id FROM ori WHERE ori.rob_id=rob.rob_id)
	FROM	rob
	WHERE	type='3A' OR type='3B'
	UNION ALL
	SELECT	name,loc.loc_id,CAST(NULL as INTEGER),ori_id
	FROM	loc JOIN ori USING(loc_id)
	UNION ALL
	SELECT	name,loc.loc_id,CAST(NULL as INTEGER),CAST(NULL as INTEGER)
	FROM	loc;
	



CREATE VIEW rob_type_pos(type,pos) AS
	SELECT text '1A', 0
	UNION ALL
	SELECT text '1B', 1
	UNION ALL
	SELECT text '1A', 2
	UNION ALL
	SELECT text '2B', 3
	UNION ALL
	SELECT text '3A', 4
	UNION ALL
	SELECT text '3B', 5
	UNION ALL
	SELECT text '4A', 6
	UNION ALL
	SELECT text '4B', 7;

CREATE VIEW rob_types(type) AS
	SELECT text '1A'
	UNION ALL
	SELECT text '1B'
	UNION ALL
	SELECT text '2B'
	UNION ALL
	SELECT text '3A'
	UNION ALL
	SELECT text '3B'
	UNION ALL
	SELECT text '4A'
	UNION ALL
	SELECT text '4B';

CREATE VIEW roc_types(type,layer,stack,c) AS
	SELECT 'L' || l || 'C' || CASE s WHEN 2 THEN 0 ELSE 1 END, l,s, 
		CASE s WHEN 2 THEN 0 ELSE 1 END 
	FROM 	generate_series(0,5) AS l, 
		generate_series(0,4) AS s;



CREATE VIEW rob_positions(type,name,loc_id,roc_type,roc_serial,roc_pos,rob_id) 
AS
	SELECT	type,
		roc_type || '_' || lpad(roc_serial,3,'0') || ':' || pos,
		cast(NULL as INTEGER),
		roc_type,roc_serial,pos,
		(SELECT rob_id FROM rob 
		 WHERE  rob.roc_type   = roc.roc_type AND 
			rob.roc_serial = roc.roc_serial AND 
			rob.roc_pos    = pos.pos )
	FROM	roc, rob_type_pos AS pos
	WHERE (substring(roc_type FROM 3 FOR 2)='C0' AND pos<6) OR
	      (substring(roc_type FROM 3 FOR 2)='C1' AND pos<8)
 	UNION ALL
	SELECT	rob_types.type,name,loc.loc_id, CAST(NULL as TEXT),
		CAST(NULL as INTEGER),CAST(NULL as INTEGER),rob_id
	FROM	rob_types, loc JOIN rob USING(loc_id)
	WHERE	rob.type=rob_types.type
 	UNION ALL
	SELECT	rob_types.type,name,loc.loc_id, CAST(NULL as TEXT),
		CAST(NULL as INTEGER),CAST(NULL as INTEGER),
		CAST(NULL as INTEGER)
	FROM	rob_types, loc;





CREATE VIEW roc_positions(type,name,loc_id,sm_id,sm_layer,sm_stack,roc_type,roc_serial) AS
	SELECT  pos.type,
		'SM_' || to_char(sm_id,'FMRN') || ':L' || pos.layer 
		|| 'S' || pos.stack,
		CAST(NULL as INTEGER),
		sm.sm_id, pos.layer, pos.stack,
		(SELECT roc_type FROM roc 
		 WHERE  roc.sm_id=sm.sm_id AND roc.sm_layer=pos.layer AND 
			roc.sm_stack=pos.stack),
		(SELECT roc_serial FROM roc 
		 WHERE  roc.sm_id=sm.sm_id AND roc.sm_layer=pos.layer AND 
			roc.sm_stack=pos.stack)
	FROM	sm,roc_types AS pos
	UNION ALL
	SELECT	roc_type,name,loc.loc_id,
		CAST(NULL as INTEGER),CAST(NULL as INTEGER),CAST(NULL as INTEGER),
		roc_type,roc_serial
	FROM	loc JOIN roc USING(loc_id)
	UNION ALL
	SELECT	type,name,loc.loc_id,
		CAST(NULL as INTEGER),CAST(NULL as INTEGER),CAST(NULL as INTEGER),
		CAST(NULL as CHAR(4)),CAST(NULL as INTEGER)
	FROM	loc,(SELECT DISTINCT type FROM roc_types) AS types;
	



CREATE VIEW sm_positions(name,loc_id,trd_pos,sm_id) AS
	SELECT	'TRD Slot '||pos AS name,
		CAST(NULL as INTEGER),
		pos AS trd_pos,
		(SELECT sm_id FROM sm WHERE sm.trd_pos=p.pos)
	FROM (SELECT generate_series(1,18) AS pos) AS p
	UNION ALL
	SELECT	name,loc.loc_id,CAST(NULL as INTEGER),sm_id
	FROM	loc JOIN sm USING(loc_id)
	UNION ALL
	SELECT	name,loc.loc_id,CAST(NULL as INTEGER),CAST(NULL as INTEGER)
	FROM	loc;




