/*
Post-Deployment Script Template							
------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
			   SELECT * FROM [$(TableName)]					
------------------------------------------------------------------------------------
*/

SET IDENTITY_INSERT [dbo].[Country] ON
MERGE INTO [dbo].[Country] AS Target  
USING (VALUES 
            (1, N'RSA',N'South Africa')
       )  
       AS source ([Id],[CountryCode],[CountryName])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[CountryCode] = source.[CountryCode],
           target.[CountryName] = source.[CountryName]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id],[CountryCode],[CountryName]) VALUES ([Id],[CountryCode],[CountryName]); 
SET IDENTITY_INSERT [dbo].[Country] OFF

SET IDENTITY_INSERT [dbo].[Province] ON
MERGE INTO [dbo].[Province] AS Target  
USING (VALUES (1, 'Gauteng'), 
              (2, 'KwaZulu-Natal'), 
              (3, 'Limpopo'),
              (4, 'North-West'),
              (5, 'Northern Cape'),
              (6, 'Western Cape'),
              (7, 'Eastern Cape'),
              (8, 'Freestate'),
              (9, 'Mpumalanga'))  
       AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description] 
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description]) VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].[Province] OFF

SET IDENTITY_INSERT [dbo].[ProfileType] ON
MERGE INTO [dbo].[ProfileType] AS Target  
USING ( VALUES 
            (1, N'Lend'),
            (2, N'Borrow'),
            (3, N'BuddyFinance')
      ) AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description])
VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].[ProfileType] OFF

SET IDENTITY_INSERT [dbo].[LoanRepaymentStatus] ON
MERGE INTO [dbo].[LoanRepaymentStatus] AS Target  
USING ( VALUES 
            (1, N'Paid'),
            (2, N'Unpaid')
      ) AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description])
VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].[LoanRepaymentStatus] OFF

SET IDENTITY_INSERT [dbo].[LoanScheduleStatus] ON
MERGE INTO [dbo].[LoanScheduleStatus] AS Target  
USING ( VALUES 
            (1, N'Pending'),
            (2, N'Incomplete'),
            (3, N'Completed')
      ) AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description])
VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].[LoanScheduleStatus] OFF

SET IDENTITY_INSERT [dbo].[SourceOfFunds] ON
MERGE INTO [dbo].[SourceOfFunds] AS Target  
USING ( VALUES 
            (1, N'Salary'),
            (2, N'Wage'),
            (3, N'Other')
      ) AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description])
VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].[SourceOfFunds] OFF

SET IDENTITY_INSERT [dbo].[SourceOfIncome] ON
INSERT INTO [dbo].[SourceOfIncome] ([Id],[Description]) VALUES (1, N'Salary')
INSERT INTO [dbo].[SourceOfIncome] ([Id],[Description]) VALUES (2, N'Wage')
INSERT INTO [dbo].[SourceOfIncome] ([Id],[Description]) VALUES (3, N'Other')
SET IDENTITY_INSERT [dbo].[SourceOfIncome] OFF

SET IDENTITY_INSERT [dbo].[OfferStatus] ON
INSERT INTO [dbo].[OfferStatus]  ([Id],[Description]) VALUES (1, N'Request Sent')
INSERT INTO [dbo].[OfferStatus]  ([Id],[Description]) VALUES (2, N'Request Accepted')
INSERT INTO [dbo].[OfferStatus]  ([Id],[Description]) VALUES (3, N'Offer Accepted')
INSERT INTO [dbo].[OfferStatus]  ([Id],[Description]) VALUES (4, N'Rejected')
INSERT INTO [dbo].[OfferStatus]  ([Id],[Description]) VALUES (5, N'Not Found')
SET IDENTITY_INSERT [dbo].[OfferStatus]  OFF

SET IDENTITY_INSERT [dbo].[FicaStatus] ON
INSERT INTO [dbo].[FicaStatus] ([Id],[Description]) VALUES (1, N'Not Compliant')
INSERT INTO [dbo].[FicaStatus] ([Id],[Description]) VALUES (2, N'Partially Compliant')
INSERT INTO [dbo].[FicaStatus] ([Id],[Description]) VALUES (3, N'Fully Compliant')
SET IDENTITY_INSERT [dbo].[FicaStatus] OFF

