-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `helthcare_inc`
--

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

--
-- Table structure for table `call_logs`
--

CREATE TABLE `call_logs` (
  `uniq` varchar(100) NOT NULL DEFAULT (uuid()),
  `mainid` varchar(100) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `duration` int DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `call_status` varchar(50) DEFAULT NULL,
  `hangup_cause` varchar(100) DEFAULT NULL,
  `call_end_reason` varchar(100) DEFAULT NULL,
  `recordings` text,
  `ultravox_call_id` varchar(100) DEFAULT NULL,
  `twilio_call_id` varchar(100) DEFAULT NULL,
  `plivo_call_id` varchar(100) DEFAULT NULL,
  `transcription` longtext,
  `summary` text,
  `shortsummary` text,
  `campaign_id` varchar(100) DEFAULT NULL,
  `list_id` varchar(100) DEFAULT NULL,
  `agent_name` varchar(255) DEFAULT NULL,
  `bank_name` varchar(255) DEFAULT NULL,
  `due_amount` decimal(10,2) DEFAULT NULL,
  `months_due` int DEFAULT NULL,
  `customer_response_received` tinyint(1) DEFAULT '0',
  `recording_start_time` datetime DEFAULT NULL,
  `recording_end_time` datetime DEFAULT NULL,
  `updated` tinyint(1) DEFAULT '0',
  `processed_at` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_logs_recordings`
--

CREATE TABLE `call_logs_recordings` (
  `sid` varchar(64) NOT NULL,
  `from_number` varchar(20) DEFAULT NULL,
  `to_number` varchar(20) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `duration` varchar(10) DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `recording_url` varchar(255) DEFAULT NULL,
  `recording_path` varchar(255) DEFAULT NULL,
  `transcriptionText` longtext
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_logs_recordings_tellink`
--

CREATE TABLE `call_logs_recordings_tellink` (
  `sid` varchar(64) NOT NULL,
  `account_sid` varchar(64) DEFAULT NULL,
  `from_number` varchar(32) DEFAULT NULL,
  `to_number` varchar(32) DEFAULT NULL,
  `caller_name` varchar(255) DEFAULT NULL,
  `status` varchar(32) DEFAULT NULL,
  `direction` varchar(32) DEFAULT NULL,
  `duration` int DEFAULT NULL,
  `price` decimal(10,5) DEFAULT NULL,
  `price_unit` varchar(10) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `answered_by` varchar(64) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_updated` datetime DEFAULT NULL,
  `recording_url` text,
  `recording_path` text,
  `uri` text,
  `subresource_uris` text,
  `transcriptionText` longtext,
  `ticket_generated` enum('yes','no') DEFAULT 'no',
  `call_id` varchar(255) DEFAULT NULL,
  `summary` text,
  `short_summary` text
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_summaries`
--

CREATE TABLE `call_summaries` (
  `call_id` varchar(255) NOT NULL,
  `summary` text,
  `short_summary` text,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_ticket_logs`
--

CREATE TABLE `call_ticket_logs` (
  `id` int NOT NULL,
  `call_id` varchar(255) DEFAULT NULL,
  `caller_name` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `business_name` varchar(255) DEFAULT NULL,
  `site_address` text,
  `caller_number` varchar(20) DEFAULT NULL,
  `dialed_number` varchar(20) DEFAULT NULL,
  `issue_description` text,
  `conversation_summary` text,
  `call_start_time` datetime DEFAULT NULL,
  `call_type` enum('Inbound','Outbound') DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `ticket_generated` enum('yes','no') DEFAULT 'no',
  `zendesk_ticket_id` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `processed_at` timestamp NULL DEFAULT NULL,
  `error_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `ticket_body` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_tracking`
--

CREATE TABLE `call_tracking` (
  `id` int NOT NULL,
  `call_id` varchar(255) NOT NULL,
  `twilio_sid` varchar(255) DEFAULT NULL,
  `transcript` text,
  `ticket_generated` varchar(3) DEFAULT 'no',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_transcripts`
--

CREATE TABLE `call_transcripts` (
  `id` int NOT NULL,
  `twilioCallSid` varchar(255) NOT NULL,
  `ultravoxCallId` varchar(255) DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  `speaker` varchar(50) DEFAULT NULL,
  `text` text,
  `createdAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_transfers`
--

CREATE TABLE `call_transfers` (
  `id` int NOT NULL,
  `twilioCallSid` varchar(255) NOT NULL,
  `ultravoxCallId` varchar(255) DEFAULT NULL,
  `fromDepartment` varchar(50) DEFAULT NULL,
  `toDepartment` varchar(50) NOT NULL,
  `transferPhoneNumber` varchar(50) DEFAULT NULL,
  `transferStatus` varchar(50) DEFAULT NULL,
  `transferStartTime` datetime DEFAULT NULL,
  `transferEndTime` datetime DEFAULT NULL,
  `transferDuration` int DEFAULT NULL,
  `createdAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `monthly_call_summary_telelink`
--

CREATE TABLE `monthly_call_summary_telelink` (
  `id` int NOT NULL,
  `month_year` varchar(7) NOT NULL,
  `total_duration` int DEFAULT '0',
  `is_completed` enum('yes','no') DEFAULT 'no',
  `updated_at` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `twilio_calls`
--

CREATE TABLE `twilio_calls` (
  `id` int NOT NULL,
  `twilioCallSid` varchar(255) NOT NULL,
  `ultravoxCallId` varchar(255) DEFAULT NULL,
  `phoneNumber` varchar(50) DEFAULT NULL,
  `status` varchar(50) NOT NULL,
  `intent` varchar(50) DEFAULT NULL,
  `afterHours` tinyint(1) DEFAULT NULL,
  `startTime` datetime DEFAULT NULL,
  `transferTime` datetime DEFAULT NULL,
  `transferTarget` varchar(50) DEFAULT NULL,
  `transferCompletionTime` datetime DEFAULT NULL,
  `endTime` datetime DEFAULT NULL,
  `duration` int DEFAULT NULL,
  `recordingUrl` text,
  `createdAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `ultravox_logs`
--

CREATE TABLE `ultravox_logs` (
  `id` int NOT NULL,
  `call_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `from_number` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `client_version` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `joined` datetime DEFAULT NULL,
  `ended` datetime DEFAULT NULL,
  `end_reason` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_speaker` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_speaker_agent` tinyint(1) DEFAULT '0',
  `inactivity_messages` json DEFAULT NULL,
  `initial_output_medium` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `join_timeout` int DEFAULT NULL,
  `join_url` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `language_hint` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `max_duration` int DEFAULT NULL,
  `medium` json DEFAULT NULL,
  `model` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `recording_enabled` tinyint(1) DEFAULT '0',
  `system_prompt` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `temperature` decimal(3,2) DEFAULT NULL,
  `time_exceeded_message` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `voice` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `transcript_optional` tinyint(1) DEFAULT '0',
  `error_count` int DEFAULT '0',
  `vad_settings` json DEFAULT NULL,
  `short_summary` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `summary` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `experimental_settings` json DEFAULT NULL,
  `metadata` json DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `call_logs`
--
ALTER TABLE `call_logs`
  ADD PRIMARY KEY (`uniq`);

--
-- Indexes for table `call_logs_recordings`
--
ALTER TABLE `call_logs_recordings`
  ADD PRIMARY KEY (`sid`);

--
-- Indexes for table `call_logs_recordings_tellink`
--
ALTER TABLE `call_logs_recordings_tellink`
  ADD PRIMARY KEY (`sid`);

--
-- Indexes for table `call_summaries`
--
ALTER TABLE `call_summaries`
  ADD PRIMARY KEY (`call_id`);

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

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

--
-- Indexes for table `call_transcripts`
--
ALTER TABLE `call_transcripts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_twilioCallSid` (`twilioCallSid`),
  ADD KEY `idx_ultravoxCallId` (`ultravoxCallId`);

--
-- Indexes for table `call_transfers`
--
ALTER TABLE `call_transfers`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_twilioCallSid` (`twilioCallSid`),
  ADD KEY `idx_ultravoxCallId` (`ultravoxCallId`);

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

--
-- Indexes for table `twilio_calls`
--
ALTER TABLE `twilio_calls`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_twilioCallSid` (`twilioCallSid`),
  ADD KEY `idx_ultravoxCallId` (`ultravoxCallId`);

--
-- Indexes for table `ultravox_logs`
--
ALTER TABLE `ultravox_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_call_id` (`call_id`),
  ADD KEY `idx_from_number` (`from_number`),
  ADD KEY `idx_created` (`created`),
  ADD KEY `idx_ended` (`ended`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

--
-- AUTO_INCREMENT for table `ultravox_logs`
--
ALTER TABLE `ultravox_logs`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;
COMMIT;

