COPILOT SPEC: WhatsApp Business API – WABA/Plantillas Sync
Author: Artech Solutions
Date: Now
------------------------------------------------------------------

CONTEXT
- We already SEND messages successfully using a phone number on WhatsApp Business Cloud API.
- We must SYNC WhatsApp message templates (APPROVED/PENDING/REJECTED) to our system.
- We have the following confirmed identifiers from Meta Business Manager and Graph API:

CONSTANTS
- WABA_ID (WhatsApp Business Account): 799342059122741
- PHONE_NUMBER_ID: 817854531405693
- DISPLAY_PHONE_NUMBER: +593981802659
- GRAPH_VERSION: v18.0
- BASE_URL: https://graph.facebook.com/v18.0

GOALS
1) Verify that PHONE_NUMBER_ID belongs to WABA_ID.
2) List message templates for WABA_ID and store their status locally.
3) (Optional) Fetch template components for rendering variable schemas.
4) Block outbound HSM sends if the template is not APPROVED.

AUTH / PERMISSIONS
- Token must include SCOPES:
  * whatsapp_business_management
  * business_management
  (Plus whatsapp_business_messaging is used for sending messages, but not needed to read templates.)
- Recommended Token Type: Business System User token assigned to the Business that owns the WABA.
  Steps (summary):
    Business Settings → Users → System users → Add → Assign assets (WABA 799342059122741) with "Manage WhatsApp account"
    → Generate new token (select your App) → Add scopes above.

STEP 1 – VERIFY PHONE NUMBER BELONGS TO WABA (POSTMAN/HTTP)
- METHOD: GET
- URL: {BASE_URL}/{WABA_ID}/phone_numbers
- QUERY: fields=id,display_phone_number,verified_name,status
- AUTH: Bearer <ACCESS_TOKEN>

Expected JSON must include:
{
  "data": [
    {
      "id": "817854531405693",
      "display_phone_number": "+593981802659",
      "verified_name": "Artech Solution 2659",
      "status": "CONNECTED"
    }
  ]
}

STEP 2 – LIST MESSAGE TEMPLATES (POSTMAN/HTTP)
- METHOD: GET
- URL: {BASE_URL}/{WABA_ID}/message_templates
- QUERY:
    fields=id,name,language,category,status,quality_score,last_updated_time
    limit=100
- AUTH: Bearer <ACCESS_TOKEN>
- Pagination: follow paging.next until exhausted.

Sample (already obtained):
{
  "data": [
    {
      "id": "842953218285409",
      "name": "bienvenida_cuenta_activa",
      "language": "es",
      "category": "UTILITY",
      "status": "APPROVED",
      "quality_score": { "score": "UNKNOWN", "date": 1762016287 },
      "last_updated_time": "2025-10-30T23:00:18+0000"
    },
    {
      "id": "2270732353386229",
      "name": "mensaje_prueba",
      "language": "es",
      "category": "MARKETING",
      "status": "APPROVED",
      "quality_score": { "score": "UNKNOWN", "date": 1758161041 },
      "last_updated_time": "2025-09-17T20:42:37+0000"
    },
    {
      "id": "1886511532287164",
      "name": "hello_world",
      "language": "en_US",
      "category": "UTILITY",
      "status": "APPROVED",
      "quality_score": { "score": "UNKNOWN", "date": 1762016287 },
      "last_updated_time": "2025-09-17T20:06:01+0000"
    }
  ]
}

STEP 2b – GET A SINGLE TEMPLATE DETAILS (COMPONENTS)
- METHOD: GET
- URL: {BASE_URL}/{TEMPLATE_ID}
- QUERY: fields=name,language,category,status,components,quality_score,last_updated_time

