-- Create calendar_integration table for each tenant database
-- This table stores OAuth tokens for Google Calendar and Outlook integration
-- Run this script for EACH tenant database (e.g., tenant_clinique_la_rive1_bleue1)

CREATE TABLE IF NOT EXISTS calendar_integration (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    provider VARCHAR(50) NOT NULL COMMENT 'google or outlook',
    email VARCHAR(255) NOT NULL COMMENT 'User email from OAuth',
    access_token TEXT NOT NULL COMMENT 'OAuth access token',
    refresh_token TEXT COMMENT 'OAuth refresh token',
    token_expiry DATETIME COMMENT 'When the access token expires',
    calendar_id VARCHAR(255) COMMENT 'Calendar ID (for Google)',
    is_active BOOLEAN DEFAULT TRUE COMMENT 'Is this integration active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_provider (provider),
    INDEX idx_provider (provider),
    INDEX idx_email (email),
    INDEX idx_is_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Stores OAuth integration tokens for calendar services (Google, Outlook)';

-- Example: Run for your tenant database
-- USE tenant_clinique_la_rive1_bleue1;
-- Then execute the CREATE TABLE statement above
