<?php
declare(strict_types=1);

require_once __DIR__ . '/bootstrap.php';

function pd_db(): PDO {
    global $pdo;
    if (!($pdo instanceof PDO)) {
        pd_json(500, ['success' => false, 'error' => 'PDO not configured. Set DB credentials in php/config.php']);
    }
    return $pdo;
}

function pd_db_ensure_tables(): void {
    $pdo = pd_db();
    // Simple table for idempotency + mapping PandaDoc doc -> Shopify order.
    $pdo->exec("
        CREATE TABLE IF NOT EXISTS pandadoc_orders (
            id INT AUTO_INCREMENT PRIMARY KEY,
            shopify_order_id BIGINT NULL,
            shopify_draft_order_id BIGINT NULL,
            entity_type VARCHAR(16) NOT NULL DEFAULT 'order',
            shopify_order_gid VARCHAR(96) NOT NULL,
            pandadoc_document_id VARCHAR(96) NULL,
            status VARCHAR(32) NOT NULL DEFAULT 'idle',
            drive_pdf_url TEXT NULL,
            last_error TEXT NULL,
            last_api_step VARCHAR(192) NULL,
            last_api_response MEDIUMTEXT NULL,
            created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_shopify_order_id (shopify_order_id),
            UNIQUE KEY uniq_shopify_draft_order_id (shopify_draft_order_id),
            KEY idx_pandadoc_document_id (pandadoc_document_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    ");
    // Add new columns if table already existed (ignore error if columns exist).
    foreach (
        [
            'last_api_step VARCHAR(192) NULL',
            'last_api_response MEDIUMTEXT NULL',
            'completed_pdf_path VARCHAR(512) NULL',
            'completed_pdf_clean_path VARCHAR(512) NULL',
            'completed_pdf_clean_hubspot_url TEXT NULL',
            'shopify_draft_order_id BIGINT NULL',
            "entity_type VARCHAR(16) NOT NULL DEFAULT 'order'",
        ] as $colDef
    ) {
        try {
            $pdo->exec("ALTER TABLE pandadoc_orders ADD COLUMN $colDef");
        } catch (Throwable $e) {
            $msg = $e->getMessage();
            if (stripos($msg, 'Duplicate column') === false && stripos($msg, '1060') === false) throw $e;
        }
    }
    // Widen last_api_step so it can store "file:line:step" (e.g. orchestrator.php:115:fetch_order).
    try {
        $pdo->exec("ALTER TABLE pandadoc_orders MODIFY COLUMN last_api_step VARCHAR(192) NULL");
    } catch (Throwable $e) {
        if (stripos($e->getMessage(), 'Duplicate column') === false) { /* ignore */ }
    }
    // Longer paths for pdf-sanitize/upload on deep server trees
    foreach (
        [
            'completed_pdf_path VARCHAR(1024) NULL',
            'completed_pdf_clean_path VARCHAR(1024) NULL',
            'shopify_order_id BIGINT NULL',
        ] as $colMod
    ) {
        try {
            $pdo->exec("ALTER TABLE pandadoc_orders MODIFY COLUMN $colMod");
        } catch (Throwable $e) {
            /* ignore if column missing or already correct */
        }
    }
    try {
        $pdo->exec('ALTER TABLE pandadoc_orders ADD UNIQUE KEY uniq_shopify_draft_order_id (shopify_draft_order_id)');
    } catch (Throwable $e) {
        $msg = $e->getMessage();
        if (stripos($msg, 'Duplicate key') === false && stripos($msg, '1061') === false && stripos($msg, 'already exists') === false) {
            /* ignore if index already present */
        }
    }
}

function pd_db_row_entity_type(array $row): string {
    $type = trim((string)($row['entity_type'] ?? ''));
    return $type !== '' ? $type : 'order';
}

function pd_db_get_or_create_draft_row(int $shopifyDraftOrderId): array {
    $pdo = pd_db();
    $gid = 'gid://shopify/DraftOrder/' . $shopifyDraftOrderId;

    $stmt = $pdo->prepare('SELECT * FROM pandadoc_orders WHERE shopify_draft_order_id = ? LIMIT 1');
    $stmt->execute([$shopifyDraftOrderId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (is_array($row)) {
        return $row;
    }

    $ins = $pdo->prepare(
        "INSERT INTO pandadoc_orders (shopify_order_id, shopify_draft_order_id, shopify_order_gid, entity_type, status)
         VALUES (NULL, ?, ?, 'draft_order', 'idle')"
    );
    $ins->execute([$shopifyDraftOrderId, $gid]);

    $stmt->execute([$shopifyDraftOrderId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return is_array($row) ? $row : [
        'shopify_draft_order_id' => $shopifyDraftOrderId,
        'shopify_order_gid' => $gid,
        'entity_type' => 'draft_order',
        'status' => 'idle',
    ];
}

function pd_db_update_draft_row(int $shopifyDraftOrderId, array $fields): void {
    $pdo = pd_db();
    if (empty($fields)) {
        return;
    }
    $cols = [];
    $vals = [];
    foreach ($fields as $k => $v) {
        $cols[] = "`$k` = ?";
        $vals[] = $v;
    }
    $vals[] = $shopifyDraftOrderId;
    $sql = 'UPDATE pandadoc_orders SET ' . implode(', ', $cols) . ' WHERE shopify_draft_order_id = ?';
    $stmt = $pdo->prepare($sql);
    $stmt->execute($vals);
}

/** Draft-order rows that already have (or are generating) a PandaDoc document. */
function pd_db_list_draft_rows_with_documents(): array {
    $pdo = pd_db();
    $stmt = $pdo->query(
        "SELECT * FROM pandadoc_orders
         WHERE entity_type = 'draft_order'
           AND shopify_draft_order_id IS NOT NULL
           AND pandadoc_document_id IS NOT NULL
           AND pandadoc_document_id != ''
           AND status IN ('queued', 'building', 'sent', 'viewed', 'completed')
         ORDER BY id DESC"
    );
    if ($stmt === false) {
        return [];
    }
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return is_array($rows) ? $rows : [];
}

function pd_db_get_or_create_order_row(int $shopifyOrderId): array {
    $pdo = pd_db();
    // Do not call pd_db_ensure_tables() here: it runs DDL (CREATE/ALTER) which causes MySQL implicit COMMIT and breaks the caller's transaction. Caller must call pd_db_ensure_tables() before beginTransaction().
    $gid = 'gid://shopify/Order/' . $shopifyOrderId;

    $stmt = $pdo->prepare("SELECT * FROM pandadoc_orders WHERE shopify_order_id = ? LIMIT 1");
    $stmt->execute([$shopifyOrderId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    if (is_array($row)) return $row;

    $ins = $pdo->prepare("INSERT INTO pandadoc_orders (shopify_order_id, shopify_order_gid, status) VALUES (?, ?, 'idle')");
    $ins->execute([$shopifyOrderId, $gid]);

    $stmt->execute([$shopifyOrderId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return is_array($row) ? $row : ['shopify_order_id' => $shopifyOrderId, 'shopify_order_gid' => $gid, 'status' => 'idle'];
}

function pd_db_update_order(int $shopifyOrderId, array $fields): void {
    $pdo = pd_db();
    if (empty($fields)) return;
    $cols = [];
    $vals = [];
    foreach ($fields as $k => $v) {
        $cols[] = "`$k` = ?";
        $vals[] = $v;
    }
    $vals[] = $shopifyOrderId;
    $sql = "UPDATE pandadoc_orders SET " . implode(', ', $cols) . " WHERE shopify_order_id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute($vals);
}

function pd_db_find_by_pandadoc_document(string $pandadocId): ?array {
    $pdo = pd_db();
    // Do not call pd_db_ensure_tables() here if caller may be inside a transaction (DDL causes implicit COMMIT).
    $stmt = $pdo->prepare("SELECT * FROM pandadoc_orders WHERE pandadoc_document_id = ? LIMIT 1");
    $stmt->execute([$pandadocId]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return is_array($row) ? $row : null;
}

