# AI-Driven Jira Ticket Classification & BigQuery Integration Pipeline

## Overview

This document describes the end-to-end automated pipeline for daily Jira ticket ingestion, AI-based classification, and data integration into BigQuery for analytics and reporting. The workflow is designed for full automation (e.g., via cron), with clear modular steps and robust data traceability.

## Workflow Steps

### 1. Fetch Updated Jira Tickets

- **Action:** Use the CLI tool to fetch all Jira tickets updated within the last day (or a specified date range).
- **Command Example:**
  ```
  jira fetch -p <PROJECT_KEY> --from <YYYY-MM-DD> --to <YYYY-MM-DD> -o jira-cleaned.json
  ```
- **Output:** Cleaned JSON file containing the latest ticket data.

### 2. Prepare AI Batch Input

- **Action:** Combine the cleaned Jira data with a system prompt to generate a JSONL file suitable for Gemini batch processing.
- **Command Example:**
  ```
  jira prepare -i jira-cleaned.json -p <PROMPT_FILE> -o batch-input.jsonl
  ```
- **Output:** `batch-input.jsonl` (one JSON object per ticket, ready for AI batch classification).

### 3. Submit Gemini Batch Job

- **Action:** Upload the JSONL file to Gemini and create a batch classification job.
- **Command Example:**
  ```
  jira classify submit -i batch-input.jsonl
  ```
- **Output:** Job ID for tracking the batch process.

### 4. Monitor Batch Job Status

- **Action:** Periodically check the status of the Gemini batch job until completion.
- **Command Example:**
  ```
  jira classify status <jobId>
  ```
- **Output:** Job status (e.g., running, completed, failed).

### 5. Download AI Classification Results

- **Action:** Once the batch job is complete, download the results as a JSONL file.
- **Command Example:**
  ```
  jira classify download <jobId> -o results.jsonl
  ```
- **Output:** `results.jsonl` (AI classification results for each ticket).

### 6. Merge Jira Data with AI Results

- **Action:** Merge the original Jira ticket data with the corresponding AI classification results, mapping fields according to the BigQuery schema.
- **Command Example:**
  ```
  jira merge --cleaned-file jira-cleaned.json --results-file results.jsonl -o final.jsonl
  ```
- **Output:** `final.jsonl` (each line matches the BigQuery table schema).

### 7. (Optional) Convert to CSV

- **Action:** If required, convert the merged JSONL data to CSV for easier inspection or manual upload.
- **Implementation:** Use a script or extend the merge logic to support CSV output.

### 8. Upload to BigQuery

- **Action:** Load the merged data into the BigQuery table, using the defined schema for `ticket_classification_analysis`.
- **Command Example:**
  ```
  jira upload -i final.jsonl --project-id <GCP_PROJECT> --dataset <BQ_DATASET> --table <BQ_TABLE>
  ```
- **Output:** Data is available in BigQuery for analytics and reporting.

### 9. Automation

- **Action:** Bundle the above commands into a shell script and schedule it via cron (or other scheduler) for daily execution.

## Data Schema Reference

- The BigQuery table schema is defined in `context/data_schema_design.md`.
- Only AI-related fields are included in the Gemini batch output; all other fields are merged from Jira data during the merge step.

## Error Handling & Logging

- All CLI commands should log errors and exit codes for monitoring.
- Failed steps should trigger alerts or retries as appropriate.

## Extensibility

- The pipeline can be extended to support additional AI models, new classification fields, or alternative output formats (e.g., CSV, Parquet).
- Human verification and feedback can be integrated as a downstream process.