drop function if exists prepare_tube_record(varchar) cascade;
drop function if exists insert_thumbs(integer, varchar) cascade;
drop function if exists insert_tags(integer, varchar) cascade;
drop function if exists import_data() cascade;
drop type if exists tube_struct_table cascade;

-- delete from video;

create temporary table if not exists data_csv (
   id integer,
   embed_url varchar,
   title varchar,
   duration varchar,
   date_added varchar,
   thumbs varchar,
   tags varchar
);

copy data_csv from 'C:/Users/.../example.csv' with (
   delimiter '|'
);

create type tube_struct_table as (
   domain varchar,
   embed_format_url varchar
);


create function prepare_tube_record(url varchar) returns tube_struct_table as $$
declare format varchar;
declare tube_rec tube_struct_table;
begin
   -- format: http://xhamster.com/xembed.php?video=217
   tube_rec.domain := (select regexp_matches(url, E'(https?:)?\/\/(www\.)?([^\/]+)'))[3];
   format := regexp_replace(url, E'//[^\/]+', '//<DOMAIN>');
   tube_rec.embed_format_url := regexp_replace(format, E'=[0-9]+$', '=<ID>');
   return tube_rec;
end;
$$ language 'plpgsql';


create function insert_thumbs(_video_id integer, thumbs varchar) returns unknown as $$
declare thumbs_arr varchar array;
begin
   thumbs_arr := regexp_split_to_array(thumbs, E';');
   insert into thumb (thumb_url, video_id) select unnest(thumbs_arr), _video_id;
   return 'thumbs are loaded.';
end;
$$ language 'plpgsql';

create function insert_tags(_video_id integer, tags varchar) returns varchar as $$
declare tags_arr varchar[] = regexp_split_to_array(tags, E';');
declare result record;
begin
   insert into tag (tag_name) (select distinct a.a from unnest(tags_arr) a left join tag b
      on a.a = b.tag_name where b.tag_name is null);

   insert into vid_tag_rel (tag_id, video_id) select distinct b.id, _video_id
      from unnest(tags_arr) a left join tag b on a.a = b.tag_name;
   return 'tags are loaded.';
end;
$$ language 'plpgsql';


CREATE FUNCTION import_data() RETURNS varchar AS $$
declare _row record;
declare tube_rec tube_struct_table;
declare _tube_id integer;
declare _video_id integer;
declare cnt integer;
BEGIN
   cnt := 0;
   for _row in select * from data_csv loop
      cnt := cnt + 1;
      -- insert in tube table
      tube_rec := prepare_tube_record(_row.embed_url);
      select id from tube into _tube_id where domain = tube_rec.domain;
      if _tube_id is null then
         insert into tube (domain, embed_format_url) values ((tube_rec).*) returning id into _tube_id;
      end if;

      -- insert in video table
      select id from video into _video_id where tube_id = _tube_id and embed_key = cast(_row.id as varchar);
      if _video_id is null then
         insert into video (title, duration, date_added, embed_key, tube_id) values (
            _row.title,
            _row.duration,
            to_timestamp(_row.date_added, 'YYYY-MM-DD'),
            cast(_row.id as varchar),
            _tube_id
         ) returning id into _video_id;

         --insert thumbs
         perform insert_thumbs(_video_id, _row.thumbs);

         --insert
         perform insert_tags(_video_id, _row.tags);
      end if;
      raise notice 'Val: %', cnt;
   end loop;
   return 'successfull.';
END;
$$ LANGUAGE 'plpgsql';

select import_data();

BEGIN TRANSACTION; SELECT import_data(); COMMIT;