Organizations Schema

CREATE TABLE public.organizations (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  logo_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

SELECT * FROM organizations WHERE id = 'org-uuid';

CREATE TABLE public.groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  ldap_dn TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO groups (organization_id, name, description)
VALUES (
  'org-uuid',
  'Engineering',
  'Engineering team members'
)
RETURNING *;

SELECT * FROM groups
WHERE organization_id = 'org-uuid'
ORDER BY name;

CREATE TABLE public.user_groups (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  group_id UUID NOT NULL REFERENCES public.groups(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(user_id, group_id)
);

INSERT INTO user_groups (user_id, group_id)
VALUES ('user-uuid', 'group-uuid')
ON CONFLICT (user_id, group_id) DO NOTHING;

SELECT g.* 
FROM groups g
JOIN user_groups ug ON ug.group_id = g.id
WHERE ug.user_id = 'user-uuid';

SELECT p.id, p.full_name, p.role
FROM profiles p
JOIN user_groups ug ON ug.user_id = p.id
WHERE ug.group_id = 'group-uuid'
ORDER BY p.full_name;

CREATE TABLE public.permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL UNIQUE,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO permissions (name, description) VALUES
  ('resources:read', 'View resources'),
  ('resources:write', 'Create and update resources'),
  ('resources:delete', 'Delete resources'),
  ('users:read', 'View users'),
  ('users:write', 'Create and update users'),
  ('groups:read', 'View groups'),
  ('groups:write', 'Manage groups'),
  ('devices:read', 'View devices'),
  ('devices:manage', 'Manage device enrollment');

CREATE TABLE public.role_permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  role TEXT NOT NULL,
  permission_id UUID NOT NULL REFERENCES public.permissions(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(role, permission_id)
);

INSERT INTO role_permissions (role, permission_id)
SELECT 'org_admin', id FROM permissions WHERE name = 'resources:write';

SELECT p.name, p.description
FROM permissions p
JOIN role_permissions rp ON rp.permission_id = p.id
WHERE rp.role = 'org_admin';

CREATE TABLE public.policies (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  description TEXT,
  policy_type TEXT NOT NULL DEFAULT 'access',
  status TEXT NOT NULL DEFAULT 'draft',
  conditions JSONB DEFAULT '[]'::jsonb,
  applies_to INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

[
  {
    "type": "device_trust",
    "operator": "equals",
    "value": "high"
  },
  {
    "type": "user_group",
    "operator": "in",
    "value": ["group-uuid-1", "group-uuid-2"]
  },
  {
    "type": "time_window",
    "operator": "between",
    "value": {"start": "09:00", "end": "17:00"}
  }
]

CREATE TRIGGER update_policies_updated_at
BEFORE UPDATE ON public.policies
FOR EACH ROW
EXECUTE FUNCTION public.update_updated_at_column();

-- Users can view their own organization
CREATE POLICY "Users can view their own organization"
ON public.organizations FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.profiles
    WHERE profiles.id = auth.uid()
    AND profiles.organization_id = organizations.id
  )
);

-- Global admins can view all organizations
CREATE POLICY "Global admins can view all organizations"
ON public.organizations FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = 'global_admin'
  )
);

-- Global admins can create organizations
CREATE POLICY "Global admins can insert organizations"
ON public.organizations FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = 'global_admin'
  )
);

-- Users can view groups in their org
CREATE POLICY "Users can view groups in their org"
ON public.groups FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id FROM public.profiles WHERE id = auth.uid()
  )
);

-- Admins can manage groups
CREATE POLICY "Admins can manage groups in their org"
ON public.groups FOR ALL
USING (
  organization_id IN (
    SELECT organization_id FROM public.profiles 
    WHERE id = auth.uid() AND role IN ('org_admin', 'global_admin')
  )
);

-- Users can view their own group memberships
CREATE POLICY "Users can view their group memberships"
ON public.user_groups FOR SELECT
USING (user_id = auth.uid());

-- Admins can manage user groups
CREATE POLICY "Admins can manage user groups"
ON public.user_groups FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM profiles p1
    JOIN profiles p2 ON p1.organization_id = p2.organization_id
    WHERE p1.id = auth.uid() 
    AND p2.id = user_groups.user_id
    AND p1.role = ANY (ARRAY['org_admin'::user_role, 'global_admin'::user_role])
  )
);

-- All authenticated users can view permissions
CREATE POLICY "All users can view permissions"
ON public.permissions FOR SELECT
TO authenticated USING (true);

-- All users can view role permissions
CREATE POLICY "All users can view role permissions"
ON public.role_permissions FOR SELECT
USING (true);

-- Only global admins can manage
CREATE POLICY "Global admins can manage role permissions"
ON public.role_permissions FOR ALL
USING (get_user_role(auth.uid()) = 'global_admin'::user_role);

-- Admins can manage policies in their org
CREATE POLICY "Admins can manage policies in their org"
ON public.policies FOR ALL
USING (
  organization_id = get_user_org_id(auth.uid())
  AND get_user_role(auth.uid()) = ANY (ARRAY['org_admin'::user_role, 'global_admin'::user_role])
);

-- Users can view policies in their org
CREATE POLICY "Users can view policies in their org"
ON public.policies FOR SELECT
USING (organization_id = get_user_org_id(auth.uid()));