import path from 'path';
import { Client } from 'pg';
import { execSync } from 'child_process';
import { createConnectionUrl } from '../../database';
import { config } from '../../config';

const TEMPLATE_NAME = 'test_template';
const rootConnection = {
  username: config.database.username,
  host: config.database.host,
  password: config.database.password,
  port: config.database.port,
  name: 'postgres',
};

(async () => {
  // remove existing template
  await tearDownTemplateDb();
  // rebuild new template
  await createTemplateDb();
})();

async function createTemplateDb() {
  console.log(`create template db: ${TEMPLATE_NAME}`);
  const migrateBinary = path.join(process.cwd(), 'node_modules', '.bin', 'graphile-migrate');
  let templateClient;

  process.env.DATABASE_URL = createConnectionUrl({ ...rootConnection, name: TEMPLATE_NAME });

  // create testing db if it doesnt exist
  const rootClient = new Client(rootConnection);
  await rootClient.connect();

  try {
    // const result = await rootClient.query(`SELECT EXISTS ( SELECT datname FROM pg_catalog.pg_database WHERE datname = 'farts');`)
    // console.log(result);
    await rootClient.query(`CREATE DATABASE ${TEMPLATE_NAME}`)
    await rootClient.query(`ALTER DATABASE ${TEMPLATE_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: `${TEMPLATE_NAME}`,
        },
        stdio: 'inherit'
      },
    );
  } catch (err) {
    // ignore 'db already exists' error
    if (err.code !== '42P04') throw err;
  } finally {
    await rootClient.end();
  }

  templateClient = new Client({
    ...rootConnection,
    database: TEMPLATE_NAME,
  });
  await templateClient.connect();
  await templateClient.query(functionQuery);
  await templateClient.end();

  console.log('template created');
  return templateClient;
}

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;
`
async function tearDownTemplateDb() {
  console.log('template teardown');
  const dbName = 'test_template';

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

  try {
    await rootClient.query(`ALTER DATABASE ${dbName} WITH is_template FALSE;`);
    await rootClient.query(`DROP DATABASE ${dbName};`);
  } catch (err) {
    // ignore 'db already exists' error
    if (err.code !== '3D000') throw err;
  } finally {
    await rootClient.end();
  }
}