SET IDENTITY_INSERT [dbo].[LoanRequestStatus] ON
INSERT INTO [dbo].[LoanRequestStatus] ([Id],[Description]) VALUES (1, N'New')
INSERT INTO [dbo].[LoanRequestStatus] ([Id],[Description]) VALUES (2, N'Pre-Approved')
INSERT INTO [dbo].[LoanRequestStatus] ([Id],[Description]) VALUES (3, N'Approved')
INSERT INTO [dbo].[LoanRequestStatus] ([Id],[Description]) VALUES (4, N'Rejected')
INSERT INTO [dbo].[LoanRequestStatus] ([Id],[Description]) VALUES (5, N'Expired')
SET IDENTITY_INSERT [dbo].[LoanRequestStatus] OFF

SET IDENTITY_INSERT [dbo].[LoanStatus] ON
INSERT INTO [dbo].[LoanStatus] ([Id],[Description]) VALUES (1, N'Active')
INSERT INTO [dbo].[LoanStatus] ([Id],[Description]) VALUES (2, N'Paid Up')
INSERT INTO [dbo].[LoanStatus] ([Id],[Description]) VALUES (3, N'Pending')
INSERT INTO [dbo].[LoanStatus] ([Id],[Description]) VALUES (4, N'Deactive')
INSERT INTO [dbo].[LoanStatus] ([Id],[Description]) VALUES (5, N'Declined')
SET IDENTITY_INSERT [dbo].[LoanStatus] OFF

SET IDENTITY_INSERT [dbo].[TermType] ON
INSERT INTO [dbo].[TermType] ([Id],[Description]) VALUES (1, N'Days')
INSERT INTO [dbo].[TermType] ([Id],[Description]) VALUES (2, N'Month(s)')
SET IDENTITY_INSERT [dbo].[TermType] OFF

SET IDENTITY_INSERT [dbo].[TransactionType] ON
INSERT INTO [dbo].[TransactionType] ([Id],[Description]) VALUES (1, N'Credit')
INSERT INTO [dbo].[TransactionType] ([Id],[Description]) VALUES (2, N'Debit')
SET IDENTITY_INSERT [dbo].[TransactionType] OFF

SET IDENTITY_INSERT [dbo].[DocumentType] ON
MERGE INTO [dbo].[DocumentType] AS Target  
USING ( VALUES 
            (1, N'Certified SA ID'),
            (2, N'3-Months Bank Statement / Bank Letter'),
            (3, N'Latest Payslip'),
            (4, N'Proof of Residence')
      ) AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description])
VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].[DocumentType] OFF

SET IDENTITY_INSERT [dbo].[AddressType] ON
INSERT INTO [dbo].[AddressType] ([Id],[Description]) VALUES (1, N'Physical Address')
INSERT INTO [dbo].[AddressType] ([Id],[Description]) VALUES (2, N'Postal Address')
SET IDENTITY_INSERT [dbo].[AddressType] OFF

SET IDENTITY_INSERT [dbo].[Gender] ON
INSERT INTO [dbo].[Gender] ([Id],[Description]) VALUES (1, N'Male')
INSERT INTO [dbo].[Gender] ([Id],[Description]) VALUES (2, N'Female')
SET IDENTITY_INSERT [dbo].[Gender] OFF

SET IDENTITY_INSERT [dbo].[NotificationType] ON
INSERT INTO [dbo].[NotificationType] ([Id],[Description]) VALUES (1, N'Email')
INSERT INTO [dbo].[NotificationType] ([Id],[Description]) VALUES (2, N'SMS')
SET IDENTITY_INSERT [dbo].[NotificationType] OFF

SET IDENTITY_INSERT [dbo].[IdentificationType] ON
INSERT INTO [dbo].[IdentificationType] ([Id],[Description]) VALUES (1, N'Identiy Document')
INSERT INTO [dbo].[IdentificationType] ([Id],[Description]) VALUES (2, N'Passport')
INSERT INTO [dbo].[IdentificationType] ([Id],[Description]) VALUES (3, N'Driver''s License')
SET IDENTITY_INSERT [dbo].[IdentificationType] OFF

SET IDENTITY_INSERT [dbo].[TaxIdentificationType] ON
INSERT INTO [dbo].[TaxIdentificationType] ([Id],[Description]) VALUES (1, N'Personal Income Tax')
INSERT INTO [dbo].[TaxIdentificationType] ([Id],[Description]) VALUES (2, N'Corporate Income Tax')
SET IDENTITY_INSERT [dbo].[TaxIdentificationType] OFF

