-- =============================================================================
-- Migration: 040_add_message_type_attribute_redo.sql
-- Description: Add message_type ENUM, attribute_id FK, and is_redo flag to the
--              messages table to support alternating general/attribute/
--              important_date message generation cycles.
-- Retroactive defaults:
--   message_type = 'general'  (correct for all pre-existing rows)
--   is_redo      = 0          (correct for all pre-existing rows)
--   attribute_id = NULL       (correct for all pre-existing rows)
-- =============================================================================

-- ----------------------------------------------------------------------------
-- 1. Add message_type column
-- ----------------------------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'message_type');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE messages ADD COLUMN message_type ENUM(''general'',''attribute'',''important_date'') NOT NULL DEFAULT ''general'' COMMENT ''Kind of message generated: general, attribute-focused, or occasion-driven''',
  'SELECT 1');
PREPARE _stmt FROM @sql; EXECUTE _stmt; DEALLOCATE PREPARE _stmt;

-- ----------------------------------------------------------------------------
-- 2. Add attribute_id column
-- ----------------------------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'attribute_id');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE messages ADD COLUMN attribute_id INT NULL DEFAULT NULL COMMENT ''FK to profile_attributes.id; populated only when message_type = ''''attribute''''''',
  'SELECT 1');
PREPARE _stmt FROM @sql; EXECUTE _stmt; DEALLOCATE PREPARE _stmt;

-- ----------------------------------------------------------------------------
-- 3. Add FK constraint on attribute_id (guarded against duplicate constraint)
-- ----------------------------------------------------------------------------
SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME        = 'messages'
    AND CONSTRAINT_NAME   = 'fk_messages_attribute_id'
    AND CONSTRAINT_TYPE   = 'FOREIGN KEY'
);

SET @sql = IF(
  @fk_exists = 0,
  'ALTER TABLE messages
     ADD CONSTRAINT fk_messages_attribute_id
       FOREIGN KEY (attribute_id)
       REFERENCES profile_attributes(id)
       ON DELETE SET NULL
       ON UPDATE CASCADE',
  'SELECT ''FK fk_messages_attribute_id already exists — skipped'' AS migration_note'
);

PREPARE _stmt FROM @sql;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;

-- ----------------------------------------------------------------------------
-- 4. Add is_redo column
-- ----------------------------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'messages' AND COLUMN_NAME = 'is_redo');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE messages ADD COLUMN is_redo TINYINT(1) NOT NULL DEFAULT 0 COMMENT ''1 = redo-generated message; excluded from alternation position counting''',
  'SELECT 1');
PREPARE _stmt FROM @sql; EXECUTE _stmt; DEALLOCATE PREPARE _stmt;

-- ----------------------------------------------------------------------------
-- 5. Index: profile_id + message_type + generated_at DESC + id DESC
--    Fast lookup of last attribute (or important_date) message per profile
-- ----------------------------------------------------------------------------
SET @idx1_exists = (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = 'messages'
    AND INDEX_NAME   = 'idx_messages_profile_type_generated'
);

SET @sql = IF(
  @idx1_exists = 0,
  'CREATE INDEX idx_messages_profile_type_generated
     ON messages (profile_id, message_type, generated_at DESC, id DESC)',
  'SELECT ''Index idx_messages_profile_type_generated already exists — skipped'' AS migration_note'
);

PREPARE _stmt FROM @sql;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;

-- ----------------------------------------------------------------------------
-- 6. Index: profile_id + is_redo
--    Fast position count (WHERE is_redo = 0) to determine next message type
-- ----------------------------------------------------------------------------
SET @idx2_exists = (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = 'messages'
    AND INDEX_NAME   = 'idx_messages_profile_is_redo'
);

SET @sql = IF(
  @idx2_exists = 0,
  'CREATE INDEX idx_messages_profile_is_redo
     ON messages (profile_id, is_redo)',
  'SELECT ''Index idx_messages_profile_is_redo already exists — skipped'' AS migration_note'
);

PREPARE _stmt FROM @sql;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;
