/* @name getPageInfoByIds */
WITH input_pages AS (SELECT unnest(:pageIds::bigint[]) AS pageid),
input_objects AS (
  SELECT ip.pageid, object.colid AS object, object.ord
  FROM input_pages ip
  JOIN public."tPg" tp ON tp."Pg" = ip.pageid
  JOIN LATERAL unnest(tp."Cols") WITH ORDINALITY AS object(colid, ord) ON true
),
step1_and_2_combined AS (
  SELECT io.pageid, ti."Item", ti."Object" AS object, tc."Row" AS row, io.ord
  FROM input_objects io
  JOIN public."tItem" ti ON ti."Object" = io.object
  JOIN public."tCell" tc ON tc."Items"::bigint[] @> ARRAY[ti."Item"]
  GROUP BY io.pageid, ti."Item", ti."Object", tc."Row", io.ord
),
step3_items_per_row AS (
  SELECT tc."Row" AS row, tc."Items" AS items, s12.pageid, s12.object, s12.ord
  FROM public."tCell" tc
  JOIN step1_and_2_combined s12 ON s12.row = tc."Row"
  WHERE tc."Col" = :colNameColId
),
unnested_items AS (
  SELECT s3.pageid, s3.row, s3.object, s3.ord, unnest(s3.items) AS item
  FROM step3_items_per_row s3
),
col_font_styles AS (
  SELECT "Object" AS colid, MAX("FontStyle") AS colfs
  FROM public."tFormat"
  GROUP BY "Object"
),
cols_with_names AS (
  SELECT ui.pageid, ui.object AS colid, ti."JSON" AS colname, cfs.colfs, ui.ord
  FROM unnested_items ui
  JOIN public."tItem" ti ON ti."Item" = ui.item
  LEFT JOIN col_font_styles cfs ON cfs.colid = ui.object
),
page_info AS (
  SELECT 
    p."Pg" AS pageid,
    (SELECT i."JSON"
     FROM public."tRow" r
     JOIN public."tCell" c ON c."Row" = r."Row" AND c."Col" = :pgIdColId
     JOIN public."tItem" i_match ON i_match."Object" = p."Pg" AND i_match."Item" = ANY(c."Items")
     JOIN public."tCell" c_name ON c_name."Row" = r."Row" AND c_name."Col" = :pgNameColId
     JOIN public."tItem" i ON i."Item" = ANY(c_name."Items")
     WHERE r."Pg" = :masterPgId
     LIMIT 1) AS "PageName"
  FROM public."tPg" p
  JOIN input_pages ip ON ip.pageid = p."Pg"
),
page_font_styles AS (
  SELECT "Object" AS pageid, MAX("FontStyle") AS pagefs
  FROM public."tFormat"
  WHERE "Object" IN (SELECT pageid FROM input_pages)
  GROUP BY "Object"
)
SELECT 
  cwn.pageid::text AS "PageID",
  pi."PageName",
  pfs.pagefs AS "PageFS",
  cwn.colid::text AS "ColID",
  cwn.colname AS "ColName",
  cwn.colfs AS "ColFS"
FROM cols_with_names cwn
LEFT JOIN page_info pi ON pi.pageid = cwn.pageid
LEFT JOIN page_font_styles pfs ON pfs.pageid = cwn.pageid
ORDER BY cwn.pageid, cwn.ord;

