# Code Block Formatting Script

This script automatically formats code blocks in blog posts by detecting programming languages and formatting code according to language conventions using OpenAI's API.

## Features

- Automatically detects programming languages in code blocks
- Formats code according to language-specific conventions
- Processes multiple posts concurrently for better performance
- Generates SQL UPDATE statements for incremental database updates
- Supports filtering and limiting posts for testing

## Prerequisites

- Python 3.12 or higher
- OpenAI API key
- Access to the TiDB database (for generating posts.json)

## Setup

1. Install dependencies:

```bash
# Using uv (recommended)
uv sync

# Or using pip
pip install -r requirements.txt
```

2. Create a `.env` file in the script directory with your OpenAI API key:

```bash
OPENAI_API_KEY=your_api_key_here
```

## Generating posts.json

Before running the script, you need to generate the `posts.json` file from the database. Execute the following SQL query and export the results as JSON:

```sql
SELECT
    id,
    title,
    slug,
    CONCAT('https://tidb.net/blog/', slug) AS link,
    source_url,
    JSON_VALID(content) AS is_valid_json,
    content,
    content LIKE '%"type": "code"%' AS has_code_block
FROM posts
WHERE source_url IS NOT NULL AND deleted_at IS NULL
HAVING has_code_block = 1;
```

Save the query results as `data/posts.json`. The JSON file should be an array of post objects, where each post contains at least:

- `id`: Post ID
- `title`: Post title
- `slug`: Post slug
- `content`: JSON string containing the post content with code blocks

## Usage

### Basic Usage

Process all posts from the default `data/posts.json` file:

```bash
python main.py
```

### Command Line Options

- `-n, --number`: Limit the number of posts to process (useful for testing)

  ```bash
  python main.py -n 5
  ```

- `-f, --file`: Specify a custom path to the posts JSON file

  ```bash
  python main.py -f data/posts_1.json
  ```

- `-o, --output-sql-file`: Specify the output SQL file path

  ```bash
  python main.py -o data/custom_update.sql
  ```

- `-w, --where`: Filter posts using a JSON object (exact field matching)

  ```bash
  python main.py -w '{"has_code_block": 1}'
  ```

- `-v, --verbose`: Enable verbose logging (debug level)

  ```bash
  python main.py -v
  ```

### Examples

1. Process first 10 posts with verbose logging:

   ```bash
   python main.py -n 10 -v
   ```

2. Process posts from a specific file and save to custom SQL file:

   ```bash
   python main.py -f data/posts_1.json -o data/update_1.sql
   ```

3. Process posts with filtering:

   ```bash
   python main.py -w '{"has_code_block": 1}' -n 5
   ```

## Output

The script generates a SQL file (default: `data/format_code_update.sql`) containing UPDATE statements for each processed post. Each statement updates the `content` field of a post identified by its `slug`.

Example SQL output:

```sql
UPDATE posts SET content = '...' WHERE slug = 'example-post';
```

## How It Works

1. **Load Posts**: Reads posts from the JSON file
2. **Extract Code Blocks**: Recursively finds all code blocks in the post content
3. **Format Code Blocks**: For each code block:
   - Extracts the code text
   - Calls OpenAI API to detect the programming language
   - Formats the code according to language conventions
   - Updates the code block with detected language and formatted code
4. **Generate SQL**: Creates UPDATE statements for each processed post
5. **Write Incrementally**: Writes SQL statements to the output file as posts are processed