create table
  public.panel_settings (
    display_name text not null default 'User'::text,
    terms text null default '<ul><li>No refunds given</li></ul>'::text,
    id uuid not null default gen_random_uuid (),
    discord text null default ''::text,
    logo text not null default '/default.svg'::text,
    avatar_url text null default '/logo.svg'::text,
    created_at timestamp with time zone null default now(),
    api_key uuid null default gen_random_uuid (),
    api_uses_left smallint not null default '500'::smallint,
    license_active boolean not null default false,
    subscription_id text null,
    settings JSONB DEFAULT '{"currency_prefix": "$"}',
    psk TEXT,
    sk TEXT,
    constraint panel_settings_pkey primary key (id),
    constraint panel_settings_api_key_key unique (api_key),
    constraint panel_settings_discord_check check ((length(discord) < 30)),
    constraint panel_settings_display_name_check check ((length(display_name) < 100)),
    constraint panel_settings_logo_check check ((length(logo) < 500)),
    constraint panel_settings_terms_check check ((length(terms) < 500)),
    constraint panel_settings_uses_left_check check ((api_uses_left < 5000)),
    constraint panel_settings_avatar_url_check check ((length(avatar_url) < 1000))
  ) tablespace pg_default;

create table
  public.panel_clients (
    id uuid not null default gen_random_uuid (),
    created_at timestamp with time zone not null default now(),
    username text not null default ''::text,
    discord text null,
    email text null,
    password text null,
    avatar_url text null default ''::text,
    panel_id uuid not null default auth.uid (),
    stripe_id text null,
    constraint panel_clients_pkey primary key (id),
    constraint panel_clients_panel_id_fkey foreign key (panel_id) references panel_settings (id) on update cascade on delete cascade,
    constraint panel_clients_avatar_url_check check ((length(avatar_url) < 10000)),
    constraint panel_clients_discord_check check ((length(discord) < 30)),
    constraint panel_clients_email_check check ((length(email) < 50)),
    constraint panel_clients_username_check check ((length(username) < 30))
  ) tablespace pg_default;

create table
  public.notifications (
    id bigint generated by default as identity,
    created_at timestamp with time zone not null default now(),
    panel_id uuid not null default auth.uid (),
    title text null default 'New notification'::text,
    message text null,
    subject uuid null default gen_random_uuid (),
    link text not null default '/notifications'::text,
    read boolean null default false,
    constraint notifications_pkey primary key (id),
    constraint public_notifications_panel_id_fkey foreign key (panel_id) references panel_settings (id) on update cascade on delete cascade,
    constraint public_notifications_subject_fkey foreign key (subject) references panel_clients (id) on update cascade on delete cascade
  ) tablespace pg_default;

create table
  public.products (
    id bigint generated by default as identity,
    creator uuid not null default auth.uid (),
    description text null default ''::text,
    date_created timestamp with time zone not null default now(),
    constraint products_pkey primary key (id),
    constraint products_creator_fkey foreign key (creator) references panel_settings (id) on update cascade,
    constraint products_description_check check ((length(description) < 36))
  ) tablespace pg_default;

create table
  public.panel_commissions (
    created_at timestamp with time zone not null default now(),
    title text null default 'New commission'::text,
    client uuid null,
    total_value numeric null default 0.00,
    total_paid numeric null default 0.00,
    start_date timestamp with time zone null default now(),
    deadline timestamp with time zone null default now(),
    status text not null default 'not_started'::text,
    id uuid not null default gen_random_uuid (),
    panel_id uuid not null default auth.uid (),
    tracking_id uuid not null default gen_random_uuid (),
    product bigint null,
    pinned boolean null default false,
    notes text null,
    constraint panel_commissions_pkey primary key (id),
    constraint panel_commissions_tracking_id_key unique (tracking_id),
    constraint panel_commissions_panel_id_fkey foreign key (panel_id) references panel_settings (id) on update cascade on delete cascade,
    constraint panel_commissions_client_fkey foreign key (client) references panel_clients (id) on update cascade on delete cascade,
    constraint panel_commissions_product_fkey foreign key (product) references products (id) on update cascade on delete restrict,
    constraint panel_commissions_notes_check check ((length(notes) < 1500)),
    constraint panel_commissions_status_check check ((length(status) < 20))
  ) tablespace pg_default;

