<?php 

include('_global.php');

$properties = [];

$echo = '';

$today = date('Y-m-d');

if($_REQUEST['today'] == $today)
{
	$sqlList = <<<EOT
DROP TABLE IF EXISTS SALE;
DROP TABLE IF EXISTS AGENT;
DROP TABLE IF EXISTS OFFICE;
DROP TABLE IF EXISTS DESTINATION;
CREATE TABLE IF NOT EXISTS DESTINATION (
  DestinationID int(11) NOT NULL AUTO_INCREMENT,
  DestinationName varchar(100) NOT NULL,
  PRIMARY KEY (DestinationID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;
INSERT INTO DESTINATION (DestinationID, DestinationName) VALUES
(1, 'Rome'),
(2, 'Paris'),
(3, 'London'),
(4, 'Dubai'),
(5, 'Delhi'),
(6, 'Moscow'),
(7, 'Kochi'),
(8, 'Bangkok'),
(9, 'Taipei'),
(10, 'Sydney'),
(11, 'Perth'),
(12, 'Auckland'),
(13, 'Mexico City'),
(14, 'Sao Paulo');
CREATE TABLE IF NOT EXISTS OFFICE (
  OfficeID int(11) NOT NULL AUTO_INCREMENT,
  OfficeLocation varchar(100) NOT NULL,
  PRIMARY KEY (OfficeID)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
INSERT INTO OFFICE (OfficeID, OfficeLocation) VALUES
(1, 'Honolulu'),
(2, 'Manoa'),
(3, 'Waipahu'),
(4, 'Mililani'),
(5, 'Puna'),
(6, 'Hawaii Kai'),
(7, 'Kaneohe'),
(8, 'Kahului');
CREATE TABLE IF NOT EXISTS AGENT (
  AgentID int(11) NOT NULL AUTO_INCREMENT,
  AgentName varchar(100) NOT NULL DEFAULT '',
  OfficeKey int(11) NOT NULL,
  PRIMARY KEY (AgentID),
  KEY OfficeKey (OfficeKey)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=13 ;
INSERT INTO AGENT (AgentID, AgentName, OfficeKey) VALUES
(1, 'Tom Brower', 1),
(2, 'Isaac Choy', 2),
(3, 'Ty Cullen', 3),
(4, 'Ryan Yamane', 4),
(5, 'Joy San Buenaventura', 5),
(6, 'Mark Hashem', 6),
(7, 'Jarrett Keohokalole', 7),
(8, 'Linda Ichiyama', 1),
(9, 'Ken Ito', 7),
(10, 'Takashi Ohno', 1),
(11, 'Gene Ward', 6),
(12, 'Kyle Yamashita', 8);
CREATE TABLE IF NOT EXISTS SALE (
  SaleID int(11) NOT NULL AUTO_INCREMENT,
  SaleDate date DEFAULT NULL,
  AgentKey int(11) NOT NULL,
  Amount decimal(11,2) DEFAULT NULL,
  DestinationKey int(11) NOT NULL,
  PRIMARY KEY (SaleID),
  KEY AgentKey (AgentKey),
  KEY DestinationKey (DestinationKey)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31 ;
INSERT INTO SALE (SaleID, SaleDate, AgentKey, Amount, DestinationKey) VALUES
(1, '2014-12-02', 4, '200.00', 12),
(2, '2014-02-03', 3, '1200.00', 2),
(3, '2014-06-25', 2, '400.00', 5),
(4, '2014-04-04', 8, '3000.00', 5),
(5, '2014-03-08', 1, '2500.00', 7),
(6, '2014-11-05', 5, '300.00', 3),
(7, '2014-05-12', 8, '250.00', 1),
(8, '2014-03-05', 4, '2000.00', 10),
(9, '2014-03-04', 3, '300.00', 11),
(10, '2014-05-06', 4, '4500.00', 10),
(11, '2014-06-07', 5, '3400.00', 12),
(12, '2014-07-08', 6, '20.00', 13),
(13, '2014-08-09', 7, '2000.00', 14),
(14, '2014-09-10', 8, '2300.00', 9),
(15, '2014-02-21', 9, '1200.00', 8),
(16, '2016-08-26', 11, '2300.00', 7),
(17, '2014-04-21', 10, '700.00', 6),
(18, '2014-03-15', 9, '6000.00', 5),
(19, '2014-02-24', 8, '4000.00', 4),
(20, '2014-01-31', 7, '4000.00', 3),
(21, '2014-07-26', 6, '3600.00', 2),
(22, '2014-08-12', 5, '3400.00', 1),
(23, '2014-09-13', 4, '4500.00', 2),
(24, '2014-04-05', 3, '6700.00', 3),
(25, '2014-02-03', 2, '2300.00', 4),
(26, '2014-02-08', 1, '230.00', 5),
(27, '2014-02-08', 1, '320.00', 6),
(28, '2014-02-06', 2, '3210.00', 6),
(29, '2014-01-06', 3, '3200.00', 7),
(30, '2014-09-08', 4, '1090.00', 8);
ALTER TABLE AGENT
  ADD CONSTRAINT AGENT_ibfk_1 FOREIGN KEY (OfficeKey) REFERENCES OFFICE (OfficeID);
ALTER TABLE SALE
  ADD CONSTRAINT SALE_ibfk_1 FOREIGN KEY (AgentKey) REFERENCES AGENT (AgentID),
  ADD CONSTRAINT SALE_ibfk_2 FOREIGN KEY (DestinationKey) REFERENCES DESTINATION (DestinationID);
EOT;
	$sqls = explode(";",$sqlList);
	foreach ($sqls as &$sql)
	{
		if($sql>'')
		{
			$echo .= "$sql\n";
			$sqlExecute = mysql_query($sql, $conn) or die("Couldn't perform query $sql (".__LINE__."): " . mysql_error() . '.');
		}
	}

	$sqlSelect = "SHOW TABLE STATUS";

	$sqlTable = mysql_query($sqlSelect, $conn) or die("Couldn't perform query $sqlSelect (".__LINE__."): " . mysql_error() . '.');


	while($sqlRecord = mysql_fetch_assoc($sqlTable)) array_push($properties,$sqlRecord);

	$message = "Done";
}
else
{
	$message = "?today=$today";
}

$datetime=date('Y-m-d H:i:s');

$json = json_encode([
		"today" => $_REQUEST['today'],
		"datetime" => $datetime,
		"properties" => $properties,
		"echo" => $echo,
		"message" => $message
], JSON_PRETTY_PRINT);

echo $json;

?>