fp/scripts/data-migrations/2024-10-07-from-strapi-to-postgrest.sql

SELECT dblink_connect(
    'old_db_conn',
    'dbname=futureporn_strapi_old user=postgres passfile=/tmp/.pgpass'
);


-- Temporary schema changes that I don't want to save in @futureporn/migrations
-- 1. ADD api.s3_files.id_old
-- 2. ADD api.vods.id_old
-- 3. ADD api.vods_s3_join.[id_old,vod_id_old,b_2_file_id_old]
-- 4. ADD api.vtubers.id_old
-- 5. ADD api.vods_s3_files_joins.id_old
ALTER TABLE IF EXISTS api.s3_files
    ADD COLUMN IF NOT EXISTS id_old int;

ALTER TABLE IF EXISTS api.vods
    ADD COLUMN IF NOT EXISTS id_old int;

ALTER TABLE api.vods_s3_files_joins
    ADD COLUMN IF NOT EXISTS id_old int;

ALTER TABLE api.vods_s3_files_joins
    ADD COLUMN IF NOT EXISTS vod_id_old int;

ALTER TABLE api.vods_s3_files_joins
    ADD COLUMN IF NOT EXISTS b_2_file_id_old int;

ALTER TABLE IF EXISTS api.vtubers
    ADD COLUMN IF NOT EXISTS id_old int;

ALTER TABLE api.vods_s3_files_joins
  ADD COLUMN IF NOT EXISTS id_old int;

ALTER TABLE api.vods_s3_files_joins
  ADD COLUMN IF NOT EXISTS s3_file_id UUID;

ALTER TABLE api.vods_s3_files_joins
  ADD COLUMN IF NOT EXISTS s3_file_id_old int;

CREATE TABLE IF NOT EXISTS api.a_temporary_vods (
    id integer, 
    video_src_hash character varying, 
    video_720_hash character varying, 
    video_480_hash character varying, 
    video_360_hash character varying, 
    video_240_hash character varying, 
    thin_hash character varying, 
    thicc_hash character varying, 
    announce_title character varying, 
    announce_url character varying, 
    note text, 
    date timestamp(6) without time zone, 
    spoilers text, 
    created_at timestamp(6) without time zone, 
    updated_at timestamp(6) without time zone, 
    published_at timestamp(6) without time zone, 
    created_by_id integer, 
    updated_by_id integer, 
    title character varying, 
    chat_log text, 
    date_2 character varying, 
    cuid character varying, 
    archive_status character varying
);

-- Enable the dblink extension
-- this lets us copy data between two different databases
-- in our case, we are copying tables from futureporn_strapi_old.public.streams to futureporn.api.streams
CREATE EXTENSION IF NOT EXISTS dblink;


SELECT dblink_connect(
    'old_db_conn',
    'dbname=futureporn_strapi_old user=postgres passfile=/tmp/.pgpass'
);


-- Migrate vtubers table
INSERT INTO api.vtubers (
  id,
  id_old,
  chaturbate,
  twitter,
  patreon,
  twitch,
  tiktok,
  onlyfans,
  youtube,
  linktree,
  carrd,
  fansly,
  pornhub,
  discord,
  reddit,
  throne,
  instagram,
  facebook,
  merch,
  slug,
  image,
  display_name,
  description1,
  description2,
  created_at,
  updated_at,
  theme_color,
  image_blur
)
SELECT DISTINCT 
    gen_random_uuid() AS id,
    v.id AS id_old,
    v.chaturbate,
    v.twitter,
    v.patreon,
    v.twitch,
    v.tiktok,
    v.onlyfans,
    v.youtube,
    v.linktree,
    v.carrd,
    v.fansly,
    v.pornhub,
    v.discord,
    v.reddit,
    v.throne,
    v.instagram,
    v.facebook,
    v.merch,
    v.slug,
    v.image,
    v.display_name,
    v.description_1,
    v.description_2,
    v.created_at,
    v.updated_at,
    v.theme_color,
    v.image_blur
