Use cw_data

-- 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 = 15960
SET @contiguousUSIncrement = 5680
SET @alaskaOnePerson = 19950
SET @alaskaIncrement = 7100
SET @hawaiiOnePerson = 18360
SET @hawaiiIncrement = 6530

-- 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 = 2026)

-- 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(2026,'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 (79,2026,@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 (80,2026,@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 (81,2026,@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 (2026,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 (2026,'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 (2026,'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) = 2026