-- Database migration to support multiple integrations (Google Sheets, Trello, etc.)
-- This migration adds support for multiple project management integrations

-- 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,
ADD COLUMN connection_id VARCHAR(255) NOT NULL DEFAULT '' AFTER integration_type,
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 = google_sheet_id,
  integration_settings = JSON_OBJECT(
    'sheetTabName', sheet_tab_name,
    'autoCreateColumns', true,
    'defaultStatus', 'Pending'
  )
WHERE google_sheet_id IS NOT NULL AND google_sheet_id != '';

-- 3. 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);

-- 4. 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)
);

-- 5. 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
);

-- 6. 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);

-- 7. 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)
);

-- 8. Add trigger to auto-create integration_status entries
DELIMITER //
CREATE TRIGGER after_project_insert
AFTER INSERT ON projects
FOR EACH ROW
BEGIN
  INSERT INTO integration_status 
  (project_id, integration_type, connection_id, status)
  VALUES 
  (NEW.id, NEW.integration_type, NEW.connection_id, 'active');
END//

CREATE TRIGGER after_project_update_integration
AFTER UPDATE ON projects
FOR EACH ROW
BEGIN
  IF OLD.integration_type != NEW.integration_type OR OLD.connection_id != NEW.connection_id THEN
    UPDATE integration_status 
    SET 
      integration_type = NEW.integration_type,
      connection_id = NEW.connection_id,
      status = 'testing',
      updated_at = CURRENT_TIMESTAMP
    WHERE project_id = NEW.id;
  END IF;
END//
DELIMITER ;

-- 9. 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;

-- 10. Add sample Trello credentials (these should be set via environment variables)
-- Note: In production, these would be managed through environment variables
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);

-- 11. Add validation constraints
ALTER TABLE projects 
ADD CONSTRAINT chk_connection_id_not_empty 
CHECK (connection_id IS NOT NULL AND connection_id != '');

-- 12. 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);

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

-- Note: After running this migration, you may want to:
-- 1. Update application code to use new integration_type and connection_id fields
-- 2. Gradually phase out google_sheet_id and sheet_tab_name columns (keep them for backward compatibility initially)
-- 3. Set up environment variables for Trello API credentials
-- 4. Test all integrations to ensure they work with the new schema

-- Future considerations:
-- 1. Add encryption for sensitive credentials in integration_credentials table
-- 2. Add rate limiting information per integration type
-- 3. Add webhook configuration for real-time integrations
-- 4. Add integration-specific metadata storage