-- reports-analytics (wave A): dashboards + dashboard_widgets for custom analytics layouts

CREATE TABLE dashboards (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id     UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  name        TEXT NOT NULL,
  is_default  BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_dashboards_tenant_user ON dashboards(tenant_id, user_id);

CREATE TABLE dashboard_widgets (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  dashboard_id  UUID NOT NULL REFERENCES dashboards(id) ON DELETE CASCADE,
  tenant_id     UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  widget_type   TEXT NOT NULL CHECK (widget_type IN (
                  'revenue_kpi','invoice_status_pipeline','open_invoices_aging',
                  'time_by_project','time_by_team_member','billable_vs_nonbillable',
                  'customer_revenue','expense_breakdown','leads_funnel',
                  'lead_pipeline_by_stage','ticket_resolution_time','ticket_volume_by_category')),
  position_x    INTEGER NOT NULL,
  position_y    INTEGER NOT NULL,
  width         INTEGER NOT NULL DEFAULT 1,
  height        INTEGER NOT NULL DEFAULT 1,
  config        JSONB NOT NULL DEFAULT '{}',
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_dashboard_widgets_dashboard ON dashboard_widgets(dashboard_id);
