-- Saved reports (presets): a standard report frozen with its date range +
-- summarize + a name, per user. Behaves like a custom report — shows in the
-- sidebar, opens at its saved period without re-picking dates, and (when a
-- Google Sheet is mapped) re-syncs to that sheet with the saved period.
--
-- The sheet-sync target is embedded (1 preset = 1 sheet), so a preset is fully
-- self-contained. Column names mirror export_templates (spreadsheet_id,
-- data_tab_name, detail_tab_name, summarize_by) so both share the same
-- SyncsReportToSheet trait without per-row remapping.
--
-- Per-user like custom_reports/export_templates (migration 011): scoped by
-- user_id, UNIQUE on (user_id, name) so re-saving the same name overwrites.

CREATE TABLE IF NOT EXISTS saved_reports (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  user_id         INT NOT NULL,
  company_id      INT NOT NULL,
  name            VARCHAR(100) NOT NULL,
  report_type     VARCHAR(50) NOT NULL,
  period_start    DATE NOT NULL,
  period_end      DATE NOT NULL,
  summarize_by    VARCHAR(20) NOT NULL DEFAULT 'Month',
  -- Optional sync target (embedded). NULL = view-only preset (no sheet yet).
  spreadsheet_id  VARCHAR(255) DEFAULT NULL,
  spreadsheet_url VARCHAR(512) DEFAULT NULL,
  data_tab_name   VARCHAR(100) DEFAULT NULL,
  detail_tab_name VARCHAR(100) DEFAULT NULL,
  last_synced_at  TIMESTAMP NULL,
  created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_user_name (user_id, name),
  FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
