INSERT INTO [dbo].[SysParamGroups]([GroupName])
VALUES('Forecast years for charging bays calculation')

DECLARE @paramGroupId INT;

SELECT @paramGroupId = Id
FROM [dbo].[SysParamGroups] 
WHERE GroupName LIKE '%Forecast years for charging bays calculation%';

INSERT INTO [dbo].[SysParams](
	[ParamName]
    ,[ParamValue]
    ,[SysParamGroupId]
    ,[IsComplex])

VALUES
('Short term forecast year', CAST(YEAR(GETDATE()) as decimal(10,2)), @paramGroupId, 0),
('Mid term forecast year', CAST(2025 as decimal(10,2)), @paramGroupId, 0),
('Long term forecast year', CAST(2030 as decimal(10,2)), @paramGroupId, 0)

ALTER TABLE [dbo].[ReportFixedContent] ADD Overview NVARCHAR(MAX) NULL;

UPDATE [dbo].[ReportFixedContent]
SET [Overview] = '{0} is a major retail site outside of {1}. The site has {2} car parking spaces with no existing on-site Electric Vehicle (EV) charging facilities. The report presents an analysis of the site and its local market for electric vehicles today and out to 2030 as the market develops. evlab® has a proprietary algorithm that calculates a site score - indicating the site’s strength as a charging location versus the UK average. The report sets out the size and speed of charging service required at the site and how many charging bays the site should be prepared for in the future.'

CREATE TABLE dbo.BaysWeights
(
	Id INT NOT NULL PRIMARY KEY CLUSTERED,
	NumberOfBaysIntervalStart INT NOT NULL,
	NumberOfBaysIntervalEnd INT NOT NULL,
	[Weight] DECIMAL(4,2) NOT NULL
)

INSERT INTO dbo.BaysWeights(Id, NumberOfBaysIntervalStart, NumberOfBaysIntervalEnd, [Weight])
VALUES
	(1, 0, 2, 1.7),
	(2, 2, 5, 1.85),
	(3, 5, 10, 1.0),
	(4, 10, 15, 1.15),
	(5, 15, 20, 1.3)


INSERT INTO dbo.SysParamGroups(GroupName)
VALUES ('EV Lab score')

DECLARE @evLabScroreGroupId INT;
SELECT @evLabScroreGroupId = Id
FROM dbo.SysParamGroups where GroupName = 'EV Lab score'

INSERT INTO dbo.SysParams(IsComplex, ParamName, ParamValue, SysParamGroupId)
VALUES(0, 'EV Lab score', 0.0, @evLabScroreGroupId)

DECLARE @evLabScoreSysId INT;
SELECT @evLabScoreSysId = Id
FROM dbo.SysParams where ParamName = 'EV Lab score'

INSERT INTO dbo.SysParamsRanges(Category, [Description], EqualsValue, GreaterThan, LessThan, [Name], SysParamId)
VALUES
(NULL, 'The site demand for EV charging is significantly lower than the UK average, and should not be prioritised.',  NULL, NULL, 80.00, 'EV Lab score', @evLabScoreSysId),
(NULL, 'The site demand for EV charging is lower than the UK average.',  NULL, 80.00, 90.00, 'EV Lab score', @evLabScoreSysId),
(NULL, 'The site demand for EV charging is in line with the UK average.',  NULL, 90.00, 110.00, 'EV Lab score', @evLabScoreSysId),
(NULL, 'The site demand for EV charging is higher than the UK average.',  NULL, 110.00, 120.00, 'EV Lab score', @evLabScoreSysId),
(NULL, 'The site demand for EV charging is significantly higher than the UK average and should be prioritised.',  NULL, 120.00, NULL, 'EV Lab score', @evLabScoreSysId)

UPDATE [dbo].[SysParamsRanges]
SET [Name] = 'Parking'
WHERE SysParamId = 10

DECLARE @sysGroupId INT;
SELECT @sysGroupId = SysParamGroupId
FROM [dbo].[SysParams]
WHERE ParamName = 'Car Ownership Score'

INSERT INTO [dbo].[SysParams](
      [ParamName]
      ,[ParamValue]
      ,[SysParamGroupId]
      ,[IsComplex]
)
VALUES('Parking Score', 0.2, @sysGroupId, 0)

UPDATE [dbo].[SysParamsRanges]
SET [Name] = 'Local EV market'
WHERE [Name] = 'Local EV penetration'

UPDATE [dbo].[SysParamsRanges]
SET [Name] = 'Local socio-economic indicators'
WHERE [Name] = 'Expected EV adoption rate'

UPDATE [dbo].[SysParamsRanges]
SET [Name] = 'Economic Indicator'
WHERE [Name] = 'Wealth'

UPDATE [dbo].[ReportFixedContent]
SET [Forecast] = 'UK EV penetration forecasts, combined with an assessment of the local market and the site strength (evlab score), have been used to forecast how many EV charging bays will be required at the subject site for {0} and out to {1}.'

UPDATE [dbo].[ReportFixedContent]
SET Overview =  '{0} is a {1} {2}. The site has {3} car parking spaces with {4} existing on-site Electric Vehicle (EV) charging facilities. The report presents an analysis of the site and its local market for electric vehicles today and out to 2030 as the market develops. evlab® has a proprietary algorithm that calculates a site score - indicating the site’s strength as a charging location versus the UK average. The report sets out the size and speed of charging service required at the site and how many charging bays the site should be prepared for in the future.'

  UPDATE [dbo].[BaysWeights]
  SET [Weight] = 0.7
  WHERE [Id] = 1

  UPDATE [dbo].[BaysWeights]
  SET [Weight] = 0.85
  WHERE [Id] = 2