create table
  public.panel_quotes (
    created_at timestamp with time zone not null default now(),
    title text null default 'New quote'::text,
    client uuid null,
    proposed_amount integer null default 0,
    start_date timestamp with time zone not null default now(),
    deadline timestamp with time zone not null default now(),
    payment_terms text not null default 'custom'::text,
    status text not null default 'pending'::text,
    id uuid not null default gen_random_uuid (),
    panel_id uuid not null default auth.uid (),
    constraint panel_quotes_pkey primary key (id),
    constraint panel_quotes_client_fkey foreign key (client) references panel_clients (id) on update cascade on delete cascade,
    constraint panel_quotes_panel_id_fkey foreign key (panel_id) references panel_settings (id) on update cascade on delete cascade,
    constraint panel_quotes_proposed_amount_check check ((proposed_amount < 10000000))
  ) tablespace pg_default;

create table
  public.panel_requests (
    created_at timestamp with time zone not null default now(),
    description text not null,
    offered_amount numeric null default 0.00,
    deadline timestamp with time zone null,
    status text not null default 'requested'::text,
    id uuid not null default gen_random_uuid (),
    panel_id uuid not null default auth.uid (),
    commission uuid null,
    paid boolean not null default false,
    constraint panel_requests_pkey primary key (id),
    constraint panel_requests_commission_fkey foreign key (commission) references panel_commissions (id) on update cascade on delete cascade,
    constraint panel_requests_panel_id_fkey foreign key (panel_id) references panel_settings (id) on update cascade on delete cascade,
    constraint panel_requests_description_check check ((length(description) > 0)),
    constraint panel_requests_status_check check ((length(status) < 20))
  ) tablespace pg_default;

create table
  public.panel_views (
    id bigint generated by default as identity,
    data json not null,
    panel_id uuid null default auth.uid (),
    name text not null default 'New view'::text,
    constraint panel_views_pkey primary key (id),
    constraint panel_views_panel_id_fkey foreign key (panel_id) references panel_settings (id) on update cascade on delete cascade,
    constraint panel_views_name_check check ((length(name) < 30))
  ) tablespace pg_default;

create table
  public.panel_invoices (
    created_at timestamp with time zone not null default now(),
    amount real null default '0'::real,
    link text null,
    paid_at timestamp with time zone null,
    client uuid null,
    title text null,
    pinned boolean null default false,
    panel_id uuid null default auth.uid (),
    id uuid not null default gen_random_uuid (),
    due_date timestamp with time zone null,
    status text null default 'unpaid'::text,
    stripe_invoice_id text null,
    memo text null,
    type TEXT,
    commission UUID,
    constraint panel_invoices_pkey primary key (id),
    constraint panel_invoices_id_key unique (id),
    constraint panel_invoices_client_fkey foreign key (client) references panel_clients (id),
    constraint panel_invoices_panel_id_fkey foreign key (panel_id) references panel_settings (id) on update cascade on delete cascade,
    constraint fk_commission foreign key (commission) references panel_commissions (id)
  ) tablespace pg_default;

ALTER TABLE public.panel_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.panel_clients ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.panel_commissions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.panel_quotes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.panel_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.panel_invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.products ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.panel_views ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS panel_settings_select ON public.panel_settings;
DROP POLICY IF EXISTS panel_settings_insert ON public.panel_settings;
DROP POLICY IF EXISTS panel_settings_update ON public.panel_settings;

CREATE POLICY panel_settings_select
ON public.panel_settings
FOR SELECT
TO authenticated
USING (id = (SELECT auth.uid()));

CREATE POLICY panel_settings_insert
ON public.panel_settings
FOR INSERT
TO authenticated
WITH CHECK (
  id = (SELECT auth.uid())
  AND NOT EXISTS (
    SELECT 1
    FROM public.panel_clients pc
    WHERE pc.id = (SELECT auth.uid())
  )
);

CREATE POLICY panel_settings_update
ON public.panel_settings
FOR UPDATE
TO authenticated
USING (id = (SELECT auth.uid()))
WITH CHECK (id = (SELECT auth.uid()));

