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

START TRANSACTION;



--
-- Database: `healthcare`
--

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

--
-- Table structure for table `block_list`
--

CREATE TABLE `block_list` (
  `id` int NOT NULL,
  `phone_number` varchar(25) NOT NULL,
  `country_name` varchar(100) NOT NULL,
  `description` text NOT NULL,
  `entry_date_time` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

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

CREATE TABLE `call_logs` (
  `uniq` bigint UNSIGNED NOT NULL,
  `mainid` varchar(50) NOT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `twilio_call_id` varchar(64) DEFAULT NULL,
  `start_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `end_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `duration` int DEFAULT NULL,
  `status` varchar(20) DEFAULT NULL,
  `recordings` text,
  `ultravox_call_id` varchar(50) DEFAULT NULL,
  `plivo_call_id` varchar(50) DEFAULT NULL,
  `updated` varchar(50) NOT NULL DEFAULT 'no',
  `summary` text,
  `shortsummary` text,
  `campaign_id` varchar(50) DEFAULT NULL,
  `transcription` text,
  `list_id` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `agent_name` varchar(100) DEFAULT NULL,
  `bank_name` varchar(100) DEFAULT NULL,
  `due_amount` varchar(100) DEFAULT NULL,
  `months_due` varchar(100) DEFAULT NULL,
  `call_status` varchar(100) DEFAULT NULL,
  `hangup_cause` varchar(100) DEFAULT NULL,
  `recording_start_time` datetime DEFAULT NULL,
  `recording_end_time` datetime DEFAULT NULL,
  `processed_at` datetime DEFAULT NULL,
  `call_end_reason` varchar(50) DEFAULT NULL,
  `customer_response_received` enum('yes','no') DEFAULT 'no',
  `last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `customer_name` varchar(255) DEFAULT NULL,
  `verticaltype` varchar(100) DEFAULT NULL,
  `update_flag` enum('NEW','SENT','FAILED') DEFAULT 'NEW'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `campaign`
--

CREATE TABLE `campaign` (
  `id` int NOT NULL,
  `created_date` datetime NOT NULL,
  `campaign_id` varchar(100) NOT NULL,
  `campaign_name` varchar(100) NOT NULL,
  `calltype` varchar(100) DEFAULT NULL,
  `verticaltype` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `candidates`
--

CREATE TABLE `candidates` (
  `id` bigint UNSIGNED NOT NULL,
  `mainid` varchar(50) NOT NULL,
  `candidate_name` varchar(100) NOT NULL,
  `mobile_number` varchar(15) NOT NULL,
  `candidate_email` varchar(100) DEFAULT NULL,
  `position_name` varchar(100) DEFAULT NULL,
  `client_name` varchar(100) DEFAULT NULL,
  `skills_required` varchar(500) DEFAULT NULL,
  `total_experience` varchar(10) DEFAULT NULL,
  `relevant_experience` varchar(10) DEFAULT NULL,
  `highest_education` varchar(100) DEFAULT NULL,
  `current_company` varchar(100) DEFAULT NULL,
  `current_ctc` varchar(20) DEFAULT NULL,
  `expected_salary` varchar(20) DEFAULT NULL,
  `notice_period` varchar(10) DEFAULT NULL,
  `notice_period_comment` varchar(500) DEFAULT NULL,
  `interest` varchar(5) DEFAULT NULL,
  `available_for_interviews` varchar(5) DEFAULT NULL,
  `agent_remark` varchar(500) DEFAULT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `customer_responses`
--

CREATE TABLE `customer_responses` (
  `id` int NOT NULL,
  `mainid` varchar(255) NOT NULL,
  `sentiment_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `physical_health_response` text,
  `mental_wellbeing_response` text,
  `appointment_response` text,
  `medication_adherence_response` text,
  `insurance_billing_response` text,
  `post_care_response` text,
  `room_request_response` text,
  `other_notes` text,
  `urgency_level` enum('High','Medium','Low') NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `daywise_appointment_scheduled` text,
  `health_checkup_reminder_calls` text,
  `followup_calls_with_new_report` text,
  `what_happened_parameters` text,
  `feedback_response` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `verticaltype` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `feedback_responses`
--

CREATE TABLE `feedback_responses` (
  `id` int NOT NULL,
  `mainid` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `feedback_response` varchar(255) DEFAULT NULL,
  `feedback_rating` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `verticaltype` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `followup_responses`
--

CREATE TABLE `followup_responses` (
  `id` int NOT NULL,
  `mainid` varchar(255) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `followup_response` varchar(255) DEFAULT NULL,
  `medication_response` varchar(255) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `verticaltype` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `plivo_logs`
--

CREATE TABLE `plivo_logs` (
  `id` int NOT NULL,
  `ALegRequestUUID` varchar(50) NOT NULL,
  `ALegUUID` varchar(50) NOT NULL,
  `BillRate` decimal(10,5) DEFAULT NULL,
  `CallStatus` varchar(20) DEFAULT NULL,
  `CallUUID` varchar(50) NOT NULL,
  `CountryCode` varchar(10) DEFAULT NULL,
  `Direction` varchar(20) DEFAULT NULL,
  `Event` varchar(20) DEFAULT NULL,
  `FromNumber` varchar(20) DEFAULT NULL,
  `Machine` tinyint(1) DEFAULT NULL,
  `ParentAuthID` varchar(50) DEFAULT NULL,
  `RequestUUID` varchar(50) DEFAULT NULL,
  `RouteType` varchar(50) DEFAULT NULL,
  `STIRAttestation` varchar(50) DEFAULT NULL,
  `STIRVerification` varchar(50) DEFAULT NULL,
  `SessionStart` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  `AddTime` timestamp NULL DEFAULT NULL,
  `ConferenceName` varchar(100) DEFAULT NULL,
  `MonthlyRecordingStorageAmount` decimal(10,5) DEFAULT NULL,
  `RecordingDurationMs` decimal(12,5) DEFAULT NULL,
  `RecordingEndMs` bigint DEFAULT NULL,
  `RecordingFormat` varchar(10) DEFAULT NULL,
  `RecordingId` varchar(50) DEFAULT NULL,
  `RecordingStartMs` bigint DEFAULT NULL,
  `RecordingStorageDuration` bigint DEFAULT NULL,
  `RecordingStorageRate` decimal(10,5) DEFAULT NULL,
  `RecordingType` varchar(20) DEFAULT NULL,
  `RecordingUrl` text,
  `ResourceUri` text,
  `RoundedRecordingDuration` int DEFAULT NULL,
  `ToNumber` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `restricted_calls_log`
--

CREATE TABLE `restricted_calls_log` (
  `id` int NOT NULL,
  `list_id` varchar(500) DEFAULT NULL,
  `phone_number` varchar(20) DEFAULT NULL,
  `campaign_id` varchar(20) DEFAULT NULL,
  `status` varchar(100) NOT NULL,
  `run_time` datetime DEFAULT CURRENT_TIMESTAMP,
  `voicebot_listID` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

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

CREATE TABLE `ultravox_logs` (
  `callId` varchar(50) NOT NULL,
  `clientVersion` varchar(50) DEFAULT NULL,
  `created` timestamp NULL DEFAULT NULL,
  `joined` timestamp NULL DEFAULT NULL,
  `ended` timestamp NULL DEFAULT NULL,
  `endReason` varchar(20) DEFAULT NULL,
  `firstSpeaker` varchar(30) DEFAULT NULL,
  `firstSpeakerAgent` json DEFAULT NULL,
  `inactivityMessages` json DEFAULT NULL,
  `initialOutputMedium` varchar(30) DEFAULT NULL,
  `joinTimeout` varchar(10) DEFAULT NULL,
  `joinUrl` varchar(255) DEFAULT NULL,
  `languageHint` varchar(50) DEFAULT NULL,
  `maxDuration` varchar(10) DEFAULT NULL,
  `medium` varchar(50) DEFAULT NULL,
  `model` varchar(50) DEFAULT NULL,
  `recordingEnabled` tinyint(1) DEFAULT NULL,
  `systemPrompt` text,
  `temperature` float DEFAULT NULL,
  `timeExceededMessage` varchar(255) DEFAULT NULL,
  `voice` varchar(50) DEFAULT NULL,
  `transcriptOptional` varchar(250) DEFAULT NULL,
  `errorCount` int DEFAULT NULL,
  `vadSettings` json DEFAULT NULL,
  `shortSummary` text,
  `summary` text,
  `experimentalSettings` json DEFAULT NULL,
  `metadata` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `voicebot_list`
--

CREATE TABLE `voicebot_list` (
  `id` int NOT NULL,
  `created_date` datetime DEFAULT NULL,
  `list_id` varchar(30) NOT NULL,
  `list_name` varchar(30) NOT NULL,
  `status` enum('OPEN','NEW','SENT','DIALED','FAILED','Restricted') CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT 'OPEN',
  `phone_number` varchar(30) NOT NULL,
  `no_calls` varchar(3) DEFAULT NULL,
  `campaign_id` varchar(100) NOT NULL,
  `customer_name` varchar(100) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL,
  `calltype` varchar(100) DEFAULT NULL,
  `verticaltype` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `call_logs`
--
ALTER TABLE `call_logs`
  ADD PRIMARY KEY (`uniq`),
  ADD UNIQUE KEY `uniq` (`uniq`),
  ADD KEY `mainid` (`mainid`,`start_time`,`end_time`,`campaign_id`),
  ADD KEY `ultravox_call_id` (`ultravox_call_id`,`plivo_call_id`,`updated`),
  ADD KEY `idx_call_logs_start_time` (`start_time`),
  ADD KEY `idx_call_logs_phone` (`phone_number`),
  ADD KEY `idx_call_logs_phone_number` (`phone_number`),
  ADD KEY `idx_call_logs_mainid` (`mainid`);

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

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

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

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

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

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

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

--
-- Indexes for table `ultravox_logs`
--
ALTER TABLE `ultravox_logs`
  ADD PRIMARY KEY (`callId`);

--
-- Indexes for table `voicebot_list`
--
ALTER TABLE `voicebot_list`
  ADD PRIMARY KEY (`id`),
  ADD KEY `phone_number` (`phone_number`);

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `call_logs`
--
ALTER TABLE `call_logs`
  MODIFY `uniq` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

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

--
-- AUTO_INCREMENT for table `candidates`
--
ALTER TABLE `candidates`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

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

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

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

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

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

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

