#!/usr/bin/env bash
# deploy-go-live.sh — Run pending migrations, deploy crons, populate entity cache,
# create ERPNext item, and verify Playwright selectors.
#
# Usage:
#   ssh deploy@207.174.124.71 -p 22022
#   cd /opt/performancewest
#   bash scripts/deploy-go-live.sh
#
# Or from a machine with SSH access:
#   scp scripts/deploy-go-live.sh deploy@207.174.124.71:/opt/performancewest/scripts/
#   ssh -p 22022 deploy@207.174.124.71 'cd /opt/performancewest && bash scripts/deploy-go-live.sh'

set -euo pipefail

PROJECT_DIR="${PROJECT_DIR:-/opt/performancewest}"
cd "$PROJECT_DIR"

echo "=========================================="
echo " Performance West — Go-Live Deployment"
echo "=========================================="
echo ""

# ─────────────────────────────────────────────────
# #1: Run pending DB migrations (069-073)
# ─────────────────────────────────────────────────
echo ">>> Step 1: Running pending database migrations..."

# Source DATABASE_URL from the API container's env
DB_URL=$(docker compose exec -T api printenv DATABASE_URL 2>/dev/null || echo "")
if [ -z "$DB_URL" ]; then
    echo "ERROR: Cannot read DATABASE_URL from api container. Is it running?"
    echo "Try: docker compose up -d api"
    exit 1
fi

