-- phpMyAdmin SQL Dump
-- version 4.9.5deb2
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Oct 21, 2020 at 09:19 PM
-- Server version: 8.0.21-0ubuntu0.20.04.4
-- PHP Version: 7.4.3

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

--
-- Database: `skiptrace_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `billing`
--

CREATE TABLE `billing` (
  `id` int UNSIGNED NOT NULL,
  `intUserID` int UNSIGNED NOT NULL,
  `vcStripePaymentIntentID` varchar(256) COLLATE utf8mb4_general_ci NOT NULL,
  `vcDescription` varchar(256) COLLATE utf8mb4_general_ci NOT NULL,
  `fAmount` float(10,2) NOT NULL,
  `vcPaymentMethod` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `dtCreated` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `contacts`
--

CREATE TABLE `contacts` (
  `id` int UNSIGNED NOT NULL,
  `intUserID` int UNSIGNED NOT NULL,
  `intIDICoreContactID` int UNSIGNED NOT NULL DEFAULT '0',
  `vcFirstName` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcMiddleName` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcLastName` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcAddress1` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcAddress2` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcCity` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcState` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcPostalCode` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcMailingAddress1` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcMailingAddress2` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcMailingCity` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcMailingState` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcMailingPostalCode` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `jsonEmails` json NOT NULL,
  `jsonPhones` json NOT NULL,
  `txtNotes` text COLLATE utf8mb4_general_ci NOT NULL,
  `dtLastModified` int UNSIGNED NOT NULL,
  `dtCreated` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

CREATE TABLE `contact_tags` (
  `id` int UNSIGNED NOT NULL,
  `intContactID` int UNSIGNED NOT NULL,
  `intTagID` int UNSIGNED NOT NULL,
  `dtCreated` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Table structure for table `cron_runs`
--

CREATE TABLE `cron_runs` (
  `id` int UNSIGNED NOT NULL,
  `tiCronType` tinyint UNSIGNED NOT NULL DEFAULT '0',
  `bManual` tinyint UNSIGNED NOT NULL DEFAULT '0',
  `intProcessID` int UNSIGNED NOT NULL DEFAULT '0',
  `bRunning` tinyint UNSIGNED NOT NULL DEFAULT '0',
  `fRunLength` float(13,3) UNSIGNED NOT NULL DEFAULT '0.000',
  `dtCreated` int UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `email_queue`
--

CREATE TABLE `email_queue` (
  `id` int UNSIGNED NOT NULL,
  `bNotification` tinyint(1) NOT NULL DEFAULT '0',
  `intSentByID` int UNSIGNED DEFAULT '0',
  `bSent` tinyint(1) NOT NULL DEFAULT '0',
  `dtSent` int UNSIGNED DEFAULT '0',
  `vcSent` varchar(48) DEFAULT NULL,
  `vcTo` varchar(255) DEFAULT NULL,
  `vcCc` varchar(255) DEFAULT NULL,
  `vcBcc` varchar(255) DEFAULT NULL,
  `vcSubject` varchar(255) DEFAULT NULL,
  `txtBody` text,
  `txtAttachments` text,
  `vcError` varchar(255) DEFAULT NULL,
  `vcCreated` varchar(48) DEFAULT NULL,
  `dtCreated` int UNSIGNED DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `idicore_contacts`
--

CREATE TABLE `idicore_contacts` (
  `id` int UNSIGNED NOT NULL,
  `vcPID` varchar(256) COLLATE utf8mb4_general_ci NOT NULL,
  `jsonIDICoreData` json NOT NULL,
  `dtLastModified` int NOT NULL,
  `dtCreated` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `media`
--

CREATE TABLE `media` (
  `id` int UNSIGNED NOT NULL,
  `bActive` tinyint UNSIGNED NOT NULL DEFAULT '1',
  `bDeleted` tinyint(1) NOT NULL DEFAULT '0',
  `vcFileName` char(255) DEFAULT NULL,
  `vcSrcPath` char(255) DEFAULT NULL,
  `vcThumbPath` char(255) DEFAULT NULL,
  `tiMediaType` tinyint UNSIGNED DEFAULT '0',
  `vcMimeType` char(255) DEFAULT NULL,
  `intFileSize` int DEFAULT '0',
  `intImageWidth` int DEFAULT '0',
  `intImageHeight` int DEFAULT '0',
  `dtCreated` int UNSIGNED DEFAULT '0',
  `dtLastModified` int UNSIGNED DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `metadata`
--

CREATE TABLE `metadata` (
  `id` int UNSIGNED NOT NULL,
  `intMetaType` int NOT NULL,
  `intInitiatorID` int NOT NULL DEFAULT '0',
  `jsonDetails` json DEFAULT NULL,
  `dtCreated` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `quantity_breaks`
--

CREATE TABLE `quantity_breaks` (
  `id` int UNSIGNED NOT NULL,
  `intQuantity` int UNSIGNED NOT NULL,
  `fPrice` float(10,2) UNSIGNED NOT NULL,
  `dtCreated` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `searches`
--

CREATE TABLE `searches` (
  `id` int UNSIGNED NOT NULL,
  `intUserID` int UNSIGNED NOT NULL,
  `tiSearchType` tinyint(1) NOT NULL,
  `vcSearchName` varchar(256) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `dtCreated` int NOT NULL,
  `dtFinished` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `search_contacts`
--

CREATE TABLE `search_contacts` (
  `id` int UNSIGNED NOT NULL,
  `intSearchID` int UNSIGNED NOT NULL,
  `intContactID` int UNSIGNED NOT NULL DEFAULT '0',
  `vcFirstName` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcLastName` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcAddress` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcCity` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcState` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcPostalCode` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `dtSearched` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int UNSIGNED NOT NULL,
  `bPrivate` tinyint UNSIGNED NOT NULL DEFAULT '0',
  `vcSettingDisplayName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `vcSetting` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `txtValue` text CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `vcDescription` varchar(1026) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `dtLastModified` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Application Settings';

--
-- Dumping data for table `settings`
--

INSERT INTO `settings` (`id`, `bPrivate`, `vcSettingDisplayName`, `vcSetting`, `txtValue`, `vcDescription`, `dtLastModified`) VALUES
(1, 0, 'Bulk Price Skips', 'bulk_skips_price', '0.15', 'Default value for Bulk Skips', 1603307639),
(2, 0, 'Application Enabled', 'application_enabled', '1', 'Determines whether or not the application can perform searches or bill the client.', 1603307639),
(3, 0, 'Promotional Bulk Price', 'promotional_bulk_price', '0.10', 'Customers added during the promotional period will adopt this rate until the deadline is reached.', 1603307639),
(4, 0, 'Promotional Deadline', 'promotional_deadline', '', 'The deadline for when a promotional period ends and original prices are then applied.', 1603307639);

-- --------------------------------------------------------

--
-- Table structure for table `tags`
--

CREATE TABLE `tags` (
  `id` int UNSIGNED NOT NULL,
  `intUserID` int UNSIGNED NOT NULL,
  `vcName` varchar(64) COLLATE utf8mb4_general_ci NOT NULL,
  `vcColor` varchar(16) COLLATE utf8mb4_general_ci NOT NULL,
  `dtCreated` int UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int UNSIGNED NOT NULL,
  `vcUUID` char(36) DEFAULT NULL,
  `flBitFlags` int UNSIGNED DEFAULT '0',
  `intUserRole` int UNSIGNED NOT NULL DEFAULT '0',
  `intCreatedByID` int UNSIGNED NOT NULL DEFAULT '0',
  `bActive` tinyint(1) NOT NULL DEFAULT '1',
  `bSuperUser` tinyint(1) NOT NULL DEFAULT '0',
  `bConfirmed` tinyint(1) NOT NULL DEFAULT '0',
  `vcConfirmedCode` varchar(128) DEFAULT NULL,
  `vcRemoteAddr` varchar(64) DEFAULT NULL,
  `emlEmail` varchar(64) DEFAULT NULL,
  `vcPasPassword` varchar(255) DEFAULT NULL,
  `vcAuthHash` varchar(255) DEFAULT NULL,
  `vcFirstName` varchar(32) DEFAULT NULL,
  `vcLastName` varchar(32) DEFAULT NULL,
  `vcCompanyName` varchar(128) DEFAULT NULL,
  `vcAddress1` varchar(64) DEFAULT NULL,
  `vcAddress2` varchar(64) DEFAULT NULL,
  `vcCity` varchar(64) DEFAULT NULL,
  `vcState` varchar(48) DEFAULT NULL,
  `vcPostalCode` varchar(24) DEFAULT NULL,
  `phPhone` varchar(24) DEFAULT NULL,
  `phFax` varchar(24) DEFAULT NULL,
  `vcStripeCustomerID` varchar(256) DEFAULT NULL,
  `vcStripePaymentMethodID` varchar(256) DEFAULT NULL,
  `intSkipBalance` int NOT NULL DEFAULT '0',
  `bEnableBilling` tinyint UNSIGNED NOT NULL DEFAULT '1',
  `fBulkDiscount` float(5,2) DEFAULT NULL,
  `fSingleDiscount` float(5,2) DEFAULT NULL,
  `dtCreated` int UNSIGNED DEFAULT '0',
  `dtExpireDate` int UNSIGNED DEFAULT '0',
  `dtLastLogin` int UNSIGNED DEFAULT '0',
  `dtLastModified` int UNSIGNED DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `vcUUID`, `flBitFlags`, `intUserRole`, `intCreatedByID`, `bActive`, `bSuperUser`, `bConfirmed`, `vcConfirmedCode`, `vcRemoteAddr`, `emlEmail`, `vcPasPassword`, `vcAuthHash`, `vcFirstName`, `vcLastName`, `vcCompanyName`, `vcAddress1`, `vcAddress2`, `vcCity`, `vcState`, `vcPostalCode`, `phPhone`, `phFax`, `intSkipBalance`, `bEnableBilling`, `fBulkDiscount`, `fSingleDiscount`, `dtCreated`, `dtExpireDate`, `dtLastLogin`, `dtLastModified`) VALUES
(1, 'bdd78508-dff3-43f8-85f1-3bd6431f39ce', 0, 100, 100, 1, 1, 1, NULL, NULL, 'support@sourcewebsolutions.com', '$2y$10$DSSZQ3GSQNSPoqnE93kMkep7YiFmDbVzkZpua1Wm6/Y/F18bbnWcS', NULL, 'Source', 'Web Solutions', NULL, '123 Party Ln', NULL, 'Houston', 'Texas', '77053', '1234567890', '1234567890', 0, 1, NULL, NULL, 1603304877, 0, 0, 1603304877);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `billing`
--
ALTER TABLE `billing`
  ADD PRIMARY KEY (`id`),
  ADD KEY `intUserID` (`intUserID`);

--
-- Indexes for table `contacts`
--
ALTER TABLE `contacts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `intIDICoreContactID` (`intIDICoreContactID`),
  ADD KEY `intUserID` (`intUserID`);

