Implementation Guide: Monitor competitor pricing and recommend repricing actions within policy guardrails

aws ec2 run-instances \
  --image-id ami-0c7217cdde317cfec \
  --instance-type t3.xlarge \
  --key-name msp-pricing-agent-key \
  --security-group-ids sg-xxxxxxxx \
  --subnet-id subnet-xxxxxxxx \
  --block-device-mappings '[{"DeviceName":"/dev/sda1","Ebs":{"VolumeSize":100,"VolumeType":"gp3"}}]' \
  --tag-specifications 'ResourceType=instance,Tags=[{Key=Name,Value=pricing-agent-CLIENT_NAME},{Key=Project,Value=competitive-pricing}]'

aws ec2 authorize-security-group-ingress \
  --group-id sg-xxxxxxxx \
  --protocol tcp --port 22 --cidr MSP_OFFICE_IP/32

aws ec2 authorize-security-group-ingress \
  --group-id sg-xxxxxxxx \
  --protocol tcp --port 443 --cidr 0.0.0.0/0

aws ec2 allocate-address --domain vpc
aws ec2 associate-address --instance-id i-xxxxxxxx --allocation-id eipalloc-xxxxxxxx

ssh -i msp-pricing-agent-key.pem ubuntu@ELASTIC_IP_ADDRESS

sudo apt update && sudo apt upgrade -y
sudo apt install -y ca-certificates curl gnupg lsb-release
sudo mkdir -p /etc/apt/keyrings
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo gpg --dearmor -o /etc/apt/keyrings/docker.gpg
echo "deb [arch=$(dpkg --print-architecture) signed-by=/etc/apt/keyrings/docker.gpg] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable" | sudo tee /etc/apt/sources.list.d/docker.list > /dev/null
sudo apt update
sudo apt install -y docker-ce docker-ce-cli containerd.io docker-compose-plugin

sudo usermod -aG docker ubuntu
newgrp docker

mkdir -p ~/pricing-agent/{n8n,postgres,grafana,scripts,config}
cd ~/pricing-agent

cat > ~/pricing-agent/docker-compose.yml << 'DOCKER_EOF'
version: '3.8'

services:
  postgres:
    image: timescale/timescaledb:latest-pg16
    container_name: pricing-postgres
    restart: always
    environment:
      POSTGRES_DB: pricing_agent
      POSTGRES_USER: pricing_admin
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./postgres/init.sql:/docker-entrypoint-initdb.d/init.sql
    ports:
      - '127.0.0.1:5432:5432'
    healthcheck:
      test: ['CMD-SHELL', 'pg_isready -U pricing_admin -d pricing_agent']
      interval: 10s
      timeout: 5s
      retries: 5

  n8n:
    image: n8nio/n8n:latest
    container_name: pricing-n8n
    restart: always
    environment:
      - N8N_BASIC_AUTH_ACTIVE=true
      - N8N_BASIC_AUTH_USER=${N8N_USER}
      - N8N_BASIC_AUTH_PASSWORD=${N8N_PASSWORD}
      - N8N_HOST=${N8N_HOST}
      - N8N_PORT=5678
      - N8N_PROTOCOL=https
      - WEBHOOK_URL=https://${N8N_HOST}/
      - DB_TYPE=postgresdb
      - DB_POSTGRESDB_HOST=postgres
      - DB_POSTGRESDB_PORT=5432
      - DB_POSTGRESDB_DATABASE=n8n_db
      - DB_POSTGRESDB_USER=pricing_admin
      - DB_POSTGRESDB_PASSWORD=${POSTGRES_PASSWORD}
      - N8N_ENCRYPTION_KEY=${N8N_ENCRYPTION_KEY}
      - ANTHROPIC_API_KEY=${ANTHROPIC_API_KEY}
      - OPENAI_API_KEY=${OPENAI_API_KEY}
      - PRISYNC_API_KEY=${PRISYNC_API_KEY}
      - PRISYNC_API_TOKEN=${PRISYNC_API_TOKEN}
    volumes:
      - n8n_data:/home/node/.n8n
    ports:
      - '5678:5678'
    depends_on:
      postgres:
        condition: service_healthy

  grafana:
    image: grafana/grafana:latest
    container_name: pricing-grafana
    restart: always
    environment:
      - GF_SECURITY_ADMIN_USER=${GRAFANA_USER}
      - GF_SECURITY_ADMIN_PASSWORD=${GRAFANA_PASSWORD}
      - GF_SERVER_ROOT_URL=https://${GRAFANA_HOST}/
    volumes:
      - grafana_data:/var/lib/grafana
      - ./grafana/provisioning:/etc/grafana/provisioning
    ports:
      - '3000:3000'
    depends_on:
      postgres:
        condition: service_healthy

volumes:
  postgres_data:
  n8n_data:
  grafana_data:
DOCKER_EOF

cat > ~/pricing-agent/.env << 'ENV_EOF'
POSTGRES_PASSWORD=GENERATE_STRONG_PASSWORD_HERE
N8N_USER=admin
N8N_PASSWORD=GENERATE_STRONG_PASSWORD_HERE
N8N_HOST=n8n.clientdomain.com
N8N_ENCRYPTION_KEY=GENERATE_32_CHAR_RANDOM_STRING
ANTHROPIC_API_KEY=sk-ant-REPLACE_WITH_ACTUAL_KEY
OPENAI_API_KEY=sk-REPLACE_WITH_ACTUAL_KEY
PRISYNC_API_KEY=REPLACE_WITH_PRISYNC_API_KEY
PRISYNC_API_TOKEN=REPLACE_WITH_PRISYNC_TOKEN
GRAFANA_USER=admin
GRAFANA_PASSWORD=GENERATE_STRONG_PASSWORD_HERE
GRAFANA_HOST=pricing.clientdomain.com
ENV_EOF

sed -i "s/GENERATE_STRONG_PASSWORD_HERE/$(openssl rand -base64 24)/g" ~/pricing-agent/.env
sed -i "s/GENERATE_32_CHAR_RANDOM_STRING/$(openssl rand -hex 16)/" ~/pricing-agent/.env

chmod 600 ~/pricing-agent/.env

cd ~/pricing-agent
docker compose up -d
docker compose ps

cat > ~/pricing-agent/postgres/init.sql << 'SQL_EOF'
-- Create additional database for n8n
CREATE DATABASE n8n_db;

