ALTER TABLE public.bank_reconciliations
  ADD COLUMN IF NOT EXISTS account_id uuid REFERENCES public.accounts(id),
  ADD COLUMN IF NOT EXISTS cleared_balance numeric NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS difference numeric NOT NULL DEFAULT 0,
  ADD COLUMN IF NOT EXISTS created_by uuid,
  ADD COLUMN IF NOT EXISTS closed_at timestamptz;

CREATE TABLE IF NOT EXISTS public.reconciliation_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  reconciliation_id uuid NOT NULL REFERENCES public.bank_reconciliations(id) ON DELETE CASCADE,
  journal_line_id uuid NOT NULL REFERENCES public.journal_lines(id) ON DELETE CASCADE,
  cleared boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (reconciliation_id, journal_line_id)
);

GRANT SELECT, INSERT, UPDATE, DELETE ON public.reconciliation_items TO authenticated;
GRANT ALL ON public.reconciliation_items TO service_role;
ALTER TABLE public.reconciliation_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY recon_items_select ON public.reconciliation_items FOR SELECT TO authenticated USING (public.can_access('cash-bank'));
CREATE POLICY recon_items_insert ON public.reconciliation_items FOR INSERT TO authenticated WITH CHECK (public.can_edit('cash-bank'));
CREATE POLICY recon_items_update ON public.reconciliation_items FOR UPDATE TO authenticated USING (public.can_edit('cash-bank')) WITH CHECK (public.can_edit('cash-bank'));
CREATE POLICY recon_items_delete ON public.reconciliation_items FOR DELETE TO authenticated USING (public.can_edit('cash-bank'));

CREATE TABLE IF NOT EXISTS public.invoice_emails (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  invoice_id uuid NOT NULL REFERENCES public.invoices(id) ON DELETE CASCADE,
  recipient text NOT NULL,
  subject text,
  status text NOT NULL DEFAULT 'sent',
  detail text,
  document_url text,
  sent_by uuid,
  created_at timestamptz NOT NULL DEFAULT now()
);

GRANT SELECT, INSERT ON public.invoice_emails TO authenticated;
GRANT ALL ON public.invoice_emails TO service_role;
ALTER TABLE public.invoice_emails ENABLE ROW LEVEL SECURITY;

CREATE POLICY invoice_emails_select ON public.invoice_emails FOR SELECT TO authenticated USING (public.can_access('invoices'));
CREATE POLICY invoice_emails_insert ON public.invoice_emails FOR INSERT TO authenticated WITH CHECK (public.can_edit('invoices'));

CREATE INDEX IF NOT EXISTS idx_invoice_emails_invoice ON public.invoice_emails (invoice_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_recon_items_recon ON public.reconciliation_items (reconciliation_id);