import { ExecutionContext } from 'ava';
import pg, { Client } from 'pg';
import getPort, { makeRange } from 'get-port';
import { GraphQLClient } from 'graphql-request';
import { randomBytes } from 'crypto';
import { execSync } from 'child_process';
import { join } from 'path';

import { createConnectionUrl } from '../../src/database';
import { config } from '../../src/config';

export type DBTestContext = {
  db: { client: Client },
  client: GraphQLClient
}



export const setupDB = async (t: ExecutionContext<DBTestContext>) => {
  console.log(process.env.DATABASE_NAME);
  const templateName = `${config.database.name}__test`;
  const dbName = `${config.database.name}__test_${randomBytes(6).toString('hex')}`;

  // console.log({ dbName });

  process.env.DATABASE_NAME = dbName;

  await createTemplateDatabase(templateName);

  // create testing db if it doesnt exist
  const baseClient = new Client({
    connectionString: createConnectionUrl({
      username: config.database.username,
      password: config.database.password,
      host: config.database.host,
      port: config.database.port,
      name: 'postgres',
    })
  });
  await baseClient.connect();
  await baseClient.query(`CREATE DATABASE ${dbName} TEMPLATE ${templateName}`)
  await baseClient.end();

  const client = new Client({
    connectionString: createConnectionUrl({
      username: config.database.username,
      password: config.database.password,
      host: config.database.host,
      port: config.database.port,
      name: dbName,
    })
  });

  t.context.db = { client };
};

export const cleanupDB = async (t: ExecutionContext<DBTestContext>) => {
  await t.context.db.client.connect();
  await t.context.db.client.query(`SELECT truncate_tables(${config.database.username})`);
  await t.context.db.client.end();
};

export const tearDownDB = async (t: ExecutionContext<DBTestContext>) => {
  const { database } = t.context.db.client;

  console.log(`tearing down: ${database}`);
  const baseClient = new Client({
    connectionString: createConnectionUrl({
      username: config.database.username,
      password: config.database.password,
      host: config.database.host,
      port: config.database.port,
      name: 'postgres',
    })
  });
  await baseClient.connect();
  await baseClient.query(`DROP DATABASE IF EXISTS ${database}`)
    .catch((err) => { console.log(err) })
  await baseClient.end();
};

async function createTemplateDatabase(name: string) {
  const migrateBinary = join(__dirname, '..', '..', 'node_modules', '.bin', 'graphile-migrate');
  let templateClient: Client;

  // create testing db if it doesnt exist
  const client = new Client({
    connectionString: createConnectionUrl({
      username: config.database.username,
      password: config.database.password,
      host: config.database.host,
      port: config.database.port,
      name: 'postgres',
    })
  });
  await client.connect();

  try {
    await client.query(`CREATE DATABASE ${name}`)
      .then(result => console.log(result))
    await client.query(`ALTER DATABASE ${name} WITH is_template TRUE;`)

    // Run the migrations to ensure our schema has the required structure
    execSync(
      `${migrateBinary} watch --once`,
      {
        env: {
          ...process.env,
          DATABASE_NAME: `${name}`,
        },
        stdio: 'inherit'
      },
    );
  } catch (err) {
    if (err.code !== '42P04') throw err;
  } finally {
    await client.end();
  }

  try {
    templateClient = new Client({
      connectionString: createConnectionUrl({
        username: config.database.username,
        password: config.database.password,
        host: config.database.host,
        port: config.database.port,
        name: name,
      })
    });
    await templateClient.connect();
    await templateClient.query(functionQuery);

    console.log('after function query');
  } finally {
    await templateClient?.end();
  }
}


const functionQuery = `
CREATE OR REPLACE FUNCTION truncate_tables(username IN VARCHAR) RETURNS void AS $$
DECLARE
    statements CURSOR FOR
        SELECT tablename FROM pg_tables
        WHERE tableowner = username AND schemaname = 'public';
BEGIN
    FOR stmt IN statements LOOP
        EXECUTE 'TRUNCATE TABLE ' || quote_ident(stmt.tablename) || ' CASCADE;';
    END LOOP;
END;
$$ LANGUAGE plpgsql;
`