FROM dblink('old_db_conn', 'SELECT id, 
    chaturbate, 
    twitter, 
    patreon, 
    twitch, 
    tiktok, 
    onlyfans, 
    youtube, 
    linktree, 
    carrd, 
    fansly, 
    pornhub, 
    discord, 
    reddit, 
    throne, 
    instagram, 
    facebook, 
    merch, 
    slug, 
    image, 
    display_name, 
    description_1, 
    description_2, 
    created_at, 
    updated_at, 
    published_at, 
    created_by_id, 
    updated_by_id, 
    theme_color, 
    image_blur 
    FROM public.vtubers') 
AS v(
  id integer,
  chaturbate character varying(255),
  twitter character varying(255),
  patreon character varying(255),
  twitch character varying(255),
  tiktok character varying(255),
  onlyfans character varying(255),
  youtube character varying(255),
  linktree character varying(255),
  carrd character varying(255),
  fansly character varying(255),
  pornhub character varying(255),
  discord character varying(255),
  reddit character varying(255),
  throne character varying(255),
  instagram character varying(255),
  facebook character varying(255),
  merch character varying(255),
  slug character varying(255),
  image character varying(255),
  display_name character varying(255),
  description_1 text,
  description_2 text,
  created_at timestamp(6) without time zone,
  updated_at timestamp(6) without time zone,
  published_at timestamp(6) without time zone,
  created_by_id integer,
  updated_by_id integer,
  theme_color character varying(255),
  image_blur character varying(255)
);



-- Migrate streams table
-- here we are taking the pre-existing data from the strapi database
-- and copying it to the postgrest database.
-- some of the columns like vtuber need to be set to NULL because they are new and strapi streams table didn't contain that info
INSERT INTO api.streams (platform_notification_type, date, vtuber, tweet, archive_status, is_chaturbate_stream, is_fansly_stream)
SELECT DISTINCT 
    NULL AS platform_notification_type,
    s.date, 
    NULL::UUID AS vtuber,
    NULL AS tweet,
    s.archive_status, 
    s.is_chaturbate_stream, 
    s.is_fansly_stream
FROM dblink('old_db_conn', 'SELECT date, archive_status, is_chaturbate_stream, is_fansly_stream FROM public.streams') 
AS s(
  date timestamp,
  archive_status character varying,
  is_chaturbate_stream boolean,
  is_fansly_stream boolean
);


-- Migrate vods b2_files join table
-- previously public.vods_video_src_b_2_links
-- new api.vods_s3_join
INSERT INTO api.vods_s3_files_joins (id, id_old, vod_id, vod_id_old, s3_file_id, s3_file_id_old)
SELECT DISTINCT
    gen_random_uuid() AS id,
    old.id AS id_old,
    NULL::UUID AS vod_id,
    old.vod_id AS vod_id_old,
    NULL::UUID AS s3_file_id,
    old.b_2_file_id AS s3_file_id_old
FROM dblink('old_db_conn', 'SELECT id, vod_id, b_2_file_id FROM public.vods_video_src_b_2_links')
AS old(
    id int,
    vod_id int,
    b_2_file_id int
);




-- Migrate B2 table
INSERT INTO api.s3_files (
    id, 
    id_old, 
    s3_id, 
    s3_key, 
    created_at, 
    updated_at, 
    bucket,
    cdn_url
)
SELECT
    gen_random_uuid()::UUID AS id,
    b2_file.id::INT AS id_old,
    b2_file.upload_id::TEXT AS s3_id,
    b2_file.key::TEXT AS s3_key,
    b2_file.created_at::TIMESTAMP(6) WITHOUT TIME ZONE AS created_at,
    b2_file.updated_at::TIMESTAMP(6) WITHOUT TIME ZONE AS updated_at,
    'futureporn-b2'::TEXT AS bucket,
    b2_file.cdn_url::TEXT AS cdn_url
FROM
    dblink('old_db_conn', 'SELECT id, key, upload_id, created_at, updated_at, cdn_url FROM public.b2_files') AS b2_file (
        id integer,
        key character varying(255),
        upload_id character varying(255),
        created_at timestamp(6) without time zone, 
        updated_at timestamp(6) without time zone, 
        cdn_url character varying(255)
    );


-- Migrate vods table
INSERT INTO api.vods (
    id,
    id_old,
    stream_id,
    created_at,
    updated_at,
    title, 
    date, 
    note, 
    ipfs_cid,
    s3_file,
    announce_title, 
    announce_url, 
    status
)
SELECT
    gen_random_uuid(),
    vods.id,
    NULL,
    vods.created_at,
    vods.updated_at,
    vods.title,
    vods.date::date,
    vods.note,
    vods.video_src_hash,
    NULL, -- old vods doesn't contain this info-- the join table is needed
    vods.announce_title,
    vods.announce_url,
    'pending_recording'
FROM
    dblink('old_db_conn', 'SELECT * FROM public.vods') AS vods (
        id integer, 
        video_src_hash character varying, 
        video_720_hash character varying, 
        video_480_hash character varying, 
        video_360_hash character varying, 
        video_240_hash character varying, 
        thin_hash character varying, 
        thicc_hash character varying, 
        announce_title character varying, 
        announce_url character varying, 
        note text, 
        date timestamp(6) without time zone, 
        spoilers text, 
        created_at timestamp(6) without time zone, 
        updated_at timestamp(6) without time zone, 
        published_at timestamp(6) without time zone, 
        created_by_id integer, 
        updated_by_id integer, 
        title character varying, 
        chat_log text, 
        date_2 character varying, 
        cuid character varying, 
        archive_status character varying
    )
    LEFT JOIN (
        -- Fetching vods_vtuber_links from the old database
        SELECT * 
        FROM dblink('old_db_conn', 'SELECT vod_id, vtuber_id FROM public.vods_vtuber_links') AS links (
            vod_id integer,
            vtuber_id integer
        )
    ) AS links ON vods.id = links.vod_id
    LEFT JOIN api.vtubers AS vtubers
        ON links.vtuber_id = vtubers.id_old;  -- Map the old `vtuber_id` to the new `uuid` in `vtubers`



-- Now we copy patron data from the old Strapi table up_user
-- Going forward we are changing how Patrons table is populated.

-- FROM up_user