Copy the following code into the Supabase SQL editor and click Run.

BEGIN;

-- Create an admin role
CREATE ROLE admin;
GRANT USAGE ON SCHEMA public TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO admin;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO admin;
GRANT admin TO authenticated;



-- Create the app_compliance table
CREATE TABLE IF NOT EXISTS app_compliance (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    html TEXT NOT NULL
);

-- Enable RLS for app_compliance
ALTER TABLE app_compliance ENABLE ROW LEVEL SECURITY;

-- Create an app_compliance policy to allow users to see compliance pages
CREATE POLICY "users can see compliance"
ON "public"."app_compliance"
FOR SELECT
TO public
using (
    true
);

-- Create a policy to allow admins to manage app_compliance
CREATE POLICY "Admins can manage app_compliance"
ON "public"."app_compliance"
FOR ALL
TO admin
USING (
    (auth.jwt() ->> 'role'::text) = 'admin'::text
)
WITH CHECK (
    (auth.jwt() ->> 'role'::text) = 'admin'::text
);

-- Insert the terms and conditions
INSERT INTO app_compliance (id, name, html)
VALUES ('terms-and-conditions', 'Terms and Conditions', '<p>This is the terms and conditions page.</p>');

-- Insert the privacy policy
INSERT INTO app_compliance (id, name, html)
VALUES ('privacy-policy', 'Privacy Policy', '<p>This is the privacy policy page.</p>');



-- Create the users table
CREATE TABLE IF NOT EXISTS public.users (
    id TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    email TEXT NULL,
    display_name TEXT NULL,
    photo_url TEXT NULL,
    bio TEXT NULL,
    CONSTRAINT users_pkey PRIMARY KEY (id)
);

-- Enable RLS for users
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Create a users policy to allow users to manage their own data
CREATE POLICY "Users can manage their own data"
ON "public"."users"
FOR ALL
TO admin, authenticated
USING (
    (auth.jwt() ->> 'sub'::text) = id::text
)
WITH CHECK (
    (auth.jwt() ->> 'sub'::text) = id::text
);

-- Create a users policy to allow admins to manage user data
CREATE POLICY "Admins can manage user data"
ON "public"."users"
FOR ALL
TO admin
USING (
    (auth.jwt() ->> 'role'::text) = 'admin'::text
)
WITH CHECK (
    (auth.jwt() ->> 'role'::text) = 'admin'::text
);



-- Create the notifications table
CREATE TABLE IF NOT EXISTS public.notifications (
    id BIGINT generated by default as identity NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    user_id TEXT NOT NULL,
    notification JSON NOT NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    CONSTRAINT notifications_pkey PRIMARY KEY (id),
    CONSTRAINT notifications_user_id_fkey FOREIGN KEY (user_id) REFERENCES users (id) ON UPDATE CASCADE ON DELETE CASCADE
) tablespace pg_default;

-- Enable RLS for notifications
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;

-- Create a policy to control notifications
CREATE POLICY "Users can manage their own notification data"
ON "public"."notifications"
FOR ALL
TO admin, authenticated
USING (
    (auth.jwt() ->> 'sub'::text) = user_id::text
)
WITH CHECK (
    (auth.jwt() ->> 'sub'::text) = user_id::text
);

COMMIT;