DROP TRIGGER ori_positions_valid ON ori;
DROP FUNCTION ori_positions_valid();

DROP VIEW roc_summary;
DROP VIEW inventory;
DROP VIEW ori_hist_inventory;
DROP VIEW ori_inventory;
DROP VIEW ori_hist;
DROP VIEW ori_positions;

DROP TABLE ori_old;
DROP TABLE ori;
DROP TABLE ori_status;

CREATE TABLE ori_status (
	status INT PRIMARY KEY,
	description VARCHAR(100)
);


CREATE TABLE ori (
	ori_id	INTEGER PRIMARY KEY,

	version INTEGER NOT NULL,
	status	INTEGER REFERENCES ori_status(status),

	rob_id	INTEGER UNIQUE REFERENCES rob,
	loc_id	INTEGER REFERENCES loc,

	CONSTRAINT ori_single_pos
	CHECK (rob_id IS NULL OR loc_id IS NULL),
	
	CONSTRAINT ori_pos_notnull
	CHECK ( (rob_id IS NOT NULL) OR (loc_id IS NOT NULL) ),

	person	CHAR(3) NOT NULL,
	begin_time TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP

	-- Check: rob in (3A,3B)
);

CREATE FUNCTION ori_positions_valid() RETURNS TRIGGER AS $func$
    BEGIN
	PERFORM 1 FROM ori JOIN rob USING (rob_id)
		WHERE rob.type NOT IN ('3A', '3B');
	IF FOUND THEN RETURN NULL; END IF;

	RETURN NEW;
    END;
$func$  LANGUAGE 'plpgsql';

CREATE TRIGGER ori_positions_valid AFTER INSERT OR UPDATE ON ori 
	FOR EACH STATEMENT EXECUTE PROCEDURE ori_positions_valid();

SELECT add_history_to_table('ori');

INSERT INTO ori_status VALUES(1,'good');
INSERT INTO ori_status VALUES(2,'bad');


INSERT INTO ori(ori_id,version,loc_id,person,begin_time,status) 
SELECT 
0,0,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2005-08-12', 1
UNION ALL SELECT
1,1,(SELECT loc_id FROM loc WHERE name='KIP.SMint'),'TD',date '2005-12-29', 1
UNION ALL SELECT
2,1,(SELECT loc_id FROM loc WHERE name='KIP.SMint'),'TD',date '2006-01-16', 1
UNION ALL SELECT
3,1,(SELECT loc_id FROM loc WHERE name='KIP.SMint'),'TD',date '2006-01-16', 1
UNION ALL SELECT
4,1,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2006-01-25', 1
UNION ALL SELECT
5,1,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2006-01-25', 1
UNION ALL SELECT
6,1,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2006-01-25', 1
UNION ALL SELECT
7,1,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2006-01-25', 1
UNION ALL SELECT
8,1,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2006-02-01', 1
UNION ALL SELECT
9,1,(SELECT loc_id FROM loc WHERE name='A.Marin'),'TD',date '2006-02-01', 1
UNION ALL SELECT
10,1,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2006-02-01', 1
UNION ALL SELECT
11,1,(SELECT loc_id FROM loc WHERE name='KIP.3.203'),'TD',date '2006-02-01', 1
UNION ALL SELECT
12,1,(SELECT loc_id FROM loc WHERE name='KIP.ROB_Test'),'TD',date '2006-02-01', 1;