SET IDENTITY_INSERT [dbo].[ProfileStatus] ON
MERGE INTO [dbo].[ProfileStatus] AS Target  
USING ( VALUES 
            (0, N'New'),
            (1, N'Pending'),
            (2, N'Approved'),
            (3, N'Rejected'),
            (4, N'Suspended'),
            (5, N'Deactivated')
      ) AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description])
VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].[ProfileStatus] OFF

SET IDENTITY_INSERT [dbo].EmploymentStatus ON
MERGE INTO [dbo].EmploymentStatus AS Target  
USING ( VALUES 
            (1, N'Employed'),
            (2, N'Self-employed'),
            (3, N'Unemployed')
      ) AS source ([Id], [Description])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description])
VALUES ([Id], [Description]); 
SET IDENTITY_INSERT [dbo].EmploymentStatus OFF

SET IDENTITY_INSERT [dbo].[EmploymentType] ON
INSERT INTO [dbo].[EmploymentType] ([Id],[Description]) VALUES (1, 'Temporal')
INSERT INTO [dbo].[EmploymentType] ([Id],[Description]) VALUES (2, 'Contract')
INSERT INTO [dbo].[EmploymentType] ([Id],[Description]) VALUES (3, 'Permanent')
SET IDENTITY_INSERT [dbo].[EmploymentType] OFF

SET IDENTITY_INSERT [dbo].[Ethnicity] ON
INSERT INTO [dbo].[Ethnicity] ([Id],[Description]) VALUES (1, 'African')
INSERT INTO [dbo].[Ethnicity] ([Id],[Description]) VALUES (2, 'White')
INSERT INTO [dbo].[Ethnicity] ([Id],[Description]) VALUES (3, 'Coloured')
INSERT INTO [dbo].[Ethnicity] ([Id],[Description]) VALUES (4, 'Indian')
INSERT INTO [dbo].[Ethnicity] ([Id],[Description]) VALUES (5, 'Asian')
SET IDENTITY_INSERT [dbo].[Ethnicity] OFF

SET IDENTITY_INSERT [dbo].[FeeType] ON
MERGE INTO [dbo].[FeeType] AS Target  
USING ( VALUES 
            (1, N'Application fee', 150, 0, 0, 0),
            (2, N'Withdrawal Fee', 15, 0.03,0, 700),
            (3, N'Service fee',60, 0,0, 0),
            (4, N'Initiation Fee', 140, 0.1, 160, 1000),	
            (5, N'Credit scoring fee',15, 0, 5, 0),
            (6, N'First Withdrawal Fee', 0, 0, 0, 0),
            (7, N'Other', 0, 0, 0, 0),
            (8, N'ViewExistingCreditScore', 5, 0,0, 0)
      ) AS source ([Id], [Description], [Amount], [Percentage], [AmountForPercentUse], [Basis])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Description] = source.[Description],
           target.[Amount] = source.[Amount],
           target.[Percentage] = source.[Percentage],
           target.[AmountForPercentUse] = source.[AmountForPercentUse],
           target.[Basis] = source.[Basis]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Description], [Amount], [Percentage], [AmountForPercentUse], [Basis])
VALUES ([Id], [Description], [Amount], [Percentage], [AmountForPercentUse], [Basis]); 
SET IDENTITY_INSERT [dbo].[FeeType] OFF

SET IDENTITY_INSERT [dbo].[UserRole] ON
MERGE INTO [dbo].[UserRole] AS Target  
USING (VALUES 
              (1, 'Super Admin'), 
              (2, 'Admin'),
              (3, 'Buddy')
         )  
       AS source ([Id], [Name])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[Name] = source.[Name]  
WHEN NOT MATCHED BY target THEN  
INSERT ([Id], [Name]) VALUES ([Id], [Name]); 
SET IDENTITY_INSERT [dbo].[UserRole] OFF

SET IDENTITY_INSERT [dbo].[LoanEarningBracket] ON
MERGE INTO [dbo].[LoanEarningBracket] AS Target  
USING ( VALUES 
            (1, 500,4999,4000),
            (2, 5000,8000,8000)
      ) AS source ([Id],[MinAmount],[MaxAmount],[QualifyingAmount])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[MinAmount] = source.[MinAmount],
            target.[MaxAmount] = source.[MaxAmount],
            target.[QualifyingAmount] = source.[QualifyingAmount]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id],[MinAmount],[MaxAmount],[QualifyingAmount])