ALTER TABLE `contact_tags`
  ADD PRIMARY KEY (`id`),
  ADD KEY `intContactID` (`intContactID`),
  ADD KEY `intTagID` (`intTagID`);
  
--
-- Indexes for table `cron_runs`
--
ALTER TABLE `cron_runs`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `email_queue`
--
ALTER TABLE `email_queue`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `idicore_contacts`
--
ALTER TABLE `idicore_contacts`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `media`
--
ALTER TABLE `media`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `metadata`
--
ALTER TABLE `metadata`
  ADD PRIMARY KEY (`id`),
  ADD KEY `intMetaType` (`intMetaType`,`intInitiatorID`),
  ADD KEY `intInitiatorID` (`intInitiatorID`);

--
-- Indexes for table `quantity_breaks`
--
ALTER TABLE `quantity_breaks`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `searches`
--
ALTER TABLE `searches`
  ADD PRIMARY KEY (`id`),
  ADD KEY `intUserID` (`intUserID`);

--
-- Indexes for table `search_contacts`
--
ALTER TABLE `search_contacts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `intSearchID` (`intSearchID`),
  ADD KEY `intContactID` (`intContactID`);

--
-- Indexes for table `settings`
--
ALTER TABLE `settings`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `vcSetting` (`vcSetting`);

--
-- Indexes for table `tags`
--
ALTER TABLE `tags`
  ADD PRIMARY KEY (`id`),
  ADD KEY `intUserID` (`intUserID`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `vcUUID` (`vcUUID`),
  ADD KEY `emlEmail` (`emlEmail`),
  ADD KEY `intCreatedByID` (`intCreatedByID`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `billing`
--
ALTER TABLE `billing`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `contacts`
--
ALTER TABLE `contacts`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `contact_tags`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;
  
--
-- AUTO_INCREMENT for table `cron_runs`
--
ALTER TABLE `cron_runs`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `email_queue`
--
ALTER TABLE `email_queue`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `idicore_contacts`
--
ALTER TABLE `idicore_contacts`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `media`
--
ALTER TABLE `media`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `metadata`
--
ALTER TABLE `metadata`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `quantity_breaks`
--
ALTER TABLE `quantity_breaks`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `searches`
--
ALTER TABLE `searches`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `search_contacts`
--
ALTER TABLE `search_contacts`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `tags`
--
ALTER TABLE `tags`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
COMMIT;

ALTER TABLE `search_contacts` ADD `vcPropertyAddress` VARCHAR(64) NULL DEFAULT NULL AFTER `vcPostalCode`, ADD `vcPropertyCity` VARCHAR(64) NULL DEFAULT NULL AFTER `vcPropertyAddress`, ADD `vcPropertyState` VARCHAR(64) NULL DEFAULT NULL AFTER `vcPropertyCity`, ADD `vcPropertyPostalCode` VARCHAR(64) NULL DEFAULT NULL AFTER `vcPropertyState`; 

ALTER TABLE `contact_addresses` ADD `bProperty` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `bMailing`; 

ALTER TABLE `searches` ADD `bReverseLookUp` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0' AFTER `tiSearchStatus`; 

ALTER TABLE `search_contacts` CHANGE `vcFirstName` `vcFirstName` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL; 

ALTER TABLE `search_contacts` CHANGE `vcLastName` `vcLastName` VARCHAR(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;