Applications Schema

CREATE TABLE public.resources (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  resource_type public.resource_type NOT NULL,
  ip_address TEXT,
  connection_method TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE TYPE public.resource_type AS ENUM (
  'windows_vm',
  'linux_vm',
  'rdp',
  'ssh',
  'guacamole_session',
  'tsplus_html5',
  'tailscale_node',
  'web_app',
  'direct',
  'custom'
);

CREATE INDEX idx_resources_organization_id ON public.resources(organization_id);

CREATE TABLE public.user_resource_access (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  resource_id UUID NOT NULL REFERENCES public.resources(id) ON DELETE CASCADE,
  status public.access_status DEFAULT 'pending',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  activated_at TIMESTAMP WITH TIME ZONE,
  revoked_at TIMESTAMP WITH TIME ZONE,
  UNIQUE(user_id, resource_id)
);

CREATE TYPE public.access_status AS ENUM (
  'pending',   -- Access requested but not yet approved
  'active',    -- Access granted and active
  'revoked'    -- Access revoked
);

CREATE INDEX idx_user_resource_access_user_id ON public.user_resource_access(user_id);
CREATE INDEX idx_user_resource_access_resource_id ON public.user_resource_access(resource_id);

CREATE TABLE public.resource_permissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource_id UUID NOT NULL REFERENCES public.resources(id) ON DELETE CASCADE,
  subject_type TEXT NOT NULL,
  subject_id UUID NOT NULL,
  permission_level TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE(resource_id, subject_type, subject_id)
);

CREATE TABLE public.cloud_providers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  provider_type TEXT NOT NULL,
  api_endpoint TEXT,
  credentials JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.hypervisors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  hypervisor_type TEXT NOT NULL,
  api_endpoint TEXT NOT NULL,
  credentials JSONB,
  metadata JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE public.ldap_configurations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES public.organizations(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  server_url TEXT NOT NULL,
  bind_dn TEXT,
  base_dn TEXT NOT NULL,
  credentials JSONB,
  created_at TIMESTAMPTZ DEFAULT now()
);

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

INSERT INTO resources (
  organization_id,
  name,
  resource_type,
  connection_method,
  metadata
) VALUES (
  'org-uuid',
  'Customer Portal',
  'web_app',
  'direct',
  '{
    "external_url": "https://portal.company.com",
    "icon_url": "https://portal.company.com/icon.png",
    "supports_sso": true
  }'::jsonb
)
RETURNING *;

INSERT INTO user_resource_access (
  user_id,
  resource_id,
  status,
  activated_at
) VALUES (
  'user-uuid',
  'resource-uuid',
  'active',
  now()
)
ON CONFLICT (user_id, resource_id) 
DO UPDATE SET 
  status = 'active',
  activated_at = now();

SELECT 
  r.id,
  r.name,
  r.resource_type,
  r.connection_method,
  r.metadata,
  ura.status,
  ura.activated_at
FROM resources r
JOIN user_resource_access ura ON ura.resource_id = r.id
WHERE ura.user_id = 'user-uuid'
  AND ura.status = 'active'
ORDER BY r.name;

SELECT 
  p.id as user_id,
  p.full_name,
  p.role,
  ura.status,
  ura.activated_at,
  ura.revoked_at
FROM user_resource_access ura
JOIN profiles p ON p.id = ura.user_id
WHERE ura.resource_id = 'resource-uuid'
ORDER BY ura.status, p.full_name;

-- Users can view resources in their organization
CREATE POLICY "Users can view resources in their organization"
ON public.resources FOR SELECT
USING (
  organization_id IN (
    SELECT organization_id FROM public.profiles WHERE id = auth.uid()
  )
  OR EXISTS (
    SELECT 1 FROM public.profiles
    WHERE profiles.id = auth.uid()
    AND profiles.role = 'global_admin'
  )
);

-- Org admins can manage resources
CREATE POLICY "Org admins can manage resources in their org"
ON public.resources 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 access
CREATE POLICY "Users can view their own access"
ON public.user_resource_access FOR SELECT
USING (user_id = auth.uid());

-- Admins can view all access in their org
CREATE POLICY "Admins can view all access in their org"
ON public.user_resource_access FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.profiles p1
    JOIN public.profiles p2 ON p1.organization_id = p2.organization_id
    WHERE p1.id = auth.uid()
    AND p2.id = user_resource_access.user_id
    AND p1.role IN ('org_admin', 'support', 'global_admin')
  )
);

-- Admins can manage access
CREATE POLICY "Admins can manage access in their org"
ON public.user_resource_access FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM public.profiles p1
    JOIN public.profiles p2 ON p1.organization_id = p2.organization_id
    WHERE p1.id = auth.uid()
    AND p2.id = user_resource_access.user_id
    AND p1.role IN ('org_admin', 'global_admin')
  )
);

-- Only admins can view cloud providers
CREATE POLICY "Only admins can view cloud providers"
ON public.cloud_providers FOR SELECT
USING (
  organization_id = public.get_user_org_id(auth.uid())
  AND public.get_user_role(auth.uid()) IN ('org_admin', 'global_admin')
);

-- Similar policies for hypervisors, LDAP, Authentik configs