/* ============================================================
   Combined normalised inserts for ALL appendices/addendums
   Source PDF: SG Developer Contributions & Infrastructure Delivery
   Draft for Consultation June 2024
   Notes:
   - costcode values are generated stable slugs (document has no numeric IDs)
   - Some appendices contain narrative-only or image tables; where no explicit
     per-unit rate is extractable from text, t003 rows are omitted.
   ============================================================ */
BEGIN;
/* Defaults */
DO $$ BEGIN END $$;
/* Tenant: edinburgh  DateValidFrom: 2024-06-01 */

/* ================================
   Appendix 1 – Education Contribution Zones (per-unit rates)
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-BJ-1-ELC','Education BJ-1 – ELC per-unit rates','education_bj_1','Appendix 1 Education rates table – zone BJ-1 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone BJ-1 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    24,
    482,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'BJ-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 1550.00),
  ('house', 3258.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-BJ-1-SECONDARY','Education BJ-1 – SECONDARY per-unit rates','education_bj_1','Appendix 1 Education rates table – zone BJ-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone BJ-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    24,
    482,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'BJ-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-BJ-1-FULL','Education BJ-1 – FULL per-unit rates','education_bj_1','Appendix 1 Education rates table – zone BJ-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone BJ-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    24,
    482,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'BJ-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 6514.00),
  ('house', 20701.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-BJ-2-ELC','Education BJ-2 – ELC per-unit rates','education_bj_2','Appendix 1 Education rates table – zone BJ-2 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone BJ-2 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    228,
    1031,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'BJ-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 1550.00),
  ('house', 3258.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-BJ-2-PRIMARY','Education BJ-2 – PRIMARY per-unit rates','education_bj_2','Appendix 1 Education rates table – zone BJ-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone BJ-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    228,
    1031,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'BJ-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 6373.00),
  ('house', 17485.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-BJ-2-SECONDARY','Education BJ-2 – SECONDARY per-unit rates','education_bj_2','Appendix 1 Education rates table – zone BJ-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone BJ-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    228,
    1031,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'BJ-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-BJ-2-FULL','Education BJ-2 – FULL per-unit rates','education_bj_2','Appendix 1 Education rates table – zone BJ-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone BJ-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    228,
    1031,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'BJ-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 12887.00),
  ('house', 38186.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-C-1-PRIMARY','Education C-1 – PRIMARY per-unit rates','education_c_1','Appendix 1 Education rates table – zone C-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone C-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1038,
    860,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'C-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 6464.00),
  ('house', 17735.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-C-1-SECONDARY','Education C-1 – SECONDARY per-unit rates','education_c_1','Appendix 1 Education rates table – zone C-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone C-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1038,
    860,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'C-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-C-1-FULL','Education C-1 – FULL per-unit rates','education_c_1','Appendix 1 Education rates table – zone C-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone C-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1038,
    860,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'C-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 11428.00),
  ('house', 35178.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-C-2-PRIMARY','Education C-2 – PRIMARY per-unit rates','education_c_2','Appendix 1 Education rates table – zone C-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone C-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1890,
    353,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'C-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 8193.00),
  ('house', 22478.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-C-2-SECONDARY','Education C-2 – SECONDARY per-unit rates','education_c_2','Appendix 1 Education rates table – zone C-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone C-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1890,
    353,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'C-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-C-2-FULL','Education C-2 – FULL per-unit rates','education_c_2','Appendix 1 Education rates table – zone C-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone C-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1890,
    353,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'C-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 13157.00),
  ('house', 39321.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-1-PRIMARY','Education CB-1 – PRIMARY per-unit rates','education_cb_1','Appendix 1 Education rates table – zone CB-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    228,
    4177,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 7171.00),
  ('house', 19673.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-1-SECONDARY','Education CB-1 – SECONDARY per-unit rates','education_cb_1','Appendix 1 Education rates table – zone CB-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    228,
    4177,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-1-FULL','Education CB-1 – FULL per-unit rates','education_cb_1','Appendix 1 Education rates table – zone CB-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    228,
    4177,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 12135.00),
  ('house', 37116.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-2-PRIMARY','Education CB-2 – PRIMARY per-unit rates','education_cb_2','Appendix 1 Education rates table – zone CB-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    239,
    175,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 955.00),
  ('house', 2619.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-2-SECONDARY','Education CB-2 – SECONDARY per-unit rates','education_cb_2','Appendix 1 Education rates table – zone CB-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    239,
    175,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-2-FULL','Education CB-2 – FULL per-unit rates','education_cb_2','Appendix 1 Education rates table – zone CB-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    239,
    175,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 5919.00),
  ('house', 20062.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-3-SECONDARY','Education CB-3 – SECONDARY per-unit rates','education_cb_3','Appendix 1 Education rates table – zone CB-3 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-3 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    191,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-3-FULL','Education CB-3 – FULL per-unit rates','education_cb_3','Appendix 1 Education rates table – zone CB-3 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-3 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    191,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-4-PRIMARY','Education CB-4 – PRIMARY per-unit rates','education_cb_4','Appendix 1 Education rates table – zone CB-4 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-4 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    211,
    554,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 9026.00),
  ('house', 24763.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-4-SECONDARY','Education CB-4 – SECONDARY per-unit rates','education_cb_4','Appendix 1 Education rates table – zone CB-4 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-4 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    211,
    554,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-CB-4-FULL','Education CB-4 – FULL per-unit rates','education_cb_4','Appendix 1 Education rates table – zone CB-4 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone CB-4 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    211,
    554,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'CB-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 13990.00),
  ('house', 42206.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-F-1-ELC','Education F-1 – ELC per-unit rates','education_f_1','Appendix 1 Education rates table – zone F-1 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone F-1 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    20,
    269,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'F-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 2760.00),
  ('house', 5799.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-F-1-SECONDARY','Education F-1 – SECONDARY per-unit rates','education_f_1','Appendix 1 Education rates table – zone F-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone F-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    20,
    269,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'F-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-F-1-FULL','Education F-1 – FULL per-unit rates','education_f_1','Appendix 1 Education rates table – zone F-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone F-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    20,
    269,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'F-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 8541.00),
  ('house', 26110.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-F-2-ELC','Education F-2 – ELC per-unit rates','education_f_2','Appendix 1 Education rates table – zone F-2 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone F-2 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    180,
    500,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'F-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 2760.00),
  ('house', 5799.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-F-2-PRIMARY','Education F-2 – PRIMARY per-unit rates','education_f_2','Appendix 1 Education rates table – zone F-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone F-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    180,
    500,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'F-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 9403.00),
  ('house', 25798.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-F-2-SECONDARY','Education F-2 – SECONDARY per-unit rates','education_f_2','Appendix 1 Education rates table – zone F-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone F-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    180,
    500,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'F-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-F-2-FULL','Education F-2 – FULL per-unit rates','education_f_2','Appendix 1 Education rates table – zone F-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone F-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    180,
    500,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'F-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 17944.00),
  ('house', 51908.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-1-PRIMARY','Education LG-1 – PRIMARY per-unit rates','education_lg_1','Appendix 1 Education rates table – zone LG-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    2489,
    524,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 8695.00),
  ('house', 23855.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-1-SECONDARY','Education LG-1 – SECONDARY per-unit rates','education_lg_1','Appendix 1 Education rates table – zone LG-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    2489,
    524,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-1-FULL','Education LG-1 – FULL per-unit rates','education_lg_1','Appendix 1 Education rates table – zone LG-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    2489,
    524,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 13659.00),
  ('house', 41298.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-2-PRIMARY','Education LG-2 – PRIMARY per-unit rates','education_lg_2','Appendix 1 Education rates table – zone LG-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    448,
    2342,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 7407.00),
  ('house', 20322.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-2-SECONDARY','Education LG-2 – SECONDARY per-unit rates','education_lg_2','Appendix 1 Education rates table – zone LG-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    448,
    2342,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-2-FULL','Education LG-2 – FULL per-unit rates','education_lg_2','Appendix 1 Education rates table – zone LG-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    448,
    2342,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 12371.00),
  ('house', 37765.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-3-SECONDARY','Education LG-3 – SECONDARY per-unit rates','education_lg_3','Appendix 1 Education rates table – zone LG-3 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-3 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    37,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-LG-3-FULL','Education LG-3 – FULL per-unit rates','education_lg_3','Appendix 1 Education rates table – zone LG-3 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone LG-3 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    37,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'LG-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-1-PRIMARY','Education NE-1 – PRIMARY per-unit rates','education_ne_1','Appendix 1 Education rates table – zone NE-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    519,
    4666,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 6612.00),
  ('house', 18140.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-1-SECONDARY','Education NE-1 – SECONDARY per-unit rates','education_ne_1','Appendix 1 Education rates table – zone NE-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    519,
    4666,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-1-FULL','Education NE-1 – FULL per-unit rates','education_ne_1','Appendix 1 Education rates table – zone NE-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    519,
    4666,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 11576.00),
  ('house', 35583.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-2-ELC','Education NE-2 – ELC per-unit rates','education_ne_2','Appendix 1 Education rates table – zone NE-2 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-2 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    252,
    3039,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 888.00),
  ('house', 1865.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-2-PRIMARY','Education NE-2 – PRIMARY per-unit rates','education_ne_2','Appendix 1 Education rates table – zone NE-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    252,
    3039,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 7677.00),
  ('house', 21062.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-2-SECONDARY','Education NE-2 – SECONDARY per-unit rates','education_ne_2','Appendix 1 Education rates table – zone NE-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    252,
    3039,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-2-FULL','Education NE-2 – FULL per-unit rates','education_ne_2','Appendix 1 Education rates table – zone NE-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    252,
    3039,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 13529.00),
  ('house', 40370.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-3-ELC','Education NE-3 – ELC per-unit rates','education_ne_3','Appendix 1 Education rates table – zone NE-3 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-3 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    496,
    329,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 2309.00),
  ('house', 4852.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-3-PRIMARY','Education NE-3 – PRIMARY per-unit rates','education_ne_3','Appendix 1 Education rates table – zone NE-3 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-3 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    496,
    329,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 3716.00),
  ('house', 10195.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-3-SECONDARY','Education NE-3 – SECONDARY per-unit rates','education_ne_3','Appendix 1 Education rates table – zone NE-3 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-3 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    496,
    329,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-3-FULL','Education NE-3 – FULL per-unit rates','education_ne_3','Appendix 1 Education rates table – zone NE-3 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-3 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    496,
    329,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 10989.00),
  ('house', 32490.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-4-SECONDARY','Education NE-4 – SECONDARY per-unit rates','education_ne_4','Appendix 1 Education rates table – zone NE-4 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-4 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    135,
    1486,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-NE-4-FULL','Education NE-4 – FULL per-unit rates','education_ne_4','Appendix 1 Education rates table – zone NE-4 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone NE-4 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    135,
    1486,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'NE-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-P-1-SECONDARY','Education P-1 – SECONDARY per-unit rates','education_p_1','Appendix 1 Education rates table – zone P-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone P-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    3,
    103,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'P-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-P-1-FULL','Education P-1 – FULL per-unit rates','education_p_1','Appendix 1 Education rates table – zone P-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone P-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    3,
    103,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'P-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-P-2-PRIMARY','Education P-2 – PRIMARY per-unit rates','education_p_2','Appendix 1 Education rates table – zone P-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone P-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    108,
    407,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'P-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 6881.00),
  ('house', 18878.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-P-2-SECONDARY','Education P-2 – SECONDARY per-unit rates','education_p_2','Appendix 1 Education rates table – zone P-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone P-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    108,
    407,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'P-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-P-2-FULL','Education P-2 – FULL per-unit rates','education_p_2','Appendix 1 Education rates table – zone P-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone P-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    108,
    407,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'P-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 11845.00),
  ('house', 36321.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-Q-1-PRIMARY','Education Q-1 – PRIMARY per-unit rates','education_q_1','Appendix 1 Education rates table – zone Q-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone Q-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    902,
    749,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Q-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 8840.00),
  ('house', 24253.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-Q-1-SECONDARY','Education Q-1 – SECONDARY per-unit rates','education_q_1','Appendix 1 Education rates table – zone Q-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone Q-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    902,
    749,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Q-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-Q-1-FULL','Education Q-1 – FULL per-unit rates','education_q_1','Appendix 1 Education rates table – zone Q-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone Q-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    902,
    749,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Q-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 13804.00),
  ('house', 41696.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-Q-2-PRIMARY','Education Q-2 – PRIMARY per-unit rates','education_q_2','Appendix 1 Education rates table – zone Q-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone Q-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    122,
    8,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Q-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 5773.00),
  ('house', 15838.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-Q-2-SECONDARY','Education Q-2 – SECONDARY per-unit rates','education_q_2','Appendix 1 Education rates table – zone Q-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone Q-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    122,
    8,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Q-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-Q-2-FULL','Education Q-2 – FULL per-unit rates','education_q_2','Appendix 1 Education rates table – zone Q-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone Q-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    122,
    8,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Q-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 10737.00),
  ('house', 33281.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-1-PRIMARY','Education SW-1 – PRIMARY per-unit rates','education_sw_1','Appendix 1 Education rates table – zone SW-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    240,
    171,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4659.00),
  ('house', 12781.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-1-SECONDARY','Education SW-1 – SECONDARY per-unit rates','education_sw_1','Appendix 1 Education rates table – zone SW-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    240,
    171,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-1-FULL','Education SW-1 – FULL per-unit rates','education_sw_1','Appendix 1 Education rates table – zone SW-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    240,
    171,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 9623.00),
  ('house', 30224.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-2-SECONDARY','Education SW-2 – SECONDARY per-unit rates','education_sw_2','Appendix 1 Education rates table – zone SW-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    0,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-2-FULL','Education SW-2 – FULL per-unit rates','education_sw_2','Appendix 1 Education rates table – zone SW-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    0,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-3-ELC','Education SW-3 – ELC per-unit rates','education_sw_3','Appendix 1 Education rates table – zone SW-3 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-3 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    143,
    1467,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 1791.00),
  ('house', 3764.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-3-PRIMARY','Education SW-3 – PRIMARY per-unit rates','education_sw_3','Appendix 1 Education rates table – zone SW-3 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-3 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    143,
    1467,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4161.00),
  ('house', 11415.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-3-SECONDARY','Education SW-3 – SECONDARY per-unit rates','education_sw_3','Appendix 1 Education rates table – zone SW-3 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-3 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    143,
    1467,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 621.00),
  ('house', 2276.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-3-FULL','Education SW-3 – FULL per-unit rates','education_sw_3','Appendix 1 Education rates table – zone SW-3 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-3 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    143,
    1467,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 6573.00),
  ('house', 17455.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-4-PRIMARY','Education SW-4 – PRIMARY per-unit rates','education_sw_4','Appendix 1 Education rates table – zone SW-4 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-4 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    47,
    181,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 169.00),
  ('house', 462.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-4-SECONDARY','Education SW-4 – SECONDARY per-unit rates','education_sw_4','Appendix 1 Education rates table – zone SW-4 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-4 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    47,
    181,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 621.00),
  ('house', 2276.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-SW-4-FULL','Education SW-4 – FULL per-unit rates','education_sw_4','Appendix 1 Education rates table – zone SW-4 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone SW-4 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    47,
    181,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'SW-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 790.00),
  ('house', 2738.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-T-1-PRIMARY','Education T-1 – PRIMARY per-unit rates','education_t_1','Appendix 1 Education rates table – zone T-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone T-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    59,
    402,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'T-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 88.00),
  ('house', 241.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-T-1-SECONDARY','Education T-1 – SECONDARY per-unit rates','education_t_1','Appendix 1 Education rates table – zone T-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone T-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    59,
    402,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'T-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 621.00),
  ('house', 2276.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-T-1-FULL','Education T-1 – FULL per-unit rates','education_t_1','Appendix 1 Education rates table – zone T-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone T-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    59,
    402,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'T-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 709.00),
  ('house', 2517.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-T-2-ELC','Education T-2 – ELC per-unit rates','education_t_2','Appendix 1 Education rates table – zone T-2 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone T-2 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    106,
    884,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'T-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 2861.00),
  ('house', 6012.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-T-2-PRIMARY','Education T-2 – PRIMARY per-unit rates','education_t_2','Appendix 1 Education rates table – zone T-2 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone T-2 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    106,
    884,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'T-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 5856.00),
  ('house', 16064.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-T-2-SECONDARY','Education T-2 – SECONDARY per-unit rates','education_t_2','Appendix 1 Education rates table – zone T-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone T-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    106,
    884,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'T-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 621.00),
  ('house', 2276.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-T-2-FULL','Education T-2 – FULL per-unit rates','education_t_2','Appendix 1 Education rates table – zone T-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone T-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    106,
    884,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'T-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 9338.00),
  ('house', 24352.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-1-PRIMARY','Education W-1 – PRIMARY per-unit rates','education_w_1','Appendix 1 Education rates table – zone W-1 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-1 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1850,
    1141,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 5951.00),
  ('house', 16326.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-1-SECONDARY','Education W-1 – SECONDARY per-unit rates','education_w_1','Appendix 1 Education rates table – zone W-1 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-1 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1850,
    1141,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-1-FULL','Education W-1 – FULL per-unit rates','education_w_1','Appendix 1 Education rates table – zone W-1 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-1 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    1850,
    1141,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-1',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 10915.00),
  ('house', 33769.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-2-SECONDARY','Education W-2 – SECONDARY per-unit rates','education_w_2','Appendix 1 Education rates table – zone W-2 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-2 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    30,
    268,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-2-FULL','Education W-2 – FULL per-unit rates','education_w_2','Appendix 1 Education rates table – zone W-2 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-2 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    30,
    268,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-2',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-3-PRIMARY','Education W-3 – PRIMARY per-unit rates','education_w_3','Appendix 1 Education rates table – zone W-3 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-3 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    102,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 11638.00),
  ('house', 0.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-3-SECONDARY','Education W-3 – SECONDARY per-unit rates','education_w_3','Appendix 1 Education rates table – zone W-3 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-3 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    102,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-3-FULL','Education W-3 – FULL per-unit rates','education_w_3','Appendix 1 Education rates table – zone W-3 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-3 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    102,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-3',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 16602.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-4-SECONDARY','Education W-4 – SECONDARY per-unit rates','education_w_4','Appendix 1 Education rates table – zone W-4 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-4 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    39,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-4-FULL','Education W-4 – FULL per-unit rates','education_w_4','Appendix 1 Education rates table – zone W-4 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-4 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    0,
    39,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-4',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-5-ELC','Education W-5 – ELC per-unit rates','education_w_5','Appendix 1 Education rates table – zone W-5 (ELC)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_elc',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-5 (ELC)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    99,
    177,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-5',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 8221.00),
  ('house', 17278.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-5-PRIMARY','Education W-5 – PRIMARY per-unit rates','education_w_5','Appendix 1 Education rates table – zone W-5 (PRIMARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_primary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-5 (PRIMARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    99,
    177,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-5',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 2399.00),
  ('house', 6580.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-5-SECONDARY','Education W-5 – SECONDARY per-unit rates','education_w_5','Appendix 1 Education rates table – zone W-5 (SECONDARY)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_secondary',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-5 (SECONDARY)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    99,
    177,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-5',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 4964.00),
  ('house', 17443.00)
) AS v(unit, costperunit) ON TRUE;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('EDU-W-5-FULL','Education W-5 – FULL per-unit rates','education_w_5','Appendix 1 Education rates table – zone W-5 (FULL)','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasedate, costbasesource,
    housedenominator, flatdenominator,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'education_full',
    t1.costcode,
    'Appendix 1 Education rates table – zone W-5 (FULL)',
    'Per-unit rates extracted from Appendix 1 Education Contribution Zones table (Draft June 2024).',
    '2022-12-31',
    'CEC SG Draft June 2024',
    99,
    177,
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'W-5',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('flat_gt_1bed', 15584.00),
  ('house', 41301.00)
) AS v(unit, costperunit) ON TRUE;

/* ================================
   Appendix 2 – Active Travel Contribution Zones
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('AT-ATPR1','Active Travel – ATPR1','active_travel_atpr1','Active Travel Route: Along Seafield Road and Portobello High Street','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbase, costsource, costbasedate, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_active_travel',
    t1.costcode,
    'Active Travel Route: Along Seafield Road and Portobello High Street',
    'Appendix 2 Active Travel: share=17.5% of total project cost (Q3 2023) allocated to development within contribution zone.',
    1568163.00,
    'Total=8960932.00; DevShare=1568163.00; SharePct=17.50',
    DATE '2023-09-30',
    'CEC SG Draft June 2024 (costs Q3 2023)',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Active Travel Contribution Zones',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_new_unit', 1719.50, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('AT-ATPR3','Active Travel – ATPR3','active_travel_atpr3','Seafield City Centre along Portobello Road/London Road','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbase, costsource, costbasedate, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_active_travel',
    t1.costcode,
    'Seafield City Centre along Portobello Road/London Road',
    'Appendix 2 Active Travel: share=11.5% of total project cost (Q3 2023) allocated to development within contribution zone.',
    935832.70,
    'Total=8137676.00; DevShare=935832.70; SharePct=11.50',
    DATE '2023-09-30',
    'CEC SG Draft June 2024 (costs Q3 2023)',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Active Travel Contribution Zones',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_new_unit', 830.40, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('AT-ATPR13','Active Travel – ATPR13','active_travel_atpr13','Redford Barracks to City Centre','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbase, costsource, costbasedate, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_active_travel',
    t1.costcode,
    'Redford Barracks to City Centre',
    'Appendix 2 Active Travel: share=15.4% of total project cost (Q3 2023) allocated to development within contribution zone.',
    1708930.80,
    'Total=11096953.00; DevShare=1708930.80; SharePct=15.40',
    DATE '2023-09-30',
    'CEC SG Draft June 2024 (costs Q3 2023)',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Active Travel Contribution Zones',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_new_unit', 2136.20, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('AT-ATPR20','Active Travel – ATPR20','active_travel_atpr20','Crewe Road South from Orchard Brae Roundabout to Crewe Toll','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbase, costsource, costbasedate, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_active_travel',
    t1.costcode,
    'Crewe Road South from Orchard Brae Roundabout to Crewe Toll',
    'Appendix 2 Active Travel: share=15.7% of total project cost (Q3 2023) allocated to development within contribution zone.',
    1260709.00,
    'Total=8029991.00; DevShare=1260709.00; SharePct=15.70',
    DATE '2023-09-30',
    'CEC SG Draft June 2024 (costs Q3 2023)',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Active Travel Contribution Zones',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_new_unit', 1585.80, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('AT-ATPR22','Active Travel – ATPR22','active_travel_atpr22','Liberton Hospital/Ellen’s Glen Road – Liberton Hospital to City Centre','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbase, costsource, costbasedate, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_active_travel',
    t1.costcode,
    'Liberton Hospital/Ellen’s Glen Road – Liberton Hospital to City Centre',
    'Appendix 2 Active Travel: share=3.4% of total project cost (Q3 2023) allocated to development within contribution zone.',
    126569.90,
    'Total=3722644.00; DevShare=126569.90; SharePct=3.40',
    DATE '2023-09-30',
    'CEC SG Draft June 2024 (costs Q3 2023)',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Active Travel Contribution Zones',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_new_unit', 1054.70, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('AT-ATPR26','Active Travel – ATPR26','active_travel_atpr26','Edinburgh BioQuarter – A7 north-south BioQuarter to City Centre and Midlothian','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbase, costsource, costbasedate, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_active_travel',
    t1.costcode,
    'Edinburgh BioQuarter – A7 north-south BioQuarter to City Centre and Midlothian',
    'Appendix 2 Active Travel: share=54.6% of total project cost (Q3 2023) allocated to development within contribution zone.',
    6318494.00,
    'Total=11572333.00; DevShare=6318494.00; SharePct=54.60',
    DATE '2023-09-30',
    'CEC SG Draft June 2024 (costs Q3 2023)',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Active Travel Contribution Zones',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_new_unit', 2503.40, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('AT-ATPR36','Active Travel – ATPR36','active_travel_atpr36','Great Junction Street to Cables Wynd','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbase, costsource, costbasedate, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_active_travel',
    t1.costcode,
    'Great Junction Street to Cables Wynd',
    'Appendix 2 Active Travel: share=15.29% of total project cost (Q3 2023) allocated to development within contribution zone.',
    171031.80,
    'Total=1118586.00; DevShare=171031.80; SharePct=15.29',
    DATE '2023-09-30',
    'CEC SG Draft June 2024 (costs Q3 2023)',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Active Travel Contribution Zones',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_new_unit', 249.32, 'edinburgh', false
FROM ins_t002 t2;

/* ================================
   Appendix 3 – Granton Waterfront Development Framework Contribution Zone
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('GRANTON-ATPR46','Granton Waterfront – ATPR46','granton_waterfront','W Granton Road / Saltire Street / W Shore Road Route development.','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
)
INSERT INTO t002costcodesubgroup (
  gidt001, category, costcode, costcodedescription,
  costbase, costsource, costbasedate, costbasesource,
  policydocument, geographicarea,
  datevalidfrom, tenant_id, softdelete
)
SELECT
  t1.gid,
  'transport_granton_waterfront',
  t1.costcode,
  'W Granton Road / Saltire Street / W Shore Road Route development.',
  91875.00,
  'Appendix 3 Granton Waterfront – project cost extracted from appendix text.',
  DATE '2023-09-30',
  'CEC SG Draft June 2024',
  'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
  'Granton Waterfront',
  '2024-06-01',
  'edinburgh',
  false
FROM ins_t001 t1;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('GRANTON-ATPR47','Granton Waterfront – ATPR47','granton_waterfront','Waterfront Broadway Key Street Interface 3','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
)
INSERT INTO t002costcodesubgroup (
  gidt001, category, costcode, costcodedescription,
  costbase, costsource, costbasedate, costbasesource,
  policydocument, geographicarea,
  datevalidfrom, tenant_id, softdelete
)
SELECT
  t1.gid,
  'transport_granton_waterfront',
  t1.costcode,
  'Waterfront Broadway Key Street Interface 3',
  3090000.00,
  'Appendix 3 Granton Waterfront – project cost extracted from appendix text.',
  DATE '2023-09-30',
  'CEC SG Draft June 2024',
  'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
  'Granton Waterfront',
  '2024-06-01',
  'edinburgh',
  false
FROM ins_t001 t1;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('GRANTON-ATPR48','Granton Waterfront – ATPR48','granton_waterfront','Key Street Interface 4 – The Diagonal','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
)
INSERT INTO t002costcodesubgroup (
  gidt001, category, costcode, costcodedescription,
  costbase, costsource, costbasedate, costbasesource,
  policydocument, geographicarea,
  datevalidfrom, tenant_id, softdelete
)
SELECT
  t1.gid,
  'transport_granton_waterfront',
  t1.costcode,
  'Key Street Interface 4 – The Diagonal',
  10355875.00,
  'Appendix 3 Granton Waterfront – project cost extracted from appendix text.',
  DATE '2023-09-30',
  'CEC SG Draft June 2024',
  'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
  'Granton Waterfront',
  '2024-06-01',
  'edinburgh',
  false
FROM ins_t001 t1;

/* ================================
   Appendix 4 – West Edinburgh Transport Contribution Zone (per housing unit)
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('WE-WE14','West Edinburgh – WE14','west_edinburgh_transport','U pgraded Bus interchange facility at Ingliston P+R H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    costbase, costsource, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_west_edinburgh',
    t1.costcode,
    'U pgraded Bus interchange facility at Ingliston P+R H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1',
    5000000.00,
    'Total intervention cost and per-unit cost extracted from Appendix 4 table.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'West Edinburgh Transport Contribution Zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_housing_unit', 449.00, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('WE-WE15','West Edinburgh – WE15','west_edinburgh_transport','Enhanced interchange at Edinburgh Gateway to connect active travel and bus services with tram and rail off Myreton Drive. Additional bus stops created on Gogar Roundabout slips. H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    costbase, costsource, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_west_edinburgh',
    t1.costcode,
    'Enhanced interchange at Edinburgh Gateway to connect active travel and bus services with tram and rail off Myreton Drive. Additional bus stops created on Gogar Roundabout slips. H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1',
    3000000.00,
    'Total intervention cost and per-unit cost extracted from Appendix 4 table.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'West Edinburgh Transport Contribution Zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_housing_unit', 269.00, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('WE-WE16','West Edinburgh – WE16','west_edinburgh_transport','I mproved northern and southern orbital bus routes from Maybury (via Maybury Road and Edinburgh Park respectively) H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    costbase, costsource, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_west_edinburgh',
    t1.costcode,
    'I mproved northern and southern orbital bus routes from Maybury (via Maybury Road and Edinburgh Park respectively) H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1',
    6500000.00,
    'Total intervention cost and per-unit cost extracted from Appendix 4 table.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'West Edinburgh Transport Contribution Zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_housing_unit', 583.00, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('WE-WE17','West Edinburgh – WE17','west_edinburgh_transport','Bus Priority South West Edinburgh H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    costbase, costsource, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_west_edinburgh',
    t1.costcode,
    'Bus Priority South West Edinburgh H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1',
    490000.00,
    'Total intervention cost and per-unit cost extracted from Appendix 4 table.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'West Edinburgh Transport Contribution Zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_housing_unit', 44.00, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('WE-WE19','West Edinburgh – WE19','west_edinburgh_transport','S egregated public transport route - West H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    costbase, costsource, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_west_edinburgh',
    t1.costcode,
    'S egregated public transport route - West H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway',
    18000000.00,
    'Total intervention cost and per-unit cost extracted from Appendix 4 table.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'West Edinburgh Transport Contribution Zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_housing_unit', 1615.00, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('WE-WE20','West Edinburgh – WE20','west_edinburgh_transport','S egregated public transport route South - Harvest Road H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    costbase, costsource, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_west_edinburgh',
    t1.costcode,
    'S egregated public transport route South - Harvest Road H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1',
    1000000.00,
    'Total intervention cost and per-unit cost extracted from Appendix 4 table.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'West Edinburgh Transport Contribution Zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_housing_unit', 90.00, 'edinburgh', false
FROM ins_t002 t2;
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('WE-WE21','West Edinburgh – WE21','west_edinburgh_transport','S egregated public transport route South - Newbridge H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    costbase, costsource, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_west_edinburgh',
    t1.costcode,
    'S egregated public transport route South - Newbridge H59 Land at Turnhouse Road (Saica), H61 Crosswinds H62 Land Adjacent to Edinburgh Gateway H63 Edinburgh 205 IBG Phase 1',
    8500000.00,
    'Total intervention cost and per-unit cost extracted from Appendix 4 table.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'West Edinburgh Transport Contribution Zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_housing_unit', 763.00, 'edinburgh', false
FROM ins_t002 t2;

/* ================================
   Appendix 5 – Shared Mobility (Car share EV scheme threshold contributions)
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('SM-CARSHARE-EV','Shared Mobility – Car share EV scheme','shared_mobility_citywide','Threshold-based contributions for EV car share vehicles (3-year operation costs).','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_shared_mobility',
    t1.costcode,
    'EV car share scheme – threshold contributions by number of residential units',
    'Appendix 5 Shared Mobility: contribution depends on development unit count bands.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Citywide',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, v.unit, v.costperunit, 'edinburgh', false
FROM ins_t002 t2
JOIN (VALUES
  ('units_3_7_lumpsum', 14000.00),
  ('units_8_15_lumpsum', 28000.00),
  ('units_16_50_lumpsum', 43000.00)
) AS v(unit, costperunit) ON TRUE;

/* ================================*/

