-- =============================================================================
-- Migration: 042_add_user_id_to_attributes.sql
-- Description: Add user_id column to the attributes table to support
--              user-specific custom attributes alongside common attributes.
--
--   user_id = NULL   → common attribute (shown to all users; existing 31 rows)
--   user_id = <id>   → user-specific attribute (visible only to that user)
--
-- Existing 31 rows automatically retain user_id = NULL (no data changes needed).
-- =============================================================================

-- ----------------------------------------------------------------------------
-- 1. Add user_id column (after is_active)
-- ----------------------------------------------------------------------------
SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attributes' AND COLUMN_NAME = 'user_id');
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE attributes ADD COLUMN user_id INT NULL DEFAULT NULL AFTER is_active',
  'SELECT 1');
PREPARE _stmt FROM @sql; EXECUTE _stmt; DEALLOCATE PREPARE _stmt;

-- ----------------------------------------------------------------------------
-- 2. Add FK constraint fk_attributes_user_id → users(id) ON DELETE CASCADE
-- ----------------------------------------------------------------------------
SET @fk_exists = (
  SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
  WHERE CONSTRAINT_SCHEMA = DATABASE()
    AND TABLE_NAME        = 'attributes'
    AND CONSTRAINT_NAME   = 'fk_attributes_user_id'
    AND CONSTRAINT_TYPE   = 'FOREIGN KEY'
);

SET @sql = IF(
  @fk_exists = 0,
  'ALTER TABLE attributes
     ADD CONSTRAINT fk_attributes_user_id
       FOREIGN KEY (user_id)
       REFERENCES users(id)
       ON DELETE CASCADE',
  'SELECT ''FK fk_attributes_user_id already exists — skipped'' AS migration_note'
);

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

-- ----------------------------------------------------------------------------
-- 3. Add index idx_attributes_user_id on attributes(user_id)
-- ----------------------------------------------------------------------------
SET @idx_exists = (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = 'attributes'
    AND INDEX_NAME   = 'idx_attributes_user_id'
);

SET @sql = IF(
  @idx_exists = 0,
  'CREATE INDEX idx_attributes_user_id ON attributes (user_id)',
  'SELECT ''Index idx_attributes_user_id already exists — skipped'' AS migration_note'
);

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

-- ----------------------------------------------------------------------------
-- 4. Add generated stored column name_lower for case-insensitive uniqueness
-- ----------------------------------------------------------------------------
SET @col_lower_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'attributes' AND COLUMN_NAME = 'name_lower');
SET @sql = IF(@col_lower_exists = 0,
  'ALTER TABLE attributes ADD COLUMN name_lower VARCHAR(100) GENERATED ALWAYS AS (LOWER(TRIM(name))) STORED',
  'SELECT ''Column name_lower already exists — skipped'' AS migration_note');
PREPARE _stmt FROM @sql; EXECUTE _stmt; DEALLOCATE PREPARE _stmt;

-- ----------------------------------------------------------------------------
-- 5. Add unique index uq_attributes_name_lower_user on (name_lower, user_id)
-- ----------------------------------------------------------------------------
SET @uq_exists = (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME   = 'attributes'
    AND INDEX_NAME   = 'uq_attributes_name_lower_user'
);
SET @sql = IF(
  @uq_exists = 0,
  'CREATE UNIQUE INDEX uq_attributes_name_lower_user ON attributes (name_lower, user_id)',
  'SELECT ''Index uq_attributes_name_lower_user already exists — skipped'' AS migration_note'
);
PREPARE _stmt FROM @sql;
EXECUTE _stmt;
DEALLOCATE PREPARE _stmt;
