-- 026_opportunity_field_meta.sql
-- Opportunity module alignment, migration 3 of 5
-- (docs/2026-08-04-opportunity-module-alignment.md)
--
-- The field meta system: sections, categories, sub-categories, fields, dropdown
-- options, the five typed EAV value tables with their history companions, and the
-- entity history table. Structure only — the section, category and static field rows
-- are seeded by 027, and the history triggers arrive in 028.
--
-- Requires 025 (opportunity.opportunities exists and holds the data).
--
-- SHAPES COME FROM THE LIVE projects SCHEMA, not from the baseline dump, which stops
-- at migration 005 and is missing all of these:
--   009  field_autocomplete_type / field_autocomplete_helper_id + the unique index
--        on field_form_id
--   018  history_action on the five *_history tables
--   021  field_options_source, field_section_icon
-- Every object below is written out explicitly with its own sequence. Nothing is
-- copied with LIKE, which would have carried nextval() defaults pointing at the
-- projects sequences.
--
-- THREE PROJECTS ARTEFACTS ARE DELIBERATELY NOT REPRODUCED (Dave, 2026-08-05):
--   · project_field_sections has a UNIQUE constraint but no primary key — here it is
--     a primary key. Foreign keys resolve against either, so nothing else changes.
--   · project_field_values_text_history's primary key is named ..._unique while its
--     four siblings are named ..._pk — here all five are ..._pk.
--   · project_field_sub_category's sequence is still called
--     newtable_field_sub_category_id_seq — here it is named for its table.
-- (The fourth, the duplicate index on project_status, was dealt with in 024.)
--
-- SUB-CATEGORIES ARE CREATED EVEN THOUGH THE UI IS HIDDEN. The rail and the field
-- modal do not offer them, but fieldMetaModuleConfig() names the table, admin_save.php
-- still carries its modes, and opportunity_fields.field_sub_category needs something to
-- point at. Hidden, not removed — same as the other modules.
--
-- NO opportunity_file_uploads TABLE. Uploads live in the shared public.file_uploads,
-- read through opportunity.vw_file_uploads (created in 025).
-- projects.project_file_uploads exists but is referenced by no PHP or JS at all.

-- ── 1. Sections, categories, sub-categories ─────────────────────────────────

CREATE SEQUENCE opportunity.opportunity_field_sections_field_section_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_sections (
    field_section_id            integer NOT NULL DEFAULT nextval('opportunity.opportunity_field_sections_field_section_id_seq'::regclass),
    field_section_name          varchar,
    field_section_display_order integer,
    field_section_ref           varchar,
    field_section_icon          varchar(50),
    CONSTRAINT opportunity_field_sections_pk PRIMARY KEY (field_section_id)
);

ALTER SEQUENCE opportunity.opportunity_field_sections_field_section_id_seq
    OWNED BY opportunity.opportunity_field_sections.field_section_id;

CREATE SEQUENCE opportunity.opportunity_field_category_field_category_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_category (
    field_category_id            integer NOT NULL DEFAULT nextval('opportunity.opportunity_field_category_field_category_id_seq'::regclass),
    field_category_name          varchar NOT NULL,
    field_category_ref           varchar,
    field_section_id             integer,
    field_category_display_order integer,
    CONSTRAINT opportunity_field_category_pk PRIMARY KEY (field_category_id),
    CONSTRAINT opportunity_field_category_sections_fk FOREIGN KEY (field_section_id)
        REFERENCES opportunity.opportunity_field_sections(field_section_id)
);

ALTER SEQUENCE opportunity.opportunity_field_category_field_category_id_seq
    OWNED BY opportunity.opportunity_field_category.field_category_id;

CREATE SEQUENCE opportunity.opportunity_field_sub_category_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_sub_category (
    field_sub_category_id   integer NOT NULL DEFAULT nextval('opportunity.opportunity_field_sub_category_id_seq'::regclass),
    field_sub_category_name varchar NOT NULL,
    field_sub_category_ref  varchar,
    CONSTRAINT opportunity_field_sub_category_pk PRIMARY KEY (field_sub_category_id)
);

ALTER SEQUENCE opportunity.opportunity_field_sub_category_id_seq
    OWNED BY opportunity.opportunity_field_sub_category.field_sub_category_id;

-- ── 2. Fields ───────────────────────────────────────────────────────────────

