-- Change tables imported from MySQL DB to the new PostgreSQL DB

ALTER TABLE boardtests ADD COLUMN state VARCHAR;

CREATE TABLE temp (
  SerialNumber varchar(100) NOT NULL default '',
  BoardType varchar(100) NOT NULL default '',
  TestDate timestamp default NULL,
  ScsnQuickTest varchar(100) default NULL,
  PreInChargeTest varchar(100) default NULL,
  TrapTest varchar(100) default NULL,
  PasaTest varchar(100) default NULL,
  NetwInTest varchar(100) default NULL,
  StressTest varchar(100) default NULL,
  OriReadoutTest varchar(100) default NULL,
  DBUpdate varchar(100) default NULL,
  state varchar(100) default NULL,
  PRIMARY KEY  (SerialNumber,BoardType)
) ;

INSERT INTO temp SELECT allboards.serialnumber,allboards.boardtype,
    NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,allboards.state
    FROM allboards;

UPDATE boardtests SET state='good' FROM allboards
    WHERE allboards.serialnumber = boardtests.serialnumber
      AND allboards.state = 'good';
UPDATE boardtests SET state='bad' FROM allboards
    WHERE allboards.serialnumber = boardtests.serialnumber
      AND allboards.state = 'bad';
UPDATE boardtests SET boardtype = 'T1A' WHERE boardtype = '1A';
UPDATE boardtests SET boardtype = 'T1B' WHERE boardtype = '1B';
UPDATE boardtests SET boardtype = 'T2B' WHERE boardtype = '2B';
UPDATE boardtests SET boardtype = 'T3A' WHERE boardtype = '3A';
UPDATE boardtests SET boardtype = 'T3B' WHERE boardtype = '3B';
UPDATE boardtests SET boardtype = 'T4A' WHERE boardtype = '4A';
UPDATE boardtests SET boardtype = 'T4B' WHERE boardtype = '4B';

DELETE FROM temp USING boardtests WHERE boardtests.serialnumber = temp.serialnumber;

INSERT INTO boardtests SELECT * FROM temp;


ALTER TABLE allboards DROP COLUMN state;
ALTER TABLE allboards DROP COLUMN location;

ALTER TABLE boardtests ADD COLUMN operator VARCHAR;
ALTER TABLE deliverynote ADD COLUMN invoice_status VARCHAR;
ALTER TABLE deliverynote ADD COLUMN invoice_amount INTEGER;

DROP TABLE temp; 
