-- ****************** SqlDBM: Microsoft SQL Server ******************
-- ******************************************************************

-- ************************************** [dbo].[Provider]

CREATE TABLE [dbo].[Provider]
(
 [Id]           int IDENTITY (1, 1) NOT NULL ,
 [ProviderName] nvarchar(50) NULL ,
 [IsAvailable]  bit NULL ,
 [Address]      nvarchar(max) NULL ,
 [Phone]        nvarchar(255) NULL ,
 [Email]        nvarchar(max) NULL ,
 [ManagerName]  nvarchar(255) NULL ,
 [License]      nvarchar(255) NULL ,
 [BrandId]      int NULL ,
 [Type]         int NULL ,
 [VATCode]      nvarchar(13) NULL ,
 [AccountNo]    nvarchar(15) NULL ,


 CONSTRAINT [PK_Provider] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO








-- ************************************** [dbo].[PromotionDetail]

CREATE TABLE [dbo].[PromotionDetail]
(
 [PromotionDetailID]   int IDENTITY (1, 1) NOT NULL ,
 [PromotionCode]       nvarchar(250) NOT NULL ,
 [PromotionDetailCode] nvarchar(250) NOT NULL ,
 [RegExCode]           nvarchar(50) NULL ,
 [MinOrderAmount]      float NULL ,
 [MaxOrderAmount]      float NULL ,
 [BuyProductCode]      nvarchar(250) NULL ,
 [MinBuyQuantity]      int NULL ,
 [MaxBuyQuantity]      int NULL ,
 [GiftProductCode]     nvarchar(50) NULL ,
 [GiftQuantity]        int NULL ,
 [DiscountRate]        float NULL ,
 [DiscountAmount]      money NULL ,
 [PointTrade]          int NULL ,
 [MinPoint]            int NULL ,
 [MaxPoint]            int NULL ,
 [ExpirationPeriod]    int NULL ,


 CONSTRAINT [PK_PromotionDetail] PRIMARY KEY CLUSTERED ([PromotionDetailID] ASC)
);
GO








-- ************************************** [dbo].[Brand]

CREATE TABLE [dbo].[Brand]
(
 [Id]                 int IDENTITY (1, 1) NOT NULL ,
 [BrandName]          nvarchar(255) NULL ,
 [CreateDate]         datetime NOT NULL ,
 [Active]             bit NOT NULL ,
 [Description]        nvarchar(255) NULL ,
 [CompanyName]        nvarchar(255) NULL ,
 [ContactPerson]      nvarchar(255) NULL ,
 [PhoneNumber]        nvarchar(255) NULL ,
 [Fax]                nvarchar(255) NULL ,
 [Website]            nvarchar(255) NULL ,
 [VATCode]            nvarchar(13) NULL ,
 [VATTemplate]        int NULL ,
 [Address]            nvarchar(100) NULL ,
 [ApiSMSKey]          nvarchar(255) NULL ,
 [SecurityApiSMSKey]  nvarchar(255) NULL ,
 [SMSType]            int NULL ,
 [BrandNameSMS]       nvarchar(255) NULL ,
 [JsonConfigUrl]      nvarchar(max) NULL ,
 [BrandFeatureFilter] nvarchar(255) NULL ,
 [WiskyId]            int NULL ,
 [DefaultDashBoard]   nvarchar(250) NULL ,
 [RSAPrivateKey]      nvarchar(max) NULL ,
 [RSAPublicKey]       nvarchar(max) NULL ,
 [PGPPassword]        nvarchar(max) NULL ,
 [PGPPrivateKey]      nvarchar(max) NULL ,
 [PGPPulblicKey]      nvarchar(max) NULL ,
 [DesKey]             nvarchar(max) NULL ,
 [DesVector]          nvarchar(max) NULL ,
 [AccessToken]        nvarchar(max) NULL ,
 [TaxCode]            nvarchar(max) NULL ,


 CONSTRAINT [PK_Brand] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO








-- ************************************** [dbo].[Store]

CREATE TABLE [dbo].[Store]
(
 [ID]                     int IDENTITY (1, 1) NOT NULL ,
 [Name]                   nvarchar(50) NOT NULL ,
 [ShortName]              nvarchar(100) NULL ,
 [Address]                nvarchar(150) NOT NULL ,
 [Lat]                    nvarchar(256) NULL ,
 [Lon]                    nvarchar(50) NULL ,
 [Province]               nvarchar(50) NULL ,
 [District]               nvarchar(50) NULL ,
 [Ward]                   nvarchar(50) NULL ,
 [isAvailable]            bit NULL ,
 [Active]                 bit NULL ,
 [Email]                  nvarchar(50) NULL ,
 [Phone]                  nvarchar(50) NULL ,
 [Fax]                    nvarchar(50) NULL ,
 [CreateDate]             datetime NULL ,
 [Type]                   int NOT NULL CONSTRAINT [DF_Store_Type] DEFAULT ((0)) ,
 [RoomRentMode]           int NULL ,
 [ReportDate]             datetime NULL ,
 [GroupId]                int NULL ,
 [OpenTime]               datetime NULL ,
 [CloseTime]              datetime NULL ,
 [DefaultAdminPassword]   nvarchar(20) NULL ,
 [HasProducts]            bit NULL ,
 [HasNews]                bit NULL ,
 [HasImageCollections]    bit NULL ,
 [HasMultipleLanguage]    bit NULL ,
 [HasWebPages]            bit NULL ,
 [HasCustomerFeedbacks]   bit NULL ,
 [BrandId]                int NULL ,
 [HasOrder]               bit NULL ,
 [HasBlogEditCollections] bit NULL ,
 [LogoUrl]                nvarchar(max) NULL ,
 [FbAccessToken]          nvarchar(max) NULL ,
 [StoreFeatureFilter]     nvarchar(255) NULL ,
 [RunReport]              bit NULL ,
 [AttendanceStoreFilter]  int NULL ,
 [StoreCode]              nchar(10) NULL ,
 [PosId]                  int NULL ,
 [StoreConfig]            ntext NULL ,
 [DefaultDashBoard]       nvarchar(250) NULL ,
 [PaymentTypeApply]       int NULL ,


 CONSTRAINT [PK_Store] PRIMARY KEY CLUSTERED ([ID] ASC),
 CONSTRAINT [FK_Store_Brand] FOREIGN KEY ([BrandId])  REFERENCES [dbo].[Brand]([Id])
);
GO


CREATE NONCLUSTERED INDEX [IX_Store_BrandId] ON [dbo].[Store] 
 (
  [BrandId] ASC
 )

GO







-- ************************************** [dbo].[Promotion]

CREATE TABLE [dbo].[Promotion]
(
 [PromotionID]         int IDENTITY (1, 1) NOT NULL ,
 [PromotionCode]       nvarchar(250) NOT NULL ,
 [PromotionName]       nvarchar(250) NOT NULL ,
 [PromotionClassName]  nvarchar(250) NOT NULL ,
 [ShortDescription]    nvarchar(250) NULL ,
 [Description]         nvarchar(max) NOT NULL ,
 [ImageCss]            nvarchar(50) NULL ,
 [ApplyLevel]          int NOT NULL ,
 [GiftType]            int NOT NULL ,
 [IsForMember]         bit NOT NULL ,
 [Active]              bit NOT NULL ,
 [FromDate]            datetime NOT NULL ,
 [ToDate]              datetime NOT NULL ,
 [ApplyFromTime]       int NULL ,
 [ApplyToTime]         int NULL ,
 [BrandId]             int NULL ,
 [ImageUrl]            nvarchar(max) NULL ,
 [IsVoucher]           bit NULL ,
 [IsApplyOnce]         bit NULL ,
 [VoucherQuantity]     int NULL ,
 [VoucherUsedQuantity] int NULL ,
 [PromotionType]       int NOT NULL CONSTRAINT [DF_Promotion_PromotionType] DEFAULT ((0)) ,
 [FromHappyDay]        int NULL ,
 [ToHappyDay]          int NULL ,
 [FromHoursHappy]      int NULL ,
 [ToHoursHappy]        int NULL ,
 [UsingPoint]          bit NULL ,
 [ApplyToPartner]      int NULL ,
 [parnerCode]          int NOT NULL ,


 CONSTRAINT [PK_Promotion] PRIMARY KEY CLUSTERED ([PromotionID] ASC),
 CONSTRAINT [UK_Promotion] UNIQUE NONCLUSTERED ([PromotionCode] ASC),
 CONSTRAINT [FK_330] FOREIGN KEY ([parnerCode])  REFERENCES [dbo].[Partner]([ID])
);
GO


CREATE NONCLUSTERED INDEX [fkIdx_330] ON [dbo].[Promotion] 
 (
  [parnerCode] ASC
 )

GO



EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'0: bill level; 1: product item level;', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Promotion', @level2type=N'COLUMN', @level2name=N'ApplyLevel';
GO
EXEC sp_addextendedproperty @name = N'MS_Description', @value = N'0: discount; 1: gift', @level0type = N'SCHEMA', @level0name = N'dbo', @level1type = N'TABLE', @level1name = N'Promotion', @level2type=N'COLUMN', @level2name=N'GiftType';
GO