CREATE SEQUENCE opportunity.opportunity_fields_field_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_fields (
    field_id                     bigint  NOT NULL DEFAULT nextval('opportunity.opportunity_fields_field_id_seq'::regclass),
    field_section                integer,
    field_category               integer,
    field_sub_category           integer,
    field_display_order          integer,
    field_data_type              varchar,
    field_name                   varchar,
    field_input_type             varchar,
    field_form_id                varchar,
    field_required               boolean,
    field_active                 boolean DEFAULT true,
    field_spacer_after           integer,
    field_type                   varchar(10),
    field_autocomplete_type      varchar(30),
    field_autocomplete_helper_id varchar(100),
    field_options_source         varchar(50),
    CONSTRAINT opportunity_fields_pk PRIMARY KEY (field_id),
    CONSTRAINT opportunity_fields_sections_fk FOREIGN KEY (field_section)
        REFERENCES opportunity.opportunity_field_sections(field_section_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_fields_category_fk FOREIGN KEY (field_category)
        REFERENCES opportunity.opportunity_field_category(field_category_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_fields_sub_category_fk FOREIGN KEY (field_sub_category)
        REFERENCES opportunity.opportunity_field_sub_category(field_sub_category_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_fields_field_id_seq
    OWNED BY opportunity.opportunity_fields.field_id;

-- Migration 009's guarantee: a form id identifies exactly one field. admin_save.php
-- enforces form-id/helper-id uniqueness in both directions on top of this.
CREATE UNIQUE INDEX opportunity_fields_field_form_id_uq
    ON opportunity.opportunity_fields USING btree (field_form_id);

COMMENT ON COLUMN opportunity.opportunity_fields.field_type IS
    'static = presentation here, storage is a real column on opportunity.opportunities whitelisted in opportunity_save.php; dynamic = stored in the EAV value tables.';

CREATE SEQUENCE opportunity.opportunity_field_dropdown_options_dropdown_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_dropdown_options (
    dropdown_id            bigint  NOT NULL DEFAULT nextval('opportunity.opportunity_field_dropdown_options_dropdown_id_seq'::regclass),
    field_id               integer NOT NULL,
    dropdown_value         varchar,
    dropdown_name          varchar,
    dropdown_display_order integer NOT NULL DEFAULT 0,
    dropdown_active        boolean NOT NULL DEFAULT true,
    CONSTRAINT opportunity_field_dropdown_options_pk PRIMARY KEY (dropdown_id),
    CONSTRAINT opportunity_field_dropdown_options_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_dropdown_options_dropdown_id_seq
    OWNED BY opportunity.opportunity_field_dropdown_options.dropdown_id;

CREATE INDEX opportunity_field_dropdown_options_field_order_idx
    ON opportunity.opportunity_field_dropdown_options USING btree (field_id, dropdown_display_order);

-- ── 3. EAV value tables and their history companions ────────────────────────
--
-- The UNIQUE (field_id, opportunity_id) on each value table is what the save
-- endpoint's ON CONFLICT upsert needs — without it the numeric and boolean write
-- blocks fixed on 2026-08-04 have nothing to conflict on.
--
-- History tables carry history_action (migration 018) with its default kept, so any
-- insert that does not name the column still succeeds.

-- text ----------------------------------------------------------------------
CREATE SEQUENCE opportunity.opportunity_field_values_text_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_text (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_text_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           text,
    record_datetime timestamp without time zone,
    record_user     integer,
    CONSTRAINT opportunity_field_values_text_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_text_field_id_opportunity_id_key UNIQUE (field_id, opportunity_id),
    CONSTRAINT opportunity_field_values_text_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_text_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_text_value_id_seq
    OWNED BY opportunity.opportunity_field_values_text.value_id;

CREATE SEQUENCE opportunity.opportunity_field_values_text_history_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_text_history (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_text_history_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           text,
    record_datetime timestamp without time zone,
    record_user     integer,
    history_action  varchar(10) NOT NULL DEFAULT 'UPDATE',
    CONSTRAINT opportunity_field_values_text_history_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_text_history_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_text_history_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_text_history_value_id_seq
    OWNED BY opportunity.opportunity_field_values_text_history.value_id;

-- int -----------------------------------------------------------------------
CREATE SEQUENCE opportunity.opportunity_field_values_int_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_int (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_int_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           bigint,
    record_datetime timestamp without time zone,
    record_user     integer,
    CONSTRAINT opportunity_field_values_int_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_int_field_id_opportunity_id_key UNIQUE (field_id, opportunity_id),
    CONSTRAINT opportunity_field_values_int_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_int_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_int_value_id_seq
    OWNED BY opportunity.opportunity_field_values_int.value_id;

CREATE SEQUENCE opportunity.opportunity_field_values_int_history_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_int_history (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_int_history_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           bigint,
    record_datetime timestamp without time zone,
    record_user     integer,
    history_action  varchar(10) NOT NULL DEFAULT 'UPDATE',
    CONSTRAINT opportunity_field_values_int_history_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_int_history_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_int_history_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_int_history_value_id_seq
    OWNED BY opportunity.opportunity_field_values_int_history.value_id;

-- numeric -------------------------------------------------------------------
CREATE SEQUENCE opportunity.opportunity_field_values_numeric_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_numeric (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_numeric_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           numeric,
    record_datetime timestamp without time zone,
    record_user     integer,
    CONSTRAINT opportunity_field_values_numeric_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_numeric_field_id_opportunity_id_key UNIQUE (field_id, opportunity_id),
    CONSTRAINT opportunity_field_values_numeric_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_numeric_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_numeric_value_id_seq
    OWNED BY opportunity.opportunity_field_values_numeric.value_id;

CREATE SEQUENCE opportunity.opportunity_field_values_numeric_history_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_numeric_history (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_numeric_history_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           numeric,
    record_datetime timestamp without time zone,
    record_user     integer,
    history_action  varchar(10) NOT NULL DEFAULT 'UPDATE',
    CONSTRAINT opportunity_field_values_numeric_history_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_numeric_history_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_numeric_history_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_numeric_history_value_id_seq
    OWNED BY opportunity.opportunity_field_values_numeric_history.value_id;

-- date ----------------------------------------------------------------------
CREATE SEQUENCE opportunity.opportunity_field_values_date_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_date (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_date_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           date,
    record_datetime timestamp without time zone,
    record_user     integer,
    CONSTRAINT opportunity_field_values_date_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_date_field_id_opportunity_id_key UNIQUE (field_id, opportunity_id),
    CONSTRAINT opportunity_field_values_date_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_date_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_date_value_id_seq
    OWNED BY opportunity.opportunity_field_values_date.value_id;

CREATE SEQUENCE opportunity.opportunity_field_values_date_history_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_date_history (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_date_history_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           date,
    record_datetime timestamp without time zone,
    record_user     integer,
    history_action  varchar(10) NOT NULL DEFAULT 'UPDATE',
    CONSTRAINT opportunity_field_values_date_history_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_date_history_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_date_history_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_date_history_value_id_seq
    OWNED BY opportunity.opportunity_field_values_date_history.value_id;

-- boolean -------------------------------------------------------------------
CREATE SEQUENCE opportunity.opportunity_field_values_boolean_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_boolean (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_boolean_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           boolean,
    record_datetime timestamp without time zone,
    record_user     integer,
    CONSTRAINT opportunity_field_values_boolean_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_boolean_field_id_opportunity_id_key UNIQUE (field_id, opportunity_id),
    CONSTRAINT opportunity_field_values_boolean_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_boolean_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_boolean_value_id_seq
    OWNED BY opportunity.opportunity_field_values_boolean.value_id;

CREATE SEQUENCE opportunity.opportunity_field_values_boolean_history_value_id_seq
    START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunity_field_values_boolean_history (
    value_id        bigint NOT NULL DEFAULT nextval('opportunity.opportunity_field_values_boolean_history_value_id_seq'::regclass),
    field_id        integer,
    opportunity_id  integer,
    value           boolean,
    record_datetime timestamp without time zone,
    record_user     integer,
    history_action  varchar(10) NOT NULL DEFAULT 'UPDATE',
    CONSTRAINT opportunity_field_values_boolean_history_pk PRIMARY KEY (value_id),
    CONSTRAINT opportunity_field_values_boolean_history_fields_fk FOREIGN KEY (field_id)
        REFERENCES opportunity.opportunity_fields(field_id) ON UPDATE RESTRICT,
    CONSTRAINT opportunity_field_values_boolean_history_opportunities_fk FOREIGN KEY (opportunity_id)
        REFERENCES opportunity.opportunities(opportunity_id) ON UPDATE RESTRICT
);

ALTER SEQUENCE opportunity.opportunity_field_values_boolean_history_value_id_seq
    OWNED BY opportunity.opportunity_field_values_boolean_history.value_id;

-- ── 4. Entity history ───────────────────────────────────────────────────────
--
-- Mirrors projects_history: every entity column, plus who/what/when. No foreign keys,
-- as projects_history has none — history must survive its subject.

CREATE SEQUENCE opportunity.opportunities_history_history_id_seq
    AS integer START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;

CREATE TABLE opportunity.opportunities_history (
    history_id            integer NOT NULL DEFAULT nextval('opportunity.opportunities_history_history_id_seq'::regclass),
    opportunity_id        integer NOT NULL,
    parent_opportunity_id integer,
    opportunity_name      varchar(150),
    opportunity_status_id integer,
    opportunity_manager   integer,
    geom                  public.geometry,
    created_datetime      timestamp without time zone,
    created_user          integer,
    modified_datetime     timestamp without time zone,
    modified_user         integer,
    company_id            integer,
    account_id            integer,
    stocklist_id          integer,
    cover_image_url       varchar(100),
    history_action        varchar(10) NOT NULL,
    history_datetime      timestamp without time zone NOT NULL DEFAULT now(),
    history_user          integer,
    CONSTRAINT opportunities_history_pk PRIMARY KEY (history_id)
);

ALTER SEQUENCE opportunity.opportunities_history_history_id_seq
    OWNED BY opportunity.opportunities_history.history_id;

CREATE INDEX opportunities_history_geom_idx
    ON opportunity.opportunities_history USING gist (geom);
CREATE INDEX opportunities_history_history_datetime_idx
    ON opportunity.opportunities_history USING btree (history_datetime);
CREATE INDEX opportunities_history_opportunity_id_idx
    ON opportunity.opportunities_history USING btree (opportunity_id);
