-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: May 14, 2024 at 05:40 AM
-- Server version: 10.6.17-MariaDB-cll-lve
-- PHP Version: 8.1.28

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00:00";

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

-- Character set and collation
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

-- Database: `ogfieldticket_test`

-- Table structure for table `FieldTicket`
CREATE TABLE `FieldTicket` (
  `Ticket` int(11) NOT NULL AUTO_INCREMENT,
  `LeaseID` varchar(10) NOT NULL,
  `WellID` varchar(20) DEFAULT NULL,
  `TicketDate` date NOT NULL,
  `Comments` varchar(255) NOT NULL DEFAULT '',
  `JobTypeID` int(11) NOT NULL,
  `UserID` varchar(10) NOT NULL,
  `Billed` char(1) NOT NULL DEFAULT 'N',
  `Note` text DEFAULT NULL,
  PRIMARY KEY (`Ticket`),
  FOREIGN KEY (`LeaseID`) REFERENCES `leases` (`LeaseID`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`JobTypeID`) REFERENCES `JobTypes` (`JobTypeID`) ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (`UserID`) REFERENCES `users` (`UserID`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `FieldTicketDetail`
CREATE TABLE `FieldTicketDetail` (
  `Ticket` int(11) NOT NULL,
  `TicketLine` int(11) NOT NULL AUTO_INCREMENT,
  `JobItemID` varchar(20) NOT NULL,
  `Quantity` int(11) NOT NULL,
  `Note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Ticket`, `TicketLine`),
  FOREIGN KEY (`Ticket`) REFERENCES `FieldTicket` (`Ticket`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`JobItemID`) REFERENCES `JobItems` (`JobItemID`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `ItemTypes`
CREATE TABLE `ItemTypes` (
  `ItemID` varchar(20) NOT NULL,
  `ItemDescription` varchar(255) NOT NULL,
  `UOM` varchar(10) DEFAULT NULL,
  `UseQuantity` char(1) NOT NULL DEFAULT 'Y',
  `UseCost` char(1) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (`ItemID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `JobItems`
CREATE TABLE `JobItems` (
  `JobTypeID` int(11) NOT NULL,
  `ItemID` varchar(20) NOT NULL,
  `JobItemID` int(11) NOT NULL AUTO_INCREMENT,
  `ItemCost` decimal(9,2) NOT NULL DEFAULT 0.00,
  `ItemQuantity` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`JobTypeID`, `ItemID`),
  FOREIGN KEY (`JobTypeID`) REFERENCES `JobTypes` (`JobTypeID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `JobTypes`
CREATE TABLE `JobTypes` (
  `JobTypeID` int(11) NOT NULL AUTO_INCREMENT,
  `Description` varchar(255) DEFAULT NULL,
  `Active` char(1) DEFAULT 'Y',
  PRIMARY KEY (`JobTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `leases`
CREATE TABLE `leases` (
  `LeaseID` varchar(10) NOT NULL,
  `LeaseName` varchar(255) NOT NULL,
  `Operator` varchar(255) DEFAULT NULL,
  `Field` varchar(255) DEFAULT NULL,
  `lat` decimal(11,7) NOT NULL,
  `lon` decimal(11,7) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `county` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  `zipcode` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`LeaseID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `purchasers`
CREATE TABLE `purchasers` (
  `PurchaserID` varchar(10) NOT NULL,
  `PurchaserName` varchar(255) NOT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(50) DEFAULT NULL,
  `State` varchar(2) DEFAULT NULL,
  `Zip` varchar(10) DEFAULT NULL,
  `Phone` varchar(15) DEFAULT NULL,
  `Email` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`PurchaserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table structure for table `users`
CREATE TABLE `users` (
  `UserID` varchar(10) NOT NULL,
  `UserName` varchar(50) NOT NULL,
  `Password` varchar(255) NOT NULL,
  `GroupID` varchar(20) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `EMail` varchar(50) NOT NULL,
  PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- AUTO_INCREMENT for dumped tables
ALTER TABLE `FieldTicket`
  MODIFY `Ticket` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `FieldTicketDetail`
  MODIFY `TicketLine` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;