/*
  # Create cobrancas table

  Core billing entity: charges with payment methods, gateway IDs, notification flags.
*/

CREATE TABLE IF NOT EXISTS cobrancas (
  id bigserial PRIMARY KEY,
  empresa_id bigint NOT NULL REFERENCES empresas(id) ON DELETE CASCADE,
  cliente_id bigint NOT NULL REFERENCES clientes(id),
  uuid uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
  descricao text NOT NULL,
  valor bigint NOT NULL,
  valor_desconto bigint NOT NULL DEFAULT 0,
  valor_juros bigint NOT NULL DEFAULT 0,
  valor_multa bigint NOT NULL DEFAULT 0,
  valor_final bigint NOT NULL DEFAULT 0,
  data_vencimento date NOT NULL,
  data_competencia date,
  data_pagamento timestamptz,
  status text NOT NULL DEFAULT 'pendente' CHECK (status IN ('pendente','pago','vencido','cancelado','estornado','aguardando')),
  metodo_pagamento text CHECK (metodo_pagamento IN ('pix','boleto','cartao_credito','cartao_debito','link')),
  gateway text,
  gateway_cobranca_id text,
  link_pagamento text,
  numero_parcelas smallint NOT NULL DEFAULT 1,
  parcela_numero smallint NOT NULL DEFAULT 1,
  cobranca_pai_id bigint REFERENCES cobrancas(id) ON DELETE SET NULL,
  recorrente boolean NOT NULL DEFAULT false,
  frequencia_recorrencia text,
  tentativas_cobranca smallint NOT NULL DEFAULT 0,
  max_tentativas smallint NOT NULL DEFAULT 3,
  juros_percentual numeric(5,2) DEFAULT 0,
  multa_percentual numeric(5,2) DEFAULT 0,
  desconto_percentual numeric(5,2) DEFAULT 0,
  instrucoes text,
  notificado_7d boolean NOT NULL DEFAULT false,
  notificado_3d boolean NOT NULL DEFAULT false,
  notificado_vencimento boolean NOT NULL DEFAULT false,
  notificado_1d_atraso boolean NOT NULL DEFAULT false,
  notificado_3d_atraso boolean NOT NULL DEFAULT false,
  notificado_7d_atraso boolean NOT NULL DEFAULT false,
  observacoes text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),
  deleted_at timestamptz
);

CREATE INDEX idx_cobrancas_empresa_status ON cobrancas(empresa_id, status);
CREATE INDEX idx_cobrancas_empresa_venc ON cobrancas(empresa_id, data_vencimento);
CREATE INDEX idx_cobrancas_empresa_cliente ON cobrancas(empresa_id, cliente_id);
CREATE INDEX idx_cobrancas_gateway ON cobrancas(gateway, gateway_cobranca_id);

ALTER TABLE cobrancas ENABLE ROW LEVEL SECURITY;

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

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

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

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

-- Public read by uuid (for customer portal)
CREATE POLICY "Public can read cobranca by uuid"
  ON cobrancas FOR SELECT
  TO anon, authenticated
  USING (uuid::text = current_setting('request.jwt.claims', true)::json->>'cobranca_uuid');