rank/scripts/setup-appwrite.mjs

import 'dotenv/config';
import { Client, TablesDB, ID, Query, TablesDBIndexType } from 'node-appwrite';

const endpoint = process.env.APPWRITE_ENDPOINT || process.env.APPWRITE_SELF_HOSTED_URL || process.env.APPWRITE_LOCAL_ENDPOINT;
const projectId = process.env.APPWRITE_PROJECT_ID || process.env.APPWRITE_SELF_HOSTED_PROJECT_ID || process.env.APPWRITE_LOCAL_PROJECT_ID;
const apiKey = process.env.APPWRITE_API_KEY || process.env.APPWRITE_SELF_HOSTED_API_KEY || process.env.APPWRITE_LOCAL_API_KEY;
const databaseId = process.env.RANK_APPWRITE_DATABASE_ID || process.env.APPWRITE_DATABASE_ID || 'priority_rank';
const ideasTableId = process.env.RANK_IDEAS_TABLE_ID || 'ideas';
const milestonesTableId = process.env.RANK_MILESTONES_TABLE_ID || 'milestones';
const activityTableId = process.env.RANK_ACTIVITY_TABLE_ID || 'activity';

if (!endpoint || !projectId || !apiKey) throw new Error('Missing Appwrite endpoint/project/key env');

const client = new Client().setEndpoint(endpoint).setProject(projectId).setKey(apiKey);
const tables = new TablesDB(client);

async function sleep(ms) { return new Promise(resolve => setTimeout(resolve, ms)); }
function isNotFound(error) { return error?.code === 404 || /not found/i.test(error?.message || ''); }
function isConflict(error) { return error?.code === 409 || /already exists|conflict/i.test(error?.message || ''); }

async function ensureDatabase() {
  try {
    await tables.get({ databaseId });
    console.log(`database exists: ${databaseId}`);
  } catch (error) {
    if (!isNotFound(error)) throw error;
    await tables.create({ databaseId, name: 'Priority Rank', enabled: true });
    console.log(`database created: ${databaseId}`);
  }
}

async function ensureTable(tableId, name) {
  try {
    await tables.getTable({ databaseId, tableId });
    console.log(`table exists: ${tableId}`);
  } catch (error) {
    if (!isNotFound(error)) throw error;
    await tables.createTable({ databaseId, tableId, name, rowSecurity: false, enabled: true });
    console.log(`table created: ${tableId}`);
    await waitForTable(tableId);
  }
}

async function waitForTable(tableId) {
  for (let i = 0; i < 30; i++) {
    const table = await tables.getTable({ databaseId, tableId });
    if (!table.status || table.status === 'available' || table.enabled) return table;
    await sleep(1000);
  }
}

async function waitForColumn(tableId, key) {
  for (let i = 0; i < 45; i++) {
    const column = await tables.getColumn({ databaseId, tableId, key });
    if (!column.status || column.status === 'available') return column;
    if (column.status === 'failed') throw new Error(`Column ${tableId}.${key} failed`);
    await sleep(1000);
  }
}

async function ensureColumn(tableId, key, create) {
  try {
    await tables.getColumn({ databaseId, tableId, key });
    return console.log(`column exists: ${tableId}.${key}`);
  } catch (error) {
    if (!isNotFound(error)) throw error;
  }
  try {
    await create();
    console.log(`column created: ${tableId}.${key}`);
  } catch (error) {
    if (!isConflict(error)) throw error;
  }
  await waitForColumn(tableId, key);
}

async function ensureIndex(tableId, key, type, columns, orders = undefined) {
  try {
    await tables.getIndex({ databaseId, tableId, key });
    return console.log(`index exists: ${tableId}.${key}`);
  } catch (error) {
    if (!isNotFound(error)) throw error;
  }
  try {
    await tables.createIndex({ databaseId, tableId, key, type, columns, orders });
    console.log(`index created: ${tableId}.${key}`);
  } catch (error) {
    if (!isConflict(error)) throw error;
  }
}

