name: sql-report-generator
description: "MANDATORY: When user asks to query database, generate SQL, or create data reports from views, you MUST: (1) read this SKILL.md, (2) execute the Python scripts in 3 steps. DO NOT generate SQL manually in chat. Generates HTML reports by querying database views."
---

# SQL Report Generator Skill

## ⚠️ CRITICAL RULES - READ BEFORE ANYTHING ELSE
**YOU MUST EXECUTE THE 3 PYTHON SCRIPTS IN ORDER. NO EXCEPTIONS.**
- ❌ DO NOT generate SQL queries directly in the chat response
- ❌ DO NOT skip any script execution
- ❌ DO NOT reply with only a file path — you MUST include data analysis text first
- ❌ DO NOT guess or fabricate the file path — ALWAYS use the exact `address` value from the script output
- ✅ ALWAYS run the exec commands below step by step
- ✅ **CRITICAL: You MUST write a short status update to the user BEFORE running EACH script. This text will be streamed to the user's UI as a progress indicator.**
- ✅ ALWAYS write a data analysis summary BEFORE the address tag
- ✅ The LAST TWO LINES of your final reply MUST be the `@@@@@` address tag followed by the `#####` suggestions tag

## When to Use
Use this skill when:
- User asks to query the database or generate SQL
- User asks for data reports based on database views
- User wants to visualize data from the database
- User asks questions that require querying the system's data views

## How to Execute
This skill requires executing 3 steps. **Before running each step, you MUST write a short text to the user explaining what you are about to do. This text MUST start with the exact ` ⏳ ` format specified below.**

### Step 1: Select Template and Views
**FIRST**, write this exact text to the user (do not use tool yet):
`【正在分析问题】正在分析问题，选择数据视图和展示模板...`

**THEN**, run this command:
```bash
cd /workspace/skill/sql-report-generator && python3 scripts/step1_select.py --query "{{USER_QUERY}}"
```
*Replace `{{USER_QUERY}}` with the user's query text.*

The script will output a JSON like:
```json
{"status": "success", "selected_views": ["视图名1"], "template_name": "barChart", "is_fixed_template": false, "reason": "..."}
```

For 园区概览 or 企业概览 queries, the output will include extra fields:
```json
{"status": "success", "selected_views": [], "template_name": "dashboard_overview", "is_fixed_template": true, "fixed_template_type": "dashboard_overview", "date_type": "year", "date": "2025", "park_name": "XX园区", "enterprise_name": null, "reason": "..."}
```
- `date_type`: `"year"` = 年度查询（用户说"2025年"），`"month"` = 月度查询（用户说"2025年3月"）
- `date`: 年度时为 `"2025"`，月度时为 `"2025-03"`
```

### Step 2: Generate SQL and Query Database
**FIRST**, write this exact text to the user:
`【正在生产查询语句】 正在生成 SQL 查询语句并查询数据库...`

**THEN**, run this command.

**⚠️ IMPORTANT: Check `is_fixed_template` from Step 1 output:**

**If `is_fixed_template` is `false` (normal flow):**
```bash
cd /workspace/skill/sql-report-generator && python3 scripts/step2_query.py --query "{{USER_QUERY}}" --views "{{selected_views_comma_separated}}" --template "{{template_name}}" --is_fixed_template false
```

**If `is_fixed_template` is `true` (园区概览 or 企业概览):**
```bash
cd /workspace/skill/sql-report-generator && python3 scripts/step2_query.py --query "{{USER_QUERY}}" --views "" --template "{{template_name}}" --is_fixed_template true --date_type "{{date_type}}" --date "{{date}}" --park_name "{{park_name}}" --enterprise_name "{{enterprise_name}}"
```
*Replace all `{{...}}` with values from Step 1 output. If a value is `null`, pass the string `"None"`.*
*`--date_type` must be `"year"` or `"month"` exactly as returned by Step 1.*

The script will output a JSON like:
```json
{"status": "success", "sql": "SELECT ...", "row_count": 10, "result_file": "/workspace/skill/sql-report-generator/output/temp_result_123.json"}
```

### Step 3: Render HTML Report
**FIRST**, write this exact text to the user:
`【正在渲染页面】 正在将查询结果渲染为图表报告...`

**THEN**, run this command:
```bash
cd /workspace/skill/sql-report-generator && python3 scripts/step3_render.py --query "{{USER_QUERY}}" --template "{{template_name}}" --result_file "{{result_file}}"
```
*Replace variables with the output from Step 2.*

The script will output a JSON at the end like:
```json
{"status": "success", "message": "Report generated successfully", "template": "barChart", "address": "/skill/sql-report-generator/output/report_1773642266.html", "summary": "..."}
```

---

### Final Reply Format
Your final reply MUST have exactly three parts:

**Part 1 — Data Analysis Summary（必须，不可省略）**
Write a concise analysis in Chinese based on the `summary` field from the Step 3 script output, or expand on it if needed.
**禁止**只写"报告已生成"之类的话，必须真正分析数据内容。

**Part 2 — Address Tag（倒数第二行，格式固定）**
The second-to-last line of your reply MUST be the `address` field value wrapped in `@@@@@`:
```
@@@@@/skill/sql-report-generator/output/report_1773642266.html@@@@@
```
⚠️ **ADDRESS TAG FORMAT — ABSOLUTE RULES:**
- The path MUST be copied EXACTLY from the `address` field in the script JSON output
- DO NOT fabricate, guess, or approximate the report filename
- Output ONLY the plain file path between the two `@@@@@` markers
- NO spaces between `@@@@@` and the path

**Part 3 — Optimization Suggestions Tag（最后一行，格式固定）**
The very last line of your reply MUST be the optimization suggestions wrapped in `#####`.
```
#####%查看详细数据%￥点击上方链接查看完整的数据分析报告￥ %继续深入分析%￥基于当前数据进行更深度的挖掘￥ %导出报告%￥将当前报告导出为PDF或图片格式￥#####
```

## Bundled Resources
This skill uses progressive disclosure. The following resources are available but should only be read if you need to debug or understand the internal workings:
- `references/views_schema.md`: Documentation of all available database views and their columns.
- `templates/*.md`: Documentation for each HTML template's required JSON structure.
- `scripts/step1_select.py`, `step2_query.py`, `step3_render.py`: The execution scripts.