DROP VIEW IF EXISTS public.video;
CREATE VIEW public.video
AS
SELECT
   video_unit.id,
   video_unit.id_user,
   video_unit.item_type,
   video_unit.type,
   video_unit.filename,
   video_unit.filesize,
   video_unit.createtime,
   video_unit.takentime,
   video_unit.mtime,
   video_unit.duplicate_hash,
   video_unit.cache_key,
   video_unit.resolution,
   video_unit.index_stage,
   video_unit.version,
   video_unit.id_item,
   video_unit.id_folder,
   video_unit.id_geocoding,
   video_unit.is_major,
   video_additional.duration,
   video_additional.video_info,
   video_additional.audio_info
FROM
   video_additional,
   (SELECT * FROM unit WHERE type = 1) AS video_unit
WHERE
   video_unit.id = video_additional.id_unit;
DROP VIEW IF EXISTS public.live;
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
FROM
   live_additional,
   (SELECT * FROM unit WHERE item_type = 3) AS live_unit
WHERE
   live_unit.id = live_additional.id_unit;
DROP VIEW IF EXISTS public.general_tag_album_view;
CREATE VIEW public.general_tag_album_view
AS
SELECT
  T.id as id_general_tag, T.name, SS.item_count, T.id_user
FROM
  general_tag as T, (
      SELECT count(DISTINCT(U.id_item)) AS item_count, R.id_general_tag, U.id_user
      FROM many_unit_has_many_general_tag as R, unit as U
      WHERE U.id = R.id_unit AND U.is_major = 't'
      GROUP BY R.id_general_tag, U.id_user
    ) SS WHERE T.id = SS.id_general_tag AND T.id_user = SS.id_user;
DROP VIEW IF EXISTS public.general_tag_timeline_view;
CREATE OR REPLACE VIEW public.general_tag_timeline_view
AS
SELECT
    U.id_item,
    U.id_user,
    U.item_type,
    U.type as unit_type,
    U.id as id_unit,
    AGG_TAG.tags AS id_general_tag,
    U.takentime
FROM unit as U left outer join (
    SELECT unit.id, unit.id_user, array_agg(distinct(R.id_general_tag)) as tags
    FROM unit left outer join many_unit_has_many_general_tag as R on unit.id = R.id_unit
    GROUP BY unit.id, unit.id_user
) as AGG_TAG ON U.id = AGG_TAG.id AND U.id_user = AGG_TAG.id_user
WHERE U.is_major = 't';
DROP VIEW IF EXISTS public.unit_to_general_tag_view;
CREATE VIEW public.unit_to_general_tag_view
AS
SELECT
   G.id as id_general_tag, name, R.id_unit, G.id_user
   FROM
   public.general_tag as G
   JOIN public.many_unit_has_many_general_tag as R ON G.id = R.id_general_tag;
ALTER TABLE public.video_convert DROP COLUMN id_user;
ALTER TABLE public.video_additional DROP COLUMN id_user;
ALTER TABLE public.thumb_preview DROP COLUMN id_user;
ALTER TABLE public.thumbnail DROP COLUMN id_user;
ALTER TABLE public.metadata DROP COLUMN id_user;
ALTER TABLE public.live_additional DROP COLUMN id_user;
ALTER TABLE public.many_unit_has_many_general_tag DROP COLUMN id_user;