{"-- Create Aman (Home Assistant) instances table for per-customer/branch SaaS integration
CREATE TABLE IF NOT EXISTS public.aman_instances (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id uuid NOT NULL,
  branch_id uuid NULL,
  name text NOT NULL,
  base_url text NOT NULL,
  access_token text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_by uuid NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
)","-- Enable Row Level Security
ALTER TABLE public.aman_instances ENABLE ROW LEVEL SECURITY","-- Trigger to keep updated_at in sync
DO $$ BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_trigger WHERE tgname = 'update_aman_instances_updated_at'
  ) THEN
    CREATE TRIGGER update_aman_instances_updated_at
    BEFORE UPDATE ON public.aman_instances
    FOR EACH ROW EXECUTE FUNCTION public.update_modified_column();
  END IF;
END $$","-- Policies: Admins & Managers of the organization can manage; all org users can view
DROP POLICY IF EXISTS \"Admins and managers can manage Aman instances\" ON public.aman_instances","CREATE POLICY \"Admins and managers can manage Aman instances\"
ON public.aman_instances
FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM public.user_roles ur
    WHERE ur.user_id = auth.uid()
      AND ur.organization_id = aman_instances.organization_id
      AND ur.role IN ('admin','manager')
  )
)
WITH CHECK (
  EXISTS (
    SELECT 1 FROM public.user_roles ur
    WHERE ur.user_id = auth.uid()
      AND ur.organization_id = aman_instances.organization_id
      AND ur.role IN ('admin','manager')
  )
)","DROP POLICY IF EXISTS \"Users can view Aman instances in their organization\" ON public.aman_instances","CREATE POLICY \"Users can view Aman instances in their organization\"
ON public.aman_instances
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM public.user_roles ur
    WHERE ur.user_id = auth.uid()
      AND ur.organization_id = aman_instances.organization_id
  )
)","-- Restrict column-level access so tokens are never exposed to clients
REVOKE ALL ON TABLE public.aman_instances FROM anon, authenticated","GRANT INSERT, UPDATE, DELETE ON TABLE public.aman_instances TO authenticated","GRANT SELECT (id, organization_id, branch_id, name, base_url, active, created_by, created_at, updated_at)
  ON public.aman_instances TO authenticated","-- Helpful indexes
CREATE INDEX IF NOT EXISTS idx_aman_instances_org ON public.aman_instances(organization_id)","CREATE INDEX IF NOT EXISTS idx_aman_instances_branch ON public.aman_instances(branch_id)"}