ALTER TABLE [dbo].[SysParams] ADD IsIntegerValue BIT NOT NULL DEFAULT(0)

UPDATE [dbo].[SysParams]
    SET [IsIntegerValue] = 1
WHERE 
    ParamName = 'Short term forecast year'
    OR ParamName = 'Mid term forecast year'
    OR ParamName = 'Long term forecast year'

UPDATE [dbo].[BaysWeights]
SET [NumberOfBaysIntervalEnd] = 999999
WHERE [NumberOfBaysIntervalStart] = 15


ALTER TABLE [dbo].[ReportFixedContent] ADD ReportForSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ClientSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ProjectRefSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD DateSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD Page1Title NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD OverviewSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD EVlabSiteStrenghtIndicatorSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD RecommendedNumberOfChargersSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD RecommendedSpeedSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD RecommendedSpecificationSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD FutureEVChargingBayRequirementsSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD NextStepsSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD Page2Title NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD DemandScoreSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD PEVLocalMarketDensitySectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD LocalNetworkRelianceSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD PluginEVDrivetimeSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD EVsByEnergySourceInLocalMarketSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD MarketGrowthSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD EVMarketIndexGrowthSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD MostPopularEVsSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD Page3Title NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD SupplyScoreSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD SupplyOfChargingInfrastructureSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD NumberOfRegisteredVehiclesPerChargerSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD NetworkSpeedSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD NetworkSpeedDistributionSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD NetworkLocalChargersTotalSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD Page4Title NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD EVAdoptionScoreSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD AverageNumberOfCarsOwnedByHouseholdSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ShareOfWorkersDrivingToWorkSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ShareOfHouseholdsWithDedicatedParkingSpaceSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD AverageNetIncomeAfterHousingCostsSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD Page5Title NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ForecastSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ForecastProjectedRequirementsYearTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ForecastProjectedRequirementsChargingBaysRequiredTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD VehicleMarketPenetrationAndAnnualSalesForecastSectionTitle NVARCHAR(1024);
ALTER TABLE [dbo].[ReportFixedContent] ADD ContactsSectionTitle NVARCHAR(1024);

UPDATE [dbo].[ReportFixedContent]
SET 
ReportForSectionTitle = 'Report generated for',
ClientSectionTitle = 'Client',
ProjectRefSectionTitle = 'Project Ref',
DateSectionTitle = 'Date',
Page1Title = 'Site Summary and Recommendations',
OverviewSectionTitle = 'Overview',
EVlabSiteStrenghtIndicatorSectionTitle = 'EVlab site strength indicator',
RecommendedNumberOfChargersSectionTitle = 'Recommended infrastructure \n ({0})',
RecommendedSpeedSectionTitle = 'Recommended speed',
RecommendedSpecificationSectionTitle = 'Recommended specification',
FutureEVChargingBayRequirementsSectionTitle = 'Future EV charging bay requirement',
NextStepsSectionTitle = 'Next Steps',
Page2Title = 'Local EV Market',
DemandScoreSectionTitle = 'Demand Score:',
PEVLocalMarketDensitySectionTitle = 'EV Ownership',
LocalNetworkRelianceSectionTitle = 'Network Reliance',
PluginEVDrivetimeSectionTitle = 'Plug-In Electric Vehicles by Drivetime',
EVsByEnergySourceInLocalMarketSectionTitle = 'Electric Vehicles by Energy Source in Local Market',
MarketGrowthSectionTitle = 'Market Growth',
EVMarketIndexGrowthSectionTitle = '3 Year EV Market Index Growth',
MostPopularEVsSectionTitle = 'Most Popular EVs',
Page3Title = 'Local Charging Network',
SupplyScoreSectionTitle = 'Supply Score:',
SupplyOfChargingInfrastructureSectionTitle = 'Supply of Charging Infrastructure',
NumberOfRegisteredVehiclesPerChargerSectionTitle = 'Number of registered vehicles per charger',
NetworkSpeedSectionTitle = 'Average Network Speed',
NetworkSpeedDistributionSectionTitle = 'Distribution of chargers speed in UK vs local market',
NetworkLocalChargersTotalSectionTitle = 'Chargers within 15 minutes drivetime',
Page4Title = 'Local Area Indicators',
EVAdoptionScoreSectionTitle = 'EV Adoption Score:',
AverageNumberOfCarsOwnedByHouseholdSectionTitle = 'Average number of cars owned by household',
ShareOfWorkersDrivingToWorkSectionTitle = 'Share of workers driving to work',
ShareOfHouseholdsWithDedicatedParkingSpaceSectionTitle = 'Share of households with a dedicated parking space',
AverageNetIncomeAfterHousingCostsSectionTitle = 'Average household net income after housing costs',
Page5Title = 'Projected Charging Infrastructure Requirement',
ForecastSectionTitle = 'Forecast',
ForecastProjectedRequirementsYearTitle = 'Year',
ForecastProjectedRequirementsChargingBaysRequiredTitle = 'EV Charging Bays Required',
VehicleMarketPenetrationAndAnnualSalesForecastSectionTitle = 'UK Electric Vehicle Market Penetration & Annual Sales Forecasts',
ContactsSectionTitle = 'Please feel free to contact us for any queries'