// Package state provides state management for AutoSSO.
// This file implements the MySQL storage backend for the Store interface.
//
// Requirements: 4.2, 4.3, 4.4
package state

import (
	"context"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"strings"
	"sync"
	"time"

	_ "github.com/go-sql-driver/mysql"

	"AutoSSO/internal/config"
)

// MySQLStore implements the Store interface using MySQL as the backend.
// It provides connection pool management and transaction support.
//
// Requirements: 4.2, 4.3, 4.4
type MySQLStore struct {
	db     *sql.DB
	config *config.DatabaseConfig
	mu     sync.RWMutex
}

const defaultMySQLInitTimeout = 10 * time.Second

type contextPinger interface {
	PingContext(ctx context.Context) error
}

func buildMySQLDSN(cfg *config.DatabaseConfig) string {
	timeout := defaultMySQLInitTimeout.String()
	return fmt.Sprintf("%s:%s@tcp(%s:%d)/%s?charset=utf8mb4&parseTime=true&loc=Local&timeout=%s&readTimeout=%s&writeTimeout=%s",
		cfg.User,
		cfg.Password,
		cfg.Host,
		cfg.Port,
		cfg.Database,
		timeout,
		timeout,
		timeout,
	)
}

func pingWithTimeout(p contextPinger, timeout time.Duration) error {
	ctx, cancel := context.WithTimeout(context.Background(), timeout)
	defer cancel()
	return p.PingContext(ctx)
}

func ensureSchemaWithTimeout(timeout time.Duration, fn func(ctx context.Context) error) error {
	ctx, cancel := context.WithTimeout(context.Background(), timeout)
	defer cancel()
	return fn(ctx)
}

// NewMySQLStore creates a new MySQL store with the given configuration.
// It initializes the connection pool with the configured settings.
//
// Parameters:
//   - cfg: Database configuration containing connection parameters
//
// Returns:
//   - *MySQLStore: The initialized MySQL store
//   - error: Any error that occurred during initialization
//
// Requirements: 4.2, 4.4
func NewMySQLStore(cfg *config.DatabaseConfig) (*MySQLStore, error) {
	if cfg == nil {
		return nil, errors.New("database configuration is required")
	}

	// Build DSN (Data Source Name)
	dsn := buildMySQLDSN(cfg)

	// Open database connection
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		return nil, fmt.Errorf("failed to open database connection: %w", err)
	}

	// Configure connection pool
	// Requirements: 4.4
	db.SetMaxOpenConns(cfg.MaxOpenConns)
	db.SetMaxIdleConns(cfg.MaxIdleConns)
	db.SetConnMaxLifetime(cfg.MaxLifetime)

	// Verify connection
	if err := pingWithTimeout(db, defaultMySQLInitTimeout); err != nil {
		db.Close()
		return nil, fmt.Errorf("failed to ping database: %w", err)
	}

	s := &MySQLStore{
		db:     db,
		config: cfg,
	}

	if err := ensureSchemaWithTimeout(defaultMySQLInitTimeout, s.ensureSchema); err != nil {
		db.Close()
		return nil, fmt.Errorf("failed to initialize database schema: %w", err)
	}

	return s, nil
}

