/*
  # Create clientes table

  Customers per company with full Brazilian data, portal access.
*/

CREATE TABLE IF NOT EXISTS clientes (
  id bigserial PRIMARY KEY,
  empresa_id bigint NOT NULL REFERENCES empresas(id) ON DELETE CASCADE,
  uuid uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  nome text NOT NULL,
  email text,
  cpf_cnpj text,
  telefone text,
  celular text,
  whatsapp text,
  tipo_pessoa text NOT NULL DEFAULT 'fisica' CHECK (tipo_pessoa IN ('fisica','juridica')),
  cep text,
  logradouro text,
  numero text,
  complemento text,
  bairro text,
  cidade text,
  estado text,
  pais text DEFAULT 'Brasil',
  status text NOT NULL DEFAULT 'ativo' CHECK (status IN ('ativo','inativo','bloqueado')),
  observacoes text,
  gateway_cliente_id jsonb,
  portal_token text,
  portal_senha text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  deleted_at timestamptz
);

CREATE INDEX idx_clientes_empresa_status ON clientes(empresa_id, status);
CREATE INDEX idx_clientes_empresa_cpf ON clientes(empresa_id, cpf_cnpj);
CREATE INDEX idx_clientes_empresa_email ON clientes(empresa_id, email);

ALTER TABLE clientes ENABLE ROW LEVEL SECURITY;

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

CREATE POLICY "Insert clientes"
  ON clientes FOR INSERT
  TO authenticated
  WITH CHECK (empresa_id = (SELECT empresa_id FROM usuarios WHERE supabase_uuid = auth.uid()));

CREATE POLICY "Update clientes"
  ON clientes FOR UPDATE
  TO authenticated
  USING (empresa_id = (SELECT empresa_id FROM usuarios WHERE supabase_uuid = auth.uid()));

CREATE POLICY "Delete clientes"
  ON clientes FOR DELETE
  TO authenticated
  USING (empresa_id = (SELECT empresa_id FROM usuarios WHERE supabase_uuid = auth.uid()));