# Check which migrations are already applied
echo "  Checking existing tables..."
HAS_AUDIT=$(docker compose exec -T api node -e "
  const {Pool} = require('pg');
  const p = new Pool({connectionString: process.env.DATABASE_URL});
  p.query(\"SELECT 1 FROM pg_tables WHERE tablename='fcc_rmd_audit_results'\")
    .then(r => { console.log(r.rows.length > 0 ? 'yes' : 'no'); p.end(); })
    .catch(() => { console.log('no'); p.end(); });
" 2>/dev/null || echo "no")

HAS_PUC=$(docker compose exec -T api node -e "
  const {Pool} = require('pg');
  const p = new Pool({connectionString: process.env.DATABASE_URL});
  p.query(\"SELECT 1 FROM pg_tables WHERE tablename='state_puc_requirements'\")
    .then(r => { console.log(r.rows.length > 0 ? 'yes' : 'no'); p.end(); })
    .catch(() => { console.log('no'); p.end(); });
" 2>/dev/null || echo "no")

HAS_RMD_REVIEW=$(docker compose exec -T api node -e "
  const {Pool} = require('pg');
  const p = new Pool({connectionString: process.env.DATABASE_URL});
  p.query(\"SELECT 1 FROM information_schema.columns WHERE table_name='compliance_orders' AND column_name='rmd_review_status'\")
    .then(r => { console.log(r.rows.length > 0 ? 'yes' : 'no'); p.end(); })
    .catch(() => { console.log('no'); p.end(); });
" 2>/dev/null || echo "no")

# Run migrations that haven't been applied
MIGRATIONS_DIR="api/migrations"

if [ "$HAS_AUDIT" = "no" ]; then
    echo "  Applying 070_rmd_audit_results.sql..."
    docker compose exec -T api sh -c "psql \$DATABASE_URL < /app/$MIGRATIONS_DIR/070_rmd_audit_results.sql"
    echo "  ✓ 070 applied"
else
    echo "  ✓ 070 already applied (fcc_rmd_audit_results exists)"
fi

if [ "$HAS_RMD_REVIEW" = "no" ]; then
    echo "  Applying 071_rmd_review_columns.sql..."
    docker compose exec -T api sh -c "psql \$DATABASE_URL < /app/$MIGRATIONS_DIR/071_rmd_review_columns.sql"
    echo "  ✓ 071 applied"
else
    echo "  ✓ 071 already applied (rmd_review_status column exists)"
fi

if [ "$HAS_PUC" = "no" ]; then
    echo "  Applying 072_state_puc_requirements.sql..."
    docker compose exec -T api sh -c "psql \$DATABASE_URL < /app/$MIGRATIONS_DIR/072_state_puc_requirements.sql"
    echo "  ✓ 072 applied"

    echo "  Applying 073_state_puc_registrations.sql..."
    docker compose exec -T api sh -c "psql \$DATABASE_URL < /app/$MIGRATIONS_DIR/073_state_puc_registrations.sql"
    echo "  ✓ 073 applied"
else
    echo "  ✓ 072-073 already applied (state_puc_requirements exists)"
fi

# Verify
PUC_COUNT=$(docker compose exec -T api node -e "
  const {Pool} = require('pg');
  const p = new Pool({connectionString: process.env.DATABASE_URL});
  p.query('SELECT COUNT(*) as c FROM state_puc_requirements')
    .then(r => { console.log(r.rows[0].c); p.end(); })
    .catch(e => { console.log('ERROR: ' + e.message); p.end(); });
" 2>/dev/null || echo "ERROR")
echo "  State PUC requirements: $PUC_COUNT rows"
echo ""

# ─────────────────────────────────────────────────
# #2: Deploy cron jobs via Ansible
# ─────────────────────────────────────────────────
echo ">>> Step 2: Deploying systemd cron timers..."

if command -v ansible-playbook &>/dev/null; then
    cd "$PROJECT_DIR/infra/ansible"
    ansible-playbook playbooks/deploy-crons.yml -i inventory/hosts.yml --connection=local 2>&1 | tail -20
    cd "$PROJECT_DIR"
    echo "  ✓ Cron timers deployed"
else
    echo "  SKIP: ansible-playbook not found. Install ansible or run manually:"
    echo "    cd infra/ansible && ansible-playbook playbooks/deploy-crons.yml -i inventory/hosts.yml --connection=local"
fi
echo ""

# ─────────────────────────────────────────────────
# #3: Verify Playwright selectors (dry-run)
# ─────────────────────────────────────────────────
echo ">>> Step 3: Playwright selector verification (smoke test)..."
echo "  The RMD filing handler uses these selectors against the FCC RMD portal:"
echo "    - text=Certification"
echo "    - text=File Certification"
echo "    - input[name='frn']"
echo "    - input[name='company_legal_name']"
echo "    - input[name='stir_shaken_status']"
echo "    - button[type='submit']"
echo "    - text=Confirmation Number"
echo ""
echo "  These are generic ServiceNow patterns. The handler has an admin-todo"
echo "  fallback if any selector fails — no orders will be lost."
echo ""
echo "  To verify live: run a test filing (non-production FRN) through the RMD"
echo "  handler and check if selectors resolve."
echo ""
echo "  Running Playwright connectivity check..."
docker compose exec -T workers python3 -c "
try:
    from patchright.sync_api import sync_playwright
    with sync_playwright() as p:
        browser = p.chromium.launch(headless=True)
        page = browser.new_page()
        page.goto('https://fccprod.servicenowservices.com/rmd', timeout=30000)
        title = page.title()
        print(f'  FCC RMD portal reachable: {title}')
        # Check if key elements exist on the landing page
        cert_link = page.locator('text=Certification').first
        if cert_link:
            print('  ✓ \"Certification\" text found on page')
        browser.close()
except Exception as e:
    print(f'  WARNING: Could not reach FCC RMD portal: {e}')
    print('  This is expected if running from a restricted network.')
    print('  The handler will fall back to admin-todo if selectors fail.')
" 2>&1 || echo "  (Playwright not available in workers container — verify manually)"
echo ""

# ─────────────────────────────────────────────────
# #4: Populate entity cache
# ─────────────────────────────────────────────────
echo ">>> Step 4: Populating entity cache (Socrata bulk download)..."
echo "  Running bulk download for all configured states (CO, NY, CT, OR, IA)..."
docker compose exec -T workers python3 -m scripts.formation.bulk_download --all 2>&1 | tail -20
echo ""

echo "  Running Florida SFTP download..."
docker compose exec -T workers python3 -m scripts.workers.fl_entity_downloader --daily 2>&1 | tail -10
echo ""

# Check totals
docker compose exec -T api node -e "
  const {Pool} = require('pg');
  const p = new Pool({connectionString: process.env.DATABASE_URL});
  p.query('SELECT state, COUNT(*) as c FROM entity_cache GROUP BY state ORDER BY c DESC')
    .then(r => {
      let total = 0;
      for (const row of r.rows) {
        console.log('  ' + row.state + ': ' + Number(row.c).toLocaleString() + ' entities');
        total += Number(row.c);
      }
      console.log('  TOTAL: ' + total.toLocaleString() + ' entities');
      p.end();
    })
    .catch(e => { console.log('  Entity cache query failed: ' + e.message); p.end(); });
" 2>/dev/null
echo ""

# ─────────────────────────────────────────────────
# #5: Create ERPNext Item for State PUC
# ─────────────────────────────────────────────────
echo ">>> Step 5: Creating ERPNext Item for State PUC..."
docker compose exec -T workers python3 -c "
from scripts.workers.erpnext_client import ERPNextClient
client = ERPNextClient()

# Check if STATE-PUC item already exists
try:
    existing = client.get_resource('Item', 'STATE-PUC')
    print('  ✓ STATE-PUC item already exists')
except Exception:
    # Create it
    try:
        client.create_resource('Item', {
            'item_code': 'STATE-PUC',
            'item_name': 'State PUC/PSC Registration',
            'item_group': 'Services',
            'stock_uom': 'Nos',
            'is_stock_item': 0,
            'is_sales_item': 1,
            'description': 'State PUC/PSC registration for VoIP, broadband, or CLEC providers. Per-state service fee.',
            'standard_rate': 399.00,
            'item_defaults': [{'company': 'Performance West Inc', 'income_account': 'Sales - PWI', 'default_warehouse': ''}],
        })
        print('  ✓ STATE-PUC item created in ERPNext')
    except Exception as e:
        print(f'  WARNING: Could not create STATE-PUC item: {e}')
        print('  Create it manually in ERPNext: Item Code=STATE-PUC, Rate=\$399, Group=Services')
" 2>&1
echo ""

echo "=========================================="
echo " Go-Live Deployment Complete"
echo "=========================================="
echo ""
echo "Remaining manual steps:"
echo "  1. Verify one test RMD filing through the Playwright handler"
echo "  2. Approve Listmonk campaign when ready (run rmd_deficiency_campaign.py)"
echo "  3. Set up California BizFileOnline weekly subscription for entity cache"
echo ""