-- 011_wayleave_field_meta_align.sql
-- Align the wayleave field-meta tables with the projects naming convention
-- (docs/2026-07-15-static-fields-to-meta.md — enables the shared renderer,
-- get_update_form.php and the admin fields UI to treat wayleave like the other
-- three modules with no aliasing).
--
-- Wayleave's meta tables predated the common shape (id/section_name/... instead
-- of field_section_id/field_section_name/...) and carried extras the other
-- modules don't have (is_active + display_order on lookups/options, a
-- category_id hierarchy on sub categories, boolean field_spacer_after). The
-- module is unfinished, so this mirrors projects exactly: rename to the common
-- columns, add the missing ref/icon columns, and drop the extras — inactive
-- rows are deleted rather than carried over.
--
-- Fails loudly if an inactive section/category/sub-category is still referenced
-- by a field (FK violation on the DELETE) — reassign or delete those fields
-- manually, then re-run.

-- ── Sections → mirror projects.project_field_sections ────────────────────────
DELETE FROM wayleave.agreement_field_sections WHERE is_active = false;
ALTER TABLE wayleave.agreement_field_sections DROP COLUMN is_active;
ALTER TABLE wayleave.agreement_field_sections RENAME COLUMN id TO field_section_id;
ALTER TABLE wayleave.agreement_field_sections RENAME COLUMN section_name TO field_section_name;
ALTER TABLE wayleave.agreement_field_sections RENAME COLUMN display_order TO field_section_display_order;
ALTER TABLE wayleave.agreement_field_sections ADD COLUMN field_section_ref character varying;
ALTER TABLE wayleave.agreement_field_sections ADD COLUMN field_section_icon character varying(50);
UPDATE wayleave.agreement_field_sections
SET field_section_ref = regexp_replace(lower(replace(field_section_name, ' ', '_')), '[^a-z0-9_]', '', 'g');

-- ── Categories → mirror projects.project_field_category ──────────────────────
DELETE FROM wayleave.agreement_field_category WHERE is_active = false;
ALTER TABLE wayleave.agreement_field_category DROP COLUMN is_active;
ALTER TABLE wayleave.agreement_field_category RENAME COLUMN id TO field_category_id;
ALTER TABLE wayleave.agreement_field_category RENAME COLUMN section_id TO field_section_id;
ALTER TABLE wayleave.agreement_field_category RENAME COLUMN category_name TO field_category_name;
ALTER TABLE wayleave.agreement_field_category RENAME COLUMN display_order TO field_category_display_order;
ALTER TABLE wayleave.agreement_field_category ADD COLUMN field_category_ref character varying;
UPDATE wayleave.agreement_field_category
SET field_category_ref = regexp_replace(lower(replace(field_category_name, ' ', '_')), '[^a-z0-9_]', '', 'g');

-- ── Sub categories → mirror projects.project_field_sub_category ──────────────
-- projects has no category link or ordering on sub categories; the FK and the
-- (category_id, name) unique constraint drop with the column.
DELETE FROM wayleave.agreement_field_sub_category WHERE is_active = false;
ALTER TABLE wayleave.agreement_field_sub_category DROP COLUMN is_active;
ALTER TABLE wayleave.agreement_field_sub_category DROP COLUMN display_order;
ALTER TABLE wayleave.agreement_field_sub_category DROP COLUMN category_id;
ALTER TABLE wayleave.agreement_field_sub_category RENAME COLUMN id TO field_sub_category_id;
ALTER TABLE wayleave.agreement_field_sub_category RENAME COLUMN sub_category_name TO field_sub_category_name;
ALTER TABLE wayleave.agreement_field_sub_category ADD COLUMN field_sub_category_ref character varying;
UPDATE wayleave.agreement_field_sub_category
SET field_sub_category_ref = regexp_replace(lower(replace(field_sub_category_name, ' ', '_')), '[^a-z0-9_]', '', 'g');

-- ── Dropdown options → mirror projects.project_field_dropdown_options ────────
-- Options are ordered by dropdown_id like the other modules from here on.
DELETE FROM wayleave.agreement_field_dropdown_options WHERE is_active = false;
ALTER TABLE wayleave.agreement_field_dropdown_options DROP COLUMN is_active;
ALTER TABLE wayleave.agreement_field_dropdown_options DROP COLUMN display_order;
ALTER TABLE wayleave.agreement_field_dropdown_options RENAME COLUMN id TO dropdown_id;
ALTER TABLE wayleave.agreement_field_dropdown_options RENAME COLUMN option_value TO dropdown_value;
ALTER TABLE wayleave.agreement_field_dropdown_options RENAME COLUMN option_label TO dropdown_name;

-- ── agreement_fields ──────────────────────────────────────────────────────────
-- field_spacer_after: boolean → integer column width like the other modules
-- (true meant "add a spacer" — map to 3, the smallest spacer the renderers emit).
ALTER TABLE wayleave.agreement_fields ALTER COLUMN field_spacer_after DROP DEFAULT;
ALTER TABLE wayleave.agreement_fields ALTER COLUMN field_spacer_after DROP NOT NULL;
ALTER TABLE wayleave.agreement_fields ALTER COLUMN field_spacer_after TYPE integer
    USING (CASE WHEN field_spacer_after THEN 3 ELSE 0 END);

-- agreement_fields was born with UNIQUE (field_form_id)
-- (agreement_fields_field_form_id_key), so the extra index migration 009
-- created on the same column is redundant — drop it. IF EXISTS keeps this
-- correct whether or not 009 has been applied yet in a given environment.
DROP INDEX IF EXISTS wayleave.agreement_fields_field_form_id_uq;