// SaveInstance saves or updates an approval instance.
// If an instance with the same ID exists, it will be updated.
//
// Requirements: 4.2, 4.3
func (s *MySQLStore) SaveInstance(ctx context.Context, instance *ApprovalInstance) error {
	if instance == nil {
		return errors.New("instance is required")
	}

	// Serialize permission sets to JSON
	permissionSetsJSON, err := json.Marshal(instance.PermissionSets)
	if err != nil {
		return fmt.Errorf("failed to marshal permission sets: %w", err)
	}

	query := `
		INSERT INTO approval_instances 
			(id, email, account_id, permission_sets, start_time, end_time, status, cloud_type, description, created_at, updated_at)
		VALUES 
			(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
		ON DUPLICATE KEY UPDATE
			email = VALUES(email),
			account_id = VALUES(account_id),
			permission_sets = VALUES(permission_sets),
			start_time = VALUES(start_time),
			end_time = VALUES(end_time),
			status = VALUES(status),
			cloud_type = VALUES(cloud_type),
			description = VALUES(description),
			updated_at = VALUES(updated_at)
	`

	now := time.Now()
	if instance.CreatedAt.IsZero() {
		instance.CreatedAt = now
	}
	instance.UpdatedAt = now

	_, err = s.db.ExecContext(ctx, query,
		instance.ID,
		instance.Email,
		instance.AccountID,
		permissionSetsJSON,
		instance.StartTime,
		instance.EndTime,
		instance.Status,
		instance.CloudType,
		instance.Description,
		instance.CreatedAt,
		instance.UpdatedAt,
	)
	if err != nil {
		return fmt.Errorf("failed to save instance: %w", err)
	}

	return nil
}

// GetInstance retrieves an approval instance by its ID.
//
// Requirements: 4.2
func (s *MySQLStore) GetInstance(ctx context.Context, instanceID string) (*ApprovalInstance, error) {
	if instanceID == "" {
		return nil, errors.New("instance ID is required")
	}

	query := `
		SELECT id, email, account_id, permission_sets, start_time, end_time, status, cloud_type, description, created_at, updated_at
		FROM approval_instances
		WHERE id = ?
	`

	var instance ApprovalInstance
	var permissionSetsJSON []byte

	err := s.db.QueryRowContext(ctx, query, instanceID).Scan(
		&instance.ID,
		&instance.Email,
		&instance.AccountID,
		&permissionSetsJSON,
		&instance.StartTime,
		&instance.EndTime,
		&instance.Status,
		&instance.CloudType,
		&instance.Description,
		&instance.CreatedAt,
		&instance.UpdatedAt,
	)
	if err != nil {
		if errors.Is(err, sql.ErrNoRows) {
			return nil, nil
		}
		return nil, fmt.Errorf("failed to get instance: %w", err)
	}

	// Deserialize permission sets from JSON
	if err := json.Unmarshal(permissionSetsJSON, &instance.PermissionSets); err != nil {
		return nil, fmt.Errorf("failed to unmarshal permission sets: %w", err)
	}

	return &instance, nil
}

// ListInstances retrieves approval instances matching the filter criteria.
// If filter is nil, all instances are returned.
//
// Requirements: 4.2
func (s *MySQLStore) ListInstances(ctx context.Context, filter *InstanceFilter) ([]*ApprovalInstance, error) {
	query := `
		SELECT id, email, account_id, permission_sets, start_time, end_time, status, cloud_type, created_at, updated_at
		FROM approval_instances
		WHERE 1=1
	`
	args := make([]interface{}, 0)

	// Build dynamic query based on filter
	if filter != nil {
		if filter.Status != "" {
			query += " AND status = ?"
			args = append(args, filter.Status)
		}
		if filter.CloudType != "" {
			query += " AND cloud_type = ?"
			args = append(args, filter.CloudType)
		}
		if filter.Email != "" {
			query += " AND email = ?"
			args = append(args, filter.Email)
		}
		if filter.AccountID != "" {
			query += " AND account_id = ?"
			args = append(args, filter.AccountID)
		}
		if !filter.StartTime.IsZero() {
			query += " AND start_time >= ?"
			args = append(args, filter.StartTime)
		}
		if !filter.EndTime.IsZero() {
			query += " AND end_time <= ?"
			args = append(args, filter.EndTime)
		}
	}

	// Add ordering
	query += " ORDER BY created_at DESC"

	// Add pagination
	if filter != nil && filter.Limit > 0 {
		query += " LIMIT ?"
		args = append(args, filter.Limit)
		if filter.Offset > 0 {
			query += " OFFSET ?"
			args = append(args, filter.Offset)
		}
	}

	rows, err := s.db.QueryContext(ctx, query, args...)
	if err != nil {
		return nil, fmt.Errorf("failed to list instances: %w", err)
	}
	defer rows.Close()

	var instances []*ApprovalInstance
	for rows.Next() {
		var instance ApprovalInstance
		var permissionSetsJSON []byte

		err := rows.Scan(
			&instance.ID,
			&instance.Email,
			&instance.AccountID,
			&permissionSetsJSON,
			&instance.StartTime,
			&instance.EndTime,
			&instance.Status,
			&instance.CloudType,
			&instance.CreatedAt,
			&instance.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan instance: %w", err)
		}

		// Deserialize permission sets from JSON
		if err := json.Unmarshal(permissionSetsJSON, &instance.PermissionSets); err != nil {
			return nil, fmt.Errorf("failed to unmarshal permission sets: %w", err)
		}

		instances = append(instances, &instance)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating instances: %w", err)
	}

	return instances, nil
}

