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

START TRANSACTION;



--
-- Database: `affiliates-app`
--

DELIMITER $$
--
-- Functions
--
CREATE DEFINER=`leads_crm`@`%` FUNCTION `calculateOrderRevenue` (`startDate` DATETIME, `endDate` DATETIME, `orderId` INT) RETURNS VARCHAR(50) CHARSET latin1 BEGIN
DECLARE price DOUBLE;
DECLARE cost DOUBLE;

SELECT SUM(ftdPrice(ftd_date, orderId, order_lead_id)), SUM(ftdCost(ftd_date, orderId, order_lead_id)) INTO price,cost FROM order_ftds WHERE `order_id`=orderId AND ftd_date BETWEEN startDate AND endDate LIMIT 1;

IF price IS NULL THEN SET price = 0;
END IF;
IF cost IS NULL THEN SET cost = 0;
END IF;

RETURN CONCAT_WS('|', price, cost);
END$$

CREATE DEFINER=`leads_crm`@`%` FUNCTION `ftdCost` (`ftdDate` DATETIME, `orderId` INT, `orderLeadId` INT) RETURNS DOUBLE UNSIGNED BEGIN
DECLARE cost DOUBLE;
DECLARE sourceId INT;
DECLARE countryId INT;

SELECT source_id, country_id INTO sourceId, countryId FROM order_leads WHERE id = orderLeadId LIMIT 1;

SELECT cpa_cost INTO cost FROM order_prices WHERE order_id = orderId 
	AND source_id = sourceId 
    AND country_id = countryId 
    AND ftdDate BETWEEN start_date AND IF(end_date IS NOT NULL,end_date,'3000-01-01 23:59:59') LIMIT 1;

RETURN cost;
END$$

CREATE DEFINER=`leads_crm`@`%` FUNCTION `ftdPrice` (`ftdDate` DATETIME, `orderId` INT, `orderLeadId` INT) RETURNS DOUBLE UNSIGNED BEGIN
DECLARE price DOUBLE;
DECLARE sourceId INT;
DECLARE countryId INT;

SELECT source_id, country_id INTO sourceId, countryId FROM order_leads WHERE id = orderLeadId LIMIT 1;

SELECT cpa_price INTO price FROM order_prices WHERE order_id = orderId 
	AND source_id = sourceId 
    AND country_id = countryId 
    AND ftdDate BETWEEN start_date AND IF(end_date IS NOT NULL,end_date,'3000-01-01 23:59:59') LIMIT 1;

RETURN price;
END$$

DELIMITER ;

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

--
-- Table structure for table `languages`
--

CREATE TABLE `languages` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `code` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
  `default` tinyint(4) NOT NULL,
  `order` tinyint(4) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `languages`
--

INSERT INTO `languages` (`id`, `user_id`, `name`, `code`, `default`, `order`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 'English', 'en', 1, 1, 1, NULL, NULL);

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

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int(10) UNSIGNED NOT NULL,
  `user_id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `permissions` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `user_id`, `name`, `description`, `permissions`, `created_at`, `updated_at`) VALUES
(1, 1, 'Admin', '', 'a:65:{i:0;s:10:\"leads.list\";i:1;s:12:\"leads.create\";i:2;s:12:\"leads.update\";i:3;s:12:\"leads.delete\";i:4;s:12:\"leads.import\";i:5;s:12:\"leads.export\";i:6;s:18:\"leads.assign-agent\";i:7;s:20:\"leads.alocate-source\";i:8;s:19:\"leads.alocate-order\";i:9;s:21:\"leads.dealocate-order\";i:10;s:10:\"leads.send\";i:11;s:12:\"history.list\";i:12;s:12:\"history.view\";i:13;s:14:\"history.delete\";i:14;s:21:\"history.change-status\";i:15;s:10:\"types.list\";i:16;s:12:\"types.create\";i:17;s:12:\"types.update\";i:18;s:12:\"types.delete\";i:19;s:18:\"lead-statuses.list\";i:20;s:20:\"lead-statuses.create\";i:21;s:20:\"lead-statuses.update\";i:22;s:20:\"lead-statuses.delete\";i:23;s:14:\"customers.list\";i:24;s:16:\"customers.create\";i:25;s:16:\"customers.update\";i:26;s:16:\"customers.delete\";i:27;s:22:\"customers.save-contact\";i:28;s:23:\"customers.contact-order\";i:29;s:24:\"customers.delete-contact\";i:30;s:31:\"customers.contact-change-status\";i:31;s:24:\"customers.update-contact\";i:32;s:17:\"source-types.list\";i:33;s:19:\"source-types.create\";i:34;s:19:\"source-types.update\";i:35;s:19:\"source-types.delete\";i:36;s:12:\"imports.list\";i:37;s:14:\"imports.create\";i:38;s:14:\"imports.delete\";i:39;s:12:\"exports.list\";i:40;s:14:\"exports.create\";i:41;s:14:\"exports.delete\";i:42;s:21:\"import-templates.list\";i:43;s:23:\"import-templates.create\";i:44;s:23:\"import-templates.delete\";i:45;s:22:\"service-providers.list\";i:46;s:24:\"service-providers.create\";i:47;s:24:\"service-providers.update\";i:48;s:24:\"service-providers.delete\";i:49;s:12:\"reports.list\";i:50;s:10:\"users.list\";i:51;s:12:\"users.create\";i:52;s:12:\"users.update\";i:53;s:12:\"users.delete\";i:54;s:10:\"roles.list\";i:55;s:12:\"roles.create\";i:56;s:12:\"roles.update\";i:57;s:12:\"roles.delete\";i:58;s:14:\"api-users.list\";i:59;s:16:\"api-users.create\";i:60;s:16:\"api-users.update\";i:61;s:16:\"api-users.delete\";i:62;s:20:\"files-manager.access\";i:63;s:13:\"settings.view\";i:64;s:15:\"settings.update\";}', '2017-10-28 21:00:00', '2018-03-13 15:41:56'),
(2, 5, 'Test', '', 'a:2:{i:0;s:10:\"leads.list\";i:1;s:12:\"history.list\";}', '2017-11-07 20:55:34', '2017-11-07 20:55:40'),
(3, 7, 'affiliate', '', 'N;', '2019-07-31 09:49:43', '2019-07-31 09:49:55');

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

--
-- Table structure for table `role_user`
--

CREATE TABLE `role_user` (
  `user_id` int(10) UNSIGNED NOT NULL,
  `role_id` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `role_user`
--

INSERT INTO `role_user` (`user_id`, `role_id`) VALUES
(1, 1),
(2, 1),
(4, 1),
(7, 1),
(10, 1),
(11, 1);

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

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

CREATE TABLE `settings` (
  `id` int(10) UNSIGNED NOT NULL,
  `key` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `value` text COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `order` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

INSERT INTO `settings` (`id`, `key`, `value`, `type`, `order`) VALUES
(1, 'debug', 'true', 'radio', 1),
(2, 'send-leads-job-finish-mail-subject', 'Send lead finish', 'input', 2),
(3, 'send-leads-job-finish-from-email', 'no-reply@traderleads.com', 'input', 4),
(4, 'send-leads-job-finish-from-name', 'Admin', 'textarea', 5),
(9, 'send-leads-job-finish-mail-body', 'Hello {name},\n\nSending leads job is done!\nOrder code: {orderCode}', 'textarea', 6),
(10, 'default_import_email', '', 'input', 7),
(11, 'default_import_email_subject', '', 'input', 8),
(12, 'imports-job-finish-mail-subject', 'Imports finish', 'input', 0),
(13, 'imports-job-finish-from-email', 'no-reply@traderleads.com', 'input', 0),
(14, 'imports-job-finish-from-name', 'Admin', 'textarea', 0),
(15, 'imports-job-finish-mail-body', 'Hello {name},\nImports job is done!', 'textarea', 0),
(16, 'export-leads-mail-subject', 'export subject', 'text', 0),
(17, 'export-leads-from-address', 'demo@demo.com', 'text', 0),
(18, 'export-leads-from-name', 'demo', 'text', 0),
(19, 'export-leads-job-finish-mail-body', '', 'textarea', 0),
(20, 'check-ftd-manual-running', '0', 'text', 0),
(21, 'check-status-manual-running', '0', 'text', 0);

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

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

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `api_username` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `api_password` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `permissions` text COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password_updated` datetime DEFAULT NULL,
  `status` tinyint(4) NOT NULL,
  `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

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

INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `api_username`, `api_password`, `permissions`, `password`, `password_updated`, `status`, `remember_token`, `created_at`, `updated_at`) VALUES
(1, 'Radu', 'Patron', 'radup@olahmedia.com', '', '', 'N;', '$2y$10$Osd0EmEcARKpLJkih64DRua9JXdFbJtRRzD9orS4HCyhlCoLQuH1O', '2021-01-11 12:43:36', 1, '7tKHBoYZTQDxJjAz1zW2f80uWgKCG50iRrYgyRrjpOaXC5zEu06lMWBZREvo', '2017-10-28 21:00:00', '2021-02-09 11:13:37'),
(2, 'Nimrod', 'Cohen', 'nimrod@trader-leads.com', '', '', 'N;', '$2y$10$5TuqKUcNorro/OdkeBCDPeuqb1kTPRKn3cm7Bz.ku/tdpVXo/2wJq', '2021-02-14 07:44:35', 1, 'sDsR8o3wanxlUZ5ZNh4p30zk79m4GFACEzik1Lh9eA0Kdouyj2UAITi9FUp6', '2017-10-28 21:00:00', '2021-04-15 04:35:18'),
(4, 'Nimrod ', 'Pinto', 'Nimrodp@players-leads.com', '', '', 'N;', '$2y$10$u144GUc4xJR7IL7n5j7HAekp735lRWcMOu09gMzXklKfvwGK1Livm', '2021-01-07 10:03:27', 1, NULL, '2017-10-29 14:59:30', '2021-01-07 08:03:27'),
(7, 'Constantin Silviu', 'Nuta', 'silviun@olahmedia.com', 'charmelle', '123456', 'N;', '$2y$10$GuDopY7WtVHhOB7kJMhZTOHfoqn2RHaKhlj9rwPAFMI.mPcpsSVrO', '2021-02-02 16:14:10', 1, 'HH18TS1hQSv5VfthBV7nDCRciljDP4tXWmyXZTAiVhU68OwhobablIaZlNEG', '2018-10-16 09:47:11', '2021-02-02 14:14:10'),
(10, 'Sarit', 'Cohen', 'sarit81co@gmail.com', '', '', 'N;', '$2y$10$VBOn0bQKB3wAZOHZQNttOen8laEaoCQA3mH/eb33HTKCpA86CE2EK', '2020-12-23 17:56:32', 1, NULL, '2020-07-28 05:40:24', '2020-12-23 15:56:32'),
(11, 'Finance', 'Finance', 'Finance@trader-leads.com', '', '', 'N;', '$2y$10$t0R9Q3OI4eChz1c/HLQHKetXm2oQQT/z7azDk0NFRLX0feTrluId2', NULL, 1, NULL, '2021-03-16 16:16:16', '2021-03-16 16:16:52');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `languages`
--
ALTER TABLE `languages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `languages_user_id_default_order_status_index` (`user_id`,`default`,`order`,`status`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `roles_name_unique` (`name`),
  ADD KEY `roles_user_id_index` (`user_id`);

--
-- Indexes for table `role_user`
--
ALTER TABLE `role_user`
  ADD PRIMARY KEY (`user_id`,`role_id`),
  ADD KEY `role_user_role_id_foreign` (`role_id`);

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

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

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `languages`
--
ALTER TABLE `languages`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `settings`
--
ALTER TABLE `settings`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;

