CREATE TABLE "report_shortcuts" (
	"id" uuid PRIMARY KEY DEFAULT gen_random_uuid() NOT NULL,
	"tenant_id" uuid NOT NULL,
	"user_id" uuid NOT NULL,
	"name" text NOT NULL,
	"report_type" text NOT NULL,
	"params" jsonb NOT NULL,
	"created_at" timestamp with time zone DEFAULT now() NOT NULL,
	CONSTRAINT "report_shortcuts_report_type_check" CHECK ("report_shortcuts"."report_type" IN (
        'revenue','invoices','payments','time','expenses',
        'profitability','revenue_forecast','leads','proposals',
        'ar_aging','bad_debt','audit','api_usage',
        'vat','pnl','cashflow','advance_tax','withholding',
        'bituach_leumi','uniform_format'
      ))
);
--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN IF NOT EXISTS "invoice_reminders_enabled" boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE "tenant_settings" ADD COLUMN IF NOT EXISTS "invoice_reminder_schedule" jsonb DEFAULT '[{"offset_days":-3,"enabled":true},{"offset_days":0,"enabled":true},{"offset_days":7,"enabled":true},{"offset_days":14,"enabled":true},{"offset_days":30,"enabled":true}]'::jsonb NOT NULL;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "reminder_last_sent_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "reminder_count" integer DEFAULT 0 NOT NULL;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "reminders_disabled" boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "next_reminder_at" timestamp with time zone;--> statement-breakpoint
ALTER TABLE "invoices" ADD COLUMN IF NOT EXISTS "next_reminder_offset" integer;--> statement-breakpoint
ALTER TABLE "report_shortcuts" ADD CONSTRAINT "report_shortcuts_tenant_id_tenants_id_fk" FOREIGN KEY ("tenant_id") REFERENCES "public"."tenants"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE "report_shortcuts" ADD CONSTRAINT "report_shortcuts_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX "idx_report_shortcuts_user" ON "report_shortcuts" USING btree ("tenant_id","user_id","created_at" DESC NULLS LAST);
--> statement-breakpoint
-- invoice-payment-reminders (wave-13): partial index for cron query
CREATE INDEX IF NOT EXISTS idx_invoices_next_reminder
  ON invoices (next_reminder_at)
  WHERE reminders_disabled = false AND next_reminder_at IS NOT NULL;
--> statement-breakpoint
-- Backfill next_reminder_at for already-payable invoices.
-- Use the next not-yet-passed stage (or first stage if none passed) to avoid
-- bursting stale reminders on overdue invoices.
-- Default schedule stages: -3, 0, 7, 14, 30 days from due_date.
UPDATE invoices
SET
  next_reminder_at = (
    CASE
      -- find the first enabled stage where (due_date + offset_days) > now()
      WHEN due_date + INTERVAL '-3 days' > now()          THEN due_date + INTERVAL '-3 days'
      WHEN due_date + INTERVAL '0 days'  > now()          THEN due_date + INTERVAL '0 days'
      WHEN due_date + INTERVAL '7 days'  > now()          THEN due_date + INTERVAL '7 days'
      WHEN due_date + INTERVAL '14 days' > now()          THEN due_date + INTERVAL '14 days'
      WHEN due_date + INTERVAL '30 days' > now()          THEN due_date + INTERVAL '30 days'
      ELSE NULL
    END
  ),
  next_reminder_offset = (
    CASE
      WHEN due_date + INTERVAL '-3 days' > now() THEN -3
      WHEN due_date + INTERVAL '0 days'  > now() THEN  0
      WHEN due_date + INTERVAL '7 days'  > now() THEN  7
      WHEN due_date + INTERVAL '14 days' > now() THEN 14
      WHEN due_date + INTERVAL '30 days' > now() THEN 30
      ELSE NULL
    END
  )
WHERE status IN ('SENT', 'APPROVED', 'TAX_ISSUED', 'PARTIALLY_PAID')
  AND due_date IS NOT NULL
  AND reminders_disabled = false;
