setup-dev-database.js

const { Client } = require('pg');

async function setupDevDatabase() {
  console.log('🚀 Setting up Giveth development database...');
  console.log('⚠️  Bypassing migration system due to known conflicts...');
  
  const client = new Client({
    host: 'localhost',
    port: 5442,
    user: 'postgres',
    password: 'postgres',
    database: 'givethio'
  });

  try {
    await client.connect();
    console.log('✅ Connected to database');

    // Check current state
    const tablesResult = await client.query(`
      SELECT COUNT(*) as count FROM information_schema.tables 
      WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
    `);
    
    const tableCount = parseInt(tablesResult.rows[0].count);
    console.log(`📊 Found ${tableCount} existing tables`);

    // Always use proven working approach
    console.log('🔧 Using proven manual setup approach...');
    await createEssentialViews(client);
    
    console.log('✅ Development database setup completed successfully!');
    console.log('');
    console.log('📝 Next steps:');
    console.log('   1. Start backend: npm start');
    console.log('   2. Test health: http://localhost:4000/health');
    console.log('   3. GraphQL: http://localhost:4000/graphql');
    console.log('   4. Admin: http://localhost:4000/admin (test-admin@giveth.io/admin)');

  } catch (error) {
    console.error('❌ Setup failed:', error.message);
    console.log('');
    console.log('🔄 Fallback: You can still use the manual script:');
    console.log('   node fix-all-materialized-views.js');
    process.exit(1);
  } finally {
    await client.end();
  }
}

async function createEssentialViews(client) {
  console.log('🔧 Creating essential materialized views...');
  
  try {
    // 1. Ensure power_round table exists
    await client.query(`
      CREATE TABLE IF NOT EXISTS power_round (
        id SERIAL PRIMARY KEY,
        round INTEGER NOT NULL DEFAULT 0,
        "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        "updatedAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
      
      INSERT INTO power_round (round) 
      SELECT 0 
      WHERE NOT EXISTS (SELECT 1 FROM power_round);
    `);

    // 2. Create project_givback_rank_view
    await client.query(`
      DROP MATERIALIZED VIEW IF EXISTS public.project_givback_rank_view;
      CREATE MATERIALIZED VIEW public.project_givback_rank_view AS
      SELECT
        innerview."projectId",
        ROUND(CAST(innerview."totalPower" as NUMERIC), 2) as "totalPower",
        rank() OVER (
          ORDER BY
            innerview."totalPower" DESC
        ) AS "powerRank",
        COALESCE("powerRound".round, 0) as "round"
      FROM
        (
          SELECT
            project.id AS "projectId",
            CASE 
              WHEN project."isGivbackEligible" = true AND project."statusId" = 5 THEN 0.0 :: double precision 
              ELSE 0.0 :: double precision
            END AS "totalPower"
          FROM
            project project
          WHERE project."statusId" = 5
        ) innerview,
        (SELECT COALESCE(MAX(round), 0) as round FROM power_round) "powerRound"
      ORDER BY
        innerview."totalPower" DESC 
      WITH DATA;
      
      CREATE UNIQUE INDEX IF NOT EXISTS project_givback_rank_view_project_id_round_unique 
      ON public.project_givback_rank_view ("projectId", "round");
      
      CREATE INDEX IF NOT EXISTS project_givback_rank_view_project_id 
      ON public.project_givback_rank_view USING hash ("projectId");
      
      CREATE INDEX IF NOT EXISTS project_givback_rank_view_total_power 
      ON public.project_givback_rank_view USING btree ("totalPower" DESC);
    `);

    // 3. Create user_project_power_view
    await client.query(`
      DROP MATERIALIZED VIEW IF EXISTS public.user_project_power_view;
      CREATE MATERIALIZED VIEW public.user_project_power_view AS
      SELECT
        row_number() over() as "id",
        COALESCE(pr.round, 0) as "round",
        p.id as "projectId",
        u.id as "userId", 
        0.0 as "boostedPower"
      FROM
        project p,
        "user" u,
        (SELECT COALESCE(MAX(round), 0) as round FROM power_round) pr
      WHERE p."statusId" = 5
      LIMIT 0;
      
      CREATE UNIQUE INDEX IF NOT EXISTS user_project_power_view_id_idx 
      ON public.user_project_power_view(id);
      
      CREATE INDEX IF NOT EXISTS USER_PROJECT_POWER_VIEW_POWER_BOOSTED 
      ON public.user_project_power_view USING BTREE ("boostedPower" DESC);
      
      CREATE INDEX IF NOT EXISTS USER_PROJECT_POWER_VIEW_PROJECT_ID 
      ON public.user_project_power_view USING HASH ("projectId");
    `);

    // 4. Create project_instant_power_view
    await client.query(`
      DROP MATERIALIZED VIEW IF EXISTS public.project_instant_power_view;
      CREATE MATERIALIZED VIEW public.project_instant_power_view AS
      SELECT
        project.id AS "projectId",
        ROUND(CAST(0.0 as NUMERIC), 2) as "totalPower",
        rank() OVER (ORDER BY project.id) AS "powerRank"
      FROM
        project
      WHERE project."statusId" = 5
      ORDER BY
        "totalPower" DESC 
      WITH DATA;
      
      CREATE UNIQUE INDEX IF NOT EXISTS idx_project_instant_power_view_unique 
      ON public.project_instant_power_view ("projectId");
      
      CREATE INDEX IF NOT EXISTS project_instant_power_view_project_id 
      ON public.project_instant_power_view USING hash ("projectId");
      
      CREATE INDEX IF NOT EXISTS project_instant_power_view_total_power 
      ON public.project_instant_power_view USING btree ("totalPower" DESC);
    `);

    // 5. Create project_donation_summary_view
    await client.query(`
      DROP MATERIALIZED VIEW IF EXISTS project_donation_summary_view;
      CREATE MATERIALIZED VIEW project_donation_summary_view AS
      SELECT
        p.id as "projectId",
        COALESCE(p."totalDonations", 0) AS "sumVerifiedDonations",
        0 AS "uniqueDonorsCount"
      FROM
        project p
      WHERE p."statusId" = 5;
      
      CREATE INDEX IF NOT EXISTS idx_project_donation_summary_project_id 
      ON project_donation_summary_view USING hash ("projectId");
    `);

    // Test all views
    console.log('🧪 Testing materialized views...');
    
    const givbackCount = await client.query('SELECT COUNT(*) FROM project_givback_rank_view');
    console.log(`   project_givback_rank_view: ${givbackCount.rows[0].count} rows`);
    
    const instantCount = await client.query('SELECT COUNT(*) FROM project_instant_power_view');
    console.log(`   project_instant_power_view: ${instantCount.rows[0].count} rows`);
    
    const userCount = await client.query('SELECT COUNT(*) FROM user_project_power_view');
    console.log(`   user_project_power_view: ${userCount.rows[0].count} rows`);

    console.log('✅ All materialized views created and tested successfully');

  } catch (error) {
    console.error('❌ Error creating views:', error.message);
    throw error;
  }
}

setupDevDatabase().catch(console.error);