/* @name getRowsByPageIds */
WITH pg_ids AS (SELECT unnest(:pageIds::bigint[]) AS "Pg"),
pg_data AS (
  SELECT "Pg", "Cols"
  FROM public."tPg"
  WHERE "Pg" IN (SELECT "Pg" FROM pg_ids)
),
rows AS (
  SELECT "Pg", "Row", "RowLevel", "ParentRow"
  FROM public."tRow"
  WHERE "Pg" IN (SELECT "Pg" FROM pg_ids)
),
cols AS (
  SELECT "Pg", unnest("Cols") AS "Col"
  FROM pg_data
),
grid AS (
  SELECT r."Pg", r."Row", r."RowLevel", r."ParentRow", c."Col"
  FROM rows r
  JOIN cols c ON r."Pg" = c."Pg"
),
cell_with_indexed_items AS (
  SELECT
    tc."Row",
    tc."Col",
    tc."Cell",
    tc."Items",
    item_with_index.item_id AS "ItemID",
    item_with_index.idx AS "ItemOrder"
  FROM public."tCell" tc
  LEFT JOIN LATERAL (
    SELECT
      item_id,
      ordinality - 1 AS idx
    FROM unnest(tc."Items") WITH ORDINALITY AS t(item_id, ordinality)
  ) AS item_with_index ON TRUE
),
item_data_with_type_name AS (
  SELECT
    cwi."Row",
    cwi."Col",
    cwi."Cell",
    cwi."Items",
    cwi."ItemID",
    cwi."ItemOrder",
    i."Item",
    i."DateTime",
    i."DataType",
    i."Object",
    i."Text",
    i."JSON",
    (
      SELECT tf."FontStyle"
      FROM public."tFormat" tf
      WHERE tf."Object" = cwi."ItemID"
      ORDER BY tf."Format" ASC
      LIMIT 1
    ) AS "ItemFS",
    dt_name."DataTypeName",
    CASE
      WHEN dt_name."DataTypeName" = 'DropDown' THEN (
        SELECT i2."JSON"
        FROM public."tCell" c2,
             unnest(c2."Items") AS item_id2
        JOIN public."tItem" i2 ON i2."Item" = item_id2
        WHERE c2."Row" = i."Object" AND c2."Col" = :tokenId
        ORDER BY c2."Cell"
        LIMIT 1
      )
      ELSE NULL
    END AS "DropDownName"
  FROM cell_with_indexed_items cwi
  LEFT JOIN public."tItem" i ON i."Item" = cwi."ItemID"
  LEFT JOIN LATERAL (
    SELECT ti."JSON"->>:langId AS "DataTypeName"
    FROM public."tCell" dtc,
         unnest(dtc."Items") AS dt_item_id
    JOIN public."tItem" ti ON ti."Item" = dt_item_id
    WHERE dtc."Row" = i."DataType" AND dtc."Col" = :unitId
    ORDER BY dtc."Cell"
    LIMIT 1
  ) AS dt_name ON TRUE
),
col_format AS (
  SELECT DISTINCT ON ("Object")
    "Object" AS "ColID",
    "FontStyle" AS "ColFS",
    "Formula"
  FROM public."tFormat"
  WHERE "Object" IN (SELECT "Col" FROM cols)
  ORDER BY "Object", "Format" ASC
)
SELECT
  g."Pg",
  g."Row" AS "RowID",
  g."RowLevel"::text,
  g."ParentRow",
  (
    SELECT tf0."FontStyle"
    FROM public."tFormat" tf0
    WHERE tf0."Object" = g."Row"
    ORDER BY tf0."Format" ASC
    LIMIT 1
  ) AS "RowFS",
  g."Col" AS "ColID",
  cf."ColFS",
  cf."Formula",
  cid."Cell" AS "CellID",
  cid."Items" AS "CellItems",
  (
    SELECT tf1."FontStyle"
    FROM public."tFormat" tf1
    WHERE tf1."Object" = cid."Cell"
    ORDER BY tf1."Format" ASC
    LIMIT 1
  ) AS "CellFS",
  cid."ItemID",
  cid."ItemOrder",
  cid."ItemFS",
  cid."DataType",
  cid."DataTypeName",
  cid."DropDownName",
  cid."DateTime",
  cid."Object",
  cid."Text",
  cid."JSON"
FROM grid g
LEFT JOIN item_data_with_type_name cid
  ON cid."Row" = g."Row" AND cid."Col" = g."Col"
LEFT JOIN col_format cf
  ON cf."ColID" = g."Col"
ORDER BY g."Pg", g."Row", g."Col", cid."ItemOrder";

/* @name getObjectName */
SELECT * FROM 
  public."tCell" c,
  unnest(c."Items") AS item_id
JOIN 
  public."tItem" i ON i."Item" = item_id
WHERE
  c."Row" = :rowId AND c."Col" = :colId
ORDER BY 
  c."Cell" ASC;

/* @name getPagePageInfo */
WITH page_info AS (
  SELECT 
    p."Pg" AS pageid,
    (
      SELECT i."JSON"->>:langId
      FROM public."tRow" r
      JOIN public."tCell" c ON c."Row" = r."Row" AND c."Col" = :pgIdColId
      JOIN public."tItem" i_match ON i_match."Object" = p."Pg" AND i_match."Item" = ANY(c."Items")
      JOIN public."tCell" c_name ON c_name."Row" = r."Row" AND c_name."Col" = :pgNameColId
      JOIN public."tItem" i ON i."Item" = ANY(c_name."Items")
      WHERE r."Pg" = :masterPgId
      LIMIT 1
    ) AS "PageName"
  FROM public."tPg" p
)
SELECT t."Pg" As "PageId", pi."PageName"
FROM public."tRow" t
LEFT JOIN page_info pi ON pi.pageid = t."Pg"
WHERE t."Row" = :pageId
ORDER BY t."Row" ASC;
