-- 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;

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);

--
-- 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`);

--
-- 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;
COMMIT;

