// One-shot script to create the Diesel Depot collections in PocketBase.
// Run with: node scripts/create-collections.mjs
// Requires env: PB_URL, PB_ADMIN_TOKEN (a superuser auth token).
//
// IMPORTANT (from AGENTS.md-style memory):
//   - Field options go at the top level of each field object (flat shape), NOT nested under `options`.
//   - Auth collections need explicit `created` + `updated` autodate fields added manually.
//   - The PocketBase REST API takes the field array under the `fields` key, not `schema`.
//     (The JS SDK accepts both, but the raw API only honours `fields`. Found the hard way
//     on 2026-07-19 — `schema` is silently ignored.)
//   - Relation fields need the relation's actual collectionId (the PB UUID), not its name.
//     We do a two-pass: first create collections without relations, then patch in relations.

import PocketBase from 'pocketbase';

const PB_URL = process.env.PB_URL || 'http://pocketbase-zinlbpsext2ro0lgb2558vfy.169.239.180.130.sslip.io';
const PB_ADMIN_TOKEN = process.env.PB_ADMIN_TOKEN;

if (!PB_ADMIN_TOKEN) {
  console.error('❌ PB_ADMIN_TOKEN env var required (a superuser auth token).');
  process.exit(1);
}

const pb = new PocketBase(PB_URL);
pb.authStore.save(PB_ADMIN_TOKEN, null);

const depsOnAllAuthUsers = '@request.auth.id != ""';

// ─── Pass 1: collections with no relations ───────────────────────────────────