// DeleteInstance removes an approval instance by its ID.
//
// Requirements: 4.2
func (s *MySQLStore) DeleteInstance(ctx context.Context, instanceID string) error {
	if instanceID == "" {
		return errors.New("instance ID is required")
	}

	query := `DELETE FROM approval_instances WHERE id = ?`

	result, err := s.db.ExecContext(ctx, query, instanceID)
	if err != nil {
		return fmt.Errorf("failed to delete instance: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rowsAffected == 0 {
		return nil // Instance not found, but not an error
	}

	return nil
}

// SavePendingRemoval saves a pending permission removal record.
//
// Requirements: 4.2, 4.3
func (s *MySQLStore) SavePendingRemoval(ctx context.Context, removal *PendingRemoval) error {
	if removal == nil {
		return errors.New("removal is required")
	}

	query := `
		INSERT INTO pending_removals 
			(account_id, email, permission_set, cloud_type, expired_at, scheduled_at, created_at)
		VALUES 
			(?, ?, ?, ?, ?, ?, ?)
		ON DUPLICATE KEY UPDATE
			expired_at = VALUES(expired_at),
			scheduled_at = VALUES(scheduled_at)
	`

	now := time.Now()
	if removal.CreatedAt.IsZero() {
		removal.CreatedAt = now
	}

	result, err := s.db.ExecContext(ctx, query,
		removal.AccountID,
		removal.Email,
		removal.PermissionSet,
		removal.CloudType,
		removal.ExpiredAt,
		removal.ScheduledAt,
		removal.CreatedAt,
	)
	if err != nil {
		return fmt.Errorf("failed to save pending removal: %w", err)
	}

	// Get the auto-generated ID if this was an insert
	if removal.ID == 0 {
		lastID, err := result.LastInsertId()
		if err == nil && lastID > 0 {
			removal.ID = lastID
		}
	}

	return nil
}

// GetPendingRemovals retrieves all pending permission removal records.
//
// Requirements: 4.2
func (s *MySQLStore) GetPendingRemovals(ctx context.Context) ([]*PendingRemoval, error) {
	query := `
		SELECT id, account_id, email, permission_set, cloud_type, expired_at, scheduled_at, created_at
		FROM pending_removals
		ORDER BY scheduled_at ASC
	`

	rows, err := s.db.QueryContext(ctx, query)
	if err != nil {
		return nil, fmt.Errorf("failed to get pending removals: %w", err)
	}
	defer rows.Close()

	var removals []*PendingRemoval
	for rows.Next() {
		var removal PendingRemoval
		err := rows.Scan(
			&removal.ID,
			&removal.AccountID,
			&removal.Email,
			&removal.PermissionSet,
			&removal.CloudType,
			&removal.ExpiredAt,
			&removal.ScheduledAt,
			&removal.CreatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan pending removal: %w", err)
		}
		removals = append(removals, &removal)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating pending removals: %w", err)
	}

	return removals, nil
}

// DeletePendingRemoval removes a pending removal record by its ID.
//
// Requirements: 4.2
func (s *MySQLStore) DeletePendingRemoval(ctx context.Context, id int64) error {
	if id <= 0 {
		return errors.New("valid ID is required")
	}

	query := `DELETE FROM pending_removals WHERE id = ?`

	result, err := s.db.ExecContext(ctx, query, id)
	if err != nil {
		return fmt.Errorf("failed to delete pending removal: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rowsAffected == 0 {
		return nil // Record not found, but not an error
	}

	return nil
}

// SaveDeletedInstance marks an instance as processed/deleted.
//
// Requirements: 4.2
func (s *MySQLStore) SaveDeletedInstance(ctx context.Context, instanceID string) error {
	if instanceID == "" {
		return errors.New("instance ID is required")
	}

	query := `
		INSERT INTO processed_instances (instance_id, processed_at)
		VALUES (?, ?)
		ON DUPLICATE KEY UPDATE processed_at = VALUES(processed_at)
	`

	_, err := s.db.ExecContext(ctx, query, instanceID, time.Now())
	if err != nil {
		return fmt.Errorf("failed to save deleted instance: %w", err)
	}

	return nil
}

// IsInstanceDeleted checks if an instance has been processed/deleted.
//
// Requirements: 4.2
func (s *MySQLStore) IsInstanceDeleted(ctx context.Context, instanceID string) (bool, error) {
	if instanceID == "" {
		return false, errors.New("instance ID is required")
	}

	query := `SELECT COUNT(*) FROM processed_instances WHERE instance_id = ?`

	var count int
	err := s.db.QueryRowContext(ctx, query, instanceID).Scan(&count)
	if err != nil {
		return false, fmt.Errorf("failed to check if instance is deleted: %w", err)
	}

	return count > 0, nil
}

// Close releases any resources held by the store.
//
// Requirements: 4.2
func (s *MySQLStore) Close() error {
	s.mu.Lock()
	defer s.mu.Unlock()

	if s.db != nil {
		if err := s.db.Close(); err != nil {
			return fmt.Errorf("failed to close database connection: %w", err)
		}
		s.db = nil
	}

	return nil
}

// Ping checks if the database connection is alive.
func (s *MySQLStore) Ping(ctx context.Context) error {
	return s.db.PingContext(ctx)
}

// Stats returns database connection pool statistics.
func (s *MySQLStore) Stats() sql.DBStats {
	return s.db.Stats()
}

// Transaction support methods

// BeginTx starts a new transaction with the given options.
//
// Requirements: 4.3
func (s *MySQLStore) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sql.Tx, error) {
	return s.db.BeginTx(ctx, opts)
}

// WithTransaction executes a function within a transaction.
// If the function returns an error, the transaction is rolled back.
// Otherwise, the transaction is committed.
//
// Requirements: 4.3
func (s *MySQLStore) WithTransaction(ctx context.Context, fn func(tx *sql.Tx) error) error {
	tx, err := s.db.BeginTx(ctx, nil)
	if err != nil {
		return fmt.Errorf("failed to begin transaction: %w", err)
	}

	defer func() {
		if p := recover(); p != nil {
			tx.Rollback()
			panic(p) // Re-throw panic after rollback
		}
	}()

	if err := fn(tx); err != nil {
		if rbErr := tx.Rollback(); rbErr != nil {
			return fmt.Errorf("failed to rollback transaction: %v (original error: %w)", rbErr, err)
		}
		return err
	}

	if err := tx.Commit(); err != nil {
		return fmt.Errorf("failed to commit transaction: %w", err)
	}

	return nil
}

// SaveInstanceWithRemoval saves an instance and creates a pending removal in a single transaction.
// This is useful when approving a permission that will eventually expire.
//
// Requirements: 4.3
func (s *MySQLStore) SaveInstanceWithRemoval(ctx context.Context, instance *ApprovalInstance, removal *PendingRemoval) error {
	return s.WithTransaction(ctx, func(tx *sql.Tx) error {
		// Save instance
		permissionSetsJSON, err := json.Marshal(instance.PermissionSets)
		if err != nil {
			return fmt.Errorf("failed to marshal permission sets: %w", err)
		}

		now := time.Now()
		if instance.CreatedAt.IsZero() {
			instance.CreatedAt = now
		}
		instance.UpdatedAt = now

		instanceQuery := `
			INSERT INTO approval_instances 
				(id, email, account_id, permission_sets, start_time, end_time, status, cloud_type, description, created_at, updated_at)
			VALUES 
				(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
			ON DUPLICATE KEY UPDATE
				email = VALUES(email),
				account_id = VALUES(account_id),
				permission_sets = VALUES(permission_sets),
				start_time = VALUES(start_time),
				end_time = VALUES(end_time),
				status = VALUES(status),
				cloud_type = VALUES(cloud_type),
				description = VALUES(description),
				updated_at = VALUES(updated_at)
		`

		_, err = tx.ExecContext(ctx, instanceQuery,
			instance.ID,
			instance.Email,
			instance.AccountID,
			permissionSetsJSON,
			instance.StartTime,
			instance.EndTime,
			instance.Status,
			instance.CloudType,
			instance.Description,
			instance.CreatedAt,
			instance.UpdatedAt,
		)
		if err != nil {
			return fmt.Errorf("failed to save instance in transaction: %w", err)
		}

		// Save pending removal if provided
		if removal != nil {
			if removal.CreatedAt.IsZero() {
				removal.CreatedAt = now
			}

			removalQuery := `
				INSERT INTO pending_removals 
					(account_id, email, permission_set, cloud_type, expired_at, scheduled_at, created_at)
				VALUES 
					(?, ?, ?, ?, ?, ?, ?)
				ON DUPLICATE KEY UPDATE
					expired_at = VALUES(expired_at),
					scheduled_at = VALUES(scheduled_at)
			`

			_, err = tx.ExecContext(ctx, removalQuery,
				removal.AccountID,
				removal.Email,
				removal.PermissionSet,
				removal.CloudType,
				removal.ExpiredAt,
				removal.ScheduledAt,
				removal.CreatedAt,
			)
			if err != nil {
				return fmt.Errorf("failed to save pending removal in transaction: %w", err)
			}
		}

		return nil
	})
}

// DeleteInstanceAndMarkProcessed deletes an instance and marks it as processed in a single transaction.
//
// Requirements: 4.3
func (s *MySQLStore) DeleteInstanceAndMarkProcessed(ctx context.Context, instanceID string) error {
	return s.WithTransaction(ctx, func(tx *sql.Tx) error {
		// Delete instance
		deleteQuery := `DELETE FROM approval_instances WHERE id = ?`
		_, err := tx.ExecContext(ctx, deleteQuery, instanceID)
		if err != nil {
			return fmt.Errorf("failed to delete instance in transaction: %w", err)
		}

		// Mark as processed
		processedQuery := `
			INSERT INTO processed_instances (instance_id, processed_at)
			VALUES (?, ?)
			ON DUPLICATE KEY UPDATE processed_at = VALUES(processed_at)
		`
		_, err = tx.ExecContext(ctx, processedQuery, instanceID, time.Now())
		if err != nil {
			return fmt.Errorf("failed to mark instance as processed in transaction: %w", err)
		}

		return nil
	})
}

// GetReadyRemovals retrieves pending removals that are ready to be processed.
// A removal is ready if the current time is after ScheduledAt.
func (s *MySQLStore) GetReadyRemovals(ctx context.Context) ([]*PendingRemoval, error) {
	query := `
		SELECT id, account_id, email, permission_set, cloud_type, expired_at, scheduled_at, created_at
		FROM pending_removals
		WHERE scheduled_at <= ?
		ORDER BY scheduled_at ASC
	`

	rows, err := s.db.QueryContext(ctx, query, time.Now())
	if err != nil {
		return nil, fmt.Errorf("failed to get ready removals: %w", err)
	}
	defer rows.Close()

	var removals []*PendingRemoval
	for rows.Next() {
		var removal PendingRemoval
		err := rows.Scan(
			&removal.ID,
			&removal.AccountID,
			&removal.Email,
			&removal.PermissionSet,
			&removal.CloudType,
			&removal.ExpiredAt,
			&removal.ScheduledAt,
			&removal.CreatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan ready removal: %w", err)
		}
		removals = append(removals, &removal)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating ready removals: %w", err)
	}

	return removals, nil
}

// UpdateInstanceStatus updates the status of an approval instance.
func (s *MySQLStore) UpdateInstanceStatus(ctx context.Context, instanceID, status string) error {
	if instanceID == "" {
		return errors.New("instance ID is required")
	}
	if !ValidStatus(status) {
		return fmt.Errorf("invalid status: %s", status)
	}

	query := `UPDATE approval_instances SET status = ?, updated_at = ? WHERE id = ?`

	result, err := s.db.ExecContext(ctx, query, status, time.Now(), instanceID)
	if err != nil {
		return fmt.Errorf("failed to update instance status: %w", err)
	}

	rowsAffected, err := result.RowsAffected()
	if err != nil {
		return fmt.Errorf("failed to get rows affected: %w", err)
	}

	if rowsAffected == 0 {
		return fmt.Errorf("instance not found: %s", instanceID)
	}

	return nil
}

// GetExpiredInstances retrieves instances that have expired but not yet been processed.
func (s *MySQLStore) GetExpiredInstances(ctx context.Context) ([]*ApprovalInstance, error) {
	query := `
		SELECT id, email, account_id, permission_sets, start_time, end_time, status, cloud_type, created_at, updated_at
		FROM approval_instances
		WHERE end_time < ? AND status = ?
		ORDER BY end_time ASC
	`

	rows, err := s.db.QueryContext(ctx, query, time.Now(), StatusApproved)
	if err != nil {
		return nil, fmt.Errorf("failed to get expired instances: %w", err)
	}
	defer rows.Close()

	var instances []*ApprovalInstance
	for rows.Next() {
		var instance ApprovalInstance
		var permissionSetsJSON []byte

		err := rows.Scan(
			&instance.ID,
			&instance.Email,
			&instance.AccountID,
			&permissionSetsJSON,
			&instance.StartTime,
			&instance.EndTime,
			&instance.Status,
			&instance.CloudType,
			&instance.CreatedAt,
			&instance.UpdatedAt,
		)
		if err != nil {
			return nil, fmt.Errorf("failed to scan expired instance: %w", err)
		}

		// Deserialize permission sets from JSON
		if err := json.Unmarshal(permissionSetsJSON, &instance.PermissionSets); err != nil {
			return nil, fmt.Errorf("failed to unmarshal permission sets: %w", err)
		}

		instances = append(instances, &instance)
	}

	if err := rows.Err(); err != nil {
		return nil, fmt.Errorf("error iterating expired instances: %w", err)
	}

	return instances, nil
}

// Ensure MySQLStore implements Store interface
var _ Store = (*MySQLStore)(nil)

// ensureSchema creates the necessary database tables if they don't exist.
func (s *MySQLStore) ensureSchema(ctx context.Context) error {
	queries := []string{
		`CREATE TABLE IF NOT EXISTS approval_instances (
			id VARCHAR(64) NOT NULL COMMENT 'Feishu approval instance ID',
			email VARCHAR(255) NOT NULL COMMENT 'User email address',
			account_id VARCHAR(64) NOT NULL COMMENT 'Cloud account/project/subscription ID',
			permission_sets JSON NOT NULL COMMENT 'List of permission sets as JSON array',
			start_time DATETIME NOT NULL COMMENT 'Permission start time',
			end_time DATETIME NOT NULL COMMENT 'Permission end time',
			status ENUM('pending', 'approved', 'expired', 'revoked') DEFAULT 'pending' COMMENT 'Instance status',
			cloud_type ENUM('AWS', 'GCP', 'Azure', 'Alibaba') NOT NULL COMMENT 'Cloud platform type',
			description TEXT COMMENT 'Access resource description',
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
			updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Record last update time',
			PRIMARY KEY (id),
			INDEX idx_status (status),
			INDEX idx_email (email),
			INDEX idx_end_time (end_time),
			INDEX idx_cloud_type (cloud_type),
			INDEX idx_account_id (account_id)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores Feishu approval instances for permission management'`,

		`CREATE TABLE IF NOT EXISTS pending_removals (
			id BIGINT AUTO_INCREMENT NOT NULL COMMENT 'Auto-increment ID',
			account_id VARCHAR(64) NOT NULL COMMENT 'Cloud account/project/subscription ID',
			email VARCHAR(255) NOT NULL COMMENT 'User email address',
			permission_set VARCHAR(255) NOT NULL COMMENT 'Permission set name to be removed',
			cloud_type ENUM('AWS', 'GCP', 'Azure', 'Alibaba') NOT NULL COMMENT 'Cloud platform type',
			expired_at DATETIME NOT NULL COMMENT 'When the permission expired',
			scheduled_at DATETIME NOT NULL COMMENT 'When the removal is scheduled to execute',
			created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation time',
			PRIMARY KEY (id),
			UNIQUE KEY uk_permission (account_id, email, permission_set),
			INDEX idx_scheduled_at (scheduled_at),
			INDEX idx_cloud_type (cloud_type)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Stores pending permission removal records'`,

		`CREATE TABLE IF NOT EXISTS processed_instances (
			instance_id VARCHAR(64) NOT NULL COMMENT 'Feishu approval instance ID',
			processed_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'When the instance was processed',
			PRIMARY KEY (instance_id),
			INDEX idx_processed_at (processed_at)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracks processed approval instances to prevent reprocessing'`,

		`CREATE TABLE IF NOT EXISTS ntgper (
			account VARCHAR(64) NOT NULL COMMENT 'Cloud account ID',
			user VARCHAR(255) NOT NULL COMMENT 'User email address',
			pername VARCHAR(255) NOT NULL COMMENT 'Permission set name',
			stime DATETIME NOT NULL COMMENT 'Permission start time',
			etime DATETIME NOT NULL COMMENT 'Permission end time',
			days INT NOT NULL COMMENT 'Permission duration in days',
			PRIMARY KEY (account, user, pername),
			INDEX idx_etime (etime)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Non-standard permission sets for special cloud permissions'`,
	}

	for _, query := range queries {
		if _, err := s.db.ExecContext(ctx, query); err != nil {
			return fmt.Errorf("failed to create table: %w", err)
		}
	}

	// Migration: Add description column if it doesn't exist
	// Note: We don't use IF NOT EXISTS to support older MySQL versions
	migrationQuery := `ALTER TABLE approval_instances ADD COLUMN description TEXT COMMENT 'Access resource description' AFTER cloud_type`
	if _, err := s.db.ExecContext(ctx, migrationQuery); err != nil {
		// Ignore duplicate column error (1060)
		// We check the error string as a fallback since specific error types depend on the driver version
		if !strings.Contains(err.Error(), "1060") && !strings.Contains(err.Error(), "Duplicate column name") {
			// Just log error for migration failure, don't fail startup
			fmt.Printf("Warning: failed to add description column: %v\n", err)
		}
	}

	return nil
}