/* NOTE: The per-use/scale-factor contribution Table 1 is not extractable as text from this PDF.
         A t001/t002 shell record is inserted; populate t003 when table values are available. */
INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
VALUES ('TRAM-OPERATION-TRAMLINES','Tram – Operation Tramlines','tram_contribution_zone','Appendix 6 Tram – contribution calculation (table not text-extractable in PDF).','active','2024-06-01','edinburgh',false)
ON CONFLICT (costcode) DO UPDATE SET updated=NOW();

/* ================================
   Appendix 7 – Traffic Regulation Orders
   ================================ */
INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
VALUES ('TRO-GENERAL','Traffic Regulation Orders','tro_citywide','Appendix 7 TRO/Stopping-up Orders – narrative basis (no standard per-unit rates in text extract).','active','2024-06-01','edinburgh',false)
ON CONFLICT (costcode) DO UPDATE SET updated=NOW();

/* ================================
   Appendix 8 – Legacy LDP Transport Contribution Zones
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('LEGACY-HERMISTON-PNR','Hermiston Park & Ride extension','legacy_transport_hermiston','Legacy LDP Transport: Hermiston P&R extension – cost per residential unit derived from appendix narrative.','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'transport_legacy_ldp',
    t1.costcode,
    'Hermiston Park & Ride extension',
    'Appendix 8 narrative sets cost per residential unit as £1,000 (derived from £10k per space / 10 units).',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Hermiston P&R contribution zone',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_residential_unit', 1000.00, 'edinburgh', false
FROM ins_t002 t2;

/* =======================*/
/* NOTE: Appendix 9 contains multiple interventions; per-unit values are not consistently extractable from text.
         Add rows here once the intervention tables/values are confirmed. */