const BASE_SCHEMAS = {
  dd_depots: [
    { name: 'name', type: 'text', required: true, max: 100, pattern: '' },
    { name: 'code', type: 'text', required: true, max: 20, pattern: '^[A-Za-z0-9_-]+$' },
    { name: 'capacity_litres', type: 'number', required: true, min: 0, onlyInt: false },
    { name: 'current_balance_litres', type: 'number', required: false, min: 0, onlyInt: false },
    { name: 'address', type: 'text', required: false, max: 500, pattern: '' },
    { name: 'latitude', type: 'number', required: false, onlyInt: false },
    { name: 'longitude', type: 'number', required: false, onlyInt: false },
    { name: 'is_active', type: 'bool', required: false },
    { name: 'created', type: 'autodate', required: true, onCreate: true, onUpdate: false },
    { name: 'updated', type: 'autodate', required: true, onCreate: true, onUpdate: true },
  ],
  dd_trucks: [
    { name: 'registration', type: 'text', required: true, max: 30, pattern: '' },
    { name: 'description', type: 'text', required: false, max: 200, pattern: '' },
    { name: 'capacity_litres', type: 'number', required: true, min: 0, onlyInt: false },
    { name: 'current_balance_litres', type: 'number', required: false, min: 0, onlyInt: false },
    { name: 'current_km', type: 'number', required: false, min: 0, onlyInt: true },
    { name: 'is_active', type: 'bool', required: false },
    { name: 'created', type: 'autodate', required: true, onCreate: true, onUpdate: false },
    { name: 'updated', type: 'autodate', required: true, onCreate: true, onUpdate: true },
  ],
  dd_clients: [
    { name: 'name', type: 'text', required: true, max: 200, pattern: '' },
    { name: 'code', type: 'text', required: false, max: 30, pattern: '^[A-Za-z0-9_-]*$' },
    { name: 'contact_person', type: 'text', required: false, max: 200, pattern: '' },
    { name: 'phone', type: 'text', required: false, max: 50, pattern: '' },
    { name: 'email', type: 'email', required: false, exceptDomains: null, onlyDomains: null },
    { name: 'address', type: 'text', required: false, max: 500, pattern: '' },
    { name: 'default_lat', type: 'number', required: false, onlyInt: false },
    { name: 'default_lng', type: 'number', required: false, onlyInt: false },
    { name: 'is_active', type: 'bool', required: false },
    { name: 'created', type: 'autodate', required: true, onCreate: true, onUpdate: false },
    { name: 'updated', type: 'autodate', required: true, onCreate: true, onUpdate: true },
  ],
  dd_users: [
    { name: 'name', type: 'text', required: true, max: 200, pattern: '' },
    { name: 'role', type: 'select', required: true, maxSelect: 1, values: ['driver', 'pump_attendant', 'admin', 'owner'] },
    { name: 'phone', type: 'text', required: false, max: 50, pattern: '' },
    { name: 'is_active', type: 'bool', required: false },
    { name: 'created', type: 'autodate', required: true, onCreate: true, onUpdate: false },
    { name: 'updated', type: 'autodate', required: true, onCreate: true, onUpdate: true },
  ],
  dd_delivery_notes: [
    { name: 'note_number', type: 'text', required: true, max: 30, pattern: '^[A-Z0-9]+$' },
    { name: 'delivery_date', type: 'date', required: true, min: '', max: '' },
    { name: 'opening_meter_reading', type: 'number', required: true, min: 0, onlyInt: false },
    { name: 'closing_meter_reading', type: 'number', required: true, min: 0, onlyInt: false },
    { name: 'qty_litres', type: 'number', required: true, min: 0, onlyInt: false },
    { name: 'meter_photo', type: 'file', required: false, maxSelect: 1, maxSize: 10_485_760, mimeTypes: ['image/jpeg', 'image/png', 'image/webp'] },
    { name: 'client_signature', type: 'file', required: false, maxSelect: 1, maxSize: 5_242_880, mimeTypes: ['image/png', 'image/jpeg', 'image/webp'] },
    { name: 'notes', type: 'text', required: false, max: 1000, pattern: '' },
    { name: 'is_internal_transfer', type: 'bool', required: false },
    { name: 'meter_continuity_status', type: 'select', required: true, maxSelect: 1, values: ['green', 'yellow', 'red'] },
    { name: 'status', type: 'select', required: true, maxSelect: 1, values: ['draft', 'completed', 'voided'] },
    { name: 'captured_lat', type: 'number', required: false, onlyInt: false },
    { name: 'captured_lng', type: 'number', required: false, onlyInt: false },
    { name: 'captured_at', type: 'date', required: true, min: '', max: '' },
    { name: 'created', type: 'autodate', required: true, onCreate: true, onUpdate: false },
    { name: 'updated', type: 'autodate', required: true, onCreate: true, onUpdate: true },
  ],
  dd_fuel_purchases: [
    { name: 'purchase_number', type: 'text', required: true, max: 30, pattern: '^[A-Z0-9]+$' },
    { name: 'purchase_date', type: 'date', required: true, min: '', max: '' },
    { name: 'supplier_name', type: 'text', required: true, max: 200, pattern: '' },
    { name: 'quantity_litres', type: 'number', required: true, min: 0, onlyInt: false },
    { name: 'price_per_litre', type: 'number', required: false, min: 0, onlyInt: false },
    { name: 'total_amount', type: 'number', required: false, min: 0, onlyInt: false },
    { name: 'reference', type: 'text', required: false, max: 200, pattern: '' },
    { name: 'notes', type: 'text', required: false, max: 1000, pattern: '' },
    { name: 'receipt', type: 'file', required: false, maxSelect: 1, maxSize: 10_485_760, mimeTypes: ['image/jpeg', 'image/png', 'image/webp', 'application/pdf'] },
    { name: 'created', type: 'autodate', required: true, onCreate: true, onUpdate: false },
    { name: 'updated', type: 'autodate', required: true, onCreate: true, onUpdate: true },
  ],
};

// ─── Pass 2: relations to add after base schemas exist ───────────────────────
// (uses the live collection IDs)

const RELATION_PATCHES = {
  dd_trucks: [
    { name: 'home_depot', type: 'relation', required: false, collectionId: 'PENDING_DD_DEPOTS', maxSelect: 1, cascadeDelete: false },
  ],
  dd_users: [
    { name: 'assigned_depot', type: 'relation', required: false, collectionId: 'PENDING_DD_DEPOTS', maxSelect: 1, cascadeDelete: false },
    { name: 'assigned_truck', type: 'relation', required: false, collectionId: 'PENDING_DD_TRUCKS', maxSelect: 1, cascadeDelete: false },
  ],
  dd_delivery_notes: [
    { name: 'truck', type: 'relation', required: true, collectionId: 'PENDING_DD_TRUCKS', maxSelect: 1, cascadeDelete: false },
    { name: 'depot', type: 'relation', required: true, collectionId: 'PENDING_DD_DEPOTS', maxSelect: 1, cascadeDelete: false },
    { name: 'client', type: 'relation', required: false, collectionId: 'PENDING_DD_CLIENTS', maxSelect: 1, cascadeDelete: false },
    { name: 'driver', type: 'relation', required: true, collectionId: 'PENDING_DD_USERS', maxSelect: 1, cascadeDelete: false },
  ],
  dd_fuel_purchases: [
    { name: 'depot', type: 'relation', required: true, collectionId: 'PENDING_DD_DEPOTS', maxSelect: 1, cascadeDelete: false },
    { name: 'purchased_by', type: 'relation', required: false, collectionId: 'PENDING_DD_USERS', maxSelect: 1, cascadeDelete: false },
  ],
};

