CREATE TABLE `deal` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `dealNo` varchar(20) NOT NULL DEFAULT '',
  `name` varchar(200) NOT NULL DEFAULT '',
  `stageId`  int(10) NOT NULL DEFAULT 0,
  `customerId` int(10) NOT NULL DEFAULT 0 ,
  `contactName` varchar(100) NOT NULL DEFAULT '',
  `contactEmail` varchar(100) NOT NULL DEFAULT '',
  `contactNo` varchar(50) NOT NULL DEFAULT '',
  `closeDate` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `dealOwnerId` int(10) NOT NULL DEFAULT 0,
  `amount` double(12,2) NOT NULL DEFAULT 0.00,
  `active` tinyint(1) NOT NULL DEFAULT 1,
  `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
  `createdById` int(10) UNSIGNED NOT NULL DEFAULT 0 ,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updatedById` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  INDEX `dealNo` (`dealNo`) USING BTREE,
  INDEX `name` (`name`) USING BTREE,
  INDEX `stageId` (`stageId`) USING BTREE,
  INDEX `customerId` (`customerId`) USING BTREE,
  INDEX `contactName` (`contactName`) USING BTREE,
  INDEX `contactEmail` (`contactEmail`) USING BTREE,
  INDEX `contactNo` (`contactNo`) USING BTREE,
  INDEX `closeDate` (`closeDate`) USING BTREE,
  INDEX `dealOwnerId` (`dealOwnerId`) USING BTREE,
  INDEX `amount` (`amount`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `dealactivity` (
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `dealId` int(10) NOT NULL DEFAULT 0,
    `dealActivityTypeId` int(10) NOT NULL DEFAULT 0,
    `activityNote` text NOT NULL,
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    `createdById` int(10) UNSIGNED NOT NULL DEFAULT 0 ,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updatedById` int(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `dealId` (`dealId`) USING BTREE,
    INDEX `dealActivityTypeId` (`dealActivityTypeId`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `dealactivitydocument` (
`id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
`dealActivityId`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`documentAssetId`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' ,
`active`  tinyint(1) NOT NULL DEFAULT 1 ,
`created`  datetime NOT NULL DEFAULT '0001-01-01 00:00:00' ,
`createdById`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
`updated`  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ,
`updatedById`  int(10) UNSIGNED NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`),
INDEX `active` (`active`) USING BTREE ,
INDEX `created` (`created`) USING BTREE ,
INDEX `createdById` (`createdById`) USING BTREE ,
INDEX `updated` (`updated`) USING BTREE ,
INDEX `updatedById` (`updatedById`) USING BTREE ,
INDEX `documentAssetId` (`documentAssetId`) USING BTREE ,
INDEX `dealActivityId` (`dealActivityId`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `dealactivitytype`(
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL DEFAULT '',
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    `createdById` int(10) UNSIGNED NOT NULL DEFAULT 0 ,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updatedById` int(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `name` (`name`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `dealstage`(
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(200) NOT NULL DEFAULT '',
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    `createdById` int(10) UNSIGNED NOT NULL DEFAULT 0 ,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updatedById` int(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `name` (`name`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `deal_order`(
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `orderId` int(10) NOT NULL,
    `dealId` int(10) NOT NULL,
    `active` tinyint(1) NOT NULL DEFAULT 1,
    `created` datetime NOT NULL DEFAULT '0001-01-01 00:00:00',
    `createdById` int(10) UNSIGNED NOT NULL DEFAULT 0 ,
    `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `updatedById` int(10) UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `orderId` (`orderId`) USING BTREE,
    INDEX `dealId` (`dealId`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (1, 'Collecting cold call info', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (2, 'No answer need follow up', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (3, 'Called-interested', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (4, 'Send catalogue /price list/quote', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (5, 'Get a Deal', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (6, 'Called-not interested', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (7, 'Lead', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (8, 'Qualified to buy', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (9, 'Quoted', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (10, 'Negotiation', '0', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (11, 'Closed won', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealstage (id, name, active, created, createdById, updated, updatedById) VALUES (12, 'Closed lost', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');

INSERT INTO dealactivitytype (id, name, active, created, createdById, updated, updatedById) VALUES (1, 'Deal Activity', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealactivitytype (id, name, active, created, createdById, updated, updatedById) VALUES (2, 'Note', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealactivitytype (id, name, active, created, createdById, updated, updatedById) VALUES (3, 'Quote', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');
INSERT INTO dealactivitytype (id, name, active, created, createdById, updated, updatedById) VALUES (4, 'Task', '1', '2022-05-20 10:20:01', '10', '2022-05-20 10:20:01', '10');

INSERT INTO systemsettings (type, value, description, active, created, createdById, updated, updatedById)
VALUES ('deal_auto_prefix', '','The prefix of deal No.', '1', '2022-06-07 10:20:01', '10', '2022-06-07 10:20:01', '10'),
  ('invoice_prefix', 'INV','The prefix of invoice number.', '1', '2022-06-07 10:20:01', '10', '2022-06-07 10:20:01', '10');

update systemsettings
set type = 'service_order_no_prefix', updated = updated
where type = 'job_no_prefix';