/* ================================
   Appendix 10 – Healthcare Contribution Zones
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('HC-PER-UNIT','Healthcare – standard per-unit contribution','healthcare_citywide','Appendix 10 Healthcare: per-unit rate inferred from table calculations (units x £1,682.49).','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'healthcare',
    t1.costcode,
    'Healthcare contribution per residential unit',
    'Appendix 10 shows multiple calculations using £1,682.49 per unit; stored as the unit rate.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'Edinburgh',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_residential_unit', 1682.49, 'edinburgh', false
FROM ins_t002 t2;

/* ================================
   Appendix 11 – City Centre Public Realm Contribution Zone
   ================================ */
WITH ins_t001 AS (
  INSERT INTO t001costcodes (costcode, costcodename, contribution_catchment, costcodedescription, ccstatus, datevalidfrom, tenant_id, softdelete)
  VALUES ('PR-CITYCENTRE-PER-SQM','City Centre Public Realm – £/sqm contribution','public_realm_city_centre','Appendix 11 Public Realm: contribution rate for new commercial floorspace (office/retail) per sqm.','active','2024-06-01','edinburgh',false)
  ON CONFLICT (costcode) DO UPDATE SET updated=NOW()
  RETURNING gid, costcode
),
ins_t002 AS (
  INSERT INTO t002costcodesubgroup (
    gidt001, category, costcode, costcodedescription,
    policybasis, costbasesource,
    policydocument, geographicarea,
    datevalidfrom, tenant_id, softdelete
  )
  SELECT
    t1.gid,
    'public_realm',
    t1.costcode,
    'Public realm contribution rate per sqm (office/retail)',
    'Appendix 11 states public realm contribution of £141.04 per sqm for office/retail floorspace.',
    'CEC SG Draft June 2024',
    'City Plan 2030 SG Developer Contributions and Infrastructure Delivery (Draft for Consultation, June 2024)',
    'City Centre',
    '2024-06-01',
    'edinburgh',
    false
  FROM ins_t001 t1
  RETURNING gid
)
INSERT INTO t003costcodesubgroupunitrates (gidt002, unit, costperunit, tenant_id, softdelete)
SELECT t2.gid, 'per_sqm_office_retail', 141.04, 'edinburgh', false
FROM ins_t002 t2;
COMMIT;