// ─── Pass 1: create / update base schemas ────────────────────────────────────

const existing = await pb.collections.getFullList();
const byName = new Map(existing.map((c) => [c.name, c]));
const idByName = new Map(existing.map((c) => [c.name, c.id]));

for (const [name, fields] of Object.entries(BASE_SCHEMAS)) {
  const ex = byName.get(name);
  if (ex) {
    console.log(`✓ ${name} already exists (${ex.id}) — ensuring base fields present`);
    const existingByName = new Map((ex.fields || []).map((f) => [f.name, f]));
    const nextFields = fields.map((want) => {
      const have = existingByName.get(want.name);
      if (have) {
        const merged = { ...have };
        for (const k of Object.keys(want)) {
          if (k === 'id') continue;
          const w = want[k];
          const h = merged[k];
          if (Array.isArray(w) && (!Array.isArray(h) || h.length === 0)) merged[k] = w;
          else if (typeof w === 'number' && w > 0 && (h === null || h === 0)) merged[k] = w;
          else if (typeof w === 'boolean' && h === undefined) merged[k] = w;
        }
        return merged;
      }
      return want;
    });
    for (const want of fields) {
      if (!existingByName.has(want.name)) nextFields.push(want);
    }
    await pb.collections.update(ex.id, { fields: nextFields, ...allRules() });
    idByName.set(name, ex.id);
    continue;
  }
  console.log(`+ Creating ${name}`);
  const created = await pb.collections.create({
    name,
    type: name === 'dd_users' ? 'auth' : 'base',
    fields,
    ...allRules(),
  });
  idByName.set(name, created.id);
}

function allRules() {
  return {
    listRule: depsOnAllAuthUsers,
    viewRule: depsOnAllAuthUsers,
    createRule: depsOnAllAuthUsers,
    updateRule: depsOnAllAuthUsers,
    deleteRule: depsOnAllAuthUsers,
  };
}

// ─── Pass 2: add relation fields using live IDs ──────────────────────────────

// Re-fetch in case pass 1 created new collections
const pass2Existing = await pb.collections.getFullList();
for (const [name, patches] of Object.entries(RELATION_PATCHES)) {
  const ex = pass2Existing.find((c) => c.name === name);
  if (!ex) continue; // shouldn't happen — pass 1 created it
  const existingByName = new Map((ex.fields || []).map((f) => [f.name, f]));
  let dirty = false;
  const nextFields = (ex.fields || []).slice();
  for (const want of patches) {
    if (existingByName.has(want.name)) continue; // already present
    const resolved = { ...want };
    // Resolve the placeholder to the real collectionId
    const placeholder = resolved.collectionId;
    if (typeof placeholder === 'string' && placeholder.startsWith('PENDING_')) {
      const targetName = placeholder.replace(/^PENDING_/, '').toLowerCase();
      const realId = idByName.get(targetName);
      if (!realId) {
        console.warn(`  ! could not resolve ${placeholder} for ${name}.${want.name}`);
        continue;
      }
      resolved.collectionId = realId;
    }
    nextFields.push(resolved);
    dirty = true;
    console.log(`  → adding ${name}.${want.name} → ${resolved.collectionId}`);
  }
  if (dirty) await pb.collections.update(ex.id, { fields: nextFields });
}

// ─── Done ────────────────────────────────────────────────────────────────────

console.log('\n✅ All Diesel Depot collections ready.\n');
const final = await pb.collections.getFullList({ filter: 'name ~ "dd_"' });
for (const c of final) {
  const fieldCount = (c.fields || []).length;
  console.log(`  ${c.id}  ${c.name}  (${c.type}, ${fieldCount} fields)`);
}