-- Add user-specific integration tokens table
-- This allows each user to connect their own Trello/Asana/etc accounts

CREATE TABLE IF NOT EXISTS user_integration_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  integration_type ENUM('trello', 'asana', 'notion', 'jira') NOT NULL,
  access_token TEXT NOT NULL,
  refresh_token TEXT NULL,
  token_expires_at TIMESTAMP NULL,
  scope VARCHAR(255) DEFAULT 'read,write',
  external_user_id VARCHAR(100) NULL,
  external_username VARCHAR(100) NULL,
  is_active BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY unique_user_integration (user_id, integration_type)
);

-- Index for performance
CREATE INDEX idx_user_integration_tokens_user_id ON user_integration_tokens(user_id);
CREATE INDEX idx_user_integration_tokens_type ON user_integration_tokens(integration_type);

-- Update projects table to track which user's token to use
ALTER TABLE projects 
ADD COLUMN token_owner_user_id INT NULL AFTER connection_id,
ADD FOREIGN KEY (token_owner_user_id) REFERENCES users(id) ON DELETE SET NULL;

-- Create index for token owner lookup
CREATE INDEX idx_projects_token_owner ON projects(token_owner_user_id);

-- Migration summary
SELECT 'User tokens table created successfully' as status;