-- ================================================
-- Consolidate Projects Table to Modern Schema
-- Remove legacy support and ensure modern integration system
-- ================================================

-- First, let's check what we're working with
SELECT 'Checking projects table structure...' as status;

-- Add modern integration columns if they don't exist
-- (This ensures all installations have the modern schema)

-- Add integration_type column if it doesn't exist
SET @sql = (SELECT
  CASE
    WHEN COUNT(*) = 0 THEN
      'ALTER TABLE projects ADD COLUMN integration_type ENUM(''sheets'', ''trello'', ''asana'', ''notion'', ''jira'', ''flochief'') DEFAULT ''sheets'' AFTER project_type'
    ELSE
      'SELECT ''integration_type column already exists'' as result'
  END
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'projects'
  AND COLUMN_NAME = 'integration_type');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add task_management_type column if it doesn't exist
SET @sql = (SELECT
  CASE
    WHEN COUNT(*) = 0 THEN
      'ALTER TABLE projects ADD COLUMN task_management_type ENUM(''sheets'', ''trello'', ''flochief'') DEFAULT ''sheets'' AFTER integration_type'
    ELSE
      'SELECT ''task_management_type column already exists'' as result'
  END
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'projects'
  AND COLUMN_NAME = 'task_management_type');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add connection_id column if it doesn't exist
SET @sql = (SELECT
  CASE
    WHEN COUNT(*) = 0 THEN
      'ALTER TABLE projects ADD COLUMN connection_id VARCHAR(255) NULL AFTER task_management_type'
    ELSE
      'SELECT ''connection_id column already exists'' as result'
  END
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'projects'
  AND COLUMN_NAME = 'connection_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add integration_settings column if it doesn't exist
SET @sql = (SELECT
  CASE
    WHEN COUNT(*) = 0 THEN
      'ALTER TABLE projects ADD COLUMN integration_settings LONGTEXT NULL AFTER connection_id'
    ELSE
      'SELECT ''integration_settings column already exists'' as result'
  END
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'projects'
  AND COLUMN_NAME = 'integration_settings');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add token_owner_user_id column if it doesn't exist
SET @sql = (SELECT
  CASE
    WHEN COUNT(*) = 0 THEN
      'ALTER TABLE projects ADD COLUMN token_owner_user_id INT NULL AFTER integration_settings'
    ELSE
      'SELECT ''token_owner_user_id column already exists'' as result'
  END
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'projects'
  AND COLUMN_NAME = 'token_owner_user_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add foreign key for token_owner_user_id if it doesn't exist
SET @sql = (SELECT
  CASE
    WHEN COUNT(*) = 0 THEN
      'ALTER TABLE projects ADD FOREIGN KEY (token_owner_user_id) REFERENCES users(id) ON DELETE SET NULL'
    ELSE
      'SELECT ''Foreign key for token_owner_user_id already exists'' as result'
  END
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'projects'
  AND COLUMN_NAME = 'token_owner_user_id'
  AND REFERENCED_TABLE_NAME = 'users');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Add indexes for better performance
CREATE INDEX IF NOT EXISTS idx_projects_integration_type ON projects(integration_type);
CREATE INDEX IF NOT EXISTS idx_projects_task_management ON projects(task_management_type);
CREATE INDEX IF NOT EXISTS idx_projects_connection ON projects(connection_id);
CREATE INDEX IF NOT EXISTS idx_projects_token_owner ON projects(token_owner_user_id);

-- Migrate legacy data to modern schema
-- For projects that only have google_sheet_id, populate the modern fields
UPDATE projects
SET
  integration_type = 'sheets',
  task_management_type = 'sheets',
  connection_id = google_sheet_id,
  integration_settings = JSON_OBJECT(
    'sheetTabName', COALESCE(sheet_tab_name, 'Sheet1')
  )
WHERE integration_type IS NULL
  AND google_sheet_id IS NOT NULL;

-- For projects with no integration data, set them to flochief (modern default)
UPDATE projects
SET
  integration_type = 'flochief',
  task_management_type = 'flochief',
  connection_id = NULL,
  integration_settings = JSON_OBJECT()
WHERE integration_type IS NULL;

-- Add a success message
SELECT 'Projects table consolidated to modern schema successfully!' as status;
SELECT
  COUNT(*) as total_projects,
  integration_type,
  task_management_type
FROM projects
GROUP BY integration_type, task_management_type
ORDER BY integration_type, task_management_type;
