-- Add missing columns to chat_themes table
ALTER TABLE chat_themes ADD COLUMN IF NOT EXISTS is_locked TINYINT(1) DEFAULT 0;
ALTER TABLE chat_themes ADD COLUMN IF NOT EXISTS locked_by INT NULL;
ALTER TABLE chat_themes ADD COLUMN IF NOT EXISTS locked_at DATETIME NULL;

-- Add foreign key for locked_by
ALTER TABLE chat_themes ADD CONSTRAINT fk_locked_by FOREIGN KEY (locked_by) REFERENCES users(id) ON DELETE SET NULL;

-- Add index for locked column
ALTER TABLE chat_themes ADD INDEX IF NOT EXISTS idx_locked (is_locked);

-- Create deleted_users table if not exists
CREATE TABLE IF NOT EXISTS deleted_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    unique_id VARCHAR(50) NOT NULL,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    profile_photo VARCHAR(255),
    deleted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    deleted_by INT NOT NULL,
    reason TEXT,
    chat_backup JSON,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_deleted_user (user_id),
    INDEX idx_deleted_by (deleted_by),
    INDEX idx_deleted_at (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
