/*
  # Create pagamentos, boletos, pix tables

  Payment records, boleto-specific data, PIX-specific data.
*/

CREATE TABLE IF NOT EXISTS pagamentos (
  id bigserial PRIMARY KEY,
  empresa_id bigint NOT NULL REFERENCES empresas(id),
  cobranca_id bigint NOT NULL REFERENCES cobrancas(id),
  gateway text NOT NULL,
  gateway_pagamento_id text,
  gateway_ordem_id text,
  valor bigint NOT NULL,
  status text NOT NULL DEFAULT 'pendente' CHECK (status IN ('pendente','aprovado','rejeitado','estornado','cancelado')),
  metodo_pagamento text,
  data_aprovacao timestamptz,
  data_rejeicao timestamptz,
  nsu text,
  codigo_autorizacao text,
  motivo_rejeicao text,
  payload_retorno jsonb,
  ip_pagador text,
  dados_cartao jsonb,
  parcelas smallint DEFAULT 1,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_pagamentos_empresa_cobranca ON pagamentos(empresa_id, cobranca_id);
CREATE INDEX idx_pagamentos_gateway ON pagamentos(gateway, gateway_pagamento_id);

ALTER TABLE pagamentos ENABLE ROW LEVEL SECURITY;

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

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

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


CREATE TABLE IF NOT EXISTS boletos (
  id bigserial PRIMARY KEY,
  empresa_id bigint NOT NULL REFERENCES empresas(id),
  cobranca_id bigint NOT NULL UNIQUE REFERENCES cobrancas(id),
  nosso_numero text,
  linha_digitavel text,
  codigo_barras text,
  url_boleto text,
  url_pdf text,
  data_vencimento date NOT NULL,
  instrucoes text,
  status_boleto text DEFAULT 'gerado',
  data_pagamento timestamptz,
  valor_pago bigint,
  gateway_id text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_boletos_empresa ON boletos(empresa_id);

ALTER TABLE boletos ENABLE ROW LEVEL SECURITY;

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

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

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


CREATE TABLE IF NOT EXISTS pix (
  id bigserial PRIMARY KEY,
  empresa_id bigint NOT NULL REFERENCES empresas(id),
  cobranca_id bigint NOT NULL UNIQUE REFERENCES cobrancas(id),
  gateway text NOT NULL,
  txid text UNIQUE,
  end_to_end_id text,
  qr_code text,
  qr_code_base64 text,
  url_qr_code text,
  chave_pix text,
  data_expiracao timestamptz,
  data_pagamento timestamptz,
  valor_pago bigint,
  gateway_id text,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

CREATE INDEX idx_pix_empresa ON pix(empresa_id);
CREATE INDEX idx_pix_txid ON pix(txid);

ALTER TABLE pix ENABLE ROW LEVEL SECURITY;

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

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

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