-- EnglishSpeak
-- Fixed migration for MariaDB/Laragon
-- Link activities.flashcard_set_id -> flashcard_sets.id

SET @db_name := DATABASE();

-- 1. Read exact type of flashcard_sets.id
SELECT COLUMN_TYPE
INTO @flashcard_set_id_type
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'flashcard_sets'
  AND COLUMN_NAME = 'id'
LIMIT 1;

-- 2. Add activities.flashcard_set_id only if missing
SELECT COUNT(*)
INTO @column_exists
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'activities'
  AND COLUMN_NAME = 'flashcard_set_id';

SET @add_column_sql := IF(
    @column_exists = 0,
    CONCAT(
        'ALTER TABLE `activities` ',
        'ADD COLUMN `flashcard_set_id` ',
        @flashcard_set_id_type,
        ' NULL AFTER `lesson_id`'
    ),
    'SELECT 1'
);

PREPARE stmt FROM @add_column_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 3. Add index only if missing
SELECT COUNT(*)
INTO @index_exists
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'activities'
  AND INDEX_NAME = 'idx_activities_flashcard_set_id';

SET @add_index_sql := IF(
    @index_exists = 0,
    'ALTER TABLE `activities` ADD INDEX `idx_activities_flashcard_set_id` (`flashcard_set_id`)',
    'SELECT 1'
);

PREPARE stmt FROM @add_index_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 4. Add foreign key only if missing
SELECT COUNT(*)
INTO @fk_exists
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'activities'
  AND COLUMN_NAME = 'flashcard_set_id'
  AND REFERENCED_TABLE_NAME = 'flashcard_sets'
  AND REFERENCED_COLUMN_NAME = 'id';

SET @add_fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE `activities`
        ADD CONSTRAINT `fk_activities_flashcard_set`
        FOREIGN KEY (`flashcard_set_id`)
        REFERENCES `flashcard_sets` (`id`)
        ON UPDATE CASCADE
        ON DELETE SET NULL',
    'SELECT 1'
);

PREPARE stmt FROM @add_fk_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 5. Verify result
SELECT
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'activities'
  AND COLUMN_NAME = 'flashcard_set_id';

SELECT
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = @db_name
  AND TABLE_NAME = 'activities'
  AND COLUMN_NAME = 'flashcard_set_id';
