---------------------------------------------------
-- テナントメンバーシップチェック関数
---------------------------------------------------
CREATE OR REPLACE FUNCTION check_tenant_membership(tenant_id_param INTEGER, user_id_param UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 
    FROM tenant_members
    WHERE tenant_id = tenant_id_param
      AND user_id = user_id_param
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TABLE tenant_members (
  tenant_id INTEGER NOT NULL REFERENCES tenants(id),
  user_id   uuid NOT NULL REFERENCES users(id),
  role      staff_role NOT NULL DEFAULT 'staff',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (user_id, tenant_id)
);

ex:

CREATE POLICY "Tenant users can create visits."
  ON visits FOR INSERT
  TO authenticated
  WITH CHECK (
    check_tenant_membership(visits.tenant_id, (select auth.uid()))
  );

CREATE POLICY "Tenant users can update visits."
  ON visits FOR UPDATE
  TO authenticated
  USING (
    check_tenant_membership(visits.tenant_id, (select auth.uid()))
  )
  WITH CHECK (
    check_tenant_membership(visits.tenant_id, (select auth.uid()))
  );

CREATE VIEW view_tenant_users AS
SELECT 
    tu.tenant_id,
    u.id AS user_id,
    u.name,
    u.created_at,
    u.updated_at,
    tu.memo
FROM tenant_users tu
JOIN users u ON tu.user_id = u.id
WHERE u.id = auth.uid();

create function public.handle_update_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  update public.users
  set email = new.email
  where id = new.id;
  return new;
end;
$$;

create trigger on_auth_user_updated
  after update on auth.users
  for each row execute procedure public.handle_update_user();

DO $$
DECLARE
    r record;
BEGIN
    FOR r IN
        SELECT t.typname AS enum_name
        FROM pg_type t
        JOIN pg_namespace n ON t.typnamespace = n.oid
        WHERE t.typtype = 'e'
          AND n.nspname = 'public'
    LOOP
        EXECUTE 'DROP TYPE public.' || quote_ident(r.enum_name) || ' CASCADE';
    END LOOP;
END$$;


DO $$ 
DECLARE 
    r RECORD;
BEGIN 
    FOR r IN (SELECT tablename FROM pg_tables WHERE schemaname = 'public') LOOP 
        EXECUTE 'DROP TABLE IF EXISTS public.' || quote_ident(r.tablename) || ' CASCADE';
    END LOOP; 
END $$;

-- Create the tasks table
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_id uuid NOT NULL REFERENCES basejump.accounts(id),
    title TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- enable RLS on tasks table
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- allow team members to view tasks
CREATE POLICY "Only members can view tasks" on tasks
    FOR SELECT
    TO authenticated
    USING (
        account_id IN (SELECT basejump.get_accounts_for_current_user())
    );

   "response": [
      {
        "headers": [
          {
            "content_length": "56",
            "content_type": "application/json",
            "date": "Sun, 09 Mar 2025 06:27:30 GMT",
            "server": "cloudflare",
            "vary": "Accept-Encoding",
            "x_sb_compute_multiplier": null,
            "x_sb_edge_region": "ap-northeast-1",
            "x_sb_resource_multiplier": null,
            "x_served_by": "supabase-edge-runtime"
          }

      let query = supabase
        .from('visits')
        .select(`
          *,
          view_tenant_users!inner(name)
        `)
        .eq('tenant_id', tenantId);

      if (filters.name) {
        query = query.filter('view_tenant_users.name', 'ilike', `%${filters.name}%`);
      } 

const { data, count } = supabase
  .from('countries')
  .select('*', { count: 'exact' })

ALTER ROLE authenticator SET pgrst.db_aggregates_enabled = 'true';
NOTIFY pgrst, 'reload config';

((bucket_id = 'shop'::text) AND EXISTS (
SELECT 1
FROM tenant_members
WHERE tenant_id = ((storage.foldername(name))[1])::integer
AND user_id = auth.uid()
))

((bucket_id = 'shop'::text) AND 
check_tenant_membership(
(
(storage.foldername(name))[1])::integer,
 ( SELECT auth.uid() AS uid))
)

rm -rf supabsae/migrations
rm -rf supabsae/schemas

# 下記のようなものをすべて実行
supabase migration repair --status reverted 20250409123154
supabase migration repair --status reverted 20250410082044
supabase migration repair --status reverted 20250419115236
supabase migration repair --status reverted 20250419115605
supabase migration repair --status reverted 20250419142729
supabase migration repair --status reverted 20250424005616

supabase db pull
supabase db pull --schema auth,storage

CREATE OR REPLACE FUNCTION public.check_tenant_role(
  tenant_id_param  INTEGER,
  user_id_param    UUID,
  required_roles   public.staff_role[]
)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''               -- ← ★ここで固定
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM public.tenant_members      -- ← ★スキーマを明示
    WHERE tenant_id = tenant_id_param
      AND user_id  = user_id_param
      AND role     = ANY(required_roles)
  );
END;
$$;


SELECT check_tenant_role(1, '00000000-0000-0000-0000-000000000000', ARRAY['admin', 'manager']::public.staff_role[]);

supabase migration repair 20250621103402 --status reverted --linked