-- Declare variables for poverty level values
DECLARE @contiguousUSOnePerson float
DECLARE @contiguousUSIncrement float

DECLARE @alaskaOnePerson float
DECLARE @alaskaIncrement float

DECLARE @hawaiiOnePerson float
DECLARE @hawaiiIncrement float

-- Set the values
SET @contiguousUSOnePerson = 15060
SET @contiguousUSIncrement = 5380

SET @alaskaOnePerson = 18810
SET @alaskaIncrement = 6730

SET @hawaiiOnePerson = 17310
SET @hawaiiIncrement = 6190

-- Check to see if the year is in the cw_year_rft table
DECLARE @yrPK nvarchar(4)

set @yrPK = (SELECT yer_rpk FROM cw_year_rft WHERE yer_rpk = 2024)

-- Add the year to the table if missing
IF @yrPK IS NULL
BEGIN
INSERT INTO cw_year_rft(yer_rpk, yer_active, yer_last_updated) VALUES(2024,'1',getdate())
END

-- Create the records for the base values (one person household)
INSERT INTO [dbo].[cw_poverty_level_rft] ([pvr_lv_rpk],[pvr_lv_yer_rfk],[pvr_lv_one_person],[pvr_lv_stt_rfk])
VALUES (73,2024,@contiguousUSOnePerson,NULL)

INSERT INTO [dbo].[cw_poverty_level_rft] ([pvr_lv_rpk],[pvr_lv_yer_rfk],[pvr_lv_one_person],[pvr_lv_stt_rfk])
VALUES (74,2024,@alaskaOnePerson,'02')

INSERT INTO [dbo].[cw_poverty_level_rft] ([pvr_lv_rpk],[pvr_lv_yer_rfk],[pvr_lv_one_person],[pvr_lv_stt_rfk])
VALUES (76,2024,@hawaiiOnePerson,'12')

-- Create the records for additional person increments
INSERT INTO [dbo].[cw_poverty_level_increment] ([pvr_lv_inc_yer_rfk],[pvr_lv_inc_stt_rfk],[pvr_lv_inc_amount],[pvr_lv_inc_min_hh_size],[pvr_lv_inc_max_hh_size])
VALUES (2024,NULL,@contiguousUSIncrement, NULL, NULL)

INSERT INTO [dbo].[cw_poverty_level_increment] ([pvr_lv_inc_yer_rfk],[pvr_lv_inc_stt_rfk],[pvr_lv_inc_amount],[pvr_lv_inc_min_hh_size],[pvr_lv_inc_max_hh_size])
VALUES (2024,'02',@alaskaIncrement, NULL, NULL)

INSERT INTO [dbo].[cw_poverty_level_increment] ([pvr_lv_inc_yer_rfk],[pvr_lv_inc_stt_rfk],[pvr_lv_inc_amount],[pvr_lv_inc_min_hh_size],[pvr_lv_inc_max_hh_size])
VALUES (2024,'12',@hawaiiIncrement, NULL, NULL)

-- The rest is for updating poverty level records for the year
DECLARE @SttRFK nvarchar(2)
DECLARE @OnePsn real
DECLARE @addlPsn real

-- Default values to lower 48
SET @OnePsn = @contiguousUSOnePerson
SET @addlPsn = @contiguousUSIncrement

-- Get the grantee state
SET @SttRFK = (SELECT dmn_stt_rfk FROM cw_domain WHERE dmn_dmn_tp_rfk = '1')

-- Alaska
IF @SttRFK = '02'
BEGIN
SET @OnePsn = @alaskaOnePerson
SET @addlPsn = @alaskaIncrement
END

-- Hawaii
IF @SttRFK = '12'
BEGIN
SET @OnePsn = @hawaiiOnePerson
SET @addlPsn = @hawaiiIncrement
END

-- Update existing values
UPDATE cw_poverty_level_assessment
SET pvr_lv_poverty_level = pvr_lv_household_income / (@OnePsn + (pvr_lv_household_size - 1) * @addlPsn)
WHERE YEAR(pvr_lv_date) = 2024