DROP INDEX IF EXISTS unit_filename_without_ext_idx CASCADE;
DROP INDEX IF EXISTS unit_file_extension_idx CASCADE;
DROP FUNCTION IF EXISTS public.get_filename_without_ext(TEXT) CASCADE;
DROP FUNCTION IF EXISTS public.get_file_extension(TEXT) CASCADE;
DROP TABLE IF EXISTS public.split_filename CASCADE;
CREATE TABLE public.split_filename
(
    id_unit INT PRIMARY KEY,
    name TEXT NOT NULL,
    lower_extension TEXT NOT NULL,
    CONSTRAINT split_filename_id_unit_fkey FOREIGN KEY (id_unit) REFERENCES public.unit (id) ON DELETE CASCADE ON UPDATE CASCADE
);
DROP VIEW IF EXISTS public.need_convert_thumbnail_view CASCADE;
CREATE VIEW public.need_convert_thumbnail_view
AS
SELECT
  u.id, u.id_user, u.filename, u.type AS unit_type, u.item_type,
  CASE
    WHEN u.type = 1 THEN public.get_json_value(v.video_info, 'video_codec')
    ELSE ''
  END AS codec,
  t.status,
  s.lower_extension,
  ARRAY_AGG(t.type)::int[] AS thumbnail_type
FROM
  unit u
  LEFT JOIN video_additional v ON u.id = v.id_unit
  JOIN split_filename s ON u.id = s.id_unit
  JOIN thumbnail t ON u.id = t.id_unit AND (t.type = 1 OR t.type = 6) AND t.status = 3
GROUP BY u.id, u.id_user, u.filename, u.type, u.item_type, t.status, s.lower_extension, codec;
DROP VIEW IF EXISTS public.live CASCADE;
CREATE VIEW public.live AS
SELECT live_unit.id,
    live_unit.id_user,
    live_unit.item_type,
    live_unit.type,
    live_unit.filename,
    live_unit.filesize,
    live_unit.createtime,
    live_unit.takentime,
    live_unit.mtime,
    live_unit.duplicate_hash,
    live_unit.cache_key,
    live_unit.resolution,
    live_unit.index_stage,
    live_unit.version,
    live_unit.id_item,
    live_unit.id_folder,
    live_unit.id_geocoding,
    live_unit.is_major,
    live_additional.grouping_key,
    s.name as name_no_ext
   FROM live_additional,
    ( SELECT unit.id,
            unit.id_user,
            unit.type,
            unit.item_type,
            unit.filename,
            unit.filesize,
            unit.createtime,
            unit.mtime,
            unit.takentime,
            unit.duplicate_hash,
            unit.cache_key,
            unit.resolution,
            unit.index_stage,
            unit.version,
            unit.id_geocoding,
            unit.id_item,
            unit.mobile_cache_mtime,
            unit.reindex_flag,
            unit.normalized_filename,
            unit.is_major,
            unit.id_folder
           FROM unit
          WHERE unit.item_type = 3) live_unit, split_filename s
  WHERE live_unit.id = live_additional.id_unit and live_unit.id = s.id_unit;