VALUES ([Id],[MinAmount],[MaxAmount],[QualifyingAmount]); 
SET IDENTITY_INSERT [dbo].[LoanEarningBracket] OFF


SET IDENTITY_INSERT [dbo].[LoanTermBracket] ON
MERGE INTO [dbo].[LoanTermBracket] AS Target  
USING ( VALUES 
            (1, 500,1499,3),
            (2, 1500,8000,6)
      ) AS source ([Id],[MinAmount],[MaxAmount],[Duration])  
ON target.[Id] = source.[Id]  
WHEN MATCHED THEN  
UPDATE SET target.[MinAmount] = source.[MinAmount],
            target.[MaxAmount] = source.[MaxAmount],
            target.[Duration] = source.[Duration]
WHEN NOT MATCHED BY target THEN  
INSERT ([Id],[MinAmount],[MaxAmount],[Duration])
VALUES ([Id],[MinAmount],[MaxAmount],[Duration]); 
SET IDENTITY_INSERT [dbo].[LoanTermBracket] OFF

SET IDENTITY_INSERT [dbo].[TransactionStatus] ON
INSERT INTO [dbo].[TransactionStatus] ([Id],[Description]) VALUES (1, N'Success')
INSERT INTO [dbo].[TransactionStatus] ([Id],[Description]) VALUES (2, N'Pending')
SET IDENTITY_INSERT [dbo].[TransactionStatus] OFF

SET IDENTITY_INSERT [dbo].[CashWithdrawalStatus] ON
INSERT INTO [dbo].[CashWithdrawalStatus] ([Id],[Description]) VALUES (1, N'Approved')
INSERT INTO [dbo].[CashWithdrawalStatus] ([Id],[Description]) VALUES (2, N'Pending')
INSERT INTO [dbo].[CashWithdrawalStatus] ([Id],[Description]) VALUES (3, N'Rejected')
INSERT INTO [dbo].[CashWithdrawalStatus] ([Id],[Description]) VALUES (4, N'Cancelled')
SET IDENTITY_INSERT [dbo].[CashWithdrawalStatus] OFF

SET IDENTITY_INSERT [dbo].[RobotScore] ON
INSERT INTO [dbo].[RobotScore]([Id],[MinScore],[MaxScore],[RobotColor],[Description])VALUES(1,0,150,N'FF0000',N'Danger Zone')
INSERT INTO [dbo].[RobotScore]([Id],[MinScore],[MaxScore],[RobotColor],[Description])VALUES(2,151,350,N'ffb400',N'Fair')
INSERT INTO [dbo].[RobotScore]([Id],[MinScore],[MaxScore],[RobotColor],[Description])VALUES(3,351,600,N'a3ff00',N'Good')
INSERT INTO [dbo].[RobotScore]([Id],[MinScore],[MaxScore],[RobotColor],[Description])VALUES(4,600,950,N'00c5ff',N'Excellent')
SET IDENTITY_INSERT [dbo].[RobotScore] OFF

INSERT INTO [dbo].[GlobalSetup] ([InterestType],[ExpenseFactor],[ServiceFee], [TransUnionSecurityCode], [TransUnionSubscriberCode]) 
			VALUES (1,0.0,0.0,N'Tho90', N'90291')

--Default Profile
SET IDENTITY_INSERT [dbo].[Profile] ON 
INSERT [dbo].[Profile] ([Id], [ProfileNumber], [Title], [Name], [Surname], [EmailAddress], [ContactNumber], [DateOfBirth], [GenderId], [CountryOfBirthId], [EthnicityId], [IdentificationTypeId], [IdentificationNumber], [AvailableFunds], [CashDeposit], [CashWithdrawal], [TaxRegistered], [SourceOfIncomeId], [OtherSourceOfIncome], [EmploymentStatusId], [StatusId], [BillingDate], [LastBillDate]) 
VALUES (1, N'BF000001', N'Mr', N'Buddy', N'Finance', N'admin@buddyfinance.co.za', N'', CAST(N'2018-01-23T14:57:41.220' AS DateTime), 1, 1, 1, 1, N'2017070754085', 1000, NULL, NULL, 0, 3, N'Money laundering', 2, 2, CAST(N'2018-01-31T20:57:32.370' AS DateTime), CAST(N'2018-01-31T20:57:32.380' AS DateTime))
SET IDENTITY_INSERT [dbo].[Profile] OFF
