#!/bin/csh -f

## this script creates a database for testing dvopsps

if ($#argv < 3) goto usage;

if ("$1" == "user") then
  if ($#argv != 4) goto usage;
  set dbhost = $2
  set dbuser = $3
  set dbpass = $4

  mysql -h $dbhost -u root -p <<EOF
   grant all on *.* to $dbuser@"$dbhost" identified by '$dbpass';
EOF
    exit 0;
endif

if ("$1" == "create") then
  if ($#argv < 4) goto usage;
  set dbhost = $2
  set dbname = $3
  set dbuser = $4
  if ($#argv == 5) then
    set dbpass = "-p$5"
  else 
    set dbpass = "-p"
  endif

  # create a database with completely minimal tables needed to test
  mysql -h $dbhost -u $dbuser $dbpass <<EOF
   CREATE DATABASE $dbname;
   USE $dbname;
  CREATE TABLE ippToPspsFake (
    my_id INT
  );
  CREATE TABLE skycell (
   radeg REAL,
   decdeg REAL,
   skycell_id CHAR(64)
  );
  CREATE TABLE rawExp (
   exp_id INT,
   exp_name CHAR(64),
   exp_time REAL,
   bg REAL,
   bg_stdev REAL,
   ra FLOAT,
   decl FLOAT,
   dateobs DATETIME 
  );
  CREATE TABLE chipRun (
    exp_id INT,
    chip_id INT
  );
  CREATE TABLE camRun (
    chip_id INT,
    cam_id INT,
    dist_group CHAR(64),
    software_ver CHAR(16)
  );
  CREATE TABLE camProcessedExp (
    path_base CHAR(255),
    cam_id INT,
    fault INT
  );
  CREATE TABLE fakeRun (
    fake_id INT,
    cam_id INT
  );
  CREATE TABLE warpRun (
   warp_id INT,
   fake_id INT
  );
  CREATE TABLE warpSkyCellMap (
   warp_id INT,
   skycell_id CHAR(64),
   class_id CHAR(64)
  );
  CREATE TABLE stackRun (
   stack_id INT,
   skycell_id CHAR(64),
   filter CHAR(64),
   software_ver CHAR(16)
  );
  CREATE TABLE stackInputSkyfile (
   stack_id INT,
   warp_id INT
  );
  CREATE TABLE stackSumSkyfile (
   stack_id INT,
   mjd_obs DOUBLE
  );
  CREATE TABLE skycalRun (
   sky_id INT,
   skycal_id INT,
   stack_id INT
  );
  CREATE TABLE skycalResult (
   path_base CHAR(255),
   skycal_id INT
  );
  CREATE TABLE addRun (
    add_id INT,
    stage CHAR(64),
    state CHAR(64),
    stage_id INT,
    minidvodb_name CHAR(64)
  );
  CREATE TABLE addProcessedExp (
    add_id INT,
    fault INT
  );
  CREATE TABLE minidvodbRun (
   minidvodb_name CHAR(64),
   minidvodb_id INT,
   state CHAR(64)
  );
  CREATE TABLE minidvodbProcessed (
   minidvodb_id INT,
   fault INT
  );
  CREATE TABLE mergedvodbRun (
   minidvodb_id INT,
   merge_id INT,
   state CHAR(64),
   mergedvodb CHAR(64)
  );
  CREATE TABLE mergedvodbProcessed (
   merge_id INT,
   fault INT
  );
EOF
    exit 0;
endif

if ("$1" == "delete") then
  if ($#argv < 4) goto usage;
  set dbhost = $2
  set dbname = $3
  set dbuser = $4
  if ($#argv == 5) then
    set dbpass = "-p$5"
  else 
    set dbpass = "-p"
  endif

  mysql -h $dbhost -u $dbuser $dbpass <<EOF
   USE $dbname;
   describe ippToPspsFake;
EOF
  if ($status) then
    echo "database does not contain ippToPspsFake, not deleting"
    exit 1;
  endif

  set ntable = `echo "USE $dbname; show tables;" | mysql -h $dbhost -u $dbuser $dbpass | wc -l`
  echo ntable: $ntable
  if ($ntable != 21) then
    echo "expected 21 lines (20 tables), but got $ntable, not deleting"
    exit 2
  endif

  echo "database is valid, deleting"
  mysql -h $dbhost -u $dbuser $dbpass <<EOF
   drop database $dbname;
EOF

   exit 0;
endif

usage:
  echo "USAGE: mkgpc1.sh (options)"
  echo ""
  echo "  mkgpc1.sh create (dbhost) (dbname) (dbuser) [dbpass]"
  echo "      create a new test db"
  echo ""
  echo "  mkgpc1.sh delete (dbhost) (dbname) (dbuser) [dbpass]"
  echo "      delete an existing test db (enter pass 3x)"
  echo ""
  echo "  mkgpc1.sh user (dbhost) (dbuser) (password)"
  echo "      create a new user and password for the test db"
  echo ""
  exit 2
