
CREATE TABLE IF NOT EXISTS temp_site (
    id INTEGER PRIMARY KEY,
    loc_name TEXT NOT NULL,
    country_code TEXT NOT NULL
);


WITH src(id, loc_name, country_code) AS (
  VALUES
    (1, 'Petzenkirchen', 'AT'),
    (2, 'Wieselburg',    'AT'),
    (3, 'Tartu',         'EE'),
    (4, 'Brasília',      'BR'),
    (5, 'Hohenheim',     'DE'),
    (6, 'Rothamsted',    'GB')
)
INSERT INTO temp_site (id, loc_name, country_code)
SELECT id, loc_name, country_code FROM src
ON CONFLICT (id) DO UPDATE
SET loc_name = EXCLUDED.loc_name,
    country_code = EXCLUDED.country_code;

-- Render as a SQLPage table
SELECT
  'table'  AS component,
  'Sites (Demo)' AS title,
  'tblsite' AS id;

SELECT
  id        AS "ID",
  loc_name  AS "Site",
  country_code AS "Country"
FROM temp_site
ORDER BY id;



SELECT 
    'form' AS component,
    '' AS title,
    'field_plot' AS id, 
    CASE 
        WHEN $action = 'Edit' THEN 'Update field'
        WHEN :no_tillage IS NULL THEN 'Next'
        ELSE 'Add field'
    END AS validate,
    CASE 
        WHEN $action = 'Edit' AND $id IS NOT NULL THEN 'actions/update_plot.sql'
        WHEN  :no_tillage  IS NULL THEN ''
        ELSE 'actions/insert_field_plot.sql'
    END as action,
    'Reset' as reset;

SELECT 'header' AS type,
'Location Information' AS label;

-- SET variables FIRST, with proper type casting
SET existing_site_id = (
    SELECT id
    FROM site
    WHERE id = CASE
        WHEN :site_name IS NOT NULL AND :site_name ~ '^[0-9]+$' THEN CAST(:site_name AS INT)
        ELSE NULL
    END
);

SET site_loc_name = (
    COALESCE((SELECT loc_name
    FROM site
    WHERE id = CAST($existing_site_id AS INTEGER))::TEXT,:site_name)  -- Cast to INTEGER for comparison
);

-- Site Name dropdown
-- Here is the problem 
SELECT
    'site_name' AS name,
    'Site Name' AS label,
    'select' AS type,
    TRUE as create_new,
    TRUE AS dropdown,
    TRUE AS required,
    'Type the Site Name' as empty_option,
    3 AS width,
    NOT ($action IN ('Edit', 'INSERT')) AS disabled,
    $site_loc_name AS value,
    (SELECT json_agg(json_build_object('label', loc_name, 'value', id) ORDER BY loc_name) FROM temp_site) AS options;

-- Country dropdown
SELECT 'site_country' AS name,
       'Country' AS label,
       'select' AS type,
       3 AS width,
       TRUE AS required,
       TRUE AS dropdown,
       'Select a Country' AS empty_option,
       NOT ($action IN ('Edit', 'INSERT')) AS disabled,
       COALESCE(
         NULLIF(:site_country, ''),
         (
           SELECT v.country_code
           FROM (
             VALUES
               (1, 'AT'),  -- Petzenkirchen
               (2, 'AT'),  -- Wieselburg
               (3, 'EE'),  -- Tartu
               (4, 'BR')   -- Brasília
           ) AS v(site_id, country_code)
           WHERE v.site_id = CASE
                               WHEN :site_name ~ '^[0-9]+$' THEN CAST(:site_name AS INT)
                               ELSE NULL
                             END
         )
       ) AS value,
       (
         SELECT json_agg(
                  json_build_object(
                    'label', v.short_name_en || ' (' || v.id || ')',
                    'value', v.id
                  )
                  ORDER BY v.short_name_en
                )
         FROM (
           VALUES
             ('AT', 'Austria'),
             ('EE', 'Estonia'),
             ('BR', 'Brazil'),
             ('DE', 'Germany'),
             ('GB', 'United Kingdom')
         ) AS v(id, short_name_en)
       ) AS options;

-- Display fields
SELECT
    'site_name_valeu' AS name,
    'Site Name' AS label,
    'text' AS type,
    3 AS width,
    $site_loc_name AS value;

SELECT
    'site_id_valeu' AS name,
    'Site ID' AS label,
    'text' AS type,
    3 AS width,
    $existing_site_id AS value;


