Table Operation: Import Record

import base64
import json
import os
from pathlib import Path

import requests

# =========================
# Settings
# =========================
BASE_URL = "【URL】"
API_KEY = "【API key】"
SITE_ID = 【Site ID】  # Site ID of the site to import into

# CSV import settings
CSV_PATH = Path("./sample.csv")  # Path of the CSV file to import
CSV_ENCODING = "UTF-8"  # Encoding of the CSV file ("UTF-8" or "Shift-JIS")
KEY_COLUMN = "ClassB"  # Key column of the CSV file (such as a management number). Used for matching in GetItems

# Root path of the image folder
IMAGES_DIR = Path("./images")

# "IssueId" or "ResultId"
RECORD_ID_FIELD = "ResultId"

# =========================
# Common processes
# =========================
# Mapping between file name prefixes and ImageHash keys (Pleasanter column names)
FILE_PREFIX_MAP = {
    "body": "Body",
    "desc_a": "DescriptionA",
    "desc_b": "DescriptionB",
    "desc_c": "DescriptionC",
    "comments": "Comments",
}

SUPPORTED_EXTENSIONS = {".jpg", ".jpeg", ".png", ".gif"}


def post_json(url: str, payload: dict) -> dict:
    response = requests.post(
        url,
        headers={"Content-Type": "application/json"},
        data=json.dumps(payload, ensure_ascii=False),
        timeout=60,
    )
    response.raise_for_status()
    return response.json()


def post_file(url: str, params: dict, file_path: Path) -> dict:
    with open(file_path, "rb") as f:
        response = requests.post(
            url,
            data={"parameters": json.dumps(params, ensure_ascii=False)},
            files={"file": (file_path.name, f, "text/csv")},
            timeout=120,
        )
    response.raise_for_status()
    return response.json()


def encode_image(file_path: Path) -> str:
    """Encode an image file in Base64 and return it as a string"""
    return base64.b64encode(file_path.read_bytes()).decode("utf-8")


# =========================
# Step 1: CSV import
# =========================
def step1_import_csv() -> None:
    print("=" * 50)
    print("Step 1: CSV import")
    print("=" * 50)

    url = f"{BASE_URL}/api/items/{SITE_ID}/import"
    params = {
        "ApiVersion": 1.1,
        "ApiKey": API_KEY,
        "Encoding": CSV_ENCODING,
        "UpdatableImport": True,  # Update records whose key matches
        "Key": KEY_COLUMN,
        "MigrationMode": False,
    }

    result = post_file(url, params, CSV_PATH)
    print(f"  Status  : {result.get('StatusCode')}")
    print(f"  Message : {result.get('Message')}")


# =========================
# Step 2: Get the record IDs
# =========================
def step2_get_record_ids() -> dict:
    """
    Get all the imported records, and return a map of key column value -> record ID.
    If there are many records, get all of them with paging.
    """
    print("=" * 50)
    print("Step 2: Get the record IDs")
    print("=" * 50)

    url = f"{BASE_URL}/api/items/{SITE_ID}/get"
    key_to_id = {}
    offset = 0
    page_size = 200

    while True:
        payload = {
            "ApiVersion": 1.1,
            "ApiKey": API_KEY,
            "Offset": offset,
            "PageSize": page_size,
        }

        result = post_json(url, payload)
        records = result.get("Response", {}).get("Data", [])

        if not records:
            break

        for rec in records:
            # Supports both the ClassHash format and direct specification
            class_hash = rec.get("ClassHash", {})
            key_val = class_hash.get(KEY_COLUMN) or rec.get(KEY_COLUMN)
            record_id = rec.get(RECORD_ID_FIELD)
            if key_val and record_id:
                key_to_id[str(key_val).strip()] = record_id

        if len(records) < page_size:
            break
        offset += page_size

    print(f"  Retrieved : {len(key_to_id)} records")
    return key_to_id


# =========================
# Step 3: Update the embedded images
# =========================
def build_image_hash(key_dir: Path) -> dict:
    """
    Scan the image files in the key directory and build a dictionary for ImageHash.
    Files that are not targets are ignored.
    """
    image_hash = {}

    for file in sorted(key_dir.iterdir()):
        if not file.is_file():
            continue
        if file.suffix.lower() not in SUPPORTED_EXTENSIONS:
            continue

        # Identify the column to register to by the file name prefix
        prefix = file.stem.lower()
        target_field = FILE_PREFIX_MAP.get(prefix)
        if target_field is None:
            print(f"    [SKIP] Not a target file name: {file.name} (skipped)")
            continue

        image_hash[target_field] = {
            "HeadNewLine": True,
            "EndNewLine": True,
            "Extension": file.suffix.lower(),
            "Base64": encode_image(file),
        }
        print(f"    {file.name} → {target_field}")

    return image_hash


def step3_update_images(key_to_id: dict) -> None:
    print("=" * 50)
    print("Step 3: Update the embedded images")
    print("=" * 50)

    if not IMAGES_DIR.exists():
        print(f"  The image folder was not found: {IMAGES_DIR}")
        return

    success = skip = error = 0

    for key_dir in sorted(IMAGES_DIR.iterdir()):
        if not key_dir.is_dir():
            continue

        key_val = key_dir.name
        record_id = key_to_id.get(key_val)

        if record_id is None:
            print(f"  [SKIP] No record was found for the key '{key_val}'")
            skip += 1
            continue

        print(f"  Key: {key_val}  →  Record ID: {record_id}")
        image_hash = build_image_hash(key_dir)

        if not image_hash:
            print(f"    [SKIP] There are no image files to register")
            skip += 1
            continue

        payload = {
            "ApiVersion": 1.1,
            "ApiKey": API_KEY,
            "ImageHash": image_hash,
        }

        url = f"{BASE_URL}/api/items/{record_id}/update"
        try:
            result = post_json(url, payload)
            if result.get("StatusCode") == 200:
                print(f"    [OK] Updated")
                success += 1
            else:
                print(
                    f"    [ERR] StatusCode={result.get('StatusCode')}  {result.get('Message')}"
                )
                error += 1
        except requests.HTTPError as e:
            print(f"    [ERR] HTTP error: {e}")
            error += 1

    print()
    print(f"Completed  Success={success}  Skipped={skip}  Error={error}")


# =========================
# Main
# =========================
def main():
    step1_import_csv()
    print()
    key_to_id = step2_get_record_ids()
    print()
    step3_update_images(key_to_id)


if __name__ == "__main__":
    main()