-- Rate table: stores the hourly labor rate used to calculate job cost
-- Run this once against the gbyerly_jruch database before deploying the updated PHP files.

CREATE TABLE IF NOT EXISTS `Rate` (
  `RateID`      int(11)        NOT NULL AUTO_INCREMENT,
  `RateName`    varchar(100)   COLLATE utf8_unicode_ci NOT NULL,
  `HourlyRate`  decimal(10,2)  NOT NULL DEFAULT '0.00',
  `EffectiveDate` date         NOT NULL,
  `Active`      tinyint(1)     NOT NULL DEFAULT 1,
  PRIMARY KEY (`RateID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Customer table
CREATE TABLE IF NOT EXISTS `Customers` (
  `CustomerID`    int(11)       NOT NULL AUTO_INCREMENT,
  `FirstName`     varchar(100)  COLLATE utf8_unicode_ci NOT NULL,
  `LastName`      varchar(100)  COLLATE utf8_unicode_ci NOT NULL,
  `CompanyName`   varchar(255)  COLLATE utf8_unicode_ci DEFAULT NULL,
  `StreetAddress` varchar(255)  COLLATE utf8_unicode_ci DEFAULT NULL,
  `City`          varchar(100)  COLLATE utf8_unicode_ci DEFAULT NULL,
  `State`         varchar(50)   COLLATE utf8_unicode_ci DEFAULT NULL,
  `Zip`           varchar(20)   COLLATE utf8_unicode_ci DEFAULT NULL,
  `Phone`         varchar(30)   COLLATE utf8_unicode_ci DEFAULT NULL,
  `Fax`           varchar(30)   COLLATE utf8_unicode_ci DEFAULT NULL,
  `Mobile`        varchar(30)   COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`CustomerID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Add CustomerID foreign key to Jobs table
ALTER TABLE `Jobs`
  ADD COLUMN `CustomerID` int(11) NULL DEFAULT NULL AFTER `JobNbr`,
  ADD CONSTRAINT `Jobs_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES `Customers` (`CustomerID`) ON DELETE SET NULL;

-- Add Notes column to JobTime table
ALTER TABLE `JobTime`
  ADD COLUMN `Notes` varchar(255) COLLATE utf8_unicode_ci NULL DEFAULT NULL;

-- Seed the initial $85/hr rate
INSERT INTO `Rate` (`RateName`, `HourlyRate`, `EffectiveDate`, `Active`)
VALUES ('Standard Labor Rate', 85.00, CURDATE(), 1);