select 'number' as type, 'no_tillage' as name, 'Number of Primary tillage operation' as label, 'How many tillage operation?' as placeholder, 1 as min, 10 as max,
coalesce(NULLIF(:no_tillage, '')::int, 1) as value, 
:no_tillage is not null as readonly,
4 as width 
;

select 'number' as type, 'no_tillage_2' as name, 'Number of Secondary tillage operation' as label, 'How many tillage operation?' as placeholder, 0 as min, 10 as max,
coalesce(NULLIF(:no_tillage_2, '')::int, 0) as value, 
:no_tillage_2 is not null as readonly,
4 as width 
;


WITH RECURSIVE
params AS (
  SELECT
    GREATEST(0, COALESCE(NULLIF(:no_tillage, '')::int, 0))   AS n_primary,
    GREATEST(0, COALESCE(NULLIF(:no_tillage_2, '')::int, 0)) AS n_secondary
),
primary_ids AS (
  SELECT 1 AS id FROM params p WHERE p.n_primary > 0
  UNION ALL
  SELECT id + 1 FROM primary_ids, params p WHERE id + 1 <= p.n_primary
),
secondary_ids AS (
  SELECT 1 AS id FROM params p WHERE p.n_secondary > 0
  UNION ALL
  SELECT id + 1 FROM secondary_ids, params p WHERE id + 1 <= p.n_secondary
),
tillage_ids AS (
  SELECT id, 'Primary'::text AS tillage_type FROM primary_ids
  UNION ALL
  SELECT id, 'Secondary'::text AS tillage_type FROM secondary_ids
),
kinds AS (           -- generate rows per slot in this order
  SELECT 'select'::text AS kind, 0 AS ord
  UNION ALL SELECT 'date', 1
  UNION ALL SELECT 'depth', 2   -- number input (Primary only)
)
SELECT
  CASE WHEN k.kind = 'select' THEN 'select'
       WHEN k.kind = 'date'   THEN 'date'
       ELSE 'number'
  END AS type,

  CASE
    WHEN k.kind = 'select' AND t.tillage_type = 'Primary'  THEN 'primary_tillage_' || t.id || '[]'
    WHEN k.kind = 'select'                                 THEN 'secondary_tillage_' || t.id || '[]'
    WHEN k.kind = 'date'   AND t.tillage_type = 'Primary'  THEN 'primary_tillage_management_date_' || t.id || '[]'
    WHEN k.kind = 'date'                                   THEN 'secondary_tillage_management_date_' || t.id || '[]'
    WHEN k.kind = 'depth'  AND t.tillage_type = 'Primary'  THEN 'primary_plough_depth_cm_' || t.id || '[]'
    ELSE 'secondary_plough_depth_cm_' || t.id || '[]'
  END AS name,

  CASE
    WHEN k.kind = 'select'
      THEN  t.tillage_type || ' Tillage operation ' || t.id 
    WHEN k.kind = 'date'
      THEN 'Operation date ' || t.id 
    ELSE 'Tillage Depth ' || t.id || ' [cm] ' 
  END AS label,

