


DELETE FROM mcm;
DELETE FROM dcs;
DELETE FROM rob;
DELETE FROM roc;
DELETE FROM sm;
DELETE FROM loc;

DELETE FROM mcm_hist;
DELETE FROM dcs_hist;
DELETE FROM rob_hist;
DELETE FROM roc_hist;
DELETE FROM sm_hist;

INSERT INTO loc(name,description) 
	VALUES ('SMint', 'SM Integration (KIP Experimantal HALL)');


INSERT INTO sm(sm_id,loc_id) 
	VALUES (1, (SELECT loc_id FROM loc WHERE name='SMint'));

INSERT INTO roc(roc_type,roc_serial,loc_id) 
	VALUES ('L1C0',17, (SELECT loc_id FROM loc WHERE name='SMint'));

INSERT INTO dcs(dcs_id,loc_id) 
	VALUES (45, (SELECT loc_id FROM loc WHERE name='SMint'));
DELETE FROM dcs;
INSERT INTO dcs(dcs_id,loc_id) 
	VALUES (45, (SELECT loc_id FROM loc WHERE name='SMint'));

INSERT INTO rob(rob_id,type,ver,form,loc_id) 
	VALUES (39,'1A',1,0, (SELECT loc_id FROM loc WHERE name='SMint'));

SELECT 'Duplicate Primary Key' AS Expected_Error;
INSERT INTO rob(rob_id,type,ver,form,loc_id) 
	VALUES (39,'1B',2,42, (SELECT loc_id FROM loc WHERE name='SMint'));

INSERT INTO rob(rob_id,type,ver,form,loc_id) 
	VALUES (40,'2B',1,0, (SELECT loc_id FROM loc WHERE name='SMint'));

SELECT 'DCS on wrong type of ROB' AS Expected_Error;
UPDATE dcs SET loc_id=NULL,rob_id=39 WHERE dcs_id=45;

UPDATE dcs SET loc_id=NULL,rob_id=40 WHERE dcs_id=45;

SELECT 'Wrong ROB pos on ROC' AS Expected_Error;
UPDATE rob
	SET roc_type='L1C0',roc_serial=17,roc_pos=1,loc_id=NULL
	WHERE rob_id=40;

UPDATE rob
	SET roc_type='L1C0',roc_serial=17,roc_pos=3,loc_id=NULL
	WHERE rob_id=40;

UPDATE roc SET sm_id=1,sm_layer=1,sm_stack=2,loc_id=NULL 
	WHERE roc_type='L1C0' AND roc_serial=17;

--UPDATE roc SET sm_id=NULL,sm_layer=NULL,sm_stack=NULL,
--	loc_id=(SELECT loc_id FROM loc WHERE name='SMint')
--	WHERE roc_type='L1C0' AND roc_serial=17;

SELECT 'MCM needs location' AS Expected_Error;
INSERT INTO mcm(mcm_id,status) VALUES (123,42);

UPDATE mcm SET status=23 WHERE mcm_id=123;







--SELECT * FROM mcm;
--SELECT * FROM mcm_hist;
--SELECT * FROM rob;
--SELECT * FROM rob_hist;

SELECT * FROM loc;
SELECT * FROM dcs;

SELECT * FROM inventory;

--SELECT * FROM roc_dcs;
SELECT * FROM roc_summary;

