migrate.sql

-- This script was generated by the Schema Diff utility in pgAdmin 4, but heavily modified by me 
-- (circuitsacul).

-- This script was generated by the Schema Diff utility in pgAdmin 4. 
-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated 
-- and may require manual changes to the script to ensure changes are applied in the correct order.
-- Please report an issue for any failure with the reproduction steps.
 BEGIN; 

-- manual
alter table aschannels rename to autostar_channels;
alter table sb_messages rename to starboard_messages;

alter table autostar_channels rename prem_locked to premium_locked;

alter table starboards rename prem_locked to premium_locked;
alter table starboards rename remove_invalid to remove_invalid_reactions;

alter table starboard_messages rename sb_message_id to starboard_message_id;

alter table overrides rename _overrides to overrides;

alter table permroles rename xproles to obtain_xproles;
alter table permroles rename vote to give_votes;
alter table permroles rename recv_votes to receive_votes;

alter table permrole_starboards rename vote to give_votes;
alter table permrole_starboards rename recv_votes to receive_votes;

alter sequence aschannels_id_seq rename to autostar_channels_id_seq;

delete from starboard_messages where starboard_message_id is null;

-- drop foreign keys
ALTER TABLE public.xproles DROP CONSTRAINT guild_id_fk;
ALTER TABLE public.votes DROP CONSTRAINT message_id_fk;
ALTER TABLE public.votes DROP CONSTRAINT starboard_id_fk;
ALTER TABLE public.votes DROP CONSTRAINT target_author_id_fk;
ALTER TABLE public.votes DROP CONSTRAINT user_id_fk;
ALTER TABLE public.overrides DROP CONSTRAINT guild_fk;
ALTER TABLE public.overrides DROP CONSTRAINT starboard_fk;
ALTER TABLE public.autostar_channels DROP CONSTRAINT guild_id_fk;
ALTER TABLE public.permrole_starboards DROP CONSTRAINT permrole_id_fk;
ALTER TABLE public.permrole_starboards DROP CONSTRAINT starboard_id_fk;
ALTER TABLE public.permroles DROP CONSTRAINT guild_id_fk;
ALTER TABLE public.members DROP CONSTRAINT guildid_fk;
ALTER TABLE public.members DROP CONSTRAINT userid_fk;
ALTER TABLE public.messages DROP CONSTRAINT author_id_fk;
ALTER TABLE public.messages DROP CONSTRAINT guild_id_fk;
ALTER TABLE public.posroles DROP CONSTRAINT guild_id_fk;
ALTER TABLE public.starboards DROP CONSTRAINT guild_id_fk;
ALTER TABLE public.starboard_messages DROP CONSTRAINT message_id_fk;
ALTER TABLE public.starboard_messages DROP CONSTRAINT starboard_id_fk;
ALTER TABLE public.posrole_members DROP CONSTRAINT role_id_fk;
ALTER TABLE public.posrole_members DROP CONSTRAINT user_id_fk;

-- misc
DROP TABLE public._migrations CASCADE;

-- users
ALTER TABLE public.users
    ALTER COLUMN user_id TYPE bigint;

ALTER TABLE public.users
    ALTER COLUMN user_id SET STORAGE PLAIN;

ALTER TABLE public.users
    ALTER COLUMN credits SET DEFAULT 0;

ALTER TABLE public.users
    ALTER COLUMN donated_cents SET DEFAULT 0;

ALTER TABLE public.users
    ALTER COLUMN patreon_status SET DEFAULT 0;
ALTER TABLE public.users DROP CONSTRAINT _users_user_id_primary_key;

ALTER TABLE public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (user_id);

-- patrons
ALTER TABLE public.patrons
    ALTER COLUMN discord_id TYPE bigint;

ALTER TABLE public.patrons
    ALTER COLUMN discord_id SET STORAGE PLAIN;

ALTER TABLE public.patrons
    ALTER COLUMN last_patreon_total_cents SET DEFAULT 0;
ALTER TABLE public.patrons DROP CONSTRAINT _patrons_patreon_id_primary_key;

ALTER TABLE public.patrons
    ADD CONSTRAINT patrons_pkey PRIMARY KEY (patreon_id);
ALTER TABLE public.patrons
    ADD CONSTRAINT patrons_discord_id_fkey FOREIGN KEY (discord_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE RESTRICT;

DROP INDEX public._btree_index_patrons__discord_id;

-- guilds
ALTER TABLE public.guilds
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.guilds
    ALTER COLUMN guild_id SET STORAGE PLAIN;
ALTER TABLE public.guilds DROP CONSTRAINT _guilds_guild_id_primary_key;

ALTER TABLE public.guilds
    ADD CONSTRAINT guilds_pkey PRIMARY KEY (guild_id);

DROP INDEX public._btree_index_guilds__premium_end;

-- messages
ALTER TABLE public.messages
    ALTER COLUMN message_id TYPE bigint;

ALTER TABLE public.messages
    ALTER COLUMN message_id SET STORAGE PLAIN;

ALTER TABLE public.messages
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.messages
    ALTER COLUMN guild_id SET STORAGE PLAIN;

ALTER TABLE public.messages
    ALTER COLUMN channel_id TYPE bigint;

ALTER TABLE public.messages
    ALTER COLUMN channel_id SET STORAGE PLAIN;

ALTER TABLE public.messages
    ALTER COLUMN author_id TYPE bigint;

ALTER TABLE public.messages
    ALTER COLUMN author_id SET STORAGE PLAIN;

ALTER TABLE public.messages
    ALTER COLUMN forced_to SET DEFAULT '{}'::integer[];

ALTER TABLE public.messages
    ALTER COLUMN trashed SET DEFAULT false;

ALTER TABLE public.messages
    ALTER COLUMN trash_reason TYPE character varying COLLATE pg_catalog."default";

ALTER TABLE public.messages
    ALTER COLUMN frozen SET DEFAULT false;
ALTER TABLE public.messages DROP CONSTRAINT _messages_message_id_primary_key;

ALTER TABLE public.messages
    ADD CONSTRAINT messages_pkey PRIMARY KEY (message_id);
ALTER TABLE public.messages
    ADD CONSTRAINT messages_author_id_fkey FOREIGN KEY (author_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.messages
    ADD CONSTRAINT messages_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

-- members
ALTER TABLE public.members
    ALTER COLUMN user_id TYPE bigint;

ALTER TABLE public.members
    ALTER COLUMN user_id SET STORAGE PLAIN;

ALTER TABLE public.members
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.members
    ALTER COLUMN guild_id SET STORAGE PLAIN;

ALTER TABLE public.members
    ALTER COLUMN xp SET DEFAULT 0;

ALTER TABLE public.members
    ALTER COLUMN autoredeem_enabled SET DEFAULT false;
ALTER TABLE public.members DROP CONSTRAINT _members_user_id_guild_id_primary_key;

ALTER TABLE public.members
    ADD CONSTRAINT members_pkey PRIMARY KEY (user_id, guild_id);
ALTER TABLE public.members
    ADD CONSTRAINT members_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.members
    ADD CONSTRAINT members_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_members__autoredeem_enabled;
DROP INDEX public._btree_index_members__xp;
DROP INDEX public._btree_index_members__guild_id;

-- starboards
ALTER TABLE public.starboards
    ALTER COLUMN channel_id TYPE bigint;

ALTER TABLE public.starboards
    ALTER COLUMN channel_id SET STORAGE PLAIN;

ALTER TABLE public.starboards
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.starboards
    ALTER COLUMN guild_id SET STORAGE PLAIN;

ALTER TABLE public.starboards
    ALTER COLUMN webhook_id TYPE bigint;

ALTER TABLE public.starboards
    ALTER COLUMN webhook_id SET STORAGE PLAIN;

ALTER TABLE public.starboards
    ALTER COLUMN premium_locked SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN display_emoji SET DEFAULT '⭐'::text;

ALTER TABLE public.starboards
    ALTER COLUMN ping_author SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN use_server_profile SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN extra_embeds SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN use_webhook SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN color DROP NOT NULL;

ALTER TABLE public.starboards
    ALTER COLUMN jump_to_message SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN attachments_list SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN replied_to SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN required SET DEFAULT 3;

ALTER TABLE public.starboards
    ALTER COLUMN required_remove SET DEFAULT 0;

ALTER TABLE public.starboards
    ALTER COLUMN upvote_emojis SET DEFAULT '{⭐}'::text[];

ALTER TABLE public.starboards
    ALTER COLUMN downvote_emojis SET DEFAULT '{}'::text[];

ALTER TABLE public.starboards
    ALTER COLUMN self_vote SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN allow_bots SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN require_image SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN older_than SET DEFAULT 0;

ALTER TABLE public.starboards
    ALTER COLUMN newer_than SET DEFAULT 0;

ALTER TABLE public.starboards
    ALTER COLUMN enabled SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN autoreact_upvote SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN autoreact_downvote SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN remove_invalid_reactions SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN link_deletes SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN link_edits SET DEFAULT true;

ALTER TABLE public.starboards
    ALTER COLUMN private SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN xp_multiplier SET DEFAULT 1.0;

ALTER TABLE public.starboards
    ALTER COLUMN cooldown_enabled SET DEFAULT false;

ALTER TABLE public.starboards
    ALTER COLUMN cooldown_count SET DEFAULT 5;

ALTER TABLE public.starboards
    ALTER COLUMN cooldown_period SET DEFAULT 5;
ALTER TABLE public.starboards DROP CONSTRAINT _starboards_id_primary_key;

ALTER TABLE public.starboards
    ADD CONSTRAINT starboards_pkey PRIMARY KEY (id);

ALTER TABLE public.starboards DROP CONSTRAINT sb_guild_name_unique;

ALTER TABLE public.starboards
    ADD CONSTRAINT starboards_guild_id_name_key UNIQUE (guild_id, name);
ALTER TABLE public.starboards
    ADD CONSTRAINT starboards_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;
DROP INDEX public._btree_index_starboards__guild_id_name;
DROP INDEX public._btree_index_starboards__channel_id;

-- overrides
ALTER TABLE public.overrides
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.overrides
    ALTER COLUMN guild_id SET STORAGE PLAIN;

ALTER TABLE public.overrides
    ALTER COLUMN channel_ids TYPE bigint[];
ALTER TABLE public.overrides
    ALTER COLUMN channel_ids SET DEFAULT '{}'::bigint[];

ALTER TABLE public.overrides
    ALTER COLUMN overrides SET DEFAULT '{}'::json;
ALTER TABLE public.overrides DROP CONSTRAINT _overrides_id_primary_key;

ALTER TABLE public.overrides
    ADD CONSTRAINT overrides_pkey PRIMARY KEY (id);
ALTER TABLE public.overrides
    ADD CONSTRAINT overrides_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.overrides
    ADD CONSTRAINT overrides_starboard_id_fkey FOREIGN KEY (starboard_id)
    REFERENCES public.starboards (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_overrides__starboard_id;
DROP INDEX public._btree_index_overrides__guild_id_name;
DROP INDEX public._gin_index_overrides__channel_ids;

-- xproles
ALTER TABLE public.xproles
    ALTER COLUMN role_id TYPE bigint;

ALTER TABLE public.xproles
    ALTER COLUMN role_id SET STORAGE PLAIN;

ALTER TABLE public.xproles
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.xproles
    ALTER COLUMN guild_id SET STORAGE PLAIN;
ALTER TABLE public.xproles DROP CONSTRAINT _xproles_role_id_primary_key;

ALTER TABLE public.xproles
    ADD CONSTRAINT xproles_pkey PRIMARY KEY (role_id);
ALTER TABLE public.xproles
    ADD CONSTRAINT xproles_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_xproles__guild_id;

-- votes
ALTER TABLE public.votes
    ALTER COLUMN message_id TYPE bigint;

ALTER TABLE public.votes
    ALTER COLUMN message_id SET STORAGE PLAIN;

ALTER TABLE public.votes
    ALTER COLUMN user_id TYPE bigint;

ALTER TABLE public.votes
    ALTER COLUMN user_id SET STORAGE PLAIN;

ALTER TABLE public.votes
    ALTER COLUMN target_author_id TYPE bigint;

ALTER TABLE public.votes
    ALTER COLUMN target_author_id SET STORAGE PLAIN;
ALTER TABLE public.votes DROP CONSTRAINT _votes_message_id_starboard_id_user_id_primary_key;

ALTER TABLE public.votes
    ADD CONSTRAINT votes_pkey PRIMARY KEY (message_id, starboard_id, user_id);
ALTER TABLE public.votes
    ADD CONSTRAINT votes_message_id_fkey FOREIGN KEY (message_id)
    REFERENCES public.messages (message_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.votes
    ADD CONSTRAINT votes_starboard_id_fkey FOREIGN KEY (starboard_id)
    REFERENCES public.starboards (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.votes
    ADD CONSTRAINT votes_target_author_id_fkey FOREIGN KEY (target_author_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.votes
    ADD CONSTRAINT votes_user_id_fkey FOREIGN KEY (user_id)
    REFERENCES public.users (user_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_votes__target_author_id;
DROP INDEX public._btree_index_votes__user_id;
DROP INDEX public._btree_index_votes__message_id;
DROP INDEX public._btree_index_votes__is_downvote;
DROP INDEX public._btree_index_votes__starboard_id;

-- autostar_channels
ALTER TABLE public.autostar_channels
    ALTER COLUMN channel_id TYPE bigint;

ALTER TABLE public.autostar_channels
    ALTER COLUMN channel_id SET STORAGE PLAIN;

ALTER TABLE public.autostar_channels
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.autostar_channels
    ALTER COLUMN guild_id SET STORAGE PLAIN;

ALTER TABLE public.autostar_channels
    ALTER COLUMN premium_locked SET DEFAULT false;

ALTER TABLE public.autostar_channels
    ALTER COLUMN emojis SET DEFAULT '{}'::text[];

ALTER TABLE public.autostar_channels
    ALTER COLUMN min_chars SET DEFAULT 0;

ALTER TABLE public.autostar_channels
    ALTER COLUMN require_image SET DEFAULT false;

ALTER TABLE public.autostar_channels
    ALTER COLUMN delete_invalid SET DEFAULT false;
ALTER TABLE public.autostar_channels DROP CONSTRAINT _aschannels_id_primary_key;

ALTER TABLE public.autostar_channels
    ADD CONSTRAINT autostar_channels_pkey PRIMARY KEY (id);

ALTER TABLE public.autostar_channels DROP CONSTRAINT asc_guild_name_unique;

ALTER TABLE public.autostar_channels
    ADD CONSTRAINT autostar_channels_guild_id_name_key UNIQUE (guild_id, name);
ALTER TABLE public.autostar_channels
    ADD CONSTRAINT autostar_channels_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_aschannels__guild_id_name;
DROP INDEX public._btree_index_aschannels__channel_id;

-- permroles
ALTER TABLE public.permroles
    ALTER COLUMN role_id SET STORAGE PLAIN;

ALTER TABLE public.permroles
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.permroles
    ALTER COLUMN guild_id SET STORAGE PLAIN;
ALTER TABLE public.permroles DROP CONSTRAINT _permroles_role_id_primary_key;

ALTER TABLE public.permroles
    ADD CONSTRAINT permroles_pkey PRIMARY KEY (role_id);
ALTER TABLE public.permroles
    ADD CONSTRAINT permroles_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_permroles__guild_id;

-- permrole_starboards
ALTER TABLE public.permrole_starboards
    ALTER COLUMN permrole_id TYPE bigint;

ALTER TABLE public.permrole_starboards
    ALTER COLUMN permrole_id SET STORAGE PLAIN;
ALTER TABLE public.permrole_starboards DROP CONSTRAINT _permrole_starboards_permrole_id_starboard_id_primary_key;

ALTER TABLE public.permrole_starboards
    ADD CONSTRAINT permrole_starboards_pkey PRIMARY KEY (permrole_id, starboard_id);
ALTER TABLE public.permrole_starboards
    ADD CONSTRAINT permrole_starboards_permrole_id_fkey FOREIGN KEY (permrole_id)
    REFERENCES public.permroles (role_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.permrole_starboards
    ADD CONSTRAINT permrole_starboards_starboard_id_fkey FOREIGN KEY (starboard_id)
    REFERENCES public.starboards (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;
ALTER TABLE public.permroles
    ALTER COLUMN role_id TYPE bigint;

-- posroles
ALTER TABLE public.posroles
    ALTER COLUMN role_id TYPE bigint;

ALTER TABLE public.posroles
    ALTER COLUMN role_id SET STORAGE PLAIN;

ALTER TABLE public.posroles
    ALTER COLUMN guild_id TYPE bigint;

ALTER TABLE public.posroles
    ALTER COLUMN guild_id SET STORAGE PLAIN;
ALTER TABLE public.posroles DROP CONSTRAINT _posroles_role_id_primary_key;

ALTER TABLE public.posroles
    ADD CONSTRAINT posroles_pkey PRIMARY KEY (role_id);
ALTER TABLE public.posroles
    ADD CONSTRAINT posroles_guild_id_fkey FOREIGN KEY (guild_id)
    REFERENCES public.guilds (guild_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_posroles__guild_id_max_members;

-- starboard_messages
ALTER TABLE public.starboard_messages
    ALTER COLUMN message_id TYPE bigint;

ALTER TABLE public.starboard_messages
    ALTER COLUMN message_id SET STORAGE PLAIN;

ALTER TABLE public.starboard_messages
    ALTER COLUMN starboard_message_id TYPE bigint;
ALTER TABLE public.starboard_messages
    ALTER COLUMN starboard_message_id SET NOT NULL;

ALTER TABLE public.starboard_messages
    ALTER COLUMN starboard_message_id SET STORAGE PLAIN;

ALTER TABLE public.starboard_messages
    ALTER COLUMN last_known_point_count SET DEFAULT 0;
ALTER TABLE public.starboard_messages DROP CONSTRAINT _sb_messages_message_id_starboard_id_primary_key;

ALTER TABLE public.starboard_messages
    ADD CONSTRAINT starboard_messages_pkey PRIMARY KEY (message_id, starboard_id);
ALTER TABLE public.starboard_messages
    ADD CONSTRAINT starboard_messages_message_id_fkey FOREIGN KEY (message_id)
    REFERENCES public.messages (message_id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

ALTER TABLE public.starboard_messages
    ADD CONSTRAINT starboard_messages_starboard_id_fkey FOREIGN KEY (starboard_id)
    REFERENCES public.starboards (id) MATCH SIMPLE
    ON UPDATE CASCADE
    ON DELETE CASCADE;

DROP INDEX public._btree_index_sb_messages__starboard_id;
DROP INDEX public._btree_index_sb_messages__last_known_point_count;
DROP INDEX public._btree_index_sb_messages__sb_message_id;

-- posrole_members
DROP TABLE posrole_members;

 END;