-- Database partitioning strategy for improved performance
-- This migration implements table partitioning for high-volume tables

-- 1. Partition tasks table by organization_id for better isolation and performance
-- First, create new partitioned tasks table
CREATE TABLE tasks_partitioned (
    id INT AUTO_INCREMENT,
    organization_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    assigned_to INT,
    created_by INT,
    project_id INT,
    due_date DATETIME,
    estimated_time INT,
    actual_time INT,
    task_type ENUM('one-time', 'recurring') DEFAULT 'one-time',
    recurrence_pattern VARCHAR(255),
    parent_task_id INT,
    slack_thread_ts VARCHAR(255),
    slack_channel_id VARCHAR(255),
    external_id VARCHAR(255),
    tags JSON,
    metadata JSON,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    completed_at TIMESTAMP NULL,
    
    PRIMARY KEY (id, organization_id),
    KEY idx_org_status_priority (organization_id, status, priority),
    KEY idx_org_assigned_status (organization_id, assigned_to, status),
    KEY idx_org_project_status (organization_id, project_id, status),
    KEY idx_org_due_date (organization_id, due_date),
    KEY idx_org_created_at (organization_id, created_at),
    
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
) PARTITION BY HASH(organization_id) PARTITIONS 8;

-- 2. Create partitioned user_activities table for audit logs
CREATE TABLE user_activities_partitioned (
    id INT AUTO_INCREMENT,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    activity_type ENUM('login', 'logout', 'task_create', 'task_update', 'task_complete', 'project_create', 'slack_interaction') NOT NULL,
    entity_type ENUM('task', 'project', 'user', 'organization') NULL,
    entity_id INT NULL,
    details JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    PRIMARY KEY (id, organization_id, created_at),
    KEY idx_org_user_activity (organization_id, user_id, activity_type),
    KEY idx_org_entity (organization_id, entity_type, entity_id),
    KEY idx_org_created_at (organization_id, created_at),
    
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
    PARTITION p_2024_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2024-04-01')),
    PARTITION p_2024_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2024-07-01')),
    PARTITION p_2024_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2024-10-01')),
    PARTITION p_2024_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01')),
    PARTITION p_2025_q1 VALUES LESS THAN (UNIX_TIMESTAMP('2025-04-01')),
    PARTITION p_2025_q2 VALUES LESS THAN (UNIX_TIMESTAMP('2025-07-01')),
    PARTITION p_2025_q3 VALUES LESS THAN (UNIX_TIMESTAMP('2025-10-01')),
    PARTITION p_2025_q4 VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 3. Create partitioned task_reminders table
CREATE TABLE task_reminders_partitioned (
    id INT AUTO_INCREMENT,
    organization_id INT NOT NULL,
    task_id INT NOT NULL,
    user_id INT NOT NULL,
    reminder_type ENUM('due_date', 'custom', 'daily_standup', 'weekly_review') NOT NULL,
    reminder_time DATETIME NOT NULL,
    status ENUM('pending', 'sent', 'cancelled') DEFAULT 'pending',
    slack_message_id VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sent_at TIMESTAMP NULL,
    
    PRIMARY KEY (id, organization_id, reminder_time),
    KEY idx_org_status_time (organization_id, status, reminder_time),
    KEY idx_org_user_time (organization_id, user_id, reminder_time),
    KEY idx_org_task (organization_id, task_id),
    
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) PARTITION BY RANGE (UNIX_TIMESTAMP(reminder_time)) (
    PARTITION p_current VALUES LESS THAN (UNIX_TIMESTAMP('2025-01-01')),
    PARTITION p_2025_h1 VALUES LESS THAN (UNIX_TIMESTAMP('2025-07-01')),
    PARTITION p_2025_h2 VALUES LESS THAN (UNIX_TIMESTAMP('2026-01-01')),
    PARTITION p_2026_h1 VALUES LESS THAN (UNIX_TIMESTAMP('2026-07-01')),
    PARTITION p_2026_h2 VALUES LESS THAN (UNIX_TIMESTAMP('2027-01-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 4. Create indexes optimized for partitioned tables
-- Additional composite indexes for common query patterns

-- Tasks partitioned table additional indexes
ALTER TABLE tasks_partitioned 
ADD KEY idx_org_status_created (organization_id, status, created_at),
ADD KEY idx_org_priority_due (organization_id, priority, due_date),
ADD KEY idx_org_type_status (organization_id, task_type, status),
ADD KEY idx_external_id (external_id),
ADD KEY idx_slack_thread (slack_thread_ts),
ADD KEY idx_parent_task (parent_task_id);

-- User activities additional indexes
ALTER TABLE user_activities_partitioned
ADD KEY idx_org_type_created (organization_id, activity_type, created_at),
ADD KEY idx_ip_address (ip_address),
ADD KEY idx_user_type_created (user_id, activity_type, created_at);

-- Task reminders additional indexes  
ALTER TABLE task_reminders_partitioned
ADD KEY idx_type_status_time (reminder_type, status, reminder_time),
ADD KEY idx_slack_message (slack_message_id);

-- 5. Create views for backward compatibility during migration
CREATE VIEW tasks_view AS
SELECT * FROM tasks
UNION ALL
SELECT * FROM tasks_partitioned;

CREATE VIEW user_activities_view AS
SELECT * FROM user_activities
WHERE EXISTS (SELECT 1 FROM user_activities LIMIT 1)
UNION ALL
SELECT * FROM user_activities_partitioned;

CREATE VIEW task_reminders_view AS
SELECT * FROM task_reminders
WHERE EXISTS (SELECT 1 FROM task_reminders LIMIT 1)
UNION ALL  
SELECT * FROM task_reminders_partitioned;

-- 6. Create stored procedures for partition management

DELIMITER //

-- Procedure to add new quarterly partitions for user_activities
CREATE PROCEDURE AddUserActivitiesPartition(IN partition_name VARCHAR(64), IN partition_date DATE)
BEGIN
    SET @sql = CONCAT('ALTER TABLE user_activities_partitioned ADD PARTITION (PARTITION ', partition_name, 
                     ' VALUES LESS THAN (UNIX_TIMESTAMP(''', partition_date, ''')))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//

-- Procedure to add new partitions for task_reminders
CREATE PROCEDURE AddTaskRemindersPartition(IN partition_name VARCHAR(64), IN partition_date DATE)
BEGIN
    SET @sql = CONCAT('ALTER TABLE task_reminders_partitioned ADD PARTITION (PARTITION ', partition_name,
                     ' VALUES LESS THAN (UNIX_TIMESTAMP(''', partition_date, ''')))');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//

-- Procedure to drop old partitions (for data retention)
CREATE PROCEDURE DropOldPartition(IN table_name VARCHAR(64), IN partition_name VARCHAR(64))
BEGIN
    SET @sql = CONCAT('ALTER TABLE ', table_name, ' DROP PARTITION ', partition_name);
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END//

-- Procedure to get partition information
CREATE PROCEDURE GetPartitionInfo(IN table_name VARCHAR(64))
BEGIN
    SELECT 
        PARTITION_NAME,
        PARTITION_EXPRESSION,
        PARTITION_DESCRIPTION,
        TABLE_ROWS,
        AVG_ROW_LENGTH,
        DATA_LENGTH,
        INDEX_LENGTH
    FROM INFORMATION_SCHEMA.PARTITIONS 
    WHERE TABLE_SCHEMA = DATABASE() 
    AND TABLE_NAME = table_name
    AND PARTITION_NAME IS NOT NULL
    ORDER BY PARTITION_ORDINAL_POSITION;
END//

DELIMITER ;

-- 7. Create events for automatic partition management
-- Note: Events require EVENT_SCHEDULER to be enabled

-- Event to automatically add quarterly partitions for user_activities
CREATE EVENT IF NOT EXISTS add_user_activities_partitions
ON SCHEDULE EVERY 3 MONTH
STARTS '2025-01-01 00:00:00'
DO
BEGIN
    DECLARE new_partition_name VARCHAR(64);
    DECLARE new_partition_date DATE;
    
    SET new_partition_date = DATE_ADD(NOW(), INTERVAL 1 YEAR);
    SET new_partition_name = CONCAT('p_', YEAR(new_partition_date), '_q', QUARTER(new_partition_date));
    
    CALL AddUserActivitiesPartition(new_partition_name, new_partition_date);
END;

-- Event to automatically add semi-annual partitions for task_reminders  
CREATE EVENT IF NOT EXISTS add_task_reminders_partitions
ON SCHEDULE EVERY 6 MONTH
STARTS '2025-01-01 00:00:00'
DO
BEGIN
    DECLARE new_partition_name VARCHAR(64);
    DECLARE new_partition_date DATE;
    
    SET new_partition_date = DATE_ADD(NOW(), INTERVAL 1 YEAR);
    SET new_partition_name = CONCAT('p_', YEAR(new_partition_date), '_h', IF(MONTH(new_partition_date) <= 6, 1, 2));
    
    CALL AddTaskRemindersPartition(new_partition_name, new_partition_date);
END;

-- 8. Performance optimization settings for partitioned tables
-- These settings optimize query performance for partitioned tables

-- Ensure partition pruning is enabled
SET GLOBAL optimizer_switch = 'partition_pruning=on';

-- Optimize for partition-wise joins
SET GLOBAL optimizer_switch = 'mrr=on,mrr_cost_based=on';

-- Batch key access optimization
SET GLOBAL optimizer_switch = 'batched_key_access=on';

-- 9. Add comments for documentation
ALTER TABLE tasks_partitioned COMMENT = 'Partitioned tasks table by organization_id for improved performance and data isolation';
ALTER TABLE user_activities_partitioned COMMENT = 'Partitioned user activities table by created_at for efficient data retention and query performance';  
ALTER TABLE task_reminders_partitioned COMMENT = 'Partitioned task reminders table by reminder_time for optimal scheduling queries';

-- Migration notes:
-- 1. This creates new partitioned tables alongside existing ones
-- 2. Views provide backward compatibility during migration
-- 3. Application code should be updated to use new table names
-- 4. Data migration should be performed separately using scripts
-- 5. Old tables can be dropped after successful migration and testing
-- 6. Monitor partition sizes and performance after implementation
-- 7. Ensure EVENT_SCHEDULER is enabled for automatic partition management