-- OTE import (fn_ote_import_from_json) používá ON CONFLICT (market_source, interval_start).
-- Bez PK PostgreSQL hlásí chybu o ON CONFLICT.
--
-- Na TimescaleDB hypertable může ALTER TABLE … ADD PRIMARY KEY selhat s:
--   ERROR: chunk not found … _timescaledb_internal._hyper_*_chunk
-- když v _timescaledb_catalog.chunk zůstaly řádky pro child tabulky, které fyzicky neexistují
-- (časté po pg_restore s paralelizací -j nebo neúplné obnově; viz timescale/timescaledb#4101).

CREATE OR REPLACE FUNCTION ems._repair_ts_orphan_chunk_rows(
  p_namespace name,
  p_rel name
)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
DECLARE
  hid           integer;
  chunk_ids     integer[];
  n             integer;
  has_dropped   boolean;
  q             text;
BEGIN
  SELECT h.id
  INTO hid
  FROM _timescaledb_catalog.hypertable h
  WHERE h.schema_name = p_namespace
    AND h.table_name = p_rel;

  IF hid IS NULL THEN
    RETURN 0;
  END IF;

  IF EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = '_timescaledb_functions'
      AND p.proname = 'remove_dropped_chunk_metadata'
  ) THEN
    PERFORM _timescaledb_functions.remove_dropped_chunk_metadata(hid);
  END IF;

  -- Sloupec chunk.dropped byl v novějším TimescaleDB odstraněn (metadata dropnutých chunků se maže).
  SELECT EXISTS (
    SELECT 1
    FROM pg_catalog.pg_attribute a
    JOIN pg_catalog.pg_class r ON r.oid = a.attrelid
    JOIN pg_catalog.pg_namespace n ON n.oid = r.relnamespace
    WHERE n.nspname = '_timescaledb_catalog'
      AND r.relname = 'chunk'
      AND a.attname = 'dropped'
      AND a.attnum > 0
      AND NOT a.attisdropped
  )
  INTO has_dropped;

  q := format(
    $fmt$
    SELECT coalesce(array_agg(c.id ORDER BY c.id), ARRAY[]::integer[])
    FROM _timescaledb_catalog.chunk c
    WHERE c.hypertable_id = %s
      %s
      AND NOT EXISTS (
        SELECT 1
        FROM pg_catalog.pg_class cl
        JOIN pg_catalog.pg_namespace ns ON ns.oid = cl.relnamespace
        WHERE ns.nspname = c.schema_name
          AND cl.relname = c.table_name
      )
    $fmt$,
    hid,
    CASE WHEN has_dropped THEN 'AND NOT c.dropped' ELSE '' END
  );

  EXECUTE q INTO chunk_ids;

  n := coalesce(array_length(chunk_ids, 1), 0);
  IF n = 0 THEN
    RETURN 0;
  END IF;

  UPDATE _timescaledb_catalog.chunk ch
  SET compressed_chunk_id = NULL
  WHERE ch.compressed_chunk_id = ANY (chunk_ids);

  DELETE FROM _timescaledb_catalog.compression_chunk_size cs
  WHERE cs.chunk_id = ANY (chunk_ids)
     OR cs.compressed_chunk_id = ANY (chunk_ids);

  DELETE FROM _timescaledb_catalog.chunk_constraint cc
  WHERE cc.chunk_id = ANY (chunk_ids);

  DELETE FROM _timescaledb_catalog.chunk_column_stats ccs
  WHERE ccs.chunk_id = ANY (chunk_ids);

  DELETE FROM _timescaledb_internal.bgw_policy_chunk_stats b
  WHERE b.chunk_id = ANY (chunk_ids);

  DELETE FROM _timescaledb_catalog.chunk c
  WHERE c.id = ANY (chunk_ids);

  RETURN n;
END;
$$;

SELECT ems._repair_ts_orphan_chunk_rows('ems', 'market_interval_price');

DO $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM pg_catalog.pg_class r
    JOIN pg_catalog.pg_namespace n ON n.oid = r.relnamespace
    WHERE n.nspname = 'ems'
      AND r.relname = 'market_interval_price'
      AND r.relkind = 'r'
  ) AND NOT EXISTS (
    SELECT 1
    FROM pg_catalog.pg_constraint c
    JOIN pg_catalog.pg_class r ON r.oid = c.conrelid
    JOIN pg_catalog.pg_namespace n ON n.oid = r.relnamespace
    WHERE n.nspname = 'ems'
      AND r.relname = 'market_interval_price'
      AND c.contype = 'p'
  ) THEN
    ALTER TABLE ems.market_interval_price
      ADD CONSTRAINT market_interval_price_pkey PRIMARY KEY (market_source, interval_start);
  END IF;
END $$;

-- Funkci znovu použije V033 pro telemetrické hypertably; drop až tam.