-- Switch to pricing_agent database
\c pricing_agent;

-- Enable TimescaleDB extension
CREATE EXTENSION IF NOT EXISTS timescaledb;

-- Products table: client's product catalog
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    sku VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(500) NOT NULL,
    category VARCHAR(200),
    brand VARCHAR(200),
    current_price DECIMAL(10,2),
    cost DECIMAL(10,2),
    map_price DECIMAL(10,2),
    min_margin_pct DECIMAL(5,2) DEFAULT 15.00,
    max_daily_change_pct DECIMAL(5,2) DEFAULT 5.00,
    pricing_strategy VARCHAR(50) DEFAULT 'match_market',
    is_active BOOLEAN DEFAULT true,
    prisync_product_id VARCHAR(100),
    ecommerce_product_id VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Competitors table
CREATE TABLE competitors (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    website_url VARCHAR(500),
    priority INTEGER DEFAULT 5,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Competitor prices: time-series data from Prisync
CREATE TABLE competitor_prices (
    time TIMESTAMPTZ NOT NULL,
    product_id INTEGER REFERENCES products(id),
    competitor_id INTEGER REFERENCES competitors(id),
    price DECIMAL(10,2),
    currency VARCHAR(3) DEFAULT 'USD',
    url VARCHAR(1000),
    in_stock BOOLEAN DEFAULT true,
    source VARCHAR(50) DEFAULT 'prisync'
);

-- Convert to TimescaleDB hypertable for efficient time-series queries
SELECT create_hypertable('competitor_prices', 'time');

-- Pricing policies: configurable guardrails
CREATE TABLE pricing_policies (
    id SERIAL PRIMARY KEY,
    policy_name VARCHAR(200) NOT NULL,
    category VARCHAR(200),
    min_margin_pct DECIMAL(5,2) NOT NULL DEFAULT 15.00,
    max_margin_pct DECIMAL(5,2) DEFAULT 60.00,
    max_daily_change_pct DECIMAL(5,2) NOT NULL DEFAULT 5.00,
    max_weekly_change_pct DECIMAL(5,2) DEFAULT 15.00,
    enforce_map BOOLEAN DEFAULT true,
    competitive_position VARCHAR(50) DEFAULT 'match_lowest',
    position_offset_pct DECIMAL(5,2) DEFAULT 0.00,
    auto_approve_threshold_pct DECIMAL(5,2) DEFAULT 2.00,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Agent recommendations
CREATE TABLE recommendations (
    id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(id),
    current_price DECIMAL(10,2),
    recommended_price DECIMAL(10,2),
    change_pct DECIMAL(5,2),
    projected_margin_pct DECIMAL(5,2),
    reason TEXT,
    competitor_context JSONB,
    policy_checks JSONB,
    confidence_score DECIMAL(3,2),
    status VARCHAR(20) DEFAULT 'pending',
    approved_by VARCHAR(100),
    approved_at TIMESTAMPTZ,
    applied_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Audit log: complete trail of all pricing actions
CREATE TABLE audit_log (
    id SERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    product_id INTEGER REFERENCES products(id),
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2),
    change_reason TEXT,
    agent_reasoning TEXT,
    policy_applied VARCHAR(200),
    user_action VARCHAR(50),
    user_id VARCHAR(100),
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_competitor_prices_product ON competitor_prices (product_id, time DESC);
CREATE INDEX idx_recommendations_status ON recommendations (status, created_at DESC);
CREATE INDEX idx_recommendations_product ON recommendations (product_id, created_at DESC);
CREATE INDEX idx_audit_log_product ON audit_log (product_id, created_at DESC);
CREATE INDEX idx_products_sku ON products (sku);
CREATE INDEX idx_products_category ON products (category);

-- Insert default pricing policies
INSERT INTO pricing_policies (policy_name, category, min_margin_pct, max_daily_change_pct, competitive_position, auto_approve_threshold_pct)
VALUES
  ('Default - Conservative', NULL, 20.00, 3.00, 'match_market_avg', 1.50),
  ('Electronics', 'Electronics', 12.00, 5.00, 'match_lowest', 2.00),
  ('Apparel', 'Apparel', 40.00, 5.00, 'premium_position', 1.00),
  ('Consumables', 'Consumables', 25.00, 3.00, 'match_lowest', 2.00);

SQL_EOF

cd ~/pricing-agent
docker compose down
docker volume rm pricing-agent_postgres_data 2>/dev/null
docker compose up -d

docker exec -it pricing-postgres psql -U pricing_admin -d pricing_agent -c '\dt'

sudo apt install -y nginx certbot python3-certbot-nginx

sudo cat > /etc/nginx/sites-available/pricing-dashboard << 'NGINX_EOF'
server {
    listen 80;
    server_name pricing.clientdomain.com;

    location / {
        proxy_pass http://127.0.0.1:3000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
    }
}

server {
    listen 80;
    server_name n8n.clientdomain.com;

    location / {
        proxy_pass http://127.0.0.1:5678;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header X-Forwarded-Proto $scheme;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
    }
}
NGINX_EOF

sudo ln -s /etc/nginx/sites-available/pricing-dashboard /etc/nginx/sites-enabled/
sudo rm /etc/nginx/sites-enabled/default
sudo nginx -t
sudo systemctl reload nginx

sudo certbot --nginx -d pricing.clientdomain.com -d n8n.clientdomain.com --non-interactive --agree-tos -m msp-admin@mspcompany.com

sudo systemctl enable certbot.timer

curl -X GET 'https://prisync.com/api/v2/list/product/id' \
  -H 'apikey: YOUR_PRISYNC_API_KEY' \
  -H 'apitoken: YOUR_PRISYNC_API_TOKEN' | python3 -m json.tool

cat > ~/pricing-agent/scripts/prepare_prisync_import.py << 'PYEOF'
import csv
import sys

def convert_shopify_to_prisync(input_file, output_file, competitor_urls_file):
    # Load competitor URL mappings (SKU -> [competitor URLs])
    url_map = {}
    with open(competitor_urls_file, 'r') as f:
        reader = csv.DictReader(f)
        for row in reader:
            sku = row['sku']
            urls = [row[f'competitor_url_{i}'] for i in range(1, 11) if row.get(f'competitor_url_{i}')]
            url_map[sku] = urls

    with open(input_file, 'r') as fin, open(output_file, 'w', newline='') as fout:
        reader = csv.DictReader(fin)
        writer = csv.writer(fout)
        header = ['name', 'category', 'brand', 'product_code', 'extra_info']
        header += [f'url_{i}' for i in range(1, 11)]
        writer.writerow(header)

        for row in reader:
            sku = row.get('Variant SKU', '')
            urls = url_map.get(sku, [])
            out_row = [
                row.get('Title', ''),
                row.get('Type', 'General'),
                row.get('Vendor', ''),
                sku,
                row.get('Variant Barcode', '')
            ]
            out_row += urls + [''] * (10 - len(urls))
            writer.writerow(out_row)

if __name__ == '__main__':
    convert_shopify_to_prisync(sys.argv[1], sys.argv[2], sys.argv[3])
PYEOF

curl -X POST 'https://prisync.com/api/v2/add/product/' \
  -H 'apikey: YOUR_PRISYNC_API_KEY' \
  -H 'apitoken: YOUR_PRISYNC_API_TOKEN' \
  -F 'name=Example Product' \
  -F 'brand=Example Brand' \
  -F 'category=Electronics' \
  -F 'productCode=SKU-12345' \
  -F 'additional_fields={"cost":"25.00","barcode":"012345678901"}'

cat > ~/pricing-agent/scripts/import_products.py << 'PYEOF'
import csv
import psycopg2
import os

DB_CONFIG = {
    'host': 'localhost',
    'port': 5432,
    'database': 'pricing_agent',
    'user': 'pricing_admin',
    'password': os.environ['POSTGRES_PASSWORD']
}

def import_products(csv_file):
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()

    with open(csv_file, 'r') as f:
        reader = csv.DictReader(f)
        for row in reader:
            cur.execute("""
                INSERT INTO products (sku, name, category, brand, current_price, cost, map_price, ecommerce_product_id)
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
                ON CONFLICT (sku) DO UPDATE SET
                    name = EXCLUDED.name,
                    current_price = EXCLUDED.current_price,
                    cost = EXCLUDED.cost,
                    updated_at = NOW()
            """, (
                row.get('Variant SKU'),
                row.get('Title'),
                row.get('Type', 'General'),
                row.get('Vendor', ''),
                float(row.get('Variant Price', 0)),
                float(row.get('Cost per item', 0)) if row.get('Cost per item') else None,
                float(row.get('MAP Price', 0)) if row.get('MAP Price') else None,
                row.get('Handle', '')
            ))

    conn.commit()
    cur.close()
    conn.close()
    print(f'Import complete.')

if __name__ == '__main__':
    import sys
    import_products(sys.argv[1])
PYEOF

sudo apt install -y python3-pip
pip3 install psycopg2-binary requests

export POSTGRES_PASSWORD=$(grep POSTGRES_PASSWORD ~/pricing-agent/.env | cut -d= -f2)
python3 ~/pricing-agent/scripts/import_products.py /path/to/shopify_export.csv

docker exec -it pricing-postgres psql -U pricing_admin -d pricing_agent -c 'SELECT COUNT(*) FROM products;'
docker exec -it pricing-postgres psql -U pricing_admin -d pricing_agent -c 'SELECT sku, name, current_price, cost FROM products LIMIT 10;'

docker cp /path/to/client_logo.svg pricing-grafana:/var/lib/grafana/public/img/custom_logo.svg

Approve: https://n8n.clientdomain.com/webhook/pricing-approve?id={{recommendation_id}}&action=approve
Reject:  https://n8n.clientdomain.com/webhook/pricing-approve?id={{recommendation_id}}&action=reject

curl -X GET "https://CLIENT-STORE.myshopify.com/admin/api/2024-01/products.json?limit=5" \
  -H "X-Shopify-Access-Token: shpat_XXXXXXXXXXXXX" | python3 -m json.tool

curl "https://clientstore.com/wp-json/wc/v3/products?per_page=5" \
  -u "consumer_key:consumer_secret" | python3 -m json.tool

cat > ~/pricing-agent/scripts/backup.sh << 'BASH_EOF'
#!/bin/bash
BACKUP_DIR=~/pricing-agent/backups
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR

# Dump the database
docker exec pricing-postgres pg_dump -U pricing_admin pricing_agent | gzip > $BACKUP_DIR/pricing_agent_$DATE.sql.gz

# Upload to S3 (if configured)
if command -v aws &> /dev/null; then
    aws s3 cp $BACKUP_DIR/pricing_agent_$DATE.sql.gz s3://msp-backups/pricing-agent/CLIENT_NAME/
fi

# Remove local backups older than 30 days
find $BACKUP_DIR -name '*.sql.gz' -mtime +30 -delete

echo "Backup completed: pricing_agent_$DATE.sql.gz"
BASH_EOF
chmod +x ~/pricing-agent/scripts/backup.sh

(crontab -l 2>/dev/null; echo '0 2 * * * ~/pricing-agent/scripts/backup.sh >> ~/pricing-agent/logs/backup.log 2>&1') | crontab -

cat > ~/pricing-agent/scripts/health_check.sh << 'BASH_EOF'
#!/bin/bash
SERVICES=("pricing-postgres" "pricing-n8n" "pricing-grafana")
ALERT_EMAIL="msp-alerts@mspcompany.com"

for service in "${SERVICES[@]}"; do
    STATUS=$(docker inspect -f '{{.State.Status}}' $service 2>/dev/null)
    if [ "$STATUS" != "running" ]; then
        echo "ALERT: $service is $STATUS on $(hostname) at $(date)" | \
          mail -s "Pricing Agent Alert: $service down" $ALERT_EMAIL
        docker compose -f ~/pricing-agent/docker-compose.yml restart $service
    fi
done
BASH_EOF
chmod +x ~/pricing-agent/scripts/health_check.sh

(crontab -l 2>/dev/null; echo '*/5 * * * * ~/pricing-agent/scripts/health_check.sh >> ~/pricing-agent/logs/health.log 2>&1') | crontab -

mkdir -p ~/pricing-agent/logs

You are a Retail Pricing Analyst Agent. Your role is to analyze competitive pricing data and recommend optimal price adjustments for a retail business, strictly within defined policy guardrails.

## YOUR RESPONSIBILITIES
1. Analyze competitor pricing positions relative to our products
2. Identify products that are over-priced or under-priced relative to the competitive landscape
3. Recommend specific price adjustments that comply with ALL policy guardrails
4. Provide clear reasoning for each recommendation
5. Flag anomalies (e.g., competitor prices that seem erroneous, sudden large changes)

## POLICY GUARDRAILS (MANDATORY — NEVER VIOLATE)
- **Minimum Margin**: Never recommend a price where (price - cost) / price < min_margin_pct
- **MAP Compliance**: Never recommend a price below the MAP (Minimum Advertised Price) for any product that has a MAP price set
- **Maximum Daily Change**: Never recommend a price change exceeding max_daily_change_pct in either direction from the current price
- **Maximum Weekly Change**: Total accumulated changes over 7 days must not exceed max_weekly_change_pct
- **No Price Below Cost**: Never recommend a price at or below the product's cost, even if competitors are priced lower

## PRICING STRATEGIES (apply based on product's pricing_strategy field)
- **match_lowest**: Match the lowest competitor price (within guardrails)
- **match_market_avg**: Match the average of all tracked competitor prices
- **premium_position**: Set price at position_offset_pct above the market average
- **undercut_lowest**: Set price at position_offset_pct below the lowest competitor
- **hold_current**: Do not recommend changes unless a guardrail is being violated

## INPUT FORMAT
You will receive a JSON object for each product containing:
{
  "product": {
    "sku": "string",
    "name": "string",
    "category": "string",
    "current_price": number,
    "cost": number,
    "map_price": number or null,
    "min_margin_pct": number,
    "max_daily_change_pct": number,
    "pricing_strategy": "string",
    "recent_price_changes": [{"date": "string", "old_price": number, "new_price": number}]
  },
  "competitors": [
    {
      "name": "string",
      "price": number,
      "in_stock": boolean,
      "last_updated": "string"
    }
  ],
  "policy": {
    "min_margin_pct": number,
    "max_daily_change_pct": number,
    "max_weekly_change_pct": number,
    "enforce_map": boolean,
    "competitive_position": "string",
    "position_offset_pct": number,
    "auto_approve_threshold_pct": number
  }
}

## OUTPUT FORMAT
Respond with a JSON object:
{
  "sku": "string",
  "action": "adjust" | "hold" | "flag",
  "recommended_price": number,
  "change_pct": number,
  "projected_margin_pct": number,
  "confidence": number (0.0 to 1.0),
  "requires_human_review": boolean,
  "reasoning": "string (2-3 sentences explaining the recommendation)",
  "guardrail_checks": {
    "margin_check": "pass" | "adjusted" | "blocked",
    "map_check": "pass" | "not_applicable" | "adjusted" | "blocked",
    "daily_change_check": "pass" | "adjusted" | "blocked",
    "weekly_change_check": "pass" | "adjusted" | "blocked"
  },
  "anomalies": ["string array of any detected anomalies"]
}

## RULES
1. Always output valid JSON. No markdown, no explanatory text outside the JSON.
2. If multiple guardrails conflict, prioritize in this order: MAP > Minimum Margin > Maximum Weekly Change > Maximum Daily Change > Competitive Position
3. If the optimal competitive price violates a guardrail, adjust to the nearest compliant price and set the relevant guardrail_check to "adjusted"
4. Set requires_human_review to true if: change_pct > auto_approve_threshold_pct, or any guardrail was "adjusted" or "blocked", or any anomalies were detected
5. Ignore competitor prices from out-of-stock competitors (in_stock: false) unless they are the only data points
6. Round all prices to 2 decimal places
7. If cost data is missing (null), set action to "flag" and note that cost data is required for margin calculation
8. Never recommend the same price as the current price — if no change is warranted, set action to "hold"

SELECT p.id, p.sku, p.name, p.category, p.brand, p.current_price, p.cost, 
       p.map_price, p.min_margin_pct, p.max_daily_change_pct, p.pricing_strategy,
       p.prisync_product_id, p.ecommerce_product_id,
       pp.min_margin_pct as policy_min_margin, pp.max_margin_pct as policy_max_margin,
       pp.max_daily_change_pct as policy_max_daily, pp.max_weekly_change_pct as policy_max_weekly,
       pp.enforce_map, pp.competitive_position, pp.position_offset_pct, pp.auto_approve_threshold_pct
FROM products p
LEFT JOIN pricing_policies pp ON (pp.category = p.category AND pp.is_active = true)
WHERE p.is_active = true
ORDER BY p.id;

SELECT old_price, new_price, created_at::text as date
FROM audit_log
WHERE product_id = {{ $json.id }} AND event_type = 'price_change'
AND created_at > NOW() - INTERVAL '7 days'
ORDER BY created_at DESC LIMIT 10;

INSERT INTO competitor_prices (time, product_id, competitor_id, price, in_stock, url)
VALUES (NOW(), {{ $json.product_id }}, {{ $json.competitor_id }}, {{ $json.price }}, {{ $json.in_stock }}, '{{ $json.url }}')
ON CONFLICT DO NOTHING;

const products = $input.all();
const batch = products.map(item => {
  return {
    product: {
      sku: item.json.sku,
      name: item.json.name,
      category: item.json.category,
      current_price: parseFloat(item.json.current_price),
      cost: item.json.cost ? parseFloat(item.json.cost) : null,
      map_price: item.json.map_price ? parseFloat(item.json.map_price) : null,
      min_margin_pct: parseFloat(item.json.min_margin_pct || item.json.policy_min_margin || 15),
      max_daily_change_pct: parseFloat(item.json.max_daily_change_pct || item.json.policy_max_daily || 5),
      pricing_strategy: item.json.pricing_strategy || item.json.competitive_position || 'match_market_avg',
      recent_price_changes: item.json.recent_changes || []
    },
    competitors: (item.json.competitor_prices || []).map(cp => ({
      name: cp.competitor_name,
      price: parseFloat(cp.price),
      in_stock: cp.in_stock !== false,
      last_updated: cp.last_updated || new Date().toISOString()
    })),
    policy: {
      min_margin_pct: parseFloat(item.json.policy_min_margin || 15),
      max_daily_change_pct: parseFloat(item.json.policy_max_daily || 5),
      max_weekly_change_pct: parseFloat(item.json.policy_max_weekly || 15),
      enforce_map: item.json.enforce_map !== false,
      competitive_position: item.json.competitive_position || 'match_market_avg',
      position_offset_pct: parseFloat(item.json.position_offset_pct || 0),
      auto_approve_threshold_pct: parseFloat(item.json.auto_approve_threshold_pct || 2)
    }
  };
});

return [{ json: { prompt_batch: JSON.stringify(batch, null, 2) } }];

{
  "model": "claude-haiku-4-5-20250501",
  "max_tokens": 4096,
  "system": "[INSERT FULL SYSTEM PROMPT FROM PRICING ANALYSIS AGENT COMPONENT]",
  "messages": [
    {
      "role": "user",
      "content": "Analyze the following batch of products and provide repricing recommendations for each. Return a JSON array of recommendation objects.\n\n{{ $json.prompt_batch }}"
    }
  ]
}

const response = $input.first().json;
const content = response.content[0].text;
let recommendations;
try {
  recommendations = JSON.parse(content);
  if (!Array.isArray(recommendations)) {
    recommendations = [recommendations];
  }
} catch (e) {
  // Try to extract JSON from response
  const match = content.match(/\[.*\]/s) || content.match(/\{.*\}/s);
  if (match) {
    recommendations = JSON.parse(match[0]);
    if (!Array.isArray(recommendations)) recommendations = [recommendations];
  } else {
    throw new Error('Failed to parse LLM response: ' + content.substring(0, 200));
  }
}

return recommendations.map(rec => ({ json: rec }));

INSERT INTO recommendations 
  (product_id, current_price, recommended_price, change_pct, projected_margin_pct, reason, competitor_context, policy_checks, confidence_score, status)
SELECT p.id, p.current_price, {{ $json.recommended_price }}, {{ $json.change_pct }}, {{ $json.projected_margin_pct }},
  '{{ $json.reasoning }}', '{{ JSON.stringify($json.competitors || {}) }}'::jsonb, '{{ JSON.stringify($json.guardrail_checks) }}'::jsonb,
  {{ $json.confidence }}, 
  CASE WHEN {{ $json.requires_human_review }} THEN 'pending' ELSE 'auto_approved' END
FROM products p WHERE p.sku = '{{ $json.sku }}'
RETURNING id;

🏷️ *Price Recommendation* for {{ $json.sku }}
*Product*: {{ $json.name }}
*Current Price*: ${{ $json.current_price }}
*Recommended*: ${{ $json.recommended_price }} ({{ $json.change_pct }}%)
*Projected Margin*: {{ $json.projected_margin_pct }}%
*Confidence*: {{ Math.round($json.confidence * 100) }}%
*Reason*: {{ $json.reasoning }}

[Approve](https://n8n.clientdomain.com/webhook/pricing-approve?id={{ $json.recommendation_id }}&action=approve) | [Reject](https://n8n.clientdomain.com/webhook/pricing-approve?id={{ $json.recommendation_id }}&action=reject)

{ "variant": { "price": "{{ $json.recommended_price }}" } }

INSERT INTO audit_log (event_type, product_id, old_price, new_price, change_reason, agent_reasoning, policy_applied, user_action, metadata)
SELECT 
  'recommendation_generated',
  p.id,
  p.current_price,
  {{ $json.recommended_price }},
  '{{ $json.reasoning }}',
  '{{ JSON.stringify($json) }}',
  '{{ JSON.stringify($json.guardrail_checks) }}',
  CASE WHEN {{ $json.requires_human_review }} THEN 'pending_approval' ELSE 'auto_approved' END,
  '{{ JSON.stringify({confidence: $json.confidence, anomalies: $json.anomalies || []}) }}'::jsonb
FROM products p WHERE p.sku = '{{ $json.sku }}';

SELECT COUNT(*) as value FROM recommendations WHERE status = 'pending';

SELECT COUNT(*) as value FROM recommendations 
WHERE status IN ('approved', 'auto_approved') 
AND approved_at > NOW() - INTERVAL '7 days';

SELECT ROUND(AVG(projected_margin_pct - 
  (SELECT (p.current_price - p.cost) / p.current_price * 100 
   FROM products p WHERE p.id = r.product_id)), 2) as value
FROM recommendations r
WHERE status IN ('approved', 'auto_approved') 
AND approved_at > NOW() - INTERVAL '7 days';

WITH latest_prices AS (
  SELECT DISTINCT ON (product_id, competitor_id) 
    product_id, price
  FROM competitor_prices
  WHERE time > NOW() - INTERVAL '24 hours'
  ORDER BY product_id, competitor_id, time DESC
)
SELECT COUNT(DISTINCT p.id) as value
FROM products p
JOIN latest_prices lp ON lp.product_id = p.id
GROUP BY p.id
HAVING p.current_price < AVG(lp.price) * 0.95;

WITH latest AS (
  SELECT DISTINCT ON (cp.product_id, cp.competitor_id)
    cp.product_id, c.name as competitor, cp.price, cp.in_stock
  FROM competitor_prices cp
  JOIN competitors c ON c.id = cp.competitor_id
  WHERE cp.time > NOW() - INTERVAL '24 hours'
  ORDER BY cp.product_id, cp.competitor_id, cp.time DESC
)
SELECT p.sku, p.name, p.current_price as our_price,
  MIN(l.price) as lowest_competitor,
  AVG(l.price)::numeric(10,2) as avg_competitor,
  MAX(l.price) as highest_competitor,
  ROUND((p.current_price - MIN(l.price)) / MIN(l.price) * 100, 1) as pct_vs_lowest,
  CASE 
    WHEN p.current_price < MIN(l.price) THEN 'Below Market'
    WHEN p.current_price <= AVG(l.price) THEN 'Competitive'
    WHEN p.current_price <= MAX(l.price) THEN 'Above Average'
    ELSE 'Premium'
  END as position
FROM products p
JOIN latest l ON l.product_id = p.id
WHERE l.in_stock = true
GROUP BY p.id, p.sku, p.name, p.current_price
ORDER BY pct_vs_lowest DESC;

SELECT time_bucket('1 day', al.created_at) as time,
  COUNT(*) FILTER (WHERE al.user_action = 'approved') as approved,
  COUNT(*) FILTER (WHERE al.user_action = 'auto_approved') as auto_approved,
  COUNT(*) FILTER (WHERE al.user_action = 'rejected') as rejected,
  COUNT(*) FILTER (WHERE al.user_action = 'pending_approval') as pending
FROM audit_log al
WHERE al.event_type = 'recommendation_generated'
AND al.created_at > $__timeFrom()
GROUP BY time_bucket('1 day', al.created_at)
ORDER BY 1;

SELECT r.id, p.sku, p.name, 
  r.current_price, r.recommended_price,
  r.change_pct || '%' as change,
  r.projected_margin_pct || '%' as margin,
  r.confidence_score,
  r.reason,
  r.created_at::text as recommended_at
FROM recommendations r
JOIN products p ON p.id = r.product_id
WHERE r.status = 'pending'
ORDER BY r.created_at DESC;

SELECT al.created_at::text as timestamp,
  al.event_type, p.sku, p.name,
  al.old_price, al.new_price,
  al.change_reason,
  al.policy_applied,
  al.user_action, al.user_id
FROM audit_log al
JOIN products p ON p.id = al.product_id
WHERE al.created_at > $__timeFrom()
ORDER BY al.created_at DESC
LIMIT 100;

#!/usr/bin/env python3
"""
Competitor Price Sync - Prisync to TimescaleDB
Runs via n8n Code node or as standalone cron job.
Syncs all product prices from Prisync and stores in local DB.
"""
import os
import json
import time
import requests
import psycopg2
from datetime import datetime, timezone

# Configuration
PRISYNC_API_KEY = os.environ.get('PRISYNC_API_KEY')
PRISYNC_API_TOKEN = os.environ.get('PRISYNC_API_TOKEN')
PRISYNC_BASE_URL = 'https://prisync.com/api/v2'

DB_CONFIG = {
    'host': os.environ.get('DB_HOST', 'localhost'),
    'port': int(os.environ.get('DB_PORT', 5432)),
    'database': os.environ.get('DB_NAME', 'pricing_agent'),
    'user': os.environ.get('DB_USER', 'pricing_admin'),
    'password': os.environ.get('DB_PASSWORD', '')
}

MAX_RETRIES = 3
RETRY_DELAY = 5  # seconds


def prisync_request(endpoint, params=None):
    """Make authenticated request to Prisync API with retry logic."""
    headers = {
        'apikey': PRISYNC_API_KEY,
        'apitoken': PRISYNC_API_TOKEN
    }
    for attempt in range(MAX_RETRIES):
        try:
            response = requests.get(
                f'{PRISYNC_BASE_URL}/{endpoint}',
                headers=headers,
                params=params,
                timeout=30
            )
            response.raise_for_status()
            return response.json()
        except requests.exceptions.RequestException as e:
            if attempt < MAX_RETRIES - 1:
                print(f'Prisync API error (attempt {attempt+1}): {e}. Retrying in {RETRY_DELAY}s...')
                time.sleep(RETRY_DELAY)
            else:
                raise


def get_all_products():
    """Fetch all products from Prisync."""
    result = prisync_request('list/product/id')
    if result.get('success'):
        return result.get('results', {})
    return {}


def get_product_prices(product_id):
    """Fetch competitor prices for a specific product."""
    result = prisync_request(f'get/product/prices/id/{product_id}')
    if result.get('success'):
        return result.get('results', [])
    return []


def sync_prices():
    """Main sync function: pull all competitor prices and store in DB."""
    conn = psycopg2.connect(**DB_CONFIG)
    cur = conn.cursor()
    now = datetime.now(timezone.utc)
    
    # Get product mapping: prisync_product_id -> our product_id
    cur.execute('SELECT id, prisync_product_id FROM products WHERE prisync_product_id IS NOT NULL AND is_active = true')
    product_map = {row[1]: row[0] for row in cur.fetchall()}
    
    # Get competitor mapping
    cur.execute('SELECT id, name FROM competitors WHERE is_active = true')
    competitor_map = {row[1].lower(): row[0] for row in cur.fetchall()}
    
    products = get_all_products()
    total_synced = 0
    errors = []
    
    for prisync_id, product_info in products.items():
        our_product_id = product_map.get(str(prisync_id))
        if not our_product_id:
            continue
        
        try:
            prices = get_product_prices(prisync_id)
            for price_entry in prices:
                comp_name = price_entry.get('competitor_name', '').lower()
                comp_id = competitor_map.get(comp_name)
                
                if not comp_id:
                    # Auto-create competitor entry
                    cur.execute(
                        'INSERT INTO competitors (name, website_url) VALUES (%s, %s) RETURNING id',
                        (price_entry.get('competitor_name', 'Unknown'), price_entry.get('url', ''))
                    )
                    comp_id = cur.fetchone()[0]
                    competitor_map[comp_name] = comp_id
                
                price_value = price_entry.get('price')
                if price_value and float(price_value) > 0:
                    cur.execute("""
                        INSERT INTO competitor_prices (time, product_id, competitor_id, price, in_stock, url)
                        VALUES (%s, %s, %s, %s, %s, %s)
                    """, (
                        now, our_product_id, comp_id,
                        float(price_value),
                        price_entry.get('in_stock', True),
                        price_entry.get('url', '')
                    ))
                    total_synced += 1
            
            # Rate limiting: Prisync API allows ~60 req/min
            time.sleep(1)
            
        except Exception as e:
            errors.append(f'Product {prisync_id}: {str(e)}')
            continue
    
    # Update product current prices from latest competitor data
    cur.execute("""
        UPDATE products p SET updated_at = NOW()
        WHERE p.is_active = true;
    """)
    
    conn.commit()
    cur.close()
    conn.close()
    
    return {
        'synced_prices': total_synced,
        'errors': errors,
        'timestamp': now.isoformat()
    }


if __name__ == '__main__':
    result = sync_prices()
    print(json.dumps(result, indent=2))

#!/usr/bin/env python3
"""
Pricing Policy Guardrail Validator
Deterministic post-LLM validation layer.
Runs as n8n Code node or standalone module.
NEVER trust the LLM's guardrail self-assessment — always re-validate.
"""
import json
from decimal import Decimal, ROUND_HALF_UP
from datetime import datetime, timedelta


class GuardrailValidator:
    
    def __init__(self, product, policy, recent_changes=None):
        self.product = product
        self.policy = policy
        self.recent_changes = recent_changes or []
        self.violations = []
        self.adjustments = []
    
    def validate_and_adjust(self, recommended_price):
        """
        Validate a recommended price against all guardrails.
        Returns adjusted price and validation report.
        """
        rec_price = Decimal(str(recommended_price)).quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)
        current_price = Decimal(str(self.product['current_price']))
        cost = Decimal(str(self.product['cost'])) if self.product.get('cost') else None
        map_price = Decimal(str(self.product['map_price'])) if self.product.get('map_price') else None
        
        adjusted_price = rec_price
        checks = {}
        
        # Check 1: Never below cost
        if cost and adjusted_price <= cost:
            min_price_above_cost = cost + Decimal('0.01')
            self.violations.append(f'Price ${adjusted_price} is at or below cost ${cost}')
            adjusted_price = max(adjusted_price, min_price_above_cost)
            checks['cost_check'] = 'blocked'
        else:
            checks['cost_check'] = 'pass'
        
        # Check 2: MAP compliance (highest priority)
        if map_price and self.policy.get('enforce_map', True):
            if adjusted_price < map_price:
                self.violations.append(f'Price ${adjusted_price} is below MAP ${map_price}')
                adjusted_price = map_price
                checks['map_check'] = 'adjusted'
            else:
                checks['map_check'] = 'pass'
        else:
            checks['map_check'] = 'not_applicable'
        
        # Check 3: Minimum margin
        if cost and cost > 0:
            min_margin_pct = Decimal(str(self.policy.get('min_margin_pct', 15)))
            margin_pct = ((adjusted_price - cost) / adjusted_price * 100).quantize(Decimal('0.01'))
            if margin_pct < min_margin_pct:
                # Calculate minimum price for required margin
                min_price_for_margin = (cost / (1 - min_margin_pct / 100)).quantize(Decimal('0.01'), rounding=ROUND_HALF_UP)
                self.adjustments.append(f'Price adjusted from ${adjusted_price} to ${min_price_for_margin} to meet {min_margin_pct}% margin requirement')
                adjusted_price = max(adjusted_price, min_price_for_margin)
                checks['margin_check'] = 'adjusted'
            else:
                checks['margin_check'] = 'pass'
        else:
            checks['margin_check'] = 'skipped_no_cost_data'
        
        # Check 4: Maximum daily change
        max_daily_pct = Decimal(str(self.policy.get('max_daily_change_pct', 5)))
        if current_price > 0:
            change_pct = abs((adjusted_price - current_price) / current_price * 100)
            if change_pct > max_daily_pct:
                if adjusted_price > current_price:
                    max_price = (current_price * (1 + max_daily_pct / 100)).quantize(Decimal('0.01'))
                    adjusted_price = min(adjusted_price, max_price)
                else:
                    min_price = (current_price * (1 - max_daily_pct / 100)).quantize(Decimal('0.01'))
                    adjusted_price = max(adjusted_price, min_price)
                self.adjustments.append(f'Price capped at ${adjusted_price} due to {max_daily_pct}% daily change limit')
                checks['daily_change_check'] = 'adjusted'
            else:
                checks['daily_change_check'] = 'pass'
        
        # Check 5: Maximum weekly change (accumulated)
        max_weekly_pct = Decimal(str(self.policy.get('max_weekly_change_pct', 15)))
        if self.recent_changes and current_price > 0:
            seven_days_ago = datetime.now() - timedelta(days=7)
            week_changes = [c for c in self.recent_changes if datetime.fromisoformat(c['date']) > seven_days_ago]
            if week_changes:
                oldest_price = Decimal(str(week_changes[-1].get('old_price', current_price)))
                total_change_pct = abs((adjusted_price - oldest_price) / oldest_price * 100)
                if total_change_pct > max_weekly_pct:
                    if adjusted_price > oldest_price:
                        max_weekly_price = (oldest_price * (1 + max_weekly_pct / 100)).quantize(Decimal('0.01'))
                        adjusted_price = min(adjusted_price, max_weekly_price)
                    else:
                        min_weekly_price = (oldest_price * (1 - max_weekly_pct / 100)).quantize(Decimal('0.01'))
                        adjusted_price = max(adjusted_price, min_weekly_price)
                    self.adjustments.append(f'Price further capped at ${adjusted_price} due to {max_weekly_pct}% weekly change limit')
                    checks['weekly_change_check'] = 'adjusted'
                else:
                    checks['weekly_change_check'] = 'pass'
            else:
                checks['weekly_change_check'] = 'pass'
        else:
            checks['weekly_change_check'] = 'pass'
        
        # Re-verify MAP after all adjustments (MAP has highest priority)
        if map_price and self.policy.get('enforce_map', True) and adjusted_price < map_price:
            adjusted_price = map_price
            checks['map_check'] = 'enforced_final'
        
        # Calculate final metrics
        final_change_pct = float(((adjusted_price - current_price) / current_price * 100).quantize(Decimal('0.01'))) if current_price > 0 else 0
        final_margin_pct = float(((adjusted_price - cost) / adjusted_price * 100).quantize(Decimal('0.01'))) if cost and adjusted_price > 0 else None
        
        # Determine if human review is needed
        auto_threshold = float(self.policy.get('auto_approve_threshold_pct', 2))
        needs_review = (
            abs(final_change_pct) > auto_threshold or
            any(v != 'pass' and v != 'not_applicable' for v in checks.values()) or
            len(self.violations) > 0
        )
        
        was_adjusted = float(adjusted_price) != float(rec_price)
        
        return {
            'original_recommendation': float(rec_price),
            'validated_price': float(adjusted_price),
            'was_adjusted': was_adjusted,
            'change_pct': final_change_pct,
            'projected_margin_pct': final_margin_pct,
            'requires_human_review': needs_review,
            'guardrail_checks': checks,
            'violations': self.violations,
            'adjustments': self.adjustments
        }


# n8n Code Node integration
def validate_recommendations(recommendations, products_db, policies_db):
    """Process a batch of LLM recommendations through guardrail validation."""
    validated = []
    for rec in recommendations:
        sku = rec.get('sku')
        product = products_db.get(sku)
        if not product:
            continue
        
        policy = policies_db.get(product.get('category'), policies_db.get('default', {}))
        
        validator = GuardrailValidator(
            product=product,
            policy=policy,
            recent_changes=product.get('recent_price_changes', [])
        )
        
        result = validator.validate_and_adjust(rec.get('recommended_price', product['current_price']))
        result['sku'] = sku
        result['name'] = product.get('name', '')
        result['current_price'] = product['current_price']
        result['llm_reasoning'] = rec.get('reasoning', '')
        result['llm_confidence'] = rec.get('confidence', 0)
        result['anomalies'] = rec.get('anomalies', [])
        
        validated.append(result)
    
    return validated

# supports Shopify, WooCommerce, BigCommerce with rollback capability

#!/usr/bin/env python3
"""
E-Commerce Price Updater
Pushes approved price changes to the client's e-commerce platform.
Supports Shopify, WooCommerce, BigCommerce.
Includes rollback capability.
"""
import os
import json
import requests
from datetime import datetime, timezone


class ShopifyUpdater:
    def __init__(self, store_url, access_token):
        self.base_url = f'https://{store_url}/admin/api/2024-01'
        self.headers = {
            'X-Shopify-Access-Token': access_token,
            'Content-Type': 'application/json'
        }
    
    def get_product_variant(self, product_handle):
        """Get variant ID and current price by product handle."""
        resp = requests.get(
            f'{self.base_url}/products.json?handle={product_handle}',
            headers=self.headers
        )
        resp.raise_for_status()
        products = resp.json().get('products', [])
        if products:
            variants = products[0].get('variants', [])
            if variants:
                return {
                    'variant_id': variants[0]['id'],
                    'product_id': products[0]['id'],
                    'current_price': variants[0]['price'],
                    'compare_at_price': variants[0].get('compare_at_price')
                }
        return None
    
    def update_price(self, variant_id, new_price, compare_at_price=None):
        """Update variant price on Shopify."""
        payload = {'variant': {'id': variant_id, 'price': str(new_price)}}
        if compare_at_price:
            payload['variant']['compare_at_price'] = str(compare_at_price)
        
        resp = requests.put(
            f'{self.base_url}/variants/{variant_id}.json',
            headers=self.headers,
            json=payload
        )
        resp.raise_for_status()
        return resp.json()
    
    def rollback_price(self, variant_id, original_price):
        """Rollback to previous price."""
        return self.update_price(variant_id, original_price)


class WooCommerceUpdater:
    def __init__(self, store_url, consumer_key, consumer_secret):
        self.base_url = f'{store_url}/wp-json/wc/v3'
        self.auth = (consumer_key, consumer_secret)
    
    def get_product_by_sku(self, sku):
        """Find product by SKU."""
        resp = requests.get(
            f'{self.base_url}/products',
            auth=self.auth,
            params={'sku': sku}
        )
        resp.raise_for_status()
        products = resp.json()
        if products:
            return {
                'product_id': products[0]['id'],
                'current_price': products[0]['price'],
                'regular_price': products[0]['regular_price'],
                'sale_price': products[0].get('sale_price', '')
            }
        return None
    
    def update_price(self, product_id, new_price):
        """Update product regular price on WooCommerce."""
        resp = requests.put(
            f'{self.base_url}/products/{product_id}',
            auth=self.auth,
            json={'regular_price': str(new_price)}
        )
        resp.raise_for_status()
        return resp.json()
    
    def rollback_price(self, product_id, original_price):
        return self.update_price(product_id, original_price)


def apply_approved_recommendation(recommendation, platform_config):
    """
    Apply a single approved recommendation to the e-commerce platform.
    Returns success status and details for audit logging.
    """
    platform = platform_config.get('platform', 'shopify')
    result = {
        'sku': recommendation['sku'],
        'old_price': recommendation['current_price'],
        'new_price': recommendation['validated_price'],
        'platform': platform,
        'timestamp': datetime.now(timezone.utc).isoformat(),
        'success': False,
        'error': None
    }
    
    try:
        if platform == 'shopify':
            updater = ShopifyUpdater(
                platform_config['store_url'],
                platform_config['access_token']
            )
            variant_info = updater.get_product_variant(recommendation.get('product_handle', recommendation['sku']))
            if variant_info:
                result['previous_price_confirmed'] = variant_info['current_price']
                updater.update_price(variant_info['variant_id'], recommendation['validated_price'])
                result['success'] = True
                result['variant_id'] = variant_info['variant_id']
            else:
                result['error'] = f"Product not found on Shopify: {recommendation['sku']}"
        
        elif platform == 'woocommerce':
            updater = WooCommerceUpdater(
                platform_config['store_url'],
                platform_config['consumer_key'],
                platform_config['consumer_secret']
            )
            product_info = updater.get_product_by_sku(recommendation['sku'])
            if product_info:
                result['previous_price_confirmed'] = product_info['current_price']
                updater.update_price(product_info['product_id'], recommendation['validated_price'])
                result['success'] = True
                result['product_id'] = product_info['product_id']
            else:
                result['error'] = f"Product not found on WooCommerce: {recommendation['sku']}"
    
    except Exception as e:
        result['error'] = str(e)
        result['success'] = False
    
    return result


def bulk_rollback(recommendations, platform_config):
    """Emergency rollback: revert all recent price changes."""
    results = []
    for rec in recommendations:
        try:
            if platform_config['platform'] == 'shopify':
                updater = ShopifyUpdater(platform_config['store_url'], platform_config['access_token'])
                variant_info = updater.get_product_variant(rec.get('product_handle', rec['sku']))
                if variant_info:
                    updater.rollback_price(variant_info['variant_id'], rec['old_price'])
                    results.append({'sku': rec['sku'], 'rolled_back': True})
            elif platform_config['platform'] == 'woocommerce':
                updater = WooCommerceUpdater(platform_config['store_url'], platform_config['consumer_key'], platform_config['consumer_secret'])
                product_info = updater.get_product_by_sku(rec['sku'])
                if product_info:
                    updater.rollback_price(product_info['product_id'], rec['old_price'])
                    results.append({'sku': rec['sku'], 'rolled_back': True})
        except Exception as e:
            results.append({'sku': rec['sku'], 'rolled_back': False, 'error': str(e)})
    return results

curl -X GET 'https://prisync.com/api/v2/list/product/id' -H 'apikey: YOUR_KEY' -H 'apitoken: YOUR_TOKEN'

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

SELECT COUNT(*) FROM products WHERE cost IS NOT NULL AND cost > 0;