DROP POLICY IF EXISTS panel_clients_select ON public.panel_clients;
DROP POLICY IF EXISTS panel_clients_update ON public.panel_clients;

CREATE POLICY panel_clients_select
ON public.panel_clients
FOR SELECT
TO authenticated
USING (
  panel_id = (SELECT auth.uid())
  OR id = (SELECT auth.uid())
);

CREATE POLICY panel_clients_update
ON public.panel_clients
FOR UPDATE
TO authenticated
USING (panel_id = (SELECT auth.uid()))
WITH CHECK (panel_id = (SELECT auth.uid()));

DROP POLICY IF EXISTS panel_commissions_select ON public.panel_commissions;
DROP POLICY IF EXISTS panel_commissions_insert ON public.panel_commissions;
DROP POLICY IF EXISTS panel_commissions_update ON public.panel_commissions;
DROP POLICY IF EXISTS panel_commissions_delete ON public.panel_commissions;

CREATE POLICY panel_commissions_select
ON public.panel_commissions
FOR SELECT
TO authenticated
USING (
  panel_id = (SELECT auth.uid())
  OR client = (SELECT auth.uid())
);

CREATE POLICY panel_commissions_insert
ON public.panel_commissions
FOR INSERT
TO authenticated
WITH CHECK (
  panel_id = (SELECT auth.uid())
  AND (
    client IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.panel_clients pc
      WHERE pc.id = client
        AND pc.panel_id = (SELECT auth.uid())
    )
  )
  AND (
    product IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.products p
      WHERE p.id = product
        AND p.creator = (SELECT auth.uid())
    )
  )
);

CREATE POLICY panel_commissions_update
ON public.panel_commissions
FOR UPDATE
TO authenticated
USING (panel_id = (SELECT auth.uid()))
WITH CHECK (
  panel_id = (SELECT auth.uid())
  AND (
    client IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.panel_clients pc
      WHERE pc.id = client
        AND pc.panel_id = (SELECT auth.uid())
    )
  )
  AND (
    product IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.products p
      WHERE p.id = product
        AND p.creator = (SELECT auth.uid())
    )
  )
);

CREATE POLICY panel_commissions_delete
ON public.panel_commissions
FOR DELETE
TO authenticated
USING (panel_id = (SELECT auth.uid()));

DROP POLICY IF EXISTS panel_quotes_select ON public.panel_quotes;
DROP POLICY IF EXISTS panel_quotes_insert ON public.panel_quotes;
DROP POLICY IF EXISTS panel_quotes_update ON public.panel_quotes;
DROP POLICY IF EXISTS panel_quotes_delete ON public.panel_quotes;

CREATE POLICY panel_quotes_select
ON public.panel_quotes
FOR SELECT
TO authenticated
USING (
  panel_id = (SELECT auth.uid())
  OR client = (SELECT auth.uid())
);

CREATE POLICY panel_quotes_insert
ON public.panel_quotes
FOR INSERT
TO authenticated
WITH CHECK (
  panel_id = (SELECT auth.uid())
  AND (
    client IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.panel_clients pc
      WHERE pc.id = client
        AND pc.panel_id = (SELECT auth.uid())
    )
  )
);

CREATE POLICY panel_quotes_update
ON public.panel_quotes
FOR UPDATE
TO authenticated
USING (panel_id = (SELECT auth.uid()))
WITH CHECK (
  panel_id = (SELECT auth.uid())
  AND (
    client IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.panel_clients pc
      WHERE pc.id = client
        AND pc.panel_id = (SELECT auth.uid())
    )
  )
);

CREATE POLICY panel_quotes_delete
ON public.panel_quotes
FOR DELETE
TO authenticated
USING (panel_id = (SELECT auth.uid()));

DROP POLICY IF EXISTS panel_requests_select ON public.panel_requests;
DROP POLICY IF EXISTS panel_requests_insert ON public.panel_requests;
DROP POLICY IF EXISTS panel_requests_update ON public.panel_requests;
DROP POLICY IF EXISTS panel_requests_delete ON public.panel_requests;

CREATE POLICY panel_requests_select
ON public.panel_requests
FOR SELECT
TO authenticated
USING (
  panel_id = (SELECT auth.uid())
  OR EXISTS (
    SELECT 1
    FROM public.panel_commissions c
    WHERE c.id = commission
      AND c.client = (SELECT auth.uid())
  )
);

