-- Temporarily disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Alter table structure for table `FieldTicket`
ALTER TABLE `FieldTicket`
  MODIFY `Ticket` int(11) NOT NULL AUTO_INCREMENT,
  MODIFY `LeaseID` varchar(10) NOT NULL,
  MODIFY `WellID` varchar(20) DEFAULT NULL,
  MODIFY `TicketDate` date NOT NULL,
  MODIFY `Comments` varchar(255) NOT NULL DEFAULT '',
  MODIFY `JobTypeID` int(11) NOT NULL,
  MODIFY `UserID` varchar(10) NOT NULL,
  MODIFY `Billed` char(1) NOT NULL DEFAULT 'N',
  MODIFY `Note` text DEFAULT NULL,
  ADD CONSTRAINT `fk_leaseid` FOREIGN KEY (`LeaseID`) REFERENCES `leases` (`LeaseID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_jobtypeid` FOREIGN KEY (`JobTypeID`) REFERENCES `JobTypes` (`JobTypeID`) ON DELETE RESTRICT ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_userid` FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Alter table structure for table `FieldTicketDetail`
ALTER TABLE `FieldTicketDetail`
  MODIFY `Ticket` int(11) NOT NULL,
  MODIFY `TicketLine` int(11) NOT NULL AUTO_INCREMENT,
  MODIFY `JobItemID` varchar(20) NOT NULL,
  MODIFY `Quantity` int(11) NOT NULL,
  MODIFY `Note` varchar(255) DEFAULT NULL,
  ADD CONSTRAINT `fk_ticket` FOREIGN KEY (`Ticket`) REFERENCES `FieldTicket` (`Ticket`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_jobitemid` FOREIGN KEY (`JobItemID`) REFERENCES `JobItems` (`JobItemID`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Alter table structure for table `ItemTypes`
ALTER TABLE `ItemTypes`
  MODIFY `ItemID` varchar(20) NOT NULL,
  MODIFY `ItemDescription` varchar(255) NOT NULL,
  MODIFY `UOM` varchar(10) DEFAULT NULL,
  MODIFY `UseQuantity` char(1) NOT NULL DEFAULT 'Y',
  MODIFY `UseCost` char(1) NOT NULL DEFAULT 'Y',
  ADD PRIMARY KEY (`ItemID`);

-- Alter table structure for table `JobItems`
ALTER TABLE `JobItems`
  MODIFY `JobTypeID` int(11) NOT NULL,
  MODIFY `ItemID` varchar(20) NOT NULL,
  MODIFY `JobItemID` int(11) NOT NULL AUTO_INCREMENT,
  MODIFY `ItemCost` decimal(9,2) NOT NULL DEFAULT 0.00,
  MODIFY `ItemQuantity` decimal(10,2) DEFAULT NULL,
  ADD PRIMARY KEY (`JobTypeID`, `ItemID`),
  ADD CONSTRAINT `fk_jobtypeid` FOREIGN KEY (`JobTypeID`) REFERENCES `JobTypes` (`JobTypeID`) ON DELETE CASCADE ON UPDATE CASCADE;

-- Alter table structure for table `JobTypes`
ALTER TABLE `JobTypes`
  MODIFY `JobTypeID` int(11) NOT NULL AUTO_INCREMENT,
  MODIFY `Description` varchar(255) DEFAULT NULL,
  MODIFY `Active` char(1) DEFAULT 'Y',
  ADD PRIMARY KEY (`JobTypeID`);

-- Alter table structure for table `leases`
ALTER TABLE `leases`
  MODIFY `LeaseID` varchar(10) NOT NULL,
  MODIFY `LeaseName` varchar(255) NOT NULL,
  MODIFY `Operator` varchar(255) DEFAULT NULL,
  MODIFY `Field` varchar(255) DEFAULT NULL,
  MODIFY `lat` decimal(11,7) NOT NULL,
  MODIFY `lon` decimal(11,7) NOT NULL,
  MODIFY `address` varchar(255) DEFAULT NULL,
  MODIFY `county` varchar(255) DEFAULT NULL,
  MODIFY `state` varchar(255) DEFAULT NULL,
  MODIFY `city` varchar(255) DEFAULT NULL,
  MODIFY `zipcode` varchar(255) DEFAULT NULL,
  ADD PRIMARY KEY (`LeaseID`);

-- Alter table structure for table `purchasers`
ALTER TABLE `purchasers`
  MODIFY `PurchaserID` varchar(10) NOT NULL,
  MODIFY `PurchaserName` varchar(255) NOT NULL,
  MODIFY `Address` varchar(255) DEFAULT NULL,
  MODIFY `City` varchar(50) DEFAULT NULL,
  MODIFY `State` varchar(2) DEFAULT NULL,
  MODIFY `Zip` varchar(10) DEFAULT NULL,
  MODIFY `Phone` varchar(15) DEFAULT NULL,
  MODIFY `Email` varchar(50) DEFAULT NULL,
  ADD PRIMARY KEY (`PurchaserID`);

-- Alter table structure for table `users`
ALTER TABLE `users`
  MODIFY `UserID` varchar(10) NOT NULL,
  MODIFY `UserName` varchar(50) NOT NULL,
  MODIFY `Password` varchar(255) NOT NULL,
  MODIFY `GroupID` varchar(20) DEFAULT NULL,
  MODIFY `Name` varchar(255) DEFAULT NULL,
  MODIFY `EMail` varchar(50) NOT NULL,
  ADD PRIMARY KEY (`UserID`);

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;