create_collections.cjs

const sdk = require('node-appwrite');
const fs = require('fs');
const path = require('path');
require('dotenv').config();

const apiKey = process.env.SECRET_API_KEY;
if(!apiKey) {
    console.error("ERROR: SECRET_API_KEY not found in environment.");
    process.exit(1);
}

const projectId = process.env.VITE_APPWRITE_PROJECT_ID || '69ed94eb0033f1ed70e4';
const endpoint = process.env.VITE_APPWRITE_ENDPOINT || 'https://sgp.cloud.appwrite.io/v1';

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

const databases = new sdk.Databases(client);
const dbId = process.env.VITE_APPWRITE_DATABASE_ID || "69ed971800381ac08e28"; 
const delay = ms => new Promise(res => setTimeout(res, ms));

async function setupAppwrite() {
    try {
        console.log(`Starting schema setup for Database: ${dbId}`);
        const perms = [
            sdk.Permission.read(sdk.Role.any()),
            sdk.Permission.create(sdk.Role.any()),
            sdk.Permission.update(sdk.Role.any()),
            sdk.Permission.delete(sdk.Role.any())
        ];

        const safeCreateAttribute = async (colId, key, type, ...args) => {
            try {
                console.log(`  - Creating attribute: ${key}`);
                const method = `create${type}Attribute`;
                await databases[method](dbId, colId, key, ...args);
                await delay(500);
            } catch (e) {
                if (e.code === 409) {
                    console.log(`  - Attribute ${key} already exists. Skipping.`);
                } else {
                    console.error(`  - Error creating attribute ${key}:`, e.message);
                }
            }
        };

        const getOrCreateCollection = async (id, name) => {
            try {
                return await databases.getCollection(dbId, id);
            } catch (e) {
                if (e.code === 404) {
                    console.log(`Creating ${name} Collection...`);
                    const col = await databases.createCollection(dbId, id, name, perms);
                    await delay(1000);
                    return col;
                }
                throw e;
            }
        };

        // 1. Bins
        const bins = await getOrCreateCollection('bins', 'bins');
        await safeCreateAttribute(bins.$id, 'name', 'String', 255, true);
        await safeCreateAttribute(bins.$id, 'type', 'String', 50, true);
        await safeCreateAttribute(bins.$id, 'lat', 'Float', true);
        await safeCreateAttribute(bins.$id, 'lng', 'Float', true);
        await safeCreateAttribute(bins.$id, 'city', 'String', 255, false);
        await safeCreateAttribute(bins.$id, 'photo_url', 'String', 2000, false);
        await safeCreateAttribute(bins.$id, 'notes', 'String', 1000, false);
        await safeCreateAttribute(bins.$id, 'upvote_count', 'Integer', false, 0);
        await safeCreateAttribute(bins.$id, 'report_count', 'Integer', false, 0);
        await safeCreateAttribute(bins.$id, 'is_deleted', 'Boolean', false, false);

        // 2. Comments
        const comments = await getOrCreateCollection('comments', 'comments');
        await safeCreateAttribute(comments.$id, 'bin_id', 'String', 100, true);
        await safeCreateAttribute(comments.$id, 'text', 'String', 500, true);

        // 3. Reports
        const reports = await getOrCreateCollection('reports', 'reports');
        await safeCreateAttribute(reports.$id, 'bin_id', 'String', 100, true);
        await safeCreateAttribute(reports.$id, 'reason', 'String', 500, true);

        // 4. Feedback
        const feedback = await getOrCreateCollection('feedback', 'feedback');
        await safeCreateAttribute(feedback.$id, 'rating', 'Integer', true);
        await safeCreateAttribute(feedback.$id, 'message', 'String', 1000, false);
        await safeCreateAttribute(feedback.$id, 'categories', 'String', 500, false, undefined, true);
        await safeCreateAttribute(feedback.$id, 'device_type', 'String', 100, false);

        // 5. Page Views
        const views = await getOrCreateCollection('page_views', 'page_views');
        await safeCreateAttribute(views.$id, 'city', 'String', 200, false);
        await safeCreateAttribute(views.$id, 'country', 'String', 200, false);
        await safeCreateAttribute(views.$id, 'device_type', 'String', 100, false);
        await safeCreateAttribute(views.$id, 'screen_width', 'Integer', false);

        // 6. Requests
        const requests = await getOrCreateCollection('requests', 'requests');
        await safeCreateAttribute(requests.$id, 'lat', 'Float', true);
        await safeCreateAttribute(requests.$id, 'lng', 'Float', true);
        await safeCreateAttribute(requests.$id, 'city', 'String', 255, false);
        await safeCreateAttribute(requests.$id, 'address', 'String', 500, false);
        await safeCreateAttribute(requests.$id, 'description', 'String', 1000, true);
        await safeCreateAttribute(requests.$id, 'photo_url', 'String', 2000, false);
        await safeCreateAttribute(requests.$id, 'upvote_count', 'Integer', false, 0);
        await safeCreateAttribute(requests.$id, 'status', 'String', 50, true, 'requested');

        // 7. Municipal Actions
        const actions = await getOrCreateCollection('municipal_actions', 'municipal_actions');
        await safeCreateAttribute(actions.$id, 'request_id', 'String', 100, true);
        await safeCreateAttribute(actions.$id, 'designation', 'String', 255, true);
        await safeCreateAttribute(actions.$id, 'action_taken', 'String', 100, true);
        await safeCreateAttribute(actions.$id, 'proof_url', 'String', 2000, false);

        console.log("\nAll Collections and Attributes verified! Updating .env...");

        const currentEnv = fs.readFileSync(path.join(__dirname, '.env'), 'utf-8');
        let updatedEnv = currentEnv;
        
        const envMappings = {
            'VITE_APPWRITE_BINS_COLLECTION_ID': bins.$id,
            'VITE_APPWRITE_COMMENTS_COLLECTION_ID': comments.$id,
            'VITE_APPWRITE_REPORTS_COLLECTION_ID': reports.$id,
            'VITE_APPWRITE_FEEDBACK_COLLECTION_ID': feedback.$id,
            'VITE_APPWRITE_VIEWS_COLLECTION_ID': views.$id,
            'VITE_APPWRITE_REQUESTS_COLLECTION_ID': requests.$id,
            'VITE_APPWRITE_ACTIONS_COLLECTION_ID': actions.$id
        };

        for (const [key, value] of Object.entries(envMappings)) {
            const regex = new RegExp(`${key}=.*`, 'g');
            if (updatedEnv.match(regex)) {
                updatedEnv = updatedEnv.replace(regex, `${key}="${value}"`);
            } else {
                updatedEnv += `\n${key}="${value}"`;
            }
        }
        
        fs.writeFileSync(path.join(__dirname, '.env'), updatedEnv);
        console.log("SUCCESS! Database setup verified and .env updated! 🎉");

    } catch(err) {
        console.error("Critical Setup Error: ", err);
    }
}

setupAppwrite();