/*
  # Create notificacoes, whatsapp_logs, webhook_logs, auditoria tables
*/

CREATE TABLE IF NOT EXISTS notificacoes (
  id bigserial PRIMARY KEY,
  empresa_id bigint NOT NULL REFERENCES empresas(id),
  cobranca_id bigint REFERENCES cobrancas(id) ON DELETE SET NULL,
  cliente_id bigint REFERENCES clientes(id) ON DELETE SET NULL,
  tipo text NOT NULL CHECK (tipo IN ('lembrete_7d','lembrete_3d','vencimento','atraso_1d','atraso_3d','atraso_7d','pagamento_confirmado','cobranca_cancelada','manual')),
  canal text NOT NULL DEFAULT 'email' CHECK (canal IN ('email','whatsapp','sms','sistema')),
  status text NOT NULL DEFAULT 'pendente' CHECK (status IN ('pendente','enviado','falhou','entregue')),
  tentativas smallint DEFAULT 0,
  enviado_em timestamptz,
  erro text,
  conteudo text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_notif_empresa_cobranca ON notificacoes(empresa_id, cobranca_id, tipo);

ALTER TABLE notificacoes ENABLE ROW LEVEL SECURITY;

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

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

CREATE POLICY "Update notificacoes"
  ON notificacoes FOR UPDATE
  TO authenticated
  USING (true);


CREATE TABLE IF NOT EXISTS whatsapp_logs (
  id bigserial PRIMARY KEY,
  empresa_id bigint NOT NULL REFERENCES empresas(id),
  cliente_id bigint REFERENCES clientes(id) ON DELETE SET NULL,
  cobranca_id bigint REFERENCES cobrancas(id) ON DELETE SET NULL,
  phone_number text NOT NULL,
  template_name text,
  parametros jsonb,
  message_id text UNIQUE,
  status text NOT NULL DEFAULT 'aceito' CHECK (status IN ('aceito','enviado','entregue','lido','falhou')),
  erro text,
  enviado_em timestamptz,
  entregue_em timestamptz,
  lido_em timestamptz,
  conteudo text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_wa_empresa_cliente ON whatsapp_logs(empresa_id, cliente_id);

ALTER TABLE whatsapp_logs ENABLE ROW LEVEL SECURITY;

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

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

CREATE POLICY "Update whatsapp_logs"
  ON whatsapp_logs FOR UPDATE
  TO authenticated
  USING (true);


CREATE TABLE IF NOT EXISTS webhook_logs (
  id bigserial PRIMARY KEY,
  provedor text NOT NULL,
  empresa_id bigint REFERENCES empresas(id) ON DELETE SET NULL,
  evento text,
  payload jsonb,
  headers jsonb,
  status text NOT NULL DEFAULT 'recebido' CHECK (status IN ('recebido','processado','falhou','ignorado')),
  processado_em timestamptz,
  erro text,
  tentativas smallint DEFAULT 0,
  ip_origem text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_webhook_provedor_status ON webhook_logs(provedor, status);
CREATE INDEX idx_webhook_empresa ON webhook_logs(empresa_id);

ALTER TABLE webhook_logs ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Webhook logs read"
  ON webhook_logs FOR SELECT
  TO authenticated
  USING (true);

CREATE POLICY "Webhook logs insert"
  ON webhook_logs FOR INSERT
  TO authenticated
  WITH CHECK (true);

CREATE POLICY "Webhook logs update"
  ON webhook_logs FOR UPDATE
  TO authenticated
  USING (true);

-- Also allow anon insert for public webhook endpoints
CREATE POLICY "Anon can insert webhook_logs"
  ON webhook_logs FOR INSERT
  TO anon
  WITH CHECK (true);


CREATE TABLE IF NOT EXISTS auditoria (
  id bigserial PRIMARY KEY,
  empresa_id bigint REFERENCES empresas(id) ON DELETE SET NULL,
  usuario_id bigint REFERENCES usuarios(id) ON DELETE SET NULL,
  model_type text NOT NULL,
  model_id bigint NOT NULL,
  evento text NOT NULL,
  valores_antigos jsonb,
  valores_novos jsonb,
  ip text,
  user_agent text,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_auditoria_empresa_model ON auditoria(empresa_id, model_type, model_id);
CREATE INDEX idx_auditoria_created ON auditoria(created_at);

ALTER TABLE auditoria ENABLE ROW LEVEL SECURITY;

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

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