#!/bin/bash

# aarc_create_db
#
#   (C) Copyright 2002-2005 Fred Gleason <fredg@salemradiolabs.com>
#
#   This program is free software; you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as
#   published by the Free Software Foundation; either version 2 of
#   the License, or (at your option) any later version.
#
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.
#
#   You should have received a copy of the GNU General Public
#   License along with this program; if not, write to the Free Software
#   Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
#

#
# Aarc mySQL Application Account
#
AARC_USER=aarcuser@localhost
AARC_PASSWORD=hackme

# Nothing should need to be changed below here!
# ############################################################################

# ############################################################################
# Create a Aarc Database
# ############################################################################
function CreateDB {
#
# Create the database
#
mysql -u $USER -p$PASSWORD -e"create database if not exists Aarc"

#
# Set Security Privledges
#
mysql -u $USER -p$PASSWORD -e"USE Aarc;
    GRANT ALL ON * TO $AARC_USER IDENTIFIED BY \"$AARC_PASSWORD\""

#
# Create SHOWS Table
#
mysql -u $USER -p$PASSWORD -e"use Aarc;
create table SHOWS (
    SHOW_CODE char(3) not null primary key,
    SHOW_TITLE char(64),
    SHOW_SUBTITLE char(64),
    MAX_SHELF_LIFE int default 365,
    AUDIO_URL char(255),
    AUDIO_EXTENSION char(8),
    RESULT_PREPAGE char(255),
    RESULT_POSTPAGE char(255),
    SUN int,
    MON int,
    TUE int,
    WED int,
    THU int,
    FRI int,
    SAT int
)"

#
# Create SEGMENTS Table
#
mysql -u $USER -p$PASSWORD -e"use Aarc;
create table SEGMENTS (
    ID int auto_increment not null primary key,
    SHOW_CODE char(3),
    SEGMENT_DATE date,
    SEGMENT_NUMBER int,
    SEGMENT_TITLE char(64),
    SEGMENT_DESCRIPTION char(255),
    SEGMENT_SHELF_LIFE int default 365,
    index SHOW_CODE_IDX (SHOW_CODE,SEGMENT_DATE)
)"

mysql -u $USER -p$PASSWORD -e"USE Aarc;
    grant all on SHOWS to $AARC_USER identified by \"$AARC_PASSWORD\""
}
# ############################################################################


# ############################################################################
# Main Routine
# ############################################################################
#
# Tell the user what's going on
#
echo
echo "This creates the database for the AArc system."
echo "Press CNTL-C to exit, or RETURN to continue"
read PROMPT

#
# Get the mySQL Administrator Login
#
echo
read -p "mySQL account with Admin access []: " USER
if [ -z $USER ] ; then
  echo "You must supply a mySQL administrator account name!"
  exit 1
fi
read -s -p "mySQL Password: " PASSWORD
echo

#
# Check that mySQL is running and accessible
#
mysql -s -u $USER -p$PASSWORD -e"use mysql" 2> /dev/null
if [ $? -ne 0 ] ; then
  echo
  echo "*** Unable to connect to the mySQL server. ***"
  echo "It's possible that the server is not currently running, or that"
  echo "the name/password you've supplied are not correct.  Please"
  echo "correct the problem and try again."
  echo
  exit 1
fi

#
# Check for an existing Aarc database
#
mysql -s -u $USER -p$PASSWORD -e"use Aarc" 2> /dev/null
if [ $? -eq 0 ] ; then
  echo
  echo "*** An Aarc database already exists! ***"
  echo "You can:"
  echo "  1) Overwrite it and create a new one."
  echo "     [WARNING: overwriting the database will COMPLETELY DESTROY"
  echo "     any existing data!]"
  echo
#  echo "  2) Upgrade the existing database."
#  echo
  echo "  2) Exit this utility."
  echo
  read -p "Your choice [2]: " OVERWRITE
  echo
  case "$OVERWRITE" in
    "1")
      echo -n "Deleting old Aarc database..."
      mysql -s -u $USER -p$PASSWORD -e"drop database Aarc"
      echo "done."
      echo -n "Creating new Aarc database..."
      CreateDB
      echo "done."
      echo
      exit 0
    ;;

   "2" | "")
    echo "Database NOT overwritten."
    echo
    exit 0
    ;;
  esac
else
  CreateDB
fi 

# ############################################################################

# End of aarc_create_db
