/*==============================================================*/
/* Upgrade struktury z wersji 69 do 70                          */
/*==============================================================*/

/* ================== Tabela ReceiptPoints =====================*/

ALTER TABLE ReceiptPoints ADD Int_2 SMALLINT NULL
GO
ALTER TABLE ReceiptPoints ADD Number_2 DECIMAL(15,4) NULL
GO
ALTER TABLE ReceiptPoints ADD Number_3 DECIMAL(15,4) NULL
GO

/* ================== Tabela ReceiptItemRule ====================*/
DROP TABLE ReceiptItemRule
GO

CREATE TABLE ReceiptItemRule (
   RuleId               numeric(9)           not null,
   PosId                numeric(9)           not null,
   ReceiptItemId        numeric(9)           not null,
   ReceiptId            numeric(9)           not null,
   ReceiptPointsOrdinal smallint             DEFAULT 0 NOT NULL,
   Type                 smallint             not null,
   Number1              decimal(15,4)        not null,
   Number2              decimal(15,4)        NULL,
   Number3              decimal(15,4)        NULL,
   Number4              decimal(15,4)        NULL,
   Int1 			    smallint             NULL,
   Int2  			    smallint             NULL,
   Str1                 varchar(255)         not null,
   constraint PK_RECEIPTITEMRULE primary key (RuleId, ReceiptId, ReceiptItemId, PosId, ReceiptPointsOrdinal, Type)
)
GO
/* ================== Tabela CustomerCard  =====================*/
DROP TABLE CustomerCard
GO

create table CustomerCard (
   CustomerCardId       INTEGER              PRIMARY KEY AUTOINCREMENT,
   IsActive             smallint             not null,
   CustomerId           numeric(9)           not null,
   CardCode             text(40)             not null,
   CardType             smallint             default 0 null,
   IsSuspended          smallint             not null,
   ValidFrom            datetime             null,
   ValidUntil           datetime             null,
   AccountId            integer              null,
   Invalidated          datetime             null,
   Owner                varchar(40)          null,
   Desc1                varchar(40)          null,
   Desc2                varchar(40)          null,
   ExternalId           varchar(20)          null
)
go


/* ================== Tabela CustomerAccount ====================*/
create table CustomerAccount (
   AccountId            INTEGER              PRIMARY KEY AUTOINCREMENT,
   CustomerId           INTEGER              not null,
   Type                 smallint             null,
   IsActive             smallint             null,
   Name                 varchar(40)          null,
   InitialBalance       decimal(15,4)        null,
   Balance              decimal(15,4)        null,
   LastUpdateBalance    datetime             not null,
   LastUpdate           datetime             not null,
   ExternalId           varchar(40)          null
)
go

create index CustomerAccountExtId on CustomerAccount (ExternalId)
go

create table PointsRuleProductCode (
   RuleId               numeric(9)                     not null,
   Code                 varchar(40)                     not null,
   Type                 int                            not null,
   Quantity             decimal(15,4)                  null,
   Price                decimal(16,4)                  null,
   Param1               int                            null,
   constraint PK_POINTSRULEPRODUCTCODE primary key (RuleId, Code, Type)
)
go

CREATE TABLE Image (
     ImageId          INTEGER            PRIMARY KEY AUTOINCREMENT,
     Img              BLOB               NOT NULL,
     Size             INT                NOT NULL,
     Name             VARCHAR(255)       NOT NULL,
     Describe         VARCHAR(2000)      NOT NULL,
     IsActive         INT	               NOT NULL,
     ExternalId       VARCHAR(20)        NULL,
     GroupType        INT                NOT NULL,
     Creation         DATETIME           NOT NULL,
     LastUpdate       DATETIME           NOT NULL
)
go

CREATE TABLE ImageParam (
     ImageParamId     INTEGER            PRIMARY KEY AUTOINCREMENT,
     ImageId          INTEGER            NOT NULL,
     ParamType        INT                NOT NULL,
     Param1           INT                NULL,
     Param2           VARCHAR(255)       NULL
)
go


CREATE TABLE ImageRelation (
     ImageRelationId  INTEGER            PRIMARY KEY AUTOINCREMENT,
     ImageId          INTEGER            NOT NULL,
     RelationId       INT                NOT NULL,
     RelationType     INT                NOT NULL
)
go

/* Update numeru struktury */
UPDATE StructureVersion SET Version = 70
GO