Current Database Structure

Jack

Current database and tables structure for TMC

--
-- Database: `tyrael`
--

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

--
-- Table structure for table `address`
--

CREATE TABLE `address` (
  `id` int(11) NOT NULL,
  `street` varchar(255) DEFAULT NULL,
  `unit_type` varchar(50) DEFAULT NULL,
  `unit_num` varchar(50) DEFAULT NULL,
  `mail_city` varchar(100) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  `latitude` double(10,7) DEFAULT NULL,
  `longitude` double(10,7) DEFAULT NULL,
  `feat_id` varchar(20) DEFAULT NULL,
  `county_id` int(11) DEFAULT NULL,
  `county` varchar(100) DEFAULT NULL,
  `jurisdiction` varchar(100) DEFAULT NULL,
  `grade` varchar(1) NOT NULL DEFAULT '0',
  `map_image_url` varchar(255) DEFAULT '0',
  `last_updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `flag` int(1) DEFAULT 0,
  `via` int(11) DEFAULT 0,
  `fullname` varchar(255) DEFAULT NULL,
  `fname` varchar(255) DEFAULT NULL,
  `lname` varchar(255) DEFAULT NULL,
  `admin` int(11) DEFAULT 0,
  `movement` varchar(10) DEFAULT NULL,
  `mailingAddress` varchar(255) DEFAULT NULL,
  `mailingCity` varchar(30) DEFAULT NULL,
  `mailingState` varchar(30) DEFAULT NULL,
  `mailingZip` varchar(10) DEFAULT NULL,
  `processing` int(11) DEFAULT 0,
  `added` timestamp NULL DEFAULT current_timestamp(),
  `DMID` varchar(100) DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `app_data`
--

CREATE TABLE `app_data` (
  `id` int(11) NOT NULL,
  `appid` int(11) NOT NULL,
  `apptime` datetime NOT NULL,
  `runstatus` varchar(50) DEFAULT NULL,
  `rep` varchar(100) DEFAULT NULL,
  `setter` varchar(100) DEFAULT NULL,
  `adminstatus` varchar(50) DEFAULT NULL,
  `pqid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `created` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `autotexterrors`
--

CREATE TABLE `autotexterrors` (
  `id` int(11) NOT NULL,
  `errormsg` varchar(255) DEFAULT NULL,
  `happened` timestamp NULL DEFAULT current_timestamp(),
  `handled` int(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `campaigns`
--

CREATE TABLE `campaigns` (
  `id` int(11) NOT NULL,
  `flag` int(11) DEFAULT NULL,
  `campaignName` varchar(255) NOT NULL,
  `added` timestamp NOT NULL DEFAULT current_timestamp(),
  `emoji` varchar(10) DEFAULT NULL,
  `vertical` int(11) DEFAULT 1,
  `textingActive` int(1) DEFAULT 0,
  `hidden` int(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `dealmachine`
--

CREATE TABLE `dealmachine` (
  `id` varchar(50) NOT NULL,
  `lead_status_value` int(11) DEFAULT NULL,
  `lead_status_label` varchar(255) DEFAULT NULL,
  `assigned_to` int(11) DEFAULT NULL,
  `lead_source` varchar(255) DEFAULT NULL,
  `creator_value` int(11) DEFAULT NULL,
  `creator_label` varchar(255) DEFAULT NULL,
  `lead_date_updated` timestamp NULL DEFAULT current_timestamp(),
  `lead_date_created` timestamp NULL DEFAULT current_timestamp(),
  `last_exported_date` timestamp NULL DEFAULT current_timestamp(),
  `number_of_stacked_lists` int(11) DEFAULT NULL,
  `campaign_status_value` varchar(255) DEFAULT NULL,
  `campaign_status_label` varchar(255) DEFAULT NULL,
  `mailer_campaign_value` int(11) DEFAULT NULL,
  `mailer_campaign_label` varchar(255) DEFAULT NULL,
  `total_times_mailers_were_sent` int(11) DEFAULT NULL,
  `last_mailer_sent_date` date DEFAULT NULL,
  `primary_image` varchar(255) DEFAULT NULL,
  `image_info` text DEFAULT NULL,
  `did_skip_trace` tinyint(1) DEFAULT NULL,
  `skip_trace_successful` tinyint(1) DEFAULT NULL,
  `number_of_mailing_addresses` int(11) DEFAULT NULL,
  `number_of_phone_numbers` int(11) DEFAULT NULL,
  `number_of_email_addresses` int(11) DEFAULT NULL,
  `sale_price` decimal(15,2) DEFAULT NULL,
  `sale_date` date DEFAULT NULL,
  `calculated_total_value` decimal(15,2) DEFAULT NULL,
  `calculated_improvement_value` decimal(15,2) DEFAULT NULL,
  `calculated_land_value` decimal(15,2) DEFAULT NULL,
  `tax_market_improvements_percent` decimal(5,2) DEFAULT NULL,
  `mortgage_amount` decimal(15,2) DEFAULT NULL,
  `lender_name` varchar(255) DEFAULT NULL,
  `mortgage_date` date DEFAULT NULL,
  `mortgage_term` int(11) DEFAULT NULL,
  `mortgage_due_date` date DEFAULT NULL,
  `second_mortgage_amount` decimal(15,2) DEFAULT NULL,
  `equity_percent` varchar(10) DEFAULT NULL,
  `preforeclosure_status` varchar(255) DEFAULT NULL,
  `building_square_feet` int(11) DEFAULT NULL,
  `units_count` int(11) DEFAULT NULL,
  `lot_acreage` decimal(10,2) DEFAULT NULL,
  `lot_square_feet` int(11) DEFAULT NULL,
  `total_baths` decimal(3,2) DEFAULT NULL,
  `total_bedrooms` int(11) DEFAULT NULL,
  `year_built` int(11) DEFAULT NULL,
  `property_type_value` varchar(100) DEFAULT NULL,
  `property_type_label` varchar(255) DEFAULT NULL,
  `property_use_standardized_value` int(11) DEFAULT NULL,
  `property_use_standardized_label` varchar(255) DEFAULT NULL,
  `parcel_id` varchar(50) DEFAULT NULL,
  `property_address_full` varchar(255) DEFAULT NULL,
  `property_address_line_1` varchar(255) DEFAULT NULL,
  `property_address_line_2` varchar(255) DEFAULT NULL,
  `property_address_city` varchar(255) DEFAULT NULL,
  `property_address_county_value` varchar(50) DEFAULT NULL,
  `property_address_county_label` varchar(255) DEFAULT NULL,
  `property_address_state` varchar(10) DEFAULT NULL,
  `property_address_zipcode` varchar(10) DEFAULT NULL,
  `property_latitude` decimal(10,7) DEFAULT NULL,
  `property_longitude` decimal(10,7) DEFAULT NULL,
  `property_latlng` varchar(50) DEFAULT NULL,
  `owner_type` varchar(255) DEFAULT NULL,
  `has_usps_address` tinyint(1) DEFAULT NULL,
  `is_vacant` tinyint(1) DEFAULT NULL,
  `owner_has_multiple_properties` tinyint(1) DEFAULT NULL,
  `owner_name` varchar(255) DEFAULT NULL,
  `owner_first_name` varchar(255) DEFAULT NULL,
  `owner_last_name` varchar(255) DEFAULT NULL,
  `owner_address_full` varchar(255) DEFAULT NULL,
  `owner_address_line_1` varchar(255) DEFAULT NULL,
  `owner_address_line_2` varchar(255) DEFAULT NULL,
  `owner_address_city` varchar(255) DEFAULT NULL,
  `owner_address_state` varchar(10) DEFAULT NULL,
  `owner_address_zipcode` varchar(10) DEFAULT NULL,
  `aid` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `dnctriggers`
--

CREATE TABLE `dnctriggers` (
  `id` int(11) NOT NULL,
  `triggerWord` varchar(255) NOT NULL,
  `added` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `emoji`
--

CREATE TABLE `emoji` (
  `e` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `genconf`
--

CREATE TABLE `genconf` (
  `id` int(11) NOT NULL,
  `firstTextActive` int(11) DEFAULT 0,
  `secondTextActive` int(11) DEFAULT 0,
  `thirdTextActive` int(1) DEFAULT 0,
  `textsAtOnce` int(11) DEFAULT 100,
  `minGrade` int(11) DEFAULT 60,
  `maxUse` int(11) DEFAULT 180,
  `gptInstructions` text DEFAULT 'Analyze the roof in the image (marked with a red indicator) and return a single integer score between 1 and 100 to indicate residential solar viability. Use these guidelines:  1. Score 1: No visible house or roof in the image. 2. Score 5: Existing solar panels on the roof. 3. Score 20: Non-residential or multi-unit properties (apartments, townhouses, mobile homes, businesses). 4. Scores 30-60: Single-family homes with challenges:    - 30-40: Significant issues (heavy shade, very small roof, or severely incorrect orientation)    - 41-50: Moderate issues (partial shade, smaller roof, or suboptimal orientation)    - 51-60: Minor issues (slight shade, average-sized roof, or slightly off ideal orientation) 5. Scores 61-100: Promising single-family homes:    - 61-75: Good candidates (minimal shade, adequate roof size, generally correct orientation)    - 76-90: Very good candidates (no significant shade, large roof area, optimal orientation)    - 91-100: Excellent candidates (no shade, very large roof area, perfect orientation, possibly high-value property or flat roof)  Consider these factors: - Roof size and available space - Shading from trees or nearby structures - Roof orientation (south-facing is ideal in the northern hemisphere) - Roof angle and type (including flat roofs) - Visible obstructions (chimneys, vents, skylights) - Overall property value and condition  Return ONLY the score as a three-digit integer (e.g., 075 for 75) without any additional text or explanation.',
  `gptModel` varchar(255) DEFAULT 'gpt-4o-2024-05-13',
  `frequency` int(11) DEFAULT 30,
  `morning` int(11) DEFAULT 0,
  `recess` int(11) DEFAULT 0,
  `dusk` int(11) DEFAULT 0,
  `bleed` int(11) DEFAULT 0,
  `zombie` int(11) DEFAULT 0,
  `vampire` int(11) DEFAULT 0,
  `internal` varchar(25) DEFAULT '+13212349111',
  `inbound1` varchar(25) DEFAULT '+18134212713'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `incoming_calls`
--

CREATE TABLE `incoming_calls` (
  `id` int(11) NOT NULL,
  `from_number` varchar(20) DEFAULT NULL,
  `to_number` varchar(20) DEFAULT NULL,
  `call_sid` varchar(64) DEFAULT NULL,
  `duration` int(11) DEFAULT NULL,
  `status` varchar(500) DEFAULT NULL,
  `recording_url` varchar(255) DEFAULT NULL,
  `received_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `recording_sid` varchar(64) DEFAULT NULL,
  `recording_duration` int(11) DEFAULT NULL,
  `pqid` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `incoming_messages`
--

CREATE TABLE `incoming_messages` (
  `id` int(11) NOT NULL,
  `from_number` varchar(20) DEFAULT NULL,
  `to_number` varchar(20) DEFAULT NULL,
  `body` text DEFAULT NULL,
  `received_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `pqid` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` int(11) NOT NULL,
  `type` varchar(50) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `status` enum('pending','processing','completed','failed','permanently_failed') NOT NULL DEFAULT 'pending',
  `started_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `failed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `error` text DEFAULT NULL,
  `retry_count` int(11) DEFAULT 0,
  `max_retries` int(11) DEFAULT 3
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `leadassignments`
--

CREATE TABLE `leadassignments` (
  `id` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `pqid` int(11) NOT NULL,
  `added` timestamp NULL DEFAULT current_timestamp(),
  `updated` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `notes`
--

CREATE TABLE `notes` (
  `id` int(11) NOT NULL,
  `aid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `note` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `ldid` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `online_users`
--

CREATE TABLE `online_users` (
  `user_id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL,
  `emoji` varchar(10) NOT NULL,
  `last_activity` timestamp NOT NULL DEFAULT current_timestamp(),
  `activity_data` text DEFAULT NULL,
  `elem_id` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `outgoing_calls`
--

CREATE TABLE `outgoing_calls` (
  `id` int(11) NOT NULL,
  `from_number` varchar(15) NOT NULL,
  `to_number` varchar(15) NOT NULL,
  `error_message` text DEFAULT NULL,
  `call_sid` varchar(34) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `recording_sid` varchar(100) DEFAULT '0',
  `duration` int(11) DEFAULT 0,
  `pqid` int(11) DEFAULT 0,
  `uid` int(11) DEFAULT 10229
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `outgoing_messages`
--

CREATE TABLE `outgoing_messages` (
  `id` int(11) NOT NULL,
  `from_number` varchar(20) DEFAULT NULL,
  `to_number` varchar(20) DEFAULT NULL,
  `body` text DEFAULT NULL,
  `sent_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `delivery_status` varchar(20) DEFAULT '0',
  `error_code` int(11) DEFAULT NULL,
  `error_message` text DEFAULT '0',
  `message_sid` varchar(34) DEFAULT NULL,
  `date_updated` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `processed` int(1) DEFAULT 0,
  `template` int(1) DEFAULT 0,
  `pqid` int(11) DEFAULT 0,
  `uid` int(11) DEFAULT 77
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `phoneareaassoc`
--

CREATE TABLE `phoneareaassoc` (
  `id` int(11) NOT NULL,
  `flag` int(11) DEFAULT NULL,
  `areaCode` int(11) DEFAULT NULL,
  `added` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `phoneassoc`
--

CREATE TABLE `phoneassoc` (
  `id` int(11) NOT NULL,
  `tnid` int(11) NOT NULL,
  `flag` int(11) NOT NULL,
  `added` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `phonequeue`
--

CREATE TABLE `phonequeue` (
  `id` int(11) NOT NULL,
  `aid` int(11) NOT NULL,
  `phone1` varchar(15) NOT NULL DEFAULT '0',
  `phone2` varchar(15) DEFAULT '0',
  `phone3` varchar(15) DEFAULT '0',
  `step` int(11) DEFAULT 0,
  `added` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `GHLID` varchar(200) DEFAULT '0',
  `NI` int(1) DEFAULT 0,
  `DNCCheck` int(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `seen_alerts`
--

CREATE TABLE `seen_alerts` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `message_id` int(11) DEFAULT NULL,
  `call_id` int(11) DEFAULT NULL,
  `seen_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `sendtextlog`
--

CREATE TABLE `sendtextlog` (
  `id` int(11) NOT NULL,
  `minGrade` int(11) NOT NULL,
  `maxUse` int(11) NOT NULL,
  `textsAtOnce` int(11) NOT NULL,
  `happened` timestamp NULL DEFAULT current_timestamp(),
  `eligible` int(11) NOT NULL,
  `uid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `steps`
--

CREATE TABLE `steps` (
  `id` int(11) NOT NULL,
  `step` varchar(255) NOT NULL,
  `added` timestamp NOT NULL DEFAULT current_timestamp(),
  `emoji` varchar(10) DEFAULT '?'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `syslog`
--

CREATE TABLE `syslog` (
  `id` int(11) NOT NULL,
  `happened` timestamp NULL DEFAULT current_timestamp(),
  `uid` int(11) DEFAULT 77,
  `action` varchar(500) DEFAULT NULL,
  `aid` int(11) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `templates`
--

CREATE TABLE `templates` (
  `id` int(11) NOT NULL,
  `body` varchar(500) DEFAULT NULL,
  `step` int(11) NOT NULL DEFAULT 0,
  `added` timestamp NOT NULL DEFAULT current_timestamp(),
  `flag` int(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `twilio_numbers`
--

CREATE TABLE `twilio_numbers` (
  `id` int(11) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `alias` varchar(55) NOT NULL,
  `active` int(11) DEFAULT 1,
  `flag` int(11) DEFAULT 0,
  `removed` int(2) DEFAULT 0,
  `added` timestamp NULL DEFAULT current_timestamp(),
  `updated` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `usercampaigns`
--

CREATE TABLE `usercampaigns` (
  `id` int(11) NOT NULL,
  `uid` int(11) DEFAULT NULL,
  `flag` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `agentStatus` int(11) DEFAULT 0,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `access` int(11) DEFAULT NULL,
  `lastIn` int(11) NOT NULL DEFAULT 0,
  `lastOut` int(11) NOT NULL DEFAULT 0,
  `lastApp` int(11) NOT NULL DEFAULT 0,
  `lastNote` int(11) DEFAULT 0,
  `fullname` varchar(255) DEFAULT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `alias` varchar(255) DEFAULT NULL,
  `cap` int(11) DEFAULT 0,
  `tenAM` varchar(11) DEFAULT '000000',
  `onePM` varchar(11) DEFAULT '000000',
  `fourPM` varchar(11) DEFAULT '000000',
  `lastPM` varchar(11) DEFAULT '000000',
  `affiliation` varchar(255) DEFAULT NULL,
  `zip` varchar(7) DEFAULT NULL,
  `disabled` int(1) DEFAULT 0,
  `rank` int(11) DEFAULT 0,
  `lastCart` int(11) DEFAULT NULL,
  `lastAct` int(11) DEFAULT 0,
  `mainTerritory` varchar(255) DEFAULT NULL,
  `teamLead` varchar(255) DEFAULT NULL,
  `defaultView` int(11) DEFAULT 0,
  `token` varchar(255) DEFAULT NULL,
  `defaultDisplay` int(11) DEFAULT 0,
  `meetingFlag` int(11) DEFAULT 0,
  `radius` int(11) NOT NULL DEFAULT 0,
  `haptic` int(1) NOT NULL DEFAULT 1,
  `GHLID` varchar(255) DEFAULT '0',
  `role` int(11) DEFAULT 0,
  `e` varchar(10) DEFAULT NULL,
  `verified` int(1) DEFAULT 0,
  `capabilities` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `verticals`
--

CREATE TABLE `verticals` (
  `id` int(11) NOT NULL,
  `verticalName` varchar(200) NOT NULL,
  `added` timestamp NOT NULL DEFAULT current_timestamp(),
  `emoji` varchar(20) DEFAULT '?'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Here are the indexes and constraints:


-- Indexes for dumped tables
--

--
-- Indexes for table `address`
--
ALTER TABLE `address`
  ADD PRIMARY KEY (`id`),
  ADD KEY `zip` (`zip`),
  ADD KEY `county_id` (`county_id`),
  ADD KEY `jurisdiction` (`jurisdiction`),
  ADD KEY `street` (`street`,`mail_city`,`county`),
  ADD KEY `grade` (`grade`),
  ADD KEY `last_updated` (`last_updated`),
  ADD KEY `flag` (`flag`,`via`),
  ADD KEY `idx_map_image_url` (`map_image_url`),
  ADD KEY `mailingCity` (`mailingCity`,`mailingZip`);
ALTER TABLE `address` ADD FULLTEXT KEY `idx_fullname` (`fullname`);
ALTER TABLE `address` ADD FULLTEXT KEY `idx_fname` (`fname`);
ALTER TABLE `address` ADD FULLTEXT KEY `idx_lname` (`lname`);
ALTER TABLE `address` ADD FULLTEXT KEY `idx_street` (`street`);

--
-- Indexes for table `app_data`
--
ALTER TABLE `app_data`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_appid` (`appid`),
  ADD KEY `idx_apptime` (`apptime`),
  ADD KEY `idx_runstatus` (`runstatus`),
  ADD KEY `idx_rep` (`rep`),
  ADD KEY `idx_setter` (`setter`),
  ADD KEY `idx_adminstatus` (`adminstatus`),
  ADD KEY `idx_pqid` (`pqid`),
  ADD KEY `idx_uid` (`uid`);

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

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

--
-- Indexes for table `dealmachine`
--
ALTER TABLE `dealmachine`
  ADD PRIMARY KEY (`id`),
  ADD KEY `aid` (`aid`);

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

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

--
-- Indexes for table `incoming_calls`
--
ALTER TABLE `incoming_calls`
  ADD PRIMARY KEY (`id`),
  ADD KEY `pqid` (`pqid`),
  ADD KEY `status` (`status`),
  ADD KEY `to_number` (`to_number`),
  ADD KEY `from_number` (`from_number`),
  ADD KEY `received_at` (`received_at`),
  ADD KEY `duration` (`duration`);

--
-- Indexes for table `incoming_messages`
--
ALTER TABLE `incoming_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `from_number` (`from_number`),
  ADD KEY `to_number` (`to_number`),
  ADD KEY `pqid` (`pqid`),
  ADD KEY `received_at` (`received_at`);

--
-- Indexes for table `jobs`
--
ALTER TABLE `jobs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status_retry_maxcreated` (`status`,`retry_count`,`max_retries`,`created_at`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_retry_count` (`retry_count`),
  ADD KEY `idx_max_retries` (`max_retries`),
  ADD KEY `idx_type` (`type`),
  ADD KEY `idx_status` (`status`);

--
-- Indexes for table `leadassignments`
--
ALTER TABLE `leadassignments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uid` (`uid`,`pqid`),
  ADD KEY `uid_2` (`uid`),
  ADD KEY `pqid` (`pqid`);

--
-- Indexes for table `notes`
--
ALTER TABLE `notes`
  ADD PRIMARY KEY (`id`),
  ADD KEY `aid` (`aid`),
  ADD KEY `uid` (`uid`);

--
-- Indexes for table `online_users`
--
ALTER TABLE `online_users`
  ADD PRIMARY KEY (`user_id`);

--
-- Indexes for table `outgoing_calls`
--
ALTER TABLE `outgoing_calls`
  ADD PRIMARY KEY (`id`),
  ADD KEY `from_number` (`from_number`),
  ADD KEY `to_number` (`to_number`),
  ADD KEY `status` (`status`),
  ADD KEY `timestamp` (`timestamp`),
  ADD KEY `pqid` (`pqid`),
  ADD KEY `uid` (`uid`),
  ADD KEY `duration` (`duration`);

--
-- Indexes for table `outgoing_messages`
--
ALTER TABLE `outgoing_messages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `from_number` (`from_number`,`to_number`,`sent_at`,`delivery_status`,`message_sid`,`date_updated`),
  ADD KEY `idx_delivery_status` (`delivery_status`),
  ADD KEY `idx_message_sid` (`message_sid`),
  ADD KEY `error_code` (`error_code`),
  ADD KEY `processed` (`processed`),
  ADD KEY `pqid` (`pqid`),
  ADD KEY `template` (`template`),
  ADD KEY `uid` (`uid`);

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

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

--
-- Indexes for table `phonequeue`
--
ALTER TABLE `phonequeue`
  ADD PRIMARY KEY (`id`),
  ADD KEY `aid` (`aid`),
  ADD KEY `phone1` (`phone1`),
  ADD KEY `phone2` (`phone2`),
  ADD KEY `phone3` (`phone3`),
  ADD KEY `step` (`step`),
  ADD KEY `added` (`added`,`updated`);

--
-- Indexes for table `seen_alerts`
--
ALTER TABLE `seen_alerts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `message_id` (`message_id`),
  ADD KEY `call_id` (`call_id`);

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

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

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

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

--
-- Indexes for table `twilio_numbers`
--
ALTER TABLE `twilio_numbers`
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `flag` (`flag`),
  ADD KEY `phone` (`phone`),
  ADD KEY `active` (`active`),
  ADD KEY `removed` (`removed`);

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

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD KEY `username_2` (`username`,`alias`),
  ADD KEY `phone` (`phone`);
ALTER TABLE `users` ADD FULLTEXT KEY `idx_alias_username` (`alias`,`username`);

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `address`
--
ALTER TABLE `address`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `app_data`
--
ALTER TABLE `app_data`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `autotexterrors`
--
ALTER TABLE `autotexterrors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `campaigns`
--
ALTER TABLE `campaigns`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `dnctriggers`
--
ALTER TABLE `dnctriggers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `genconf`
--
ALTER TABLE `genconf`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `incoming_calls`
--
ALTER TABLE `incoming_calls`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `incoming_messages`
--
ALTER TABLE `incoming_messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `jobs`
--
ALTER TABLE `jobs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `leadassignments`
--
ALTER TABLE `leadassignments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notes`
--
ALTER TABLE `notes`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `outgoing_calls`
--
ALTER TABLE `outgoing_calls`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `outgoing_messages`
--
ALTER TABLE `outgoing_messages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `phoneareaassoc`
--
ALTER TABLE `phoneareaassoc`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `phoneassoc`
--
ALTER TABLE `phoneassoc`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `phonequeue`
--
ALTER TABLE `phonequeue`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `seen_alerts`
--
ALTER TABLE `seen_alerts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sendtextlog`
--
ALTER TABLE `sendtextlog`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `steps`
--
ALTER TABLE `steps`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `syslog`
--
ALTER TABLE `syslog`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `templates`
--
ALTER TABLE `templates`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `twilio_numbers`
--
ALTER TABLE `twilio_numbers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `usercampaigns`
--
ALTER TABLE `usercampaigns`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `verticals`
--
ALTER TABLE `verticals`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `seen_alerts`
--
ALTER TABLE `seen_alerts`
  ADD CONSTRAINT `seen_alerts_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  ADD CONSTRAINT `seen_alerts_ibfk_2` FOREIGN KEY (`message_id`) REFERENCES `incoming_messages` (`id`),
  ADD CONSTRAINT `seen_alerts_ibfk_3` FOREIGN KEY (`call_id`) REFERENCES `incoming_calls` (`id`);`