DB SCHEMA (MySQL) – MINIMAL
CREATE TABLE IF NOT EXISTS waba_templates (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  language VARCHAR(32) NOT NULL,
  category VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL,
  quality_score VARCHAR(16) NULL,
  quality_date BIGINT NULL,
  last_updated_time DATETIME NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

PHP IMPLEMENTATION (cURL) – SYNC SCRIPT
----------------------------------------
ENV VARS:
  FB_TOKEN=<Access token with whatsapp_business_management + business_management>
  WABA_ID=799342059122741
  GRAPH_VERSION=v18.0

<?php
$accessToken = getenv('FB_TOKEN');
$wabaId = getenv('WABA_ID') ?: '799342059122741';
$graph = getenv('GRAPH_VERSION') ?: 'v18.0';

function fb_get(string $url): array {
  $ch = curl_init($url);
  curl_setopt_array($ch, [
    CURLOPT_RETURNTRANSFER => true,
    CURLOPT_SSL_VERIFYPEER => true,
    CURLOPT_TIMEOUT => 30,
  ]);
  $resp = curl_exec($ch);
  if ($resp === false) throw new Exception(curl_error($ch));
  $code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
  curl_close($ch);
  if ($code >= 400) throw new Exception("HTTP $code: $resp");
  return json_decode($resp, true);
}

$pdo = new PDO('mysql:host=localhost;dbname=tu_db;charset=utf8mb4','user','pass',[PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION]);
$pdo->exec("
CREATE TABLE IF NOT EXISTS waba_templates (
  id VARCHAR(32) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  language VARCHAR(32) NOT NULL,
  category VARCHAR(32) NOT NULL,
  status VARCHAR(32) NOT NULL,
  quality_score VARCHAR(16) NULL,
  quality_date BIGINT NULL,
  last_updated_time DATETIME NULL,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");

$base = "https://graph.facebook.com/{$graph}/{$wabaId}/message_templates" .
        "?fields=id,name,language,category,status,quality_score,last_updated_time&limit=100" .
        "&access_token=" . urlencode($accessToken);

$next = $base;
$up = $pdo->prepare("
INSERT INTO waba_templates (id,name,language,category,status,quality_score,quality_date,last_updated_time)
VALUES (:id,:name,:language,:category,:status,:qs,:qd,:lut)
ON DUPLICATE KEY UPDATE
  name=VALUES(name),
  language=VALUES(language),
  category=VALUES(category),
  status=VALUES(status),
  quality_score=VALUES(quality_score),
  quality_date=VALUES(quality_date),
  last_updated_time=VALUES(last_updated_time)
");

while ($next) {
  $data = fb_get($next);
  foreach (($data['data'] ?? []) as $t) {
    $up->execute([
      ':id'   => $t['id'],
      ':name' => $t['name'],
      ':language' => $t['language'],
      ':category' => $t['category'],
      ':status' => $t['status'],
      ':qs'  => $t['quality_score']['score'] ?? null,
      ':qd'  => $t['quality_score']['date'] ?? null,
      ':lut' => isset($t['last_updated_time']) ? str_replace('T',' ', substr($t['last_updated_time'],0,19)) : null
    ]);
  }
  $next = $data['paging']['next'] ?? null;
}
echo "Templates synced\n";

CHECK BEFORE SENDING (Example)
------------------------------
$stmt = $pdo->prepare("SELECT status FROM waba_templates WHERE name=? AND language=? LIMIT 1");
$stmt->execute(['bienvenida_cuenta_activa', 'es']);
if (($stmt->fetchColumn() ?? 'PENDING') !== 'APPROVED') {
  throw new Exception('Template not APPROVED');
}

POSTMAN QUICK SETTINGS
----------------------
Environment Vars:
  token    = <ACCESS_TOKEN>
  waba_id  = 799342059122741

Requests:
1) List phones
   GET {{BASE_URL}}/{{waba_id}}/phone_numbers
   Params: fields = id,display_phone_number,verified_name,status
   Auth: Bearer {{token}}

2) List templates
   GET {{BASE_URL}}/{{waba_id}}/message_templates
   Params: fields = id,name,language,category,status,quality_score,last_updated_time ; limit = 100
   Auth: Bearer {{token}}

3) Template detail
   GET {{BASE_URL}}/{TEMPLATE_ID}
   Params: fields = name,language,category,status,components,quality_score,last_updated_time
   Auth: Bearer {{token}}

ERROR HANDLING
- HTTP 400 "missing permissions": token lacks whatsapp_business_management and/or business_management or asset not assigned.
- Use Business System User token with WABA asset assigned "Manage WhatsApp account".
- Rate limits: respect pagination and avoid rapid polling (< 1 req/sec typical).

CRON
- Run the sync every 10–15 minutes if templates change frequently; otherwise hourly is fine.

END OF SPEC