CREATE TABLE IF NOT EXISTS public.similar_hash(
  id_unit integer PRIMARY KEY,
  id_user integer NOT NULL,
  pdq_hash bigint[],
  takentime bigint,
  need_action boolean NOT NULL DEFAULT true,
  skip boolean NOT NULL DEFAULT false,
  is_major boolean NOT NULL,
  id_item integer NOT NULL
);
ALTER TABLE public.unit
DROP CONSTRAINT IF EXISTS unit_major_fk CASCADE,
DROP CONSTRAINT IF EXISTS unit_item_fk CASCADE,
ADD CONSTRAINT unit_major_fk UNIQUE (id, is_major),
ADD CONSTRAINT unit_item_fk UNIQUE (id, id_item);
ALTER TABLE public.similar_hash
DROP CONSTRAINT IF EXISTS id_unit_fk,
DROP CONSTRAINT IF EXISTS id_user_fk,
DROP CONSTRAINT IF EXISTS similar_hash_unit_item_fk,
DROP CONSTRAINT IF EXISTS similar_hash_unit_major_fk,
ADD CONSTRAINT id_unit_fk FOREIGN KEY (id_unit) REFERENCES public.unit (id) ON DELETE CASCADE,
ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES public.user_info (id) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT similar_hash_unit_item_fk FOREIGN KEY (id_unit, id_item) REFERENCES public.unit (id, id_item) ON UPDATE CASCADE,
ADD CONSTRAINT similar_hash_unit_major_fk FOREIGN KEY (id_unit, is_major) REFERENCES public.unit (id, is_major) ON UPDATE CASCADE;
CREATE INDEX IF NOT EXISTS similar_hash_skip_need_action_array_length_takentime_idx ON public.similar_hash (skip, need_action, (array_length(pdq_hash, 1)), takentime);
CREATE INDEX IF NOT EXISTS similar_hash_id_user_skip_need_action_idx ON public.similar_hash (id_user, skip, need_action);
CREATE TABLE IF NOT EXISTS public.similar_group(
  id SERIAL PRIMARY KEY,
  id_user integer NOT NULL,
  top_pick integer,
  item_count integer NOT NULL,
  update_at bigint NOT NULL,
  custom_top_pick boolean NOT NULL DEFAULT false,
  version bigint NOT NULL
);
ALTER TABLE public.similar_group
DROP CONSTRAINT IF EXISTS id_user_fk,
ADD CONSTRAINT id_user_fk FOREIGN KEY (id_user) REFERENCES public.user_info (id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE public.item
ADD COLUMN IF NOT EXISTS id_similar_group integer,
ADD COLUMN IF NOT EXISTS is_similar_top_pick boolean,
DROP CONSTRAINT IF EXISTS id_similar_group_fk,
ADD CONSTRAINT id_similar_group_fk FOREIGN KEY (id_similar_group) REFERENCES public.similar_group (id) ON DELETE SET NULL ON UPDATE CASCADE;
CREATE INDEX IF NOT EXISTS item_id_similar_group_idx ON public.item (id_similar_group);
CREATE INDEX IF NOT EXISTS item_is_similar_top_pick_idx ON public.item (is_similar_top_pick);
CREATE OR REPLACE FUNCTION update_similar_group_top_pick_func( group_id integer )
  RETURNS void
  LANGUAGE plpgsql
AS $$
DECLARE
  new_top_pick integer;
BEGIN
  new_top_pick := (
    SELECT i.id
    FROM item i
    JOIN unit u ON i.id = u.id_item
    WHERE i.id_similar_group = group_id
    ORDER BY (u.resolution->>'height')::integer * (u.resolution->>'width')::integer DESC, u.filesize DESC, u.takentime DESC
    LIMIT 1
  );
  UPDATE similar_group
  SET
    top_pick = new_top_pick,
    update_at = round(extract(epoch FROM now()) * 1000)
  WHERE
    id = group_id;
  UPDATE item
  SET
    is_similar_top_pick = true
  WHERE
    id = new_top_pick;
END;
$$;