const varchar = (tableId, key, size, required = false, xdefault = undefined) => ensureColumn(tableId, key, () => tables.createVarcharColumn({ databaseId, tableId, key, size, required, xdefault }));
const text = (tableId, key, required = false, xdefault = undefined) => ensureColumn(tableId, key, () => tables.createTextColumn({ databaseId, tableId, key, required, xdefault }));
const integer = (tableId, key, required = false, min = undefined, max = undefined, xdefault = undefined) => ensureColumn(tableId, key, () => tables.createIntegerColumn({ databaseId, tableId, key, required, min, max, xdefault }));
const floatCol = (tableId, key, required = false, min = undefined, max = undefined, xdefault = undefined) => ensureColumn(tableId, key, () => tables.createFloatColumn({ databaseId, tableId, key, required, min, max, xdefault }));
const bool = (tableId, key, required = false, xdefault = undefined) => ensureColumn(tableId, key, () => tables.createBooleanColumn({ databaseId, tableId, key, required, xdefault }));

async function seedMilestones() {
  const existing = await tables.listRows({ databaseId, tableId: milestonesTableId, queries: [Query.limit(1)] });
  const rows = existing.rows || existing.documents || [];
  if (rows.length) return console.log('milestones already seeded');
  const seed = [
    { name: 'Inbox', description: 'Raw captures waiting for judgement.', horizon: 'Unsorted', color: '#8cf7ff', position: 0, active: true },
    { name: 'Now', description: 'Highest leverage work. Do not let this lane become a landfill.', horizon: 'This sprint', color: '#f8ff73', position: 10, active: true },
    { name: 'Next', description: 'Strong ideas after the current push.', horizon: 'Soon', color: '#a78bfa', position: 20, active: true },
    { name: 'Later', description: 'Useful but not urgent.', horizon: 'Backlog', color: '#6ee7b7', position: 30, active: true },
  ];
  for (const row of seed) await tables.createRow({ databaseId, tableId: milestonesTableId, rowId: row.name.toLowerCase(), data: row });
  console.log('seeded milestones');
}

await ensureDatabase();
await ensureTable(ideasTableId, 'Ideas');
await ensureTable(milestonesTableId, 'Milestones');
await ensureTable(activityTableId, 'Activity');

await varchar(ideasTableId, 'title', 180, true);
await text(ideasTableId, 'description', false);
await varchar(ideasTableId, 'source', 40, false, 'human');
await varchar(ideasTableId, 'sourceName', 80, false);
await varchar(ideasTableId, 'status', 40, false, 'inbox');
await varchar(ideasTableId, 'milestoneId', 64, false, 'inbox');
await integer(ideasTableId, 'impact', false, 0, 10, 5);
await integer(ideasTableId, 'effort', false, 1, 10, 5);
await integer(ideasTableId, 'confidence', false, 0, 10, 6);
await integer(ideasTableId, 'urgency', false, 0, 10, 5);
await floatCol(ideasTableId, 'score', false, 0, 999, 0);
await integer(ideasTableId, 'rank', false, -100000, 100000, 0);
await varchar(ideasTableId, 'labels', 768, false, '[]');
await text(ideasTableId, 'notes', false);
await bool(ideasTableId, 'archived', false, false);

await varchar(milestonesTableId, 'name', 80, true);
await text(milestonesTableId, 'description', false);
await varchar(milestonesTableId, 'horizon', 80, false);
await varchar(milestonesTableId, 'color', 24, false, '#8cf7ff');
await integer(milestonesTableId, 'position', false, -10000, 10000, 0);
await bool(milestonesTableId, 'active', false, true);

await varchar(activityTableId, 'type', 80, true);
await varchar(activityTableId, 'message', 300, true);
await varchar(activityTableId, 'ideaId', 64, false);
await varchar(activityTableId, 'meta', 800, false);

await ensureIndex(ideasTableId, 'score_rank', TablesDBIndexType.Key, ['score', 'rank'], ['DESC', 'ASC']).catch(e => console.warn('index score_rank skipped:', e.message));
await ensureIndex(ideasTableId, 'milestone_rank', TablesDBIndexType.Key, ['milestoneId', 'rank'], ['ASC', 'ASC']).catch(e => console.warn('index milestone_rank skipped:', e.message));
await ensureIndex(milestonesTableId, 'position', TablesDBIndexType.Key, ['position'], ['ASC']).catch(e => console.warn('index milestone position skipped:', e.message));

await seedMilestones();
console.log(JSON.stringify({ ok: true, endpoint, projectId, databaseId, ideasTableId, milestonesTableId, activityTableId }, null, 2));