CASE WHEN k.kind = 'select' THEN TRUE ELSE NULL END AS dropdown,
  CASE WHEN k.kind = 'select' THEN TRUE ELSE NULL END AS multiple,

  CASE WHEN k.kind = 'select' THEN 5
       WHEN k.kind = 'date'   THEN 3
       ELSE 4
  END AS width,

  CASE WHEN k.kind = 'select'
       THEN 'Select ' || lower(t.tillage_type) || ' tillage operation'
       ELSE NULL
  END AS empty_option,

  NOT ($action IN ('Edit', 'INSERT')) AS disabled,

  -- number-specific attributes (only used for depth rows)
  CASE WHEN k.kind = 'depth' THEN 'any' ELSE NULL END AS step,
  CASE WHEN k.kind = 'depth' THEN 0     ELSE NULL END AS min,
  CASE WHEN k.kind = 'depth' THEN 100   ELSE NULL END AS max,

  /* Keep value as TEXT across all rows */
  CASE
    /* --- Selected operation defaults (Primary) --- */
    WHEN k.kind = 'select' AND t.tillage_type = 'Primary' AND t.id = 1 THEN
      COALESCE(
        NULLIF(:primary_tillage,'')::int,
        (
          SELECT pms.operation_id
          FROM plot_management_schedule pms
          JOIN management_operations mo ON pms.operation_id = mo.id
          WHERE pms.plot_id = CAST($id AS INTEGER)
            AND mo.operation_type = 'Primary'
          ORDER BY pms.operation_date DESC
          LIMIT 1
        )
      )::text
    WHEN k.kind = 'select' AND t.tillage_type = 'Primary' THEN
      (
        SELECT pms.operation_id
        FROM plot_management_schedule pms
        JOIN management_operations mo ON pms.operation_id = mo.id
        WHERE pms.plot_id = CAST($id AS INTEGER)
          AND mo.operation_type = 'Primary'
        ORDER BY pms.operation_date DESC
        OFFSET (t.id - 1) LIMIT 1
      )::text

    /* --- Selected operation defaults (Secondary) --- */
    WHEN k.kind = 'select' AND t.tillage_type = 'Secondary' AND t.id = 1 THEN
      COALESCE(
        NULLIF(:secondary_tillage,'')::int,
        (
          SELECT pms.operation_id
          FROM plot_management_schedule pms
          JOIN management_operations mo ON pms.operation_id = mo.id
          WHERE pms.plot_id = CAST($id AS INTEGER)
            AND mo.operation_type = 'Secondary'
          ORDER BY pms.operation_date DESC
          LIMIT 1
        )
      )::text
    WHEN k.kind = 'select' AND t.tillage_type = 'Secondary' THEN
      (
        SELECT pms.operation_id
        FROM plot_management_schedule pms
        JOIN management_operations mo ON pms.operation_id = mo.id
        WHERE pms.plot_id = CAST($id AS INTEGER)
          AND mo.operation_type = 'Secondary'
        ORDER BY pms.operation_date DESC
        OFFSET (t.id - 1) LIMIT 1
      )::text

    /* --- Management date defaults (both types) --- */
    WHEN k.kind = 'date' THEN
      COALESCE(
        NULLIF(
          CASE
            WHEN t.tillage_type = 'Primary'   AND t.id = 1 THEN :primary_tillage_management_date
            WHEN t.tillage_type = 'Secondary' AND t.id = 1 THEN :secondary_tillage_management_date
            ELSE NULL
          END,''),
        (
          SELECT pms.operation_date::text
          FROM plot_management_schedule pms
          JOIN management_operations mo ON pms.operation_id = mo.id
          WHERE pms.plot_id = CAST($id AS INTEGER)
            AND mo.operation_type = t.tillage_type
          ORDER BY pms.operation_date DESC
          OFFSET (t.id - 1) LIMIT 1
        )
      )

    /* --- Tillage Depth defaults (now for BOTH types) --- */
    WHEN k.kind = 'depth' AND t.tillage_type = 'Primary' AND t.id = 1 THEN
      COALESCE(
        (NULLIF(:plough_depth_cm,'')::real)::text,
        (
          SELECT COALESCE(pms.plough_depth_cm, 0)::text
          FROM plot_management_schedule pms
          JOIN management_operations mo ON pms.operation_id = mo.id
          WHERE pms.plot_id = CAST($id AS INTEGER)
            AND mo.operation_type = t.tillage_type
          ORDER BY pms.operation_date DESC
          LIMIT 1
        ),
        '0'
      )
    WHEN k.kind = 'depth' AND t.tillage_type = 'Primary' THEN
      COALESCE(
        (
          SELECT COALESCE(pms.plough_depth_cm, 0)::text
          FROM plot_management_schedule pms
          JOIN management_operations mo ON pms.operation_id = mo.id
          WHERE pms.plot_id = CAST($id AS INTEGER)
            AND mo.operation_type = t.tillage_type
          ORDER BY pms.operation_date DESC
          OFFSET (t.id - 1) LIMIT 1
        ),
        '0'
      )

    ELSE NULL
  END AS value,

  CASE WHEN k.kind = 'select' THEN
    (
      SELECT json_agg(
               json_build_object('label', mo.operation_name, 'value', mo.id::text)
               ORDER BY mo.operation_name
             )
      FROM management_operations mo
      WHERE mo.operation_type = t.tillage_type   -- applies to 'Primary' and 'Secondary'
    )
  ELSE NULL::json END AS options

FROM tillage_ids t
CROSS JOIN kinds k
WHERE NOT (k.kind = 'depth' AND t.tillage_type = 'Secondary') 
ORDER BY
  CASE t.tillage_type WHEN 'Primary' THEN 0 ELSE 1 END,
  t.id,
  k.ord;