#!/usr/bin/python3
# ============================================================
# Query completed Job History from the Manifest API and
# export all step evidence (notes) to an Excel file.
#
# Usage:
#   python3 job_history_export.py
#   python3 job_history_export.py --username john@example.com
#   python3 job_history_export.py --start-date 202=6-01-01 --end-date 2026-03-25
#   python3 job_history_export.py --username john@example.com --start-date 2026-01-01
#
# Taqtile 2026
# Author: John Tomizuka
# ============================================================

import requests
import json
import argparse
import datetime
import openpyxl
from openpyxl.styles import Font

# -------------------------------------------------------
# Arguments
# -------------------------------------------------------

parser = argparse.ArgumentParser(description="Export Manifest job history evidence to Excel")
parser.add_argument("--username", help="Filter by assigned user email")
parser.add_argument("--start-date", help="Filter jobs completed on or after this date (YYYY-MM-DD)")
parser.add_argument("--end-date",   help="Filter jobs completed on or before this date (YYYY-MM-DD)")
args = parser.parse_args()

# -------------------------------------------------------
# Authentication
# -------------------------------------------------------

BASE_URL = "https://your_domain.taqmanifest.com"
login    = "your_email@your_domain.com"
password = "your_p@ssW0rd"

auth_payload = '{\r\n "email" : "' + login + '",\r\n "password" : "' + password + '"\r\n}'
auth_response = requests.request("POST", f"{BASE_URL}/rest/signin/v2",
    data=auth_payload,
    headers={"Content-Type": "application/json", "Cache-Control": "no-cache"})

auth_data = json.loads(auth_response.text)
token = auth_data["user"]["token"]
print("Authenticated as", auth_data["user"]["email"])

# -------------------------------------------------------
# GraphQL helpers
# -------------------------------------------------------

GRAPHQL_URL = f"{BASE_URL}/graphql/v2"
gql_headers = {
    "Content-Type": "application/json",
    "Authorization": token,
    "Cache-Control": "no-cache"
}

def run_query(query, variables=None):
    response = requests.post(
        GRAPHQL_URL,
        json={"query": query, "variables": variables or {}},
        headers=gql_headers
    )
    return response.json()

# -------------------------------------------------------
# Step 1: Fetch all completed jobs (paginated)
# completionDate is a Unix timestamp in milliseconds (string)
# -------------------------------------------------------

JOB_QUERY = """
query($pageNumber: Int, $itemsPerPage: Int) {
  jobs(completed: true, pageNumber: $pageNumber, itemsPerPage: $itemsPerPage) {
    id
    title
    status
    priority
    startDate
    completionDate
    assignedUserDetails {
      id
      email
      firstName
      lastName
    }
    template {
      id
      title
    }
    steps {
      id
      title
      completed
      notes {
        id
        type
        title
        text
        meterEvidence
        dateCreated
        files {
          id
          name
          url
          fileType
        }
      }
    }
  }
}
"""

PAGE_SIZE = 100
page = 1
all_jobs = []

while True:
    result = run_query(JOB_QUERY, {"pageNumber": page, "itemsPerPage": PAGE_SIZE})
    jobs = result["data"]["jobs"]
    all_jobs.extend(jobs)
    ext = result.get("extensions", {})
    total_pages = ext.get("allPages", 1)
    print(f"Fetched page {page}/{total_pages} ({len(all_jobs)} jobs so far)")
    if page >= total_pages or not jobs:
        break
    page += 1

print(f"\nTotal completed jobs fetched: {len(all_jobs)}")

# -------------------------------------------------------
# Fetch all meter evidence reports and index by jobStepId
# -------------------------------------------------------

METER_EVIDENCE_QUERY = """
query($pageNumber: Int, $itemsPerPage: Int) {
  metersEvidenceReport(pageNumber: $pageNumber, itemsPerPage: $itemsPerPage) {
    id
    jobId
    jobStepId
    jobStepName
    meterName
    meterUnitName
    meterEvidence
    meterOriginal
    meterFlag
    timeOfRecording
  }
}
"""

page = 1
meter_evidence_by_step = {}

while True:
    result = run_query(METER_EVIDENCE_QUERY, {"pageNumber": page, "itemsPerPage": PAGE_SIZE})
    records = result["data"]["metersEvidenceReport"]
    for r in records:
        step_id = r["jobStepId"]
        meter_evidence_by_step.setdefault(step_id, []).append(r)
    ext = result.get("extensions", {})
    total_pages = ext.get("allPages", 1)
    print(f"Fetched meter evidence page {page}/{total_pages} ({sum(len(v) for v in meter_evidence_by_step.values())} records so far)")
    if page >= total_pages or not records:
        break
    page += 1

print(f"Total meter evidence records: {sum(len(v) for v in meter_evidence_by_step.values())}")

# -------------------------------------------------------
# Step 2: Filter by username and/or date range (client-side)
# completionDate is Unix ms — convert to date for comparison
# -------------------------------------------------------

def ms_to_date(ms_str):
    if not ms_str:
        return None
    return datetime.datetime.fromtimestamp(int(ms_str) / 1000, tz=datetime.timezone.utc).date()

def parse_date(s):
    return datetime.datetime.strptime(s, "%Y-%m-%d").date() if s else None

start_date = parse_date(args.start_date)
end_date   = parse_date(args.end_date)

filtered_jobs = []
for job in all_jobs:
    if args.username:
        user = job.get("assignedUserDetails") or {}
        if user.get("email") != args.username:
            continue

    if start_date or end_date:
        completed = ms_to_date(job.get("completionDate"))
        if completed:
            if start_date and completed < start_date:
                continue
            if end_date and completed > end_date:
                continue

    filtered_jobs.append(job)

print(f"Jobs after filtering: {len(filtered_jobs)}")

# -------------------------------------------------------
# Step 3: Write to Excel
# One row per evidence note; job/step fields repeat.
# Jobs or steps with no notes still get a row (evidence fields blank).
# -------------------------------------------------------

wb = openpyxl.Workbook()
ws = wb.active
ws.title = "Job History"

columns = [
    "Job ID", "Job Title", "Status", "Priority", "Template",
    "Assigned User", "User Email",
    "Started At", "Completed At",
    "Step ID", "Step Title", "Step Completed",
    "Note Type", "Note Title", "Note Text", "Evidence Meter Value", "Evidence Date",
    "File Name", "File URL", "File Type"
]
for col, header in enumerate(columns, start=1):
    cell = ws.cell(row=1, column=col, value=header)
    cell.font = Font(bold=True)

def fmt_date(ms_str):
    d = ms_to_date(ms_str)
    return str(d) if d else None

row = 2
for job in filtered_jobs:
    user = job.get("assignedUserDetails") or {}
    user_name = f"{user.get('firstName') or ''} {user.get('lastName') or ''}".strip()
    template_title = (job.get("template") or {}).get("title")

    job_cells = [
        job.get("id"), job.get("title"), job.get("status"), job.get("priority"),
        template_title,
        user_name, user.get("email"),
        fmt_date(job.get("startDate")), fmt_date(job.get("completionDate"))
    ]

    steps = job.get("steps") or []
    if not steps:
        continue

    for step in steps:
        step_cells = [step.get("id"), step.get("title"), step.get("completed")]
        notes = step.get("notes") or []
        meter_evidence = meter_evidence_by_step.get(step.get("id"), [])

        for me in meter_evidence:
            recorded_date = fmt_date(me.get("timeOfRecording"))
            ws.append(job_cells + step_cells + [
                "meter", me.get("meterName"), None,
                me.get("meterEvidence"), recorded_date,
                None, None, None
            ])
            row += 1

# Auto-size columns (cap at 60 chars wide)
for col in ws.columns:
    max_len = max((len(str(cell.value)) for cell in col if cell.value), default=0)
    ws.column_dimensions[col[0].column_letter].width = min(max_len + 4, 60)

timestamp = datetime.datetime.now().strftime("%Y%m%d_%H%M%S")
filename = f"job_history_{timestamp}.xlsx"
wb.save(filename)
print(f"\nSaved {len(filtered_jobs)} jobs ({row - 2} rows) to {filename}")