-- ============================================================
-- CS2 Skin Tracker — drop a pre-existing skintracker_app role
-- Run ONCE as a superuser (e.g. postgres) BEFORE
-- 01_schema_and_roles.sql, to remove an older role and
-- everything it owns so the creation script starts clean.
--
-- IMPORTANT:
--   * Connect to the SAME database that contains the old
--     skintracker schema/tables. DROP OWNED only affects the
--     current database, so if the role owns objects in more
--     than one database, run this in each of them.
--   * This is DESTRUCTIVE: it drops the schema, tables, and any
--     data the role owns. Back up first if you need the data.
-- ============================================================

-- 1. (Optional) Terminate any live sessions still using the role,
--    otherwise the DROP ROLE can fail with "role is being used".
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'skintracker_app'
  AND pid <> pg_backend_pid();

-- 2. Reassign ownership of the DATABASE away from the role.
--    DROP OWNED BY does NOT cover database ownership (a database is a
--    cluster-level object), so the role must hand off the database
--    first or DROP ROLE fails with "owner of database skintracker".
--    Run this while connected as the target owner (e.g. postgres).
ALTER DATABASE skintracker OWNER TO CURRENT_USER;

-- 3. Drop everything the role owns (schema, tables, indexes, etc.)
--    and revoke any privileges granted to it, then drop the role.
--    Guarded so the script is safe to run even if the role is gone.
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'skintracker_app') THEN
        EXECUTE 'DROP OWNED BY skintracker_app CASCADE';
        EXECUTE 'DROP ROLE skintracker_app';
    END IF;
END
$$;