--
-- Create tables
--

CREATE TABLE `accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_name` varchar(255) NOT NULL,
  `firstname` varchar(255) NOT NULL,
  `surname` varchar(255) NOT NULL,
  `nip` varchar(11) NOT NULL,
  `regon` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `deleted` tinyint(1) NOT NULL DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT current_timestamp(),
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(20) DEFAULT 'primary',
  `house_number` varchar(6) NOT NULL,
  `flat_number` varchar(6) DEFAULT NULL,
  `street` varchar(150) NOT NULL,
  `city` varchar(50) NOT NULL,
  `postalcode` varchar(10) NOT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `calls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL DEFAULT 'Rozmowa telefoniczna',
  `description` text DEFAULT NULL,
  `state` varchar(25) DEFAULT 'new',
  `start_at` datetime DEFAULT current_timestamp(),
  `end_at` datetime DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `lead_id` int(11) DEFAULT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `campaign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `category` varchar(25) DEFAULT 'email',
  `budget_start` decimal(15,2) NOT NULL,
  `budget_end` decimal(15,2) DEFAULT NULL,
  `start_at` datetime DEFAULT NULL,
  `end_at` datetime DEFAULT NULL,
  `closed` tinyint(1) DEFAULT 0,
  `deleted` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_name` varchar(30) NOT NULL,
  `firstname` varchar(30) DEFAULT NULL,
  `surname` varchar(30) DEFAULT NULL,
  `nip` varchar(10) DEFAULT NULL,
  `regon` varchar(12) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `meetings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(150) NOT NULL DEFAULT 'Spotkanie',
  `description` text DEFAULT NULL,
  `state` varchar(25) DEFAULT 'new',
  `start_at` datetime DEFAULT current_timestamp(),
  `end_at` datetime DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `lead_id` int(11) DEFAULT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `description` text DEFAULT NULL,
  `account_id` int(11) DEFAULT NULL,
  `lead_id` int(11) DEFAULT NULL,
  `campaign_id` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `deleted_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `opportunities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `expected_value` decimal(13,2) NOT NULL,
  `sum` decimal(13,2) DEFAULT NULL,
  `status` varchar(25) DEFAULT 'new',
  `deleted` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `positions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `price` decimal(13,2) NOT NULL,
  `vat` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  `deleted` tinyint(1) DEFAULT 0,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `opportunity_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `products` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    `price` decimal(13,2) NOT NULL,
    `vat` int(11) DEFAULT NULL,
    `deleted` tinyint(1) DEFAULT 0,
    `created_at` datetime DEFAULT current_timestamp(),
    `updated_at` datetime DEFAULT NULL,
    `deleted_at` datetime DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Add constraints
--

ALTER TABLE `accounts` ADD CONSTRAINT `nip_length` CHECK (length(`nip`) = 10);
ALTER TABLE `accounts` ADD CONSTRAINT `regon_length` CHECK (length(`regon`) >= 9 and length(`regon`) <= 14);
ALTER TABLE `accounts` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);

ALTER TABLE `leads` ADD CONSTRAINT `nip_length` CHECK (length(`nip`) = 10);
ALTER TABLE `leads` ADD CONSTRAINT `regon_length` CHECK (length(`regon`) >= 9 and length(`regon`) <= 14);
ALTER TABLE `leads` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);

ALTER TABLE `calls` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);
ALTER TABLE `meetings` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);
ALTER TABLE `notes` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);
ALTER TABLE `positions` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);
ALTER TABLE `opportunities` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);
ALTER TABLE `campaign` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);
ALTER TABLE `address` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);
ALTER TABLE `products` ADD CONSTRAINT `update_gt_create` CHECK (`updated_at` >= `created_at`);

--
-- Setup indexes
--

ALTER TABLE `calls` ADD KEY `account_id` (`account_id`);
ALTER TABLE `calls` ADD KEY `lead_id` (`lead_id`);

ALTER TABLE `meetings` ADD KEY `account_id` (`account_id`);
ALTER TABLE `meetings` ADD KEY `lead_id` (`lead_id`);

ALTER TABLE `notes` ADD KEY `account_id` (`account_id`);
ALTER TABLE `notes` ADD KEY `lead_id` (`lead_id`);

ALTER TABLE `positions` ADD KEY `opportunity_id` (`opportunity_id`);

--
-- Add foreign keys
--

ALTER TABLE `calls` ADD CONSTRAINT `calls_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `calls` ADD CONSTRAINT `calls_ibfk_2` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `meetings` ADD CONSTRAINT `meetings_ibfk_1` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `meetings` ADD CONSTRAINT `meetings_ibfk_2` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `notes` ADD CONSTRAINT `notes_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `notes` ADD CONSTRAINT `notes_ibfk_2` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `positions` ADD CONSTRAINT `positions_ibfk_1` FOREIGN KEY (`opportunity_id`) REFERENCES `opportunities` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;


--
-- Load testing data
--

INSERT INTO  `accounts`
(`id`, `account_name`, `firstname`, `surname`, `nip`, `regon`, `email`, `deleted`, `deleted_at`)
VALUES
(1, 'Testowa firma 1', 'Bogdan', 'Testowy', '6786536251', '7863746527', 'bogdan@testowa1.pl', 0, NULL),
(2, 'Testowa firma 2', 'Romek', 'Boch', '5766536251', '17865367256', 'romek@testowa2.pl', 0, NULL),
(3, 'Testowa firma 3', 'Mateus', 'SzRz', '8888836251', '7864758923', 'szrz@szrz.pl', 0, NULL);

INSERT INTO `campaign` ( `id`, `title`, `category`, `budget_start`, `budget_end`, `start_at`, `end_at`, `closed`)
VALUES
(1, 'Kampania pozyskania T223', 'telefoniczna', 150000, NULL, '2018-03-01', '2018-05-01', 0);

INSERT INTO `products` (`id`, `name`, `price`, `vat`)
VALUES
(1, 'Plyta głowna | socket 775', 500.12, 23),
(2, 'Processor CORE 2 DUO', 150.50, 23);


--
-- Procedury
--


DELIMITER //

CREATE PROCEDURE addOpportunityPosition (
    IN opportunityID INT,
    IN productId INT,
    IN quantity INT
)
BEGIN

    INSERT INTO `positions` (`name`, `price`, `vat`, `quantity`, `opportunity_id`)
    SELECT `name`, `price`, `vat`, quantity, opportunityId
        FROM `products`
        WHERE `id` = productId;

END //

CREATE PROCEDURE recalcOpportunity (
    IN opportunityID INT
)
BEGIN

    UPDATE `opportunities` SET `sum` = (
            SELECT SUM(`price` * `quantity`)
            FROM `positions`
            WHERE `opportunity_id` = opportunityId AND
                `deleted` = 0
        )
    WHERE `id` = opportunityId;

END //

DELIMITER ;//

--
-- Symulowanie akcji aplikacji
--

-- Pracownik call center wykonal do pana Bogdana z firmy testowej 1 telefon z oferta
-- w ramach pierwszej kampanii marketingowej
-- zostal jednak wysmiany i nie zostal przeprowadzone dalsze akcje
INSERT INTO `calls` (`title`, `description`, `state`, `start_at`, `end_at`, `account_id`, `lead_id`, `campaign_id`)
VALUES
('Brak merytoryczność rozmowy', 'Nie chciał rozmawiać, kpił sobie, próbował udowadniać, że się nie opłaca.',
'completed', now(), date_sub(now(), interval 15 minute), 1, null, 1);


-- Pracownik call center wykonal do pana Romana z firmy testowej 2 telefon z oferta
-- w ramach pierwszej kampanii marketingowej
-- Pan Roman zamowil 2 produkty
START TRANSACTION;

INSERT INTO `calls` (`title`, `description`, `state`, `start_at`, `end_at`, `account_id`, `lead_id`, `campaign_id`)
VALUES
('Zakończona sukcesem', 'Doprowadzone do tranzakcji.',
'completed', now(), date_sub(now(), interval 25 minute), 2, null, 1);

INSERT INTO `opportunities` (`id`, `name`, `expected_value`, `sum`, `status`)
VALUES
(1, 'Test1', 1301.54, 0.0, 'new');

CALL addOpportunityPosition(1, 1, 2);
CALL addOpportunityPosition(1, 2, 2);
CALL recalcOpportunity(1);

COMMIT;