-- Add feedback_type column to idea_notes table
-- Previously defined in Prisma schema but missing from formal migrations.
-- Column may already exist from `prisma db push`; use IF NOT EXISTS pattern.

-- MySQL 8.0 does not support IF NOT EXISTS for ADD COLUMN directly.
-- Use a procedure to make this idempotent.
DROP PROCEDURE IF EXISTS add_feedback_type_if_missing;
DELIMITER $$
CREATE PROCEDURE add_feedback_type_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'idea_notes' AND COLUMN_NAME = 'feedback_type'
  ) THEN
    ALTER TABLE `idea_notes` ADD COLUMN `feedback_type` VARCHAR(191) NOT NULL DEFAULT 'idea';
  END IF;
END$$
DELIMITER ;
CALL add_feedback_type_if_missing();
DROP PROCEDURE IF EXISTS add_feedback_type_if_missing;

-- Add index if not already present
DROP PROCEDURE IF EXISTS add_feedback_type_index_if_missing;
DELIMITER $$
CREATE PROCEDURE add_feedback_type_index_if_missing()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.STATISTICS
    WHERE TABLE_NAME = 'idea_notes' AND INDEX_NAME = 'idea_notes_feedback_type_idx'
  ) THEN
    CREATE INDEX `idea_notes_feedback_type_idx` ON `idea_notes`(`feedback_type`);
  END IF;
END$$
DELIMITER ;
CALL add_feedback_type_index_if_missing();
DROP PROCEDURE IF EXISTS add_feedback_type_index_if_missing;

-- Backfill: records created before this column existed get default 'idea' via the column default.
-- Stale data correction (mismatched feedbackType vs title content) handled by seed/backfill scripts.