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

START TRANSACTION;



--
-- Database: `fluten_dev`
--

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

--
-- Stand-in structure for view `all_companies_call_summary`
-- (See below for the actual view)
--
CREATE TABLE `all_companies_call_summary` (
`user_id` bigint unsigned
,`user` varchar(255)
,`company_id` bigint unsigned
,`company_name` varchar(255)
,`date(call_date)` date
,`vapi_call_id` varchar(250)
,`call_duration` decimal(5,2)
,`cost` decimal(6,5)
,`end_reason` varchar(250)
,`call_date` datetime
);

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

--
-- Table structure for table `assistant_persona`
--

CREATE TABLE `assistant_persona` (
  `id` int NOT NULL,
  `name` varchar(250) NOT NULL,
  `gender` enum('M','F') NOT NULL,
  `persona_summary` text NOT NULL,
  `persona_prompt` text NOT NULL,
  `jobs_to_do` mediumtext,
  `first_message` tinytext,
  `end_call_phrases` tinytext,
  `end_call_message` tinytext,
  `background_sound` varchar(250) DEFAULT NULL,
  `avatar` varchar(250) NOT NULL,
  `status` varchar(2) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `bookings`
--

CREATE TABLE `bookings` (
  `id` int NOT NULL,
  `vapi_call_id` varchar(250) NOT NULL,
  `company_id` int NOT NULL,
  `bot_id` int NOT NULL,
  `booking_date` datetime NOT NULL,
  `customer_name` varchar(250) NOT NULL,
  `customer_phone` varchar(250) NOT NULL,
  `other_params` varchar(250) NOT NULL,
  `confirmed` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Stand-in structure for view `booking_calls`
-- (See below for the actual view)
--
CREATE TABLE `booking_calls` (
`id` int
,`vapi_call_id` varchar(250)
,`company_id` int
,`bot_id` int
,`booking_date` datetime
,`customer_name` varchar(250)
,`customer_phone` varchar(250)
,`other_params` varchar(250)
,`confirmed` tinyint(1)
,`created_at` datetime
,`updated_at` datetime
,`call_date` datetime
,`bot_name` varchar(250)
);

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

--
-- Table structure for table `bots`
--

CREATE TABLE `bots` (
  `id` int NOT NULL,
  `language` varchar(250) DEFAULT NULL,
  `company_id` int NOT NULL,
  `bot_name` varchar(250) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `purpose` enum('Incoming','Outgoing','Scheduling','Others') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `bot_structure` text NOT NULL,
  `prompt` text,
  `first_message` tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  `end_call_message` tinytext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  `end_call_phrases` mediumtext CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci,
  `background_sound` enum('off','office') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `forwardings` text,
  `forwarding_prompt` text,
  `picture` varchar(250) DEFAULT NULL,
  `keywords` text,
  `status` enum('active','inactive') NOT NULL,
  `voice_id` varchar(250) DEFAULT NULL,
  `created_at` date NOT NULL,
  `updated_at` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `bot_options`
--

CREATE TABLE `bot_options` (
  `id` int NOT NULL,
  `title` varchar(250) NOT NULL,
  `options` tinytext NOT NULL,
  `status` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Stand-in structure for view `bot_phone`
-- (See below for the actual view)
--
CREATE TABLE `bot_phone` (
`bot_id` int
,`bot_name` varchar(250)
,`gender` varchar(1)
,`purpose` enum('Incoming','Outgoing','Scheduling','Others')
,`bot_structure` text
,`forwardings` text
,`prompt` text
,`first_message` tinytext
,`end_call_message` tinytext
,`end_call_phrases` mediumtext
,`background_sound` enum('off','office')
,`forwarding_prompt` text
,`company_id` int
,`phone_number` varchar(250)
,`vapi_phone_id` varchar(250)
,`provider` enum('Tw','Vo')
,`org_id` varchar(250)
,`server_url` tinytext
);

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

--
-- Table structure for table `calls_server_log`
--

CREATE TABLE `calls_server_log` (
  `id` int NOT NULL,
  `log_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `log` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `call_log`
--

CREATE TABLE `call_log` (
  `id` int NOT NULL,
  `vapi_call_id` varchar(250) DEFAULT NULL,
  `twilio_call_id` varchar(250) DEFAULT NULL,
  `company_id` int NOT NULL,
  `bot_id` int NOT NULL,
  `call_date` datetime NOT NULL,
  `vapi_phone_id` varchar(250) DEFAULT NULL,
  `call_raw` text NOT NULL,
  `transcript` text NOT NULL,
  `summary` mediumtext NOT NULL,
  `customer_number` varchar(250) NOT NULL,
  `call_duration` decimal(5,2) NOT NULL,
  `end_reason` varchar(250) NOT NULL,
  `cost` decimal(6,5) NOT NULL,
  `is_new` enum('Y','N') NOT NULL DEFAULT 'N',
  `tw_call_cost` decimal(6,5) DEFAULT NULL,
  `vapi_call_cost` decimal(6,5) DEFAULT NULL,
  `recording_url` varchar(250) NOT NULL,
  `call_type` varchar(250) NOT NULL,
  `avg_sentiment` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `sentiment_score` decimal(15,10) DEFAULT NULL,
  `department_check` int DEFAULT NULL,
  `analysis` text,
  `off_time` int DEFAULT '1',
  `off_time_reason` varchar(250) DEFAULT NULL,
  `created_at` date NOT NULL,
  `updated_at` date NOT NULL,
  `status` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

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

CREATE TABLE `campaigns` (
  `id` int NOT NULL,
  `campaign_name` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `setup_assistant_detail` text COLLATE utf8mb4_general_ci,
  `status` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `response_detail` text COLLATE utf8mb4_general_ci,
  `bot_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `campaign_customer_response`
--

CREATE TABLE `campaign_customer_response` (
  `id` int NOT NULL,
  `campaign_id` int NOT NULL,
  `customer_phone_number` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `transcript` text COLLATE utf8mb4_general_ci,
  `audio_recording` text COLLATE utf8mb4_general_ci,
  `custome_response` text COLLATE utf8mb4_general_ci,
  `extra` text COLLATE utf8mb4_general_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `campaign_phone_numbers`
--

CREATE TABLE `campaign_phone_numbers` (
  `id` int NOT NULL,
  `name` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `phone` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `campaign_id` int DEFAULT NULL,
  `status` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `child_calls`
--

CREATE TABLE `child_calls` (
  `id` bigint UNSIGNED NOT NULL,
  `parentCallSid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `parent_call_log_id` int NOT NULL,
  `phone_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `company_id` int UNSIGNED NOT NULL,
  `bot_id` int UNSIGNED NOT NULL,
  `audio_url` text COLLATE utf8mb4_unicode_ci,
  `transcript` text COLLATE utf8mb4_unicode_ci,
  `keyword_count` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `cost` float DEFAULT NULL,
  `duration` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Stand-in structure for view `child_call_status`
-- (See below for the actual view)
--
CREATE TABLE `child_call_status` (
`call_date` datetime
,`staff_id` bigint unsigned
,`trf_to` varchar(255)
,`trf_person` varchar(255)
,`department_id` int
,`department` varchar(250)
,`status` varchar(255)
,`company_id` int unsigned
,`bot` varchar(250)
,`customer_number` varchar(250)
,`recording_url` varchar(250)
);

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

--
-- Table structure for table `closed_company`
--

CREATE TABLE `closed_company` (
  `id` int NOT NULL,
  `company_id` int DEFAULT NULL,
  `package_id` int DEFAULT NULL,
  `cancelling_reason` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `suggestions` text COLLATE utf8mb4_general_ci,
  `wiped_off` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `companies`
--

CREATE TABLE `companies` (
  `id` bigint UNSIGNED NOT NULL,
  `user_id` int NOT NULL,
  `company_reg_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` int NOT NULL,
  `upload_logo` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `introduction` text COLLATE utf8mb4_unicode_ci,
  `products_and_services` text COLLATE utf8mb4_unicode_ci,
  `website` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `address2` tinytext COLLATE utf8mb4_unicode_ci,
  `location` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `zip` int DEFAULT NULL,
  `country` int DEFAULT NULL,
  `official_phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bussiness_size` int DEFAULT NULL,
  `lang` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `companies_old_structure_bkp`
--

CREATE TABLE `companies_old_structure_bkp` (
  `id` bigint UNSIGNED NOT NULL DEFAULT '0',
  `user_id` int NOT NULL,
  `company_reg_number` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `company_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` int NOT NULL,
  `upload_logo` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `introduction` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `products_and_services` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `website` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `info` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `address2` tinytext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `city` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `state` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `zip` int DEFAULT NULL,
  `country` int DEFAULT NULL,
  `official_phone` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `package` int DEFAULT NULL,
  `package_start` datetime DEFAULT NULL,
  `package_end` datetime DEFAULT NULL,
  `last_invoice_date` datetime DEFAULT NULL,
  `last_invoice_link` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_amount` decimal(10,5) DEFAULT NULL,
  `current_balance` decimal(8,6) DEFAULT NULL,
  `min_balance` decimal(6,3) NOT NULL,
  `status` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `company_bookings`
--

CREATE TABLE `company_bookings` (
  `id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `company_id` bigint NOT NULL,
  `meeting_id` varchar(50) NOT NULL,
  `name` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `email` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `phone_number` varchar(100) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `time_zone` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `cancel_reason` text,
  `status` tinyint NOT NULL DEFAULT '1' COMMENT '1: active, 2: inactive, 3: cancelled',
  `gcalender_event_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Stand-in structure for view `company_calls_log`
-- (See below for the actual view)
--
CREATE TABLE `company_calls_log` (
`company_id` bigint unsigned
,`company_name` varchar(255)
,`bot_id` int
,`bot_name` varchar(250)
,`log_id` int
,`call_date` datetime
,`customer_number` varchar(250)
,`call_duration` decimal(5,2)
,`call_type` varchar(250)
,`end_reason` varchar(250)
,`cost` decimal(6,5)
,`summary` mediumtext
,`recording` varchar(250)
);

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

--
-- Table structure for table `company_contacts`
--

CREATE TABLE `company_contacts` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `person_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `person_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `company_package_payment`
--

CREATE TABLE `company_package_payment` (
  `id` int NOT NULL,
  `company_id` int DEFAULT NULL,
  `package` int DEFAULT NULL,
  `stripe_subscription_id` varchar(200) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `package_start` date DEFAULT NULL,
  `package_end` date DEFAULT NULL,
  `last_invoice_date` datetime DEFAULT NULL,
  `last_amount` decimal(10,5) DEFAULT NULL,
  `current_balance` decimal(10,5) DEFAULT NULL,
  `min_balance` int DEFAULT '10',
  `last_payment_date` timestamp NULL DEFAULT NULL,
  `last_invoice_link` text COLLATE utf8mb4_general_ci,
  `subscribed_at` datetime DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `company_phone`
--

CREATE TABLE `company_phone` (
  `id` int NOT NULL,
  `name` varchar(250) DEFAULT NULL,
  `company_id` int NOT NULL,
  `bot_id` int DEFAULT NULL,
  `provider` enum('Tw','Vo') NOT NULL,
  `phone_number` varchar(250) NOT NULL,
  `provider_keys` tinytext NOT NULL,
  `org_id` varchar(250) NOT NULL,
  `vapi_phone_id` varchar(250) NOT NULL,
  `serverUrl` tinytext NOT NULL,
  `active_status` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT 'occupied',
  `created_at` date NOT NULL,
  `updated_at` date NOT NULL,
  `status` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `company_schedule`
--

CREATE TABLE `company_schedule` (
  `id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `company_id` bigint NOT NULL,
  `bot_id` bigint NOT NULL,
  `slot_size` int NOT NULL DEFAULT '0',
  `prompt` text,
  `notifications` text,
  `schedule` text NOT NULL,
  `overrides` text,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `company_settings`
--

CREATE TABLE `company_settings` (
  `id` int NOT NULL,
  `company_id` int NOT NULL,
  `setting` varchar(250) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Stand-in structure for view `company_summary`
-- (See below for the actual view)
--
CREATE TABLE `company_summary` (
`bot_id` int
,`bot_name` varchar(250)
,`phone_number` varchar(250)
,`total_calls` bigint
,`company_id` bigint unsigned
,`call_type` varchar(250)
,`end_reason` varchar(250)
);

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

--
-- Table structure for table `company_type`
--

CREATE TABLE `company_type` (
  `id` int NOT NULL,
  `type` varchar(250) NOT NULL,
  `status` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `country`
--

CREATE TABLE `country` (
  `id` int NOT NULL,
  `iso` char(2) NOT NULL,
  `name` varchar(80) NOT NULL,
  `nicename` varchar(80) NOT NULL,
  `iso3` char(3) DEFAULT NULL,
  `numcode` smallint DEFAULT NULL,
  `phonecode` int NOT NULL,
  `seq` int NOT NULL,
  `enable` int NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int NOT NULL,
  `company_id` int NOT NULL,
  `full_name` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `email` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `phone` varchar(250) NOT NULL,
  `bot_by` int NOT NULL,
  `last_call_id` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `last_convo` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
  `last_convo_date` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `departments`
--

CREATE TABLE `departments` (
  `id` int NOT NULL,
  `name` varchar(250) NOT NULL,
  `company_id` int NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Stand-in structure for view `department_wise_directory`
-- (See below for the actual view)
--
CREATE TABLE `department_wise_directory` (
`employee_id` bigint unsigned
,`title` varchar(255)
,`phone_number` varchar(255)
,`department_id` int
,`department` varchar(250)
,`company_id` bigint unsigned
);

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

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint UNSIGNED NOT NULL,
  `uuid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` int NOT NULL,
  `invoice_no` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `company_id` int NOT NULL,
  `amount` decimal(8,5) NOT NULL,
  `description` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `Stripe_txn_id` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `package_name` int DEFAULT NULL,
  `package_id` int DEFAULT NULL,
  `invoice_link` text COLLATE utf8mb4_general_ci,
  `status` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `kb`
--

CREATE TABLE `kb` (
  `id` int NOT NULL,
  `company_id` int NOT NULL,
  `bot_id` int NOT NULL,
  `file_name` varchar(250) NOT NULL,
  `description` text NOT NULL,
  `vapi_file_id` varchar(250) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Stand-in structure for view `keywords_calls_report_data`
-- (See below for the actual view)
--
CREATE TABLE `keywords_calls_report_data` (
`call_date` datetime
,`keyword` varchar(250)
,`frequency` decimal(32,0)
,`company_id` int
,`bot_name` varchar(250)
,`end_reason` varchar(250)
,`recording_url` varchar(250)
,`customer_name` varchar(250)
,`call_id` int
);

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

--
-- Table structure for table `keyword_count`
--

CREATE TABLE `keyword_count` (
  `id` int NOT NULL,
  `company_id` int NOT NULL,
  `bot_id` int NOT NULL,
  `call_date` datetime NOT NULL,
  `keyword` varchar(250) NOT NULL,
  `frequency` int NOT NULL,
  `call_id` varchar(256) NOT NULL,
  `call_log_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int UNSIGNED NOT NULL,
  `migration` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int NOT NULL,
  `company_id` int NOT NULL,
  `bot_id` int DEFAULT NULL,
  `message` text NOT NULL,
  `status` tinyint(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `offline_messages`
--

CREATE TABLE `offline_messages` (
  `id` int NOT NULL,
  `user_id` bigint NOT NULL,
  `company_id` int NOT NULL,
  `message` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `type` enum('message','interest') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `company_phone` varchar(50) NOT NULL,
  `customer_number` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `vapi_call_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `twilio_call_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `bot_id` int NOT NULL,
  `vapi_phone_id` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
  `created_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `operational_settings`
--

CREATE TABLE `operational_settings` (
  `id` int NOT NULL,
  `company_id` int NOT NULL,
  `office_start_time` time DEFAULT NULL,
  `office_end_time` time DEFAULT NULL,
  `time_zone` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `working_weekend` int DEFAULT NULL COMMENT 'weekend on 1, off 0',
  `message_not_available` text COLLATE utf8mb4_general_ci,
  `weekly_status_update` int DEFAULT NULL COMMENT 'weekend updates on 1, off 0',
  `status` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `outbound_call_list`
--

CREATE TABLE `outbound_call_list` (
  `id` int NOT NULL,
  `list_id` int NOT NULL,
  `phone_number` varchar(250) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `status` enum('open','called') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `outbound_call_request`
--

CREATE TABLE `outbound_call_request` (
  `id` int NOT NULL,
  `ip` varchar(250) NOT NULL,
  `phone_number` varchar(250) NOT NULL,
  `call_count` int NOT NULL,
  `status` enum('blocked','open','unlimited','five') NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `outbound_phone_list`
--

CREATE TABLE `outbound_phone_list` (
  `id` int NOT NULL,
  `number` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `number_from` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `packages`
--

CREATE TABLE `packages` (
  `id` int NOT NULL,
  `package_name` varchar(250) NOT NULL,
  `pack_code` varchar(15) DEFAULT NULL,
  `is_default` int DEFAULT NULL,
  `bots` int NOT NULL,
  `purpose` varchar(250) NOT NULL,
  `price_monthly` int NOT NULL,
  `price_yearly` int NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `password_reset_tokens`
--

CREATE TABLE `password_reset_tokens` (
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `personal_access_tokens`
--

CREATE TABLE `personal_access_tokens` (
  `id` bigint UNSIGNED NOT NULL,
  `tokenable_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `tokenable_id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `token` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `abilities` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `last_used_at` timestamp NULL DEFAULT NULL,
  `expires_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `phone_directory`
--

CREATE TABLE `phone_directory` (
  `id` bigint UNSIGNED NOT NULL,
  `company_id` bigint UNSIGNED NOT NULL,
  `department_id` int DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `phone_number` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `fallback` int NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `preCastBots`
--

CREATE TABLE `preCastBots` (
  `id` int NOT NULL,
  `language` varchar(250) DEFAULT NULL,
  `bot_name` varchar(250) NOT NULL,
  `gender` varchar(1) NOT NULL,
  `purpose` enum('Incoming','Outgoing','Scheduling','Others') NOT NULL,
  `bot_structure` text NOT NULL,
  `prompt` text,
  `first_message` tinytext,
  `end_call_message` tinytext,
  `end_call_phrases` mediumtext,
  `background_sound` enum('off','office') NOT NULL,
  `picture` varchar(250) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL,
  `keywords` text,
  `voice_id` varchar(250) DEFAULT NULL,
  `description` varchar(250) DEFAULT NULL,
  `voice_url` text,
  `created_at` date NOT NULL,
  `updated_at` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `precast_industry`
--

CREATE TABLE `precast_industry` (
  `id` int NOT NULL,
  `bot_id` int NOT NULL,
  `company_type_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `promotions`
--

CREATE TABLE `promotions` (
  `id` int NOT NULL,
  `comapny_id` int NOT NULL,
  `title` tinytext NOT NULL,
  `content` tinytext NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `opt_code` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `states`
--

CREATE TABLE `states` (
  `id` int NOT NULL,
  `code` char(2) NOT NULL DEFAULT '',
  `name` varchar(128) NOT NULL DEFAULT '',
  `country_id` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

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

--
-- Table structure for table `system_settings`
--

CREATE TABLE `system_settings` (
  `id` int NOT NULL,
  `name` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `description` text COLLATE utf8mb4_general_ci,
  `value` text COLLATE utf8mb4_general_ci NOT NULL,
  `status` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'active',
  `is_encrypted` enum('Y','N') COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'N',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` int NOT NULL,
  `company_id` int NOT NULL,
  `invoice_no` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `txn_date` datetime DEFAULT NULL,
  `status` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `description` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `Stripe_txn_id` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `package_id` int DEFAULT NULL,
  `package_name` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pasckage_name` varchar(250) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `pasckage_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `users` (
  `id` bigint UNSIGNED NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `email_verified_at` timestamp NULL DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `remember_token` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `otp` int DEFAULT NULL,
  `otp_generate` timestamp NULL DEFAULT NULL,
  `role` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'User',
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `companyId` int DEFAULT NULL,
  `steps` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `current_position` varchar(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `hear_about` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `updates_system` int DEFAULT '1',
  `stripe_customer_id` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_id` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stripe_payment_method` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `google_id` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile` varchar(250) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `default_company` bigint DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `user_calender_tokens`
--

CREATE TABLE `user_calender_tokens` (
  `id` bigint NOT NULL,
  `user_id` bigint NOT NULL,
  `provider` enum('google','outlook') CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `provider_account_id` varchar(150) DEFAULT NULL,
  `access_token` varchar(255) NOT NULL,
  `refresh_token` varchar(255) DEFAULT NULL,
  `token_expiry` datetime DEFAULT NULL,
  `scope` text,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

--
-- Table structure for table `voices`
--

CREATE TABLE `voices` (
  `id` int NOT NULL,
  `name` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `language` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `provider` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `provider_id` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `audio_file` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `status` varchar(250) COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Structure for view `all_companies_call_summary`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `all_companies_call_summary`  AS SELECT `u`.`id` AS `user_id`, `u`.`name` AS `user`, `c`.`id` AS `company_id`, `c`.`company_name` AS `company_name`, cast(`cl`.`call_date` as date) AS `date(call_date)`, `cl`.`vapi_call_id` AS `vapi_call_id`, `cl`.`call_duration` AS `call_duration`, `cl`.`cost` AS `cost`, `cl`.`end_reason` AS `end_reason`, `cl`.`call_date` AS `call_date` FROM ((`users` `u` join `companies` `c` on((`u`.`id` = `c`.`user_id`))) join `call_log` `cl` on((`c`.`id` = `cl`.`company_id`))) ;

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

--
-- Structure for view `booking_calls`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `booking_calls`  AS SELECT `b`.`id` AS `id`, `b`.`vapi_call_id` AS `vapi_call_id`, `b`.`company_id` AS `company_id`, `b`.`bot_id` AS `bot_id`, `b`.`booking_date` AS `booking_date`, `b`.`customer_name` AS `customer_name`, `b`.`customer_phone` AS `customer_phone`, `b`.`other_params` AS `other_params`, `b`.`confirmed` AS `confirmed`, `b`.`created_at` AS `created_at`, `b`.`updated_at` AS `updated_at`, `c`.`call_date` AS `call_date`, `t`.`bot_name` AS `bot_name` FROM ((`bookings` `b` join `call_log` `c` on((`b`.`vapi_call_id` = `c`.`vapi_call_id`))) join `bots` `t` on((`b`.`bot_id` = `t`.`id`))) ;

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

--
-- Structure for view `bot_phone`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `bot_phone`  AS SELECT `b`.`id` AS `bot_id`, `b`.`bot_name` AS `bot_name`, `b`.`gender` AS `gender`, `b`.`purpose` AS `purpose`, `b`.`bot_structure` AS `bot_structure`, `b`.`forwardings` AS `forwardings`, `b`.`prompt` AS `prompt`, `b`.`first_message` AS `first_message`, `b`.`end_call_message` AS `end_call_message`, `b`.`end_call_phrases` AS `end_call_phrases`, `b`.`background_sound` AS `background_sound`, `b`.`forwarding_prompt` AS `forwarding_prompt`, `c`.`company_id` AS `company_id`, `c`.`phone_number` AS `phone_number`, `c`.`vapi_phone_id` AS `vapi_phone_id`, `c`.`provider` AS `provider`, `c`.`org_id` AS `org_id`, `c`.`serverUrl` AS `server_url` FROM (`bots` `b` join `company_phone` `c` on((`b`.`id` = `c`.`bot_id`))) WHERE (0 <> 1) ;

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

--
-- Structure for view `child_call_status`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`jack`@`%` SQL SECURITY DEFINER VIEW `child_call_status`  AS SELECT `cg`.`call_date` AS `call_date`, `dr`.`id` AS `staff_id`, `cl`.`phone_number` AS `trf_to`, `dr`.`title` AS `trf_person`, `dp`.`id` AS `department_id`, `dp`.`name` AS `department`, `cl`.`status` AS `status`, `cl`.`company_id` AS `company_id`, `bt`.`bot_name` AS `bot`, `cg`.`customer_number` AS `customer_number`, `cg`.`recording_url` AS `recording_url` FROM (((((`child_calls` `cl` join `call_log` `cg` on((`cl`.`parent_call_log_id` = `cg`.`id`))) join `phone_directory` `dr` on((`cl`.`phone_number` = `dr`.`phone_number`))) left join `customers` `ct` on(((`cg`.`customer_number` = `ct`.`phone`) and (`ct`.`company_id` = `cg`.`company_id`)))) join `departments` `dp` on(((`dp`.`id` = `dr`.`department_id`) and (`dp`.`company_id` = `cl`.`company_id`)))) join `bots` `bt` on((`cl`.`bot_id` = `bt`.`id`))) ;

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

--
-- Structure for view `company_calls_log`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`jack`@`%` SQL SECURITY DEFINER VIEW `company_calls_log`  AS SELECT `c`.`id` AS `company_id`, `c`.`company_name` AS `company_name`, `b`.`id` AS `bot_id`, `b`.`bot_name` AS `bot_name`, `l`.`id` AS `log_id`, `l`.`call_date` AS `call_date`, `l`.`customer_number` AS `customer_number`, `l`.`call_duration` AS `call_duration`, `l`.`call_type` AS `call_type`, `l`.`end_reason` AS `end_reason`, `l`.`cost` AS `cost`, `l`.`summary` AS `summary`, `l`.`recording_url` AS `recording` FROM ((`call_log` `l` join `bots` `b` on((`b`.`id` = `l`.`bot_id`))) join `companies` `c` on((`c`.`id` = `b`.`company_id`))) ;

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

--
-- Structure for view `company_summary`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `company_summary`  AS SELECT `b`.`bot_id` AS `bot_id`, `b`.`bot_name` AS `bot_name`, `b`.`phone_number` AS `phone_number`, count(`c`.`call_type`) AS `total_calls`, `c`.`company_id` AS `company_id`, `c`.`call_type` AS `call_type`, `c`.`end_reason` AS `end_reason` FROM (`bot_phone` `b` join `company_calls_log` `c` on((`b`.`bot_id` = `c`.`bot_id`))) GROUP BY `c`.`bot_id`, `b`.`phone_number`, `c`.`call_type`, `c`.`end_reason` ;

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

--
-- Structure for view `department_wise_directory`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `department_wise_directory`  AS SELECT `pd`.`id` AS `employee_id`, `pd`.`title` AS `title`, `pd`.`phone_number` AS `phone_number`, `dpt`.`id` AS `department_id`, `dpt`.`name` AS `department`, `pd`.`company_id` AS `company_id` FROM (`phone_directory` `pd` join `departments` `dpt` on((`pd`.`department_id` = `dpt`.`id`))) ;

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

--
-- Structure for view `keywords_calls_report_data`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `keywords_calls_report_data`  AS SELECT `c`.`call_date` AS `call_date`, `k`.`keyword` AS `keyword`, sum(`k`.`frequency`) AS `frequency`, `k`.`company_id` AS `company_id`, `b`.`bot_name` AS `bot_name`, `c`.`end_reason` AS `end_reason`, `c`.`recording_url` AS `recording_url`, `t`.`full_name` AS `customer_name`, `c`.`id` AS `call_id` FROM (`customers` `t` left join ((`keyword_count` `k` join `bots` `b` on((`b`.`id` = `k`.`bot_id`))) join `call_log` `c` on((`k`.`call_log_id` = `c`.`id`))) on((`c`.`customer_number` = `t`.`phone`))) GROUP BY `k`.`id`, `b`.`bot_name`, `c`.`end_reason`, `t`.`full_name` ;

--
-- Indexes for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Indexes for table `company_contacts`
--
ALTER TABLE `company_contacts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `company_contacts_company_id_foreign` (`company_id`);

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

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

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

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

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

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

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

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

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

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`) USING BTREE;

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

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

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

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

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

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

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

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

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

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

--
-- Indexes for table `password_reset_tokens`
--
ALTER TABLE `password_reset_tokens`
  ADD PRIMARY KEY (`email`);

--
-- Indexes for table `personal_access_tokens`
--
ALTER TABLE `personal_access_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `personal_access_tokens_token_unique` (`token`),
  ADD KEY `personal_access_tokens_tokenable_type_tokenable_id_index` (`tokenable_type`,`tokenable_id`);

--
-- Indexes for table `phone_directory`
--
ALTER TABLE `phone_directory`
  ADD PRIMARY KEY (`id`),
  ADD KEY `company_departments_company_id_foreign` (`company_id`);

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

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

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

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

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

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

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

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `company_contacts`
--
ALTER TABLE `company_contacts`
  ADD CONSTRAINT `company_contacts_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;
COMMIT;

