/*
  # Create usuarios table with UUID auth compatibility

  1. New Tables
    - `usuarios` with supabase_uuid column linking to auth.users
    
  2. Security
    - Enable RLS
    - Match auth.uid() (uuid) to supabase_uuid column
*/

CREATE TABLE IF NOT EXISTS usuarios (
  id bigserial PRIMARY KEY,
  supabase_uuid uuid REFERENCES auth.users(id) ON DELETE SET NULL,
  empresa_id bigint REFERENCES empresas(id) ON DELETE SET NULL,
  nome text NOT NULL,
  email text NOT NULL UNIQUE,
  password text NOT NULL,
  role text NOT NULL DEFAULT 'admin' CHECK (role IN ('admin','operador','visualizador')),
  ativo boolean NOT NULL DEFAULT true,
  is_super_admin boolean NOT NULL DEFAULT false,
  avatar_path text,
  telefone text,
  notificacoes_email boolean NOT NULL DEFAULT true,
  notificacoes_sistema boolean NOT NULL DEFAULT true,
  ultimo_acesso timestamptz,
  remember_token text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  deleted_at timestamptz
);

CREATE INDEX idx_usuarios_empresa ON usuarios(empresa_id, ativo);
CREATE INDEX idx_usuarios_uuid ON usuarios(supabase_uuid);

ALTER TABLE usuarios ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own record"
  ON usuarios FOR SELECT
  TO authenticated
  USING (supabase_uuid = auth.uid() OR empresa_id = (SELECT empresa_id FROM usuarios WHERE supabase_uuid = auth.uid()));

CREATE POLICY "Users can update own record"
  ON usuarios FOR UPDATE
  TO authenticated
  USING (supabase_uuid = auth.uid());

CREATE POLICY "Insert usuarios"
  ON usuarios FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Delete usuarios"
  ON usuarios FOR DELETE
  TO authenticated
  USING (true);