-- This file MUST be encoded in UTF-8

BEGIN TRANSACTION;

CREATE TABLE IF NOT EXISTS Costsources(costsource integer,description longvarchar(65536),usagetype integer,purchaserate float,calcrate float,salesrate float);

CREATE TABLE IF NOT EXISTS Coverings(projectno varchar(12),positionno float,partid integer,unituid varchar(20),elementno integer,itemno varchar(20),front bit,back bit,edge1 bit,edge2 bit,edge3 bit,edge4 bit, extinfo text);

CREATE TABLE IF NOT EXISTS Customers(customerno varchar(12),shortdesc varchar(80),name1 varchar(60),name2 varchar(60),street varchar(60),postalcode varchar(10),city varchar(60),countrycode varchar(10),phone varchar(20),fax varchar(20),email varchar(60),website varchar(60),ownsupplierno varchar(20),comment longvarchar(65536),purchasecontact varchar(60));

CREATE TABLE IF NOT EXISTS Items(itemno varchar(20),description longvarchar(65536),itemgroup varchar(20),purchaseprice float,calcprice float,salesprice float,length float,width float,thickness float,itemtype varchar(20),comment longvarchar(65536),shortdesc varchar(40),materialclass varchar(20),consumptionpersqm float,uom varchar(10),texturename varchar(40),picturelink longvarchar(65536),waste float,supplierid varchar(12),suppliername varchar(80),supplierorderno varchar(40));

CREATE TABLE IF NOT EXISTS MaterialCalculation(projectposcompound varchar(255),projectno varchar(255),positionno float,itemno varchar(20),description longvarchar(65536),comment longvarchar(65536),quantity float,purchasepriceunit float,calcpriceunit float,salespriceunit float,purchasepricetotal float,calcpricetotal float,salespricetotal float);

CREATE TABLE IF NOT EXISTS Messages(messid integer,messdate timestamp,messtime timestamp,creator integer,messagetype integer,projectno varchar(12),positionno float,comment longvarchar(65536),processed bit, docpath text);
CREATE UNIQUE INDEX IF NOT EXISTS Messages_messid_index ON Messages (messid);

CREATE TABLE IF NOT EXISTS Parts(projectno varchar(12),positionno float,partno integer,partid integer,unituid varchar(32),unitid integer,caduuid varchar(20),quantity float,length float,width float,thickness float,thicknesspart float,itemno varchar(20),uom varchar(10),itemgroup varchar(20),laminate1front varchar(20),laminate2front varchar(20),laminate1back varchar(20),laminate2back varchar(20),laminate1frontthck float,laminate2frontthck float,laminate1backthck float,laminate2backthck float,edge1 varchar(20),edge2 varchar(20),edge3 varchar(20),edge4 varchar(20),edge1length float,edge2length float,edge3length float,edge4length float,edge1width float,edge2width float,edge3width float,edge4width float,edge1thck float,edge2thck float,edge3thck float,edge4thck float,e13corner integer,e41corner integer,e32corner integer,e42corner integer,e1laminate integer,e2laminate integer,e3laminate integer,e4laminate integer,markuplengthtech float,markupwidthtech float,markuplengthmat float,markupwidthmat float,sketch varchar(255),description longvarchar(65536),joblist  varchar(255), joblistback  varchar(255), joblistformat  varchar(255), comment varchar(255), extinfo text, graindirection varchar(1), graindirectionl1f varchar(1), graindirectionl2f varchar(1), graindirectionl1b varchar(1), graindirectionl2b varchar(1), lengthoriginal float, widthoriginal float, joblistpath text, joblistbackpath text, joblistformatpath text, ispolygonal boolean, edges text, name text);

CREATE TABLE IF NOT EXISTS Projectpositions(projectposcombi varchar(40),projectno varchar(12),positionno float,quantity float,itemno varchar(20),description longvarchar(65536),itemgroup varchar(20),length float,width float,thickness float,purchasepriceunit float,calcpriceunit float,salespriceunit float,purchasepricetotal float,calcpricetotal float,salespricetotal float, posuuid varchar(32), extinfo text);

CREATE TABLE IF NOT EXISTS Projects(projectno varchar(12),projectname longvarchar(65536),description longvarchar(65536),deliverydate timestamp,deliveryweek integer,deliveryyear integer,customerno varchar(12),name1 varchar(60),name2 varchar(60),street varchar(60),postalcode varchar(10),city varchar(60),countrycode varchar(10),projectfolderpath longvarchar(65536), manager varchar(64), salutation varchar(64), firstname varchar(64), lastname varchar(64), projectstate varchar(32), engineername varchar(40), managername varchar(40), procstate varchar(40), procstateid integer, projectstateid integer, creationdate timestamp, creationyear integer);

CREATE TABLE IF NOT EXISTS Suppliers(supplierno varchar(12),shortdesc varchar(80),name1 varchar(60),name2 varchar(60),street varchar(60),postalcode varchar(10),city varchar(60),countrycode varchar(10),phone varchar(20),fax varchar(20),email varchar(60),website varchar(60),owncustomerno varchar(20),comment longvarchar(65536),salescontact varchar(60));

CREATE TABLE IF NOT EXISTS TimeCalculation(projectposcompound varchar(255),projectno varchar(255),positionno float,costsource integer,description longvarchar(65536),comment longvarchar(65536),durationtime float,purchasepriceunit float,calcpriceunit float,salespriceunit float,purchasepricetotal float,calcpricetotal float,salespricetotal float);

CREATE TABLE IF NOT EXISTS Worksteps(partid integer,elementno integer,costsource integer,durationtime float,comment longvarchar(65536),operationname varchar(40),cnc bit);

CREATE TABLE IF NOT EXISTS Objects(unituid varchar(32), projectno varchar(12), positionno float, quantity float, itemno varchar(20), description longvarchar(65536), length float, width float, thickness float, purchasepriceunit float, extinfo text);

-- New tables in interiorcad / profacto 2013

CREATE TABLE IF NOT EXISTS Paths (partuuid varchar(32), keyname varchar(32), keyvalue float);
CREATE INDEX IF NOt EXISTS Paths_index ON Paths (partuuid, keyname);

CREATE TABLE IF NOT EXISTS Machinings (machininguuid varchar(32), keyname varchar(32), keyvalue float);
CREATE INDEX IF NOT EXISTS Machinings_index ON Machinings (machininguuid, keyname);

-- New tables in pfic version 2017 (interiorcad 2016 F4)
CREATE TABLE IF NOT EXISTS Fittings(projectno varchar(12),positionno float,partno integer,partid integer,unituid varchar(32),unitid integer,caduuid varchar(20),quantity float,itemno varchar(20),uom varchar(10),sketch varchar(255),description longvarchar(65536), comment varchar(255), extinfo text);

-- New tables in pfic version 2018 (interiorcad 2018 F6)
CREATE TABLE IF NOT EXISTS ProcessState (pstatid integer, pstatdescription varchar(64));
CREATE TABLE IF NOT EXISTS Shapes (caduuid varchar(20), shape text); 


CREATE TABLE IF NOT EXISTS Version (major integer NOT NULL);
DELETE FROM Version;
INSERT INTO Version (major) VALUES (2019);

COMMIT TRANSACTION;
