-- Safe Database migration to support multiple integrations (Google Sheets, Trello, etc.)
-- This version works without SUPER privileges by avoiding triggers

-- 1. Update projects table to support multiple integration types
ALTER TABLE projects 
ADD COLUMN integration_type ENUM('sheets', 'trello', 'asana', 'notion', 'jira') DEFAULT 'sheets' AFTER project_type;

ALTER TABLE projects 
ADD COLUMN connection_id VARCHAR(255) NOT NULL DEFAULT '' AFTER integration_type;

ALTER TABLE projects 
ADD COLUMN integration_settings JSON DEFAULT NULL AFTER connection_id;

-- 2. Migrate existing Google Sheets data to new structure
UPDATE projects SET 
  integration_type = 'sheets',
  connection_id = COALESCE(google_sheet_id, ''),
  integration_settings = CASE 
    WHEN sheet_tab_name IS NOT NULL THEN JSON_OBJECT(
      'sheetTabName', sheet_tab_name,
      'autoCreateColumns', true,
      'defaultStatus', 'Pending'
    )
    ELSE JSON_OBJECT(
      'autoCreateColumns', true,
      'defaultStatus', 'Pending'
    )
  END
WHERE google_sheet_id IS NOT NULL AND google_sheet_id != '';

-- 3. Update any remaining NULL connection_ids
UPDATE projects 
SET connection_id = COALESCE(google_sheet_id, '')
WHERE connection_id = '' AND google_sheet_id IS NOT NULL;

-- 4. Add indexes for better performance
CREATE INDEX idx_projects_integration_type ON projects(integration_type);
CREATE INDEX idx_projects_connection_id ON projects(connection_id);

-- 5. Create integration_credentials table for secure credential storage
CREATE TABLE IF NOT EXISTS integration_credentials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  integration_type ENUM('sheets', 'trello', 'asana', 'notion', 'jira') NOT NULL,
  credential_name VARCHAR(100) NOT NULL,
  credential_value TEXT NOT NULL,
  is_encrypted BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_integration_credential (integration_type, credential_name)
);

-- 6. Create integration_capabilities table to track what each integration supports
CREATE TABLE IF NOT EXISTS integration_capabilities (
  id INT AUTO_INCREMENT PRIMARY KEY,
  integration_type ENUM('sheets', 'trello', 'asana', 'notion', 'jira') NOT NULL UNIQUE,
  supports_create_tasks BOOLEAN DEFAULT TRUE,
  supports_update_tasks BOOLEAN DEFAULT TRUE,
  supports_assign_tasks BOOLEAN DEFAULT TRUE,
  supports_time_tracking BOOLEAN DEFAULT FALSE,
  supports_custom_fields BOOLEAN DEFAULT FALSE,
  supports_attachments BOOLEAN DEFAULT FALSE,
  supports_comments BOOLEAN DEFAULT FALSE,
  supports_real_time_updates BOOLEAN DEFAULT FALSE,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 7. Insert default capabilities for supported integrations
INSERT INTO integration_capabilities 
(integration_type, supports_create_tasks, supports_update_tasks, supports_assign_tasks, 
 supports_time_tracking, supports_custom_fields, supports_attachments, supports_comments, supports_real_time_updates)
VALUES 
('sheets', TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, TRUE, FALSE),
('trello', TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE),
('asana', TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE),
('notion', TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, FALSE, FALSE),
('jira', TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE, TRUE)
ON DUPLICATE KEY UPDATE
  supports_create_tasks = VALUES(supports_create_tasks),
  supports_update_tasks = VALUES(supports_update_tasks),
  supports_assign_tasks = VALUES(supports_assign_tasks),
  supports_time_tracking = VALUES(supports_time_tracking),
  supports_custom_fields = VALUES(supports_custom_fields),
  supports_attachments = VALUES(supports_attachments),
  supports_comments = VALUES(supports_comments),
  supports_real_time_updates = VALUES(supports_real_time_updates);

-- 8. Create integration_status table to track connection health
CREATE TABLE IF NOT EXISTS integration_status (
  id INT AUTO_INCREMENT PRIMARY KEY,
  project_id INT NOT NULL,
  integration_type ENUM('sheets', 'trello', 'asana', 'notion', 'jira') NOT NULL,
  connection_id VARCHAR(255) NOT NULL,
  status ENUM('active', 'error', 'disabled', 'testing') DEFAULT 'active',
  last_sync TIMESTAMP NULL,
  last_error TEXT NULL,
  error_count INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
  UNIQUE KEY unique_project_integration (project_id, integration_type)
);

-- 9. Manually populate integration_status for existing projects
INSERT INTO integration_status (project_id, integration_type, connection_id, status)
SELECT id, integration_type, connection_id, 'active'
FROM projects 
WHERE connection_id IS NOT NULL AND connection_id != ''
ON DUPLICATE KEY UPDATE 
  integration_type = VALUES(integration_type),
  connection_id = VALUES(connection_id),
  status = 'active';

-- 10. Create view for easy project integration info retrieval
CREATE OR REPLACE VIEW project_integrations AS
SELECT 
  p.id as project_id,
  p.name as project_name,
  p.integration_type,
  p.connection_id,
  p.integration_settings,
  p.google_sheet_id as legacy_sheet_id,
  p.sheet_tab_name as legacy_sheet_tab,
  ist.status as integration_status,
  ist.last_sync,
  ist.last_error,
  ist.error_count,
  ic.supports_create_tasks,
  ic.supports_update_tasks,
  ic.supports_assign_tasks,
  ic.supports_time_tracking,
  ic.supports_custom_fields,
  ic.supports_attachments,
  ic.supports_comments,
  ic.supports_real_time_updates
FROM projects p
LEFT JOIN integration_status ist ON p.id = ist.project_id
LEFT JOIN integration_capabilities ic ON p.integration_type = ic.integration_type;

-- 11. Add sample Trello credentials placeholders
INSERT INTO integration_credentials (integration_type, credential_name, credential_value, is_encrypted)
VALUES 
('trello', 'api_key', 'TRELLO_API_KEY_FROM_ENV', FALSE),
('trello', 'token', 'TRELLO_TOKEN_FROM_ENV', FALSE)
ON DUPLICATE KEY UPDATE 
  credential_value = VALUES(credential_value);

-- 12. Add validation constraints (if your MySQL version supports it)
-- Note: Some shared hosting MySQL versions don't support CHECK constraints
-- ALTER TABLE projects 
-- ADD CONSTRAINT chk_connection_id_not_empty 
-- CHECK (connection_id IS NOT NULL AND connection_id != '');

-- 13. Create indexes for performance
CREATE INDEX idx_integration_status_project_id ON integration_status(project_id);
CREATE INDEX idx_integration_status_type ON integration_status(integration_type);
CREATE INDEX idx_integration_credentials_type ON integration_credentials(integration_type);

-- 14. Verify migration success
SELECT 
  'Migration Summary' as info,
  COUNT(*) as total_projects,
  COUNT(CASE WHEN integration_type = 'sheets' THEN 1 END) as sheets_projects,
  COUNT(CASE WHEN integration_type = 'trello' THEN 1 END) as trello_projects,
  COUNT(CASE WHEN connection_id IS NOT NULL AND connection_id != '' THEN 1 END) as projects_with_connection
FROM projects;

-- Show sample of migrated data
SELECT 
  id, name, integration_type, connection_id, 
  JSON_EXTRACT(integration_settings, '$.sheetTabName') as sheet_tab,
  google_sheet_id as legacy_sheet_id
FROM projects 
LIMIT 5;