CREATE DATABASE AlexisReadWrite;
GO

USE AlexisReadWrite;
GO

CREATE SCHEMA [DataAccessLayer] AUTHORIZATION dbo;
GO

CREATE TABLE [DataAccessLayer].[TableMetaData]
(
	[TableName] VARCHAR(100) NOT NULL,
	[BoundaryDateTime] DATETIME NULL,
	[BoundaryID] BIGINT NULL,
	[BoundaryType] VARCHAR(50) NOT NULL DEFAULT 'Lower', 
    CONSTRAINT [PK_TablenameT1] PRIMARY KEY CLUSTERED 
(
	[TableName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];

GO

CREATE TABLE [dbo].[Transaction](
	[ID] [bigint] IDENTITY(6150000000,1) NOT FOR REPLICATION NOT NULL,
	[PAN] [bigint] NOT NULL,
	[SysDate] [datetime] NOT NULL,
	[TransDate] [datetime] NOT NULL,
	[TransCcy] [char](3) NOT NULL,
	[BillAmt] [decimal](19, 4) NOT NULL,
	[Description] [nvarchar](300) NOT NULL,
	[OldStatus] [char](2) NOT NULL,
	[NewStatus] [char](2) NOT NULL,
	[TransactionType] [char](1) NOT NULL,
	[RecordID] [bigint] NOT NULL,
	[TransactionStatus] [char](1) NOT NULL,
	[TransAmt] [money] NULL,
	[BillNat_Conv] [decimal](18, 15) NULL,
	[Actual_Balance] [decimal](19, 4) NOT NULL,
	[Blocked_Amount] [decimal](19, 4) NULL,
	[flCredit] [smallint] NULL,
	[ProcessingCode] [varchar](6) NULL,
	[AuthMethod] [smallint] NULL,
	[LoadSRC] [smallint] NULL,
	[LoadType] [smallint] NULL,
	[OrigStan] [varchar](50) NULL,
	[Note] [varchar](500) NULL,
	[Amount_other] [varchar](20) NULL,
	[Fixed_Fee] [decimal](19, 4) NULL,
	[Rate_Fee] [decimal](19, 4) NULL,
	[Fx_Padding] [decimal](19, 4) NULL,
	[Mcc_Padding] [decimal](19, 4) NULL,
	[AuthPresDiff] [decimal](19, 4) NULL,
	[txnCountry] [varchar](5) NULL,
	[FeeID] [int] NULL,
	[Trans_Link] [bigint] NULL,
	[SettlementAmt] [decimal](19, 4) NULL,
	[Additional_Data] [varchar](50) NULL,
	[BillCcy] [char](3) NULL,
	[SettleCcy] [char](3) NULL,
	[TxnTypeProcCode] [char](2) NULL,
	[Account_From] [char](2) NULL,
	[Account_To] [char](2) NULL,
	[traceid_lifecycle] [varchar](40) NULL,
	[payment_token_id] [int] NULL,
	[AccountID] [bigint] NULL,
	[WalletID] [bigint] NULL,
 CONSTRAINT [PK_Statement1_T2] PRIMARY KEY CLUSTERED 
(
	[ID] ASC,
	[TransDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, DATA_COMPRESSION = PAGE))
GO

CREATE NONCLUSTERED INDEX [IX_FeeID_T2] ON [dbo].[Transaction]
(
	[FeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE)
GO

CREATE NONCLUSTERED INDEX [IX_PAN_T2] ON [dbo].[Transaction]
(
	[PAN] ASC,
	[TransDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE)
GO

CREATE NONCLUSTERED INDEX [IX_RecordID_T2] ON [dbo].[Transaction]
(
	[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE)
GO

CREATE NONCLUSTERED INDEX [Ix_Transaction_Trans_Link_PAN_T2] ON [dbo].[Transaction]
(
	[Trans_Link] ASC,
	[PAN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE)
GO

CREATE NONCLUSTERED INDEX [ix_Transaction_TransDate_TransactionStatus_T2] ON [dbo].[Transaction]
(
	[TransDate] ASC,
	[TransactionType] ASC,
	[TransactionStatus] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90, DATA_COMPRESSION = PAGE)
GO

CREATE TABLE [dbo].[Transaction_Int](
	[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[PAN] [bigint] NOT NULL,
	[SysDate] [datetime] NOT NULL,
	[TransDate] [datetime] NOT NULL,
	[TransCcy] [char](3) NOT NULL,
	[BillAmt] [decimal](9, 2) NOT NULL,
	[Description] [nvarchar](300) NOT NULL,
	[OldStatus] [char](2) NOT NULL,
	[NewStatus] [char](2) NOT NULL,
	[TransactionType] [char](1) NOT NULL,
	[RecordID] [int] NOT NULL,
	[TransactionStatus] [char](1) NOT NULL,
	[TransAmt] [money] NULL,
	[BillNat_Conv] [decimal](18, 15) NULL,
	[Actual_Balance] [decimal](9, 2) NOT NULL,
	[Blocked_Amount] [decimal](9, 2) NULL,
	[flCredit] [smallint] NULL,
	[ProcessingCode] [varchar](6) NULL,
	[AuthMethod] [smallint] NULL,
	[LoadSRC] [smallint] NULL,
	[LoadType] [smallint] NULL,
	[OrigStan] [varchar](50) NULL,
	[Note] [varchar](500) NULL,
	[Amount_other] [varchar](20) NULL,
	[Fixed_Fee] [decimal](9, 2) NULL,
	[Rate_Fee] [decimal](9, 2) NULL,
	[Fx_Padding] [decimal](9, 2) NULL,
	[Mcc_Padding] [decimal](9, 2) NULL,
	[AuthPresDiff] [decimal](9, 2) NULL,
	[txnCountry] [varchar](5) NULL,
	[FeeID] [int] NULL,
	[Trans_Link] [bigint] NULL,
	[SettlementAmt] [decimal](9, 2) NULL,
	[Additional_Data] [varchar](50) NULL,
	[BillCcy] [char](3) NULL,
	[SettleCcy] [char](3) NULL,
	[TxnTypeProcCode] [char](2) NULL,
	[Account_From] [char](2) NULL,
	[Account_To] [char](2) NULL,
	[traceid_lifecycle] [varchar](40) NULL,
	[payment_token_id] [int] NULL,
 CONSTRAINT [PK_Statement1] PRIMARY KEY NONCLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Statement_SysDate]  DEFAULT (getdate()) FOR [SysDate]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_Available_Balance]  DEFAULT ((1)) FOR [BillNat_Conv]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_Actual_Balance]  DEFAULT ((0)) FOR [Actual_Balance]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_Blocked_Amount]  DEFAULT ((0)) FOR [Blocked_Amount]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_flCredit]  DEFAULT ((1)) FOR [flCredit]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_LoadType]  DEFAULT ((0)) FOR [LoadType]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_Fixed_Fee]  DEFAULT ((0.0)) FOR [Fixed_Fee]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_Rate_Fee]  DEFAULT ((0.0)) FOR [Rate_Fee]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_Fx_Padding]  DEFAULT ((0.0)) FOR [Fx_Padding]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_Mcc_Padding]  DEFAULT ((0.0)) FOR [Mcc_Padding]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_AuthPresDiff]  DEFAULT ((0.00)) FOR [AuthPresDiff]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_Transaction_FeeID]  DEFAULT ((0)) FOR [FeeID]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF__Transacti__Trans__21AEDB1F]  DEFAULT ((0)) FOR [Trans_Link]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  DEFAULT ((0)) FOR [SettlementAmt]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  DEFAULT ('') FOR [Additional_Data]
GO

ALTER TABLE [dbo].[Transaction_Int] ADD  CONSTRAINT [DF_TRANSACTION__payment_token_id]  DEFAULT (NULL) FOR [payment_token_id]
GO

	CREATE PROCEDURE [DataAccessLayer].[AppendTransactionNoteById]
		@Tier INT,
		@TransactionId BIGINT,
		@Note VARCHAR(500),
		@DateFrom DateTime,
		@DateTo DateTime
	AS
	BEGIN
	
		IF @Tier = 0        --hot
		BEGIN 
			UPDATE [dbo].[Transaction]
			SET Note = SUBSTRING(ISNULL(Note, '') + @Note, 1, 500)
			WHERE Id = @TransactionId
			AND TransDate BETWEEN @DateFrom AND @DateTo
		END
		ELSE IF @Tier = 1   --warm
		BEGIN 
			UPDATE [dbo].[Transaction_Int]
			SET Note = SUBSTRING(ISNULL(Note, '') + @Note, 1, 500)
			WHERE Id = @TransactionId
			AND TransDate BETWEEN @DateFrom AND @DateTo
		END

	END
	GO