CREATE TABLE IF NOT EXISTS public.concept (
  id SERIAL PRIMARY KEY,
  stem text NOT NULL,
  hidden boolean NOT NULL DEFAULT false,
  display_threshold smallint NOT NULL,
  confidence_threshold numeric(5, 4),
  parent integer[] NOT NULL,
  sort_index integer NOT NULL DEFAULT -1
);
CREATE TABLE IF NOT EXISTS public.concept_threshold (
  id_concept integer NOT NULL,
  version smallint NOT NULL,
  confidence_threshold numeric(5, 4) NOT NULL,
  PRIMARY KEY (id_concept, version)
);
ALTER TABLE
  public.concept_threshold
ADD
  CONSTRAINT concept_fk FOREIGN KEY (id_concept) REFERENCES public.concept (id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS public.concept_rawdata (
  id_unit integer NOT NULL,
  result json NOT NULL,
  need_migrate bool NOT NULL DEFAULT false,
  version integer NOT NULL,
  id_user integer NOT NULL,
  PRIMARY KEY (id_unit)
);
CREATE INDEX ON public.concept_rawdata (need_migrate);
ALTER TABLE
  public.concept_rawdata
ADD
  CONSTRAINT composite_unit_fk FOREIGN KEY (id_unit, id_user) REFERENCES unit (id, id_user) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS public.many_unit_has_many_concept (
  id_unit integer NOT NULL,
  id_concept integer NOT NULL,
  id_user integer NOT NULL,
  confidence numeric(5, 4) NOT NULL,
  PRIMARY KEY (id_unit, id_concept)
);
CREATE INDEX ON public.many_unit_has_many_concept (id_user, id_concept, confidence);
ALTER TABLE
  public.many_unit_has_many_concept
ADD
  CONSTRAINT composite_unit_fk FOREIGN KEY (id_unit, id_user) REFERENCES unit (id, id_user) MATCH FULL ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE
  public.many_unit_has_many_concept
ADD
  CONSTRAINT concept_fk FOREIGN KEY (id_concept) REFERENCES concept (id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS public.concept_album_additional (
  id_concept integer NOT NULL,
  id_user integer NOT NULL,
  item_count bigint NOT NULL,
  custom_cover_id_unit integer,
  PRIMARY KEY (id_user, id_concept)
);
ALTER TABLE
  public.concept_album_additional
ADD CONSTRAINT concept_fk FOREIGN KEY (id_concept) REFERENCES concept (id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE
  public.concept_album_additional
ADD CONSTRAINT user_info_fk FOREIGN KEY (id_user) REFERENCES user_info (id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE TABLE IF NOT EXISTS public.concept_synonym (
  lang smallint NOT NULL,
  id_concept integer NOT NULL,
  synonym text NOT NULL,
  priority smallint NOT NULL
);
CREATE INDEX ON public.concept_synonym (synonym);
ALTER TABLE
  public.concept_synonym
ADD
  CONSTRAINT concept_fk FOREIGN KEY (id_concept) REFERENCES concept (id) ON DELETE CASCADE ON UPDATE CASCADE;
CREATE OR REPLACE VIEW public.concept_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.id AS id_unit,
  array_agg(DISTINCT r.id_concept) AS id_concept,
  u.takentime
FROM unit u
  JOIN many_unit_has_many_concept r ON u.id = r.id_unit
WHERE u.is_major = true
GROUP BY u.id_user, u.id;
CREATE OR REPLACE VIEW public.level_1_concept_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.takentime,
  u.id AS id_unit,
  g.level_1 AS level,
  g.admin_1 AS admin,
  array_agg(DISTINCT r.id_concept) AS id_concept
FROM unit u
  JOIN many_unit_has_many_concept r ON u.id = r.id_unit
  JOIN geocoding g ON u.id_geocoding = g.id AND u.id_user = g.id_user
WHERE u.is_major = true AND u.id_geocoding IS NOT NULL
GROUP BY u.id_user, u.id, g.level_1, g.admin_1;
CREATE OR REPLACE VIEW public.level_2_concept_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.takentime,
  u.id AS id_unit,
  g.level_2 AS level,
  g.admin_2 AS admin,
  array_agg(DISTINCT r.id_concept) AS id_concept
FROM unit u
  JOIN many_unit_has_many_concept r ON u.id = r.id_unit
  JOIN geocoding g ON u.id_geocoding = g.id AND u.id_user = g.id_user
WHERE u.is_major = true AND u.id_geocoding IS NOT NULL
GROUP BY u.id_user, u.id, g.level_2, g.admin_2;
CREATE OR REPLACE VIEW public.level_3_concept_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.takentime,
  u.id AS id_unit,
  g.level_3 AS level,
  g.admin_3 AS admin,
  array_agg(DISTINCT r.id_concept) AS id_concept
FROM unit u
  JOIN many_unit_has_many_concept r ON u.id = r.id_unit
  JOIN geocoding g ON u.id_geocoding = g.id AND u.id_user = g.id_user
WHERE u.is_major = true AND u.id_geocoding IS NOT NULL
GROUP BY u.id_user, u.id, g.level_3, g.admin_3;
CREATE OR REPLACE VIEW public.level_4_concept_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.takentime,
  u.id AS id_unit,
  g.level_4 AS level,
  g.admin_4 AS admin,
  array_agg(DISTINCT r.id_concept) AS id_concept
FROM unit u
  JOIN many_unit_has_many_concept r ON u.id = r.id_unit
  JOIN geocoding g ON u.id_geocoding = g.id AND u.id_user = g.id_user
WHERE u.is_major = true AND u.id_geocoding IS NOT NULL
GROUP BY u.id_user, u.id, g.level_4, g.admin_4;
CREATE OR REPLACE VIEW public.level_5_concept_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.takentime,
  u.id AS id_unit,
  g.level_5 AS level,
  g.admin_5 AS admin,
  array_agg(DISTINCT r.id_concept) AS id_concept
FROM unit u
  JOIN many_unit_has_many_concept r ON u.id = r.id_unit
  JOIN geocoding g ON u.id_geocoding = g.id AND u.id_user = g.id_user
WHERE u.is_major = true AND u.id_geocoding IS NOT NULL
GROUP BY u.id_user, u.id, g.level_5, g.admin_5;
CREATE OR REPLACE VIEW public.level_6_concept_timeline_view AS
SELECT u.id_item,
  u.id_user,
  u.item_type,
  u.type AS unit_type,
  u.takentime,
  u.id AS id_unit,
  g.level_6 AS level,
  g.admin_6 AS admin,
  array_agg(DISTINCT r.id_concept) AS id_concept
FROM unit u
  JOIN many_unit_has_many_concept r ON u.id = r.id_unit
  JOIN geocoding g ON u.id_geocoding = g.id AND u.id_user = g.id_user
WHERE u.is_major = true AND u.id_geocoding IS NOT NULL
GROUP BY u.id_user, u.id, g.level_6, g.admin_6;
DROP FUNCTION IF EXISTS public.increase_concept_album_additional() CASCADE;
CREATE FUNCTION public.increase_concept_album_additional()
RETURNS TRIGGER AS
$$
BEGIN
  IF EXISTS (SELECT id_concept, id_user FROM concept_album_additional WHERE id_user = NEW.id_user AND id_concept = NEW.id_concept) THEN
    UPDATE concept_album_additional SET item_count = item_count + 1
    WHERE
      (SELECT is_major FROM unit WHERE unit.id = NEW.id_unit)
      AND concept_album_additional.id_user = NEW.id_user
      AND concept_album_additional.id_concept = NEW.id_concept;
  ELSIF (SELECT is_major FROM unit WHERE id = NEW.id_unit) THEN
    INSERT INTO concept_album_additional (id_user, id_concept, item_count) VALUES (NEW.id_user, NEW.id_concept, 1);
  END IF;
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER increase_concept_album_additional
AFTER INSERT ON public.many_unit_has_many_concept
FOR EACH ROW
EXECUTE FUNCTION public.increase_concept_album_additional();
DROP FUNCTION IF EXISTS public.recalculate_concept_album_additional(integer);
CREATE FUNCTION public.recalculate_concept_album_additional(user_id integer)
RETURNS VOID AS
$$
BEGIN
  INSERT INTO
  concept_album_additional (id_concept, item_count, id_user)
  SELECT
    concept.id AS id_concept,
    (
      SELECT
        COUNT(*)
      FROM
        many_unit_has_many_concept m
        JOIN unit on unit.id = m.id_unit
      WHERE
        m.id_user = user_id
        AND concept.id = m.id_concept
        AND unit.is_major
    ) as item_count,
    user_id as id_user
  FROM
    concept
  ON CONFLICT(id_user, id_concept) DO UPDATE
  SET
    (id_concept, item_count, id_user) = (EXCLUDED.id_concept, EXCLUDED.item_count, EXCLUDED.id_user);
END;
$$ LANGUAGE plpgsql;
DROP FUNCTION IF EXISTS public.update_filter_after_many_unit_has_many_concept_delete_func() CASCADE;
CREATE FUNCTION public.update_filter_after_many_unit_has_many_concept_delete_func()
RETURNS TRIGGER AS
$$
BEGIN
  DELETE FROM public.filter
  WHERE id_unit = OLD.id_unit AND id_user = OLD.id_user AND filter_type = 'concept';
  RETURN NULL;
END
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS update_filter_after_many_unit_has_many_concept_delete_trigger ON public.many_unit_has_many_concept CASCADE;
CREATE TRIGGER update_filter_after_many_unit_has_many_concept_delete_trigger
  AFTER DELETE ON public.many_unit_has_many_concept
  FOR EACH ROW
  EXECUTE PROCEDURE public.update_filter_after_many_unit_has_many_concept_delete_func();
DELETE FROM user_flag a USING user_flag b WHERE a.ctid < b.ctid AND a.id_user = b.id_user AND a.flag = b.flag;
ALTER TABLE public.user_flag ADD CONSTRAINT user_flag_pk PRIMARY KEY (id_user,flag);
DROP FUNCTION IF EXISTS public.update_user_flag_after_live_photo_update_func() CASCADE;
CREATE OR REPLACE FUNCTION public.update_user_flag_after_live_photo_update_func()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.item_type=3 AND OLD.is_major IS DISTINCT FROM NEW.is_major THEN
    INSERT INTO public.user_flag (id_user, flag, value) VALUES (NEW.id_user, 'live_photo_updated', 'true')
    ON CONFLICT (id_user, flag) DO UPDATE SET value = 'true';
  END IF;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS update_user_flag_after_live_photo_update_trigger ON public.unit CASCADE;
CREATE TRIGGER update_user_flag_after_live_photo_update_trigger
AFTER INSERT OR UPDATE ON public.unit
FOR EACH ROW
EXECUTE PROCEDURE public.update_user_flag_after_live_photo_update_func();
DROP FUNCTION IF EXISTS public.concept_album_list(integer);
CREATE FUNCTION public.concept_album_list(user_id integer)
RETURNS TABLE(
  id_concept integer,
  stem text,
  parent integer[],
  display_threshold smallint,
  sort_index integer,
  item_count bigint,
  default_cover_id_unit integer[],
  custom_cover_id_unit integer
) AS $$ BEGIN RETURN QUERY
  WITH main AS(
    SELECT concept.id AS id_concept,
      (
        SELECT caa.custom_cover_id_unit
        FROM concept_album_additional caa
        WHERE concept.id = caa.id_concept
          AND caa.id_user = user_id
      ) as custom_cover_unit_id,
      (
        SELECT caa.item_count
        FROM concept_album_additional caa
        WHERE concept.id = caa.id_concept
          AND caa.id_user = user_id
      ) as item_count,
      ARRAY(
        (
          SELECT m.id_unit
          FROM many_unit_has_many_concept m
            JOIN unit ON unit.id = m.id_unit
          WHERE m.id_user = user_id
            AND concept.id = m.id_concept
            AND unit.is_major = true
          ORDER BY m.confidence
          LIMIT 5
        )
      ) as default_cover_id_unit
    FROM concept
    WHERE concept.hidden = false
    ORDER BY concept.sort_index
  )
  SELECT main.id_concept,
    concept.stem,
    concept.parent,
    concept.display_threshold,
    concept.sort_index,
    main.item_count,
    main.default_cover_id_unit,
    CASE
      WHEN (
        EXISTS(
          SELECT 1
          FROM many_unit_has_many_concept m
          WHERE m.id_unit = main.custom_cover_unit_id
            AND m.id_concept = main.id_concept
        )
      ) THEN main.custom_cover_unit_id
      ELSE NULL
    END as custom_cover_id_unit
  FROM main
    JOIN concept on concept.id = main.id_concept;
END;
$$ LANGUAGE plpgsql;