-- Make custom_reports and export_templates per-user.
--
-- Previously both were keyed by company_id only, so every authenticated user
-- saw the same set of custom reports and synced to the same Google Sheets.
-- Now each user owns their own list. Cached report data (cached_reports table)
-- stays company-level since the underlying QBO data is shared.
--
-- Backfill strategy:
-- Existing rows are assigned to the first active analyst, falling back to the
-- first allowed user of any role. Analyst-first is intentional because Catalina
-- (analyst) built OBM Expenses and is the analyst-role owner across both dev
-- seed data and production, while Yaritza (admin) is the other operator.
--
-- Ordering note: the old UNIQUE keys (company_id, ...) are what currently
-- supports the company_id foreign keys. We add a plain index on company_id
-- BEFORE dropping the unique, so the FK keeps an index available.

-- --- custom_reports ---------------------------------------------------------

-- Add nullable first so we can backfill intelligently before NOT NULL kicks in.
ALTER TABLE custom_reports
  ADD COLUMN user_id INT NULL AFTER company_id;

-- Backfill: prefer the first analyst, fall back to the first allowed user.
-- COALESCE makes the migration safe in setups missing one of the two roles.
UPDATE custom_reports SET user_id = COALESCE(
  (SELECT id FROM users WHERE role = 'analyst' AND is_allowed = 1 ORDER BY id LIMIT 1),
  (SELECT id FROM users WHERE is_allowed = 1 ORDER BY id LIMIT 1)
) WHERE user_id IS NULL;

ALTER TABLE custom_reports
  MODIFY user_id INT NOT NULL;
ALTER TABLE custom_reports
  ADD CONSTRAINT fk_custom_reports_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE custom_reports
  ADD KEY idx_company_id (company_id);
ALTER TABLE custom_reports
  DROP INDEX unique_company_name;
ALTER TABLE custom_reports
  ADD UNIQUE KEY unique_user_name (user_id, company_id, name);

-- --- export_templates -------------------------------------------------------

ALTER TABLE export_templates
  ADD COLUMN user_id INT NULL AFTER company_id;

UPDATE export_templates SET user_id = COALESCE(
  (SELECT id FROM users WHERE role = 'analyst' AND is_allowed = 1 ORDER BY id LIMIT 1),
  (SELECT id FROM users WHERE is_allowed = 1 ORDER BY id LIMIT 1)
) WHERE user_id IS NULL;

ALTER TABLE export_templates
  MODIFY user_id INT NOT NULL;
ALTER TABLE export_templates
  ADD CONSTRAINT fk_export_templates_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
ALTER TABLE export_templates
  ADD KEY idx_company_id (company_id);
ALTER TABLE export_templates
  DROP INDEX unique_mapping;
ALTER TABLE export_templates
  ADD UNIQUE KEY unique_user_mapping (user_id, company_id, spreadsheet_id, report_type, summarize_by);