CREATE POLICY panel_requests_insert
ON public.panel_requests
FOR INSERT
TO authenticated
WITH CHECK (
  panel_id = (SELECT auth.uid())
  AND (
    commission IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.panel_commissions c
      WHERE c.id = commission
        AND c.panel_id = (SELECT auth.uid())
    )
  )
);

CREATE POLICY panel_requests_update
ON public.panel_requests
FOR UPDATE
TO authenticated
USING (panel_id = (SELECT auth.uid()))
WITH CHECK (
  panel_id = (SELECT auth.uid())
  AND (
    commission IS NULL
    OR EXISTS (
      SELECT 1
      FROM public.panel_commissions c
      WHERE c.id = commission
        AND c.panel_id = (SELECT auth.uid())
    )
  )
);

CREATE POLICY panel_requests_delete
ON public.panel_requests
FOR DELETE
TO authenticated
USING (panel_id = (SELECT auth.uid()));

DROP POLICY IF EXISTS panel_invoices_select ON public.panel_invoices;
DROP POLICY IF EXISTS panel_invoices_update ON public.panel_invoices;
DROP POLICY IF EXISTS panel_invoices_delete ON public.panel_invoices;

CREATE POLICY panel_invoices_select
ON public.panel_invoices
FOR SELECT
TO authenticated
USING (
  panel_id = (SELECT auth.uid())
  OR client = (SELECT auth.uid())
);

CREATE POLICY panel_invoices_update
ON public.panel_invoices
FOR UPDATE
TO authenticated
USING (panel_id = (SELECT auth.uid()))
WITH CHECK (panel_id = (SELECT auth.uid()));

CREATE POLICY panel_invoices_delete
ON public.panel_invoices
FOR DELETE
TO authenticated
USING (panel_id = (SELECT auth.uid()));

DROP POLICY IF EXISTS notifications_select ON public.notifications;
DROP POLICY IF EXISTS notifications_update ON public.notifications;
DROP POLICY IF EXISTS notifications_delete ON public.notifications;

CREATE POLICY notifications_select
ON public.notifications
FOR SELECT
TO authenticated
USING (panel_id = (SELECT auth.uid()));

CREATE POLICY notifications_update
ON public.notifications
FOR UPDATE
TO authenticated
USING (panel_id = (SELECT auth.uid()))
WITH CHECK (panel_id = (SELECT auth.uid()));

CREATE POLICY notifications_delete
ON public.notifications
FOR DELETE
TO authenticated
USING (panel_id = (SELECT auth.uid()));

DROP POLICY IF EXISTS products_select ON public.products;
DROP POLICY IF EXISTS products_insert ON public.products;
DROP POLICY IF EXISTS products_update ON public.products;
DROP POLICY IF EXISTS products_delete ON public.products;

CREATE POLICY products_select
ON public.products
FOR SELECT
TO authenticated
USING (creator = (SELECT auth.uid()));

CREATE POLICY products_insert
ON public.products
FOR INSERT
TO authenticated
WITH CHECK (creator = (SELECT auth.uid()));

CREATE POLICY products_update
ON public.products
FOR UPDATE
TO authenticated
USING (creator = (SELECT auth.uid()))
WITH CHECK (creator = (SELECT auth.uid()));

CREATE POLICY products_delete
ON public.products
FOR DELETE
TO authenticated
USING (creator = (SELECT auth.uid()));

DROP POLICY IF EXISTS panel_views_select ON public.panel_views;
DROP POLICY IF EXISTS panel_views_insert ON public.panel_views;
DROP POLICY IF EXISTS panel_views_delete ON public.panel_views;

CREATE POLICY panel_views_select
ON public.panel_views
FOR SELECT
TO authenticated
USING (panel_id = (SELECT auth.uid()));

CREATE POLICY panel_views_insert
ON public.panel_views
FOR INSERT
TO authenticated
WITH CHECK (panel_id = (SELECT auth.uid()));

CREATE POLICY panel_views_delete
ON public.panel_views
FOR DELETE
TO authenticated
USING (panel_id = (SELECT auth.uid()));