START TRANSACTION;
CREATE TABLE `ob_ec_category` (
  `categoryId` int(11) NOT NULL,
  `description` varchar(1000) NOT NULL,
  `inMenu` char(1) NOT NULL,
  `hideInEcom` char(1) NOT NULL,
  `skipAttributes` char(1) NOT NULL,
  `metaTitle` varchar(255) NOT NULL,
  `metaDescription` text NOT NULL,
  `friendlyUrl` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ob_ec_category` ADD PRIMARY KEY (`categoryId`);


CREATE TABLE `ob_ec_items` ( 
    `itemId` INT NOT NULL , 
    `displayName` VARCHAR(255) NOT NULL , 
    `summary` VARCHAR(750) NOT NULL , 
    `description` TEXT NOT NULL , 
    `minOrderQty` DECIMAL(9,3) NOT NULL , 
    `weight` DECIMAL(9,3) NOT NULL , 
    `additionalShippingCost` DECIMAL(11,3) NOT NULL , 
    `metaTitle` VARCHAR(255) NOT NULL , 
    `metaDescription` TEXT NOT NULL ,
     `friendlyUrl` VARCHAR(255) NOT NULL 
) ENGINE = InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ob_ec_items` ADD PRIMARY KEY( `itemId`);

DROP INDEX IF EXISTS cid_itemid ON `ob_item_images`;

ALTER TABLE `ob_item_images` ADD `imgFileExt` VARCHAR(5) NOT NULL AFTER `orderId`;

CREATE TABLE `ob_ec_config` ( 
    `cId` INT NOT NULL , 
    `code` VARCHAR(24) NOT NULL , 
    `value` TEXT NULL DEFAULT NULL 
) ENGINE = InnoDB;

ALTER TABLE `ob_ec_config` ADD UNIQUE(`cId`, `code`);

CREATE TABLE `ob_ec_pages` (
    `id` int(10) NOT NULL,
    `title` varchar(50) NOT NULL,
    `content` text,
    `inMenu` char(1) NOT NULL,
    `cId` int(11) NOT NULL,
    `metaTitle` varchar(255) NOT NULL,
    `metaDescription` text NOT NULL,
    `friendlyUrl` varchar(255) NOT NULL,
    `isDefault` tinyint(1) NOT NULL DEFAULT '0',
    `pCode`  CHAR(3) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ob_ec_pages` (`id`, `title`, `content`, `inMenu`, `cId`, `metaTitle`, `metaDescription`, `friendlyUrl`, `isDefault`,`pCode`) VALUES
(1, 'About', '<p>About us</p>', '', 0, 'about', 'about page', 'about', 1,''),
(2, 'Delivery Information', '<p>Delivery Info</p>', 'T', 0, '', '', '', 1,''),
(3, 'Privacy Policy', '<p>This Privacy Policy describes how your personal information is collected, used, and shared when you visit or make a purchase from %%$companyUrl$%%.</p><p></p><h3>WHAT PERSONAL INFORMATION WE COLLECT</h3><p>When you make a purchase or attempt to make a purchase through the Site, we collect certain information from you, including your name, billing address, shipping address, payment information (including credit card numbers ), email address, and phone number. This is called Order Information.</p><p></p><p>By Personal Information in this Privacy Policy, we are talking both about Order Information.</p><p></p><h3>HOW DO WE USE YOUR PERSONAL INFORMATION</h3><p></p><p>We use the Order Information that we collect generally to fulfil any orders placed through the Site (including processing your payment information, arranging for shipping, and providing you with invoices and/or order confirmations).</p><p></p><p>Additionally, we use this Order Information to:</p><p></p><ul><li>Communicate with you.</li><li>Screen our orders for potential risk or fraud.</li><li>When in line with the preferences you have shared with us, provide you with information or advertising relating to our products or services.</li></ul><p></p><p></p><h3>SHARING YOUR PERSONAL INFORMATION</h3><p></p><p>We use Google Analytics to help us understand how our customers use %%$compName$%%. How Google uses your Personal Information.</p><p>We may share your Personal Information to comply with applicable laws and regulations, to respond to a subpoena, search warrant or other lawful requests for information we receive, or to otherwise protect our rights.</p><p></p><h3>YOUR RIGHTS</h3><p></p><p>If you are a Indian/US resident, you have the right to access the personal information we hold about you and to ask that your personal information is corrected, updated, or deleted. If you would like to exercise this right, please contact us.</p><p></p><p>Additionally, if you are a Indian/US resident we note that we are processing your information in order to fulfil contracts we might have with you (for example if you make an order through the Site), or otherwise to pursue our legitimate business interests listed above.</p><p></p><h3>DATA RETENTION</h3><p></p><p>When you place an order through the Site, we will maintain your Order Information for our records unless and until you ask us to delete this information.</p><p></p><p></p><h3>CHANGES</h3><p></p><p>We may update this privacy policy from time to time in order to reflect, for example, changes to our practices or for other operational, legal or regulatory reasons.</p><p></p><p>If you have questions and/or require more information, do not hesitate to contact us.</p><p></p> ', '', 0, '', '', '', 1,'PP'),
(4, 'Terms & Conditions', '<p>Please read these terms and conditions ("terms and conditions", "terms") carefully before using  %%$companyUrl$%%. This site is operated by %%$compName$%%</p><h3>Conditions of use</h3><p>By using this website, you certify that you have read and reviewed this Agreement and that you agree to comply with its terms. If you do not want to be bound by the terms of this Agreement, you are advised to leave the website accordingly.</p><h3>Privacy policy</h3><p>Before you continue using our website, we advise you to read our %%$privacypolicy$%% regarding our user data collection. It will help you better understand our practices.</p><h3>Age restriction</h3><p>You must be at least 18 (eighteen) years of age before you can use this website. By using this website, you warrant that you are at least 18 years of age and you may legally adhere to this Agreement.  %%$compName$%% assumes no responsibility for liabilities related to age misrepresentation.</p><h3>Intellectual property</h3><p>You agree that all materials, products, and services provided on this website are the property of  %%$compName$%%, its affiliates, directors, officers, employees, agents, suppliers, or licensors including all copyrights, trade secrets, trademarks, patents, and other intellectual property. You also agree that you will not reproduce or redistribute the  %%$compName$%%’s intellectual property in any way, including electronic, digital, or new trademark registrations.</p><p>You grant  %%$compName$%% a royalty-free and non-exclusive license to display, use, copy, transmit, and broadcast the content you upload and publish. For issues regarding intellectual property claims, you should contact the company in order to come to an agreement.</p><h3>User accounts</h3><p>As a user of this website, you may be asked to register with us and provide private information. You are responsible for ensuring the accuracy of this information, and you are responsible for maintaining the safety and security of your identifying information. You are also responsible for all activities that occur under your account or password.</p><p>If you think there are any possible issues regarding the security of your account on the website, inform us immediately so we may address them accordingly.</p><p>We reserve all rights to terminate accounts, edit or remove content and cancel orders at our sole discretion.</p><h3>Applicable law</h3><p>By visiting this website, you agree that the laws of the [location], without regard to principles of conflict laws, will govern these terms and conditions, or any dispute of any sort that might come between  %%$compName$%% and you, or its business partners and associates.</p><h3>Disputes</h3><p>Any dispute related in any way to your visit to this website or to products you purchase from us shall be arbitrated by state or federal court [location] and you consent to exclusive jurisdiction and venue of such courts.</p><h3>Indemnification</h3><p>You agree to indemnify  %%$compName$%% and its affiliates and hold  %%$compName$%% harmless against legal claims and demands that may arise from your use or misuse of our services. We reserve the right to select our own legal counsel. </p><h3>Limitation on liability</h3><p> %%$compName$%% is not liable for any damages that may occur to you as a result of your misuse of our website.</p><p> %%$compName$%% reserves the right to edit, modify, and change this Agreement at any time. We shall let our users know of these changes through electronic mail. This Agreement is an understanding between  %%$compName$%% and the user, and this supersedes and replaces all prior agreements regarding the use of this website.</p><p></p>', '', 0, '', '', '', 1,'TC'),
(5, 'Contact Us', '', 'T', 0, '', '', '', 1,''),
(6, 'Home', '', '', 0, '', '', '', 1,'HOM'),
(7, 'Thank You', '<h1> Your Order is Confirmed </h1><div class="ui main" id="sucess-order"> 	<h3>Your order is placed</h3>	<div>Thank you for shopping with us</div><br/>	<a href="%%$companyUrl$%%/c-all">Continue shopping</a></div>','' , 0, '', '', '', 1,'TY'),( 8,'Email Order Confirmation', '', '',0 , '', '', '', 1,'OC');

ALTER TABLE `ob_ec_pages` ADD PRIMARY KEY (`id`);

ALTER TABLE `ob_ec_pages` MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;


CREATE TABLE `ob_ec_appmodule` (
  `id` int(11) NOT NULL,
  `cId` int(11) NOT NULL,
  `modId` int(11) NOT NULL,
  `modCode` varchar(3) NOT NULL,
  `pageCode` varchar(3) NOT NULL,
  `position` varchar(100) NOT NULL,
  `config` text NOT NULL,
  `isActive` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `ob_ec_appmodule` ADD `sortNo` INT(11) NOT NULL AFTER `isActive`;

ALTER TABLE `ob_ec_appmodule` ADD PRIMARY KEY (`id`);

ALTER TABLE `ob_ec_appmodule` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

ALTER TABLE `ob_ec_appmodule` ADD `modName` VARCHAR(100) NOT NULL AFTER `modCode`;

CREATE TABLE `ob_ec_shipay` ( 
    `id` INT NOT NULL , 
    `title` VARCHAR(40) NOT NULL , 
    `description` VARCHAR(1000) NOT NULL , 
    `stype` CHAR(1) NOT NULL COMMENT 'F - Flat Rate/W - Weight Based/C - Carrier Calculated' , `perOrder` DECIMAL(9,2) NOT NULL , 
    `perItem` DECIMAL(9,2) NOT NULL , 
    `minAmount` DECIMAL(11,2) NOT NULL , 
    `isActive` TINYINT(1) NOT NULL , 
    `cId` INT NOT NULL 
) ENGINE = InnoDB;

ALTER TABLE `ob_ec_shipay` ADD PRIMARY KEY (`id`);
ALTER TABLE `ob_ec_shipay` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

CREATE TABLE `ob_ec_ship_range` ( 
    `id` INT NOT NULL , 
    `ship_id` INT NOT NULL , 
    `range_from` DECIMAL(9,3) NOT NULL , 
    `range_to` DECIMAL(9,3) NOT NULL , 
    `amount` DECIMAL(11,2) NOT NULL 
) ENGINE = InnoDB;

ALTER TABLE `ob_ec_ship_range` ADD PRIMARY KEY (`id`);

ALTER TABLE `ob_ec_ship_range` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `ob_ec_ship_range` ADD INDEX `ship_id` (`ship_id`);

ALTER TABLE `ob_ec_items` ADD `hideInEcom` TINYINT(1) NOT NULL AFTER `friendlyUrl`;

ALTER TABLE `ob_ec_shipay` ADD `config` VARCHAR(7000) NOT NULL AFTER `isActive`;

ALTER TABLE `ob_ec_shipay` ADD `sortNo` INT NOT NULL AFTER `cId`;

CREATE TABLE `ob_ec_sessions` (
  `id` varchar(128) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `timestamp` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `data` blob NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `ob_ec_sessions`
  ADD PRIMARY KEY (`id`,`ip_address`),
  ADD KEY `ob_sessions_timestamp` (`timestamp`);


CREATE TABLE `ob_ec_module` (
    `id` int(11) NOT NULL,
    `modCode` varchar(3) NOT NULL,
    `modName` varchar(100) NOT NULL,
    `pageCodes` varchar(3) NOT NULL,
    `title` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `ob_ec_module` (`id`, `modCode`, `modName`, `pageCodes`, `title`) VALUES
(1, 'ISL', 'imageslider', 'HOM', 'Image Slider'),
(2, 'ISK', 'image_stack', 'HOM', 'Image Stack'),
(3, 'HCG', 'categorylist', 'HOM', 'Categories/Brands'),
(4, 'HLP', 'latestproducts', 'HOM', 'Latest Products'),
(5, 'HFP', 'featuredproducts', 'HOM', 'Featured Products'),
(6, 'CB', 'content_block', 'HOM', 'Content Block'),
(7, 'AC', 'addtocart', 'ALL', 'Add to Cart'),
(8, 'PTM', 'Paytm', 'PAY', 'Paytm'),
(9, 'RZP', 'Razorpay', 'PAY', 'Razorpay'),
(10, 'CB', 'content_block', 'FTR', 'Content Block'),
(11, 'MP', 'menu', 'FTR', 'Menu'),
(12, 'MP', 'menu', 'HOM', 'Menu'),
(13, 'OMI', 'Omise', 'PAY', 'Omise');

ALTER TABLE `ob_ec_module` ADD PRIMARY KEY (`id`);

ALTER TABLE `ob_ec_module` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

ALTER TABLE `ob_ec_pages` ADD `parentId` INT NOT NULL DEFAULT '0' AFTER `isDefault`;
ALTER TABLE `ob_ec_pages` ADD `sortNo` INT NOT NULL DEFAULT '0' AFTER `parentId`;

INSERT INTO `ob_role`(`cId`, `roleCode`, `roleName`, `is_system`) select `id`,'E','E-Commerce',1 from `ob_companies`;

INSERT INTO `ob_role_permissions` (roleid,cid,modcode,actionRights) select roleid,cId,'ECO',4 from `ob_role` where roleCode='E';

CREATE TABLE `ob_ec_customers` ( 
  `id` INT NOT NULL , 
  `email` VARCHAR(200) NOT NULL , 
  `password` VARCHAR(64) NOT NULL , 
  `phone` VARCHAR(20) NOT NULL , 
  `firstName` VARCHAR(60) NOT NULL , 
  `lastName` VARCHAR(60) NOT NULL , 
  `cId` INT NOT NULL , 
  `conId` INT NOT NULL DEFAULT '0' , 
  `detail` TEXT NOT NULL , 
  `countryCode` VARCHAR(3) NOT NULL , 
  `country` VARCHAR(20) NOT NULL ,
  `activation` VARCHAR(64) NOT NULL
) ENGINE = InnoDB;

ALTER TABLE `ob_ec_customers` ADD PRIMARY KEY (`id`);

ALTER TABLE `ob_ec_customers` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `ob_ec_customers` ADD UNIQUE (`email`);

CREATE TABLE `ob_ec_menu` ( 
  `id` INT NOT NULL , 
  `menuTitle` VARCHAR(255) NOT NULL , 
  `parentId` INT NOT NULL , 
  `position` CHAR(1) NOT NULL COMMENT 'T-Top, B-Bottom' , 
  `sortNo` INT NOT NULL , 
  `menuType` CHAR(1) NOT NULL COMMENT 'P-Page, C-Category, M-Menu, I-Product Item, L-Link, S-Seperator, T-Text Only' , 
  `icon` VARCHAR(255) NOT NULL , 
  `toolTipText` VARCHAR(50) NOT NULL 
) ENGINE = InnoDB;

ALTER TABLE `ob_ec_menu` ADD PRIMARY KEY (`id`);
ALTER TABLE `ob_ec_menu` CHANGE `id` `id` INT(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `ob_ec_menu` ADD `cId` INT NOT NULL AFTER `id`;
ALTER TABLE `ob_ec_menu` ADD `reference` TEXT NOT NULL AFTER `menuTitle`;
ALTER TABLE `ob_ec_items` ADD `mrp` DECIMAL(11,3) NOT NULL AFTER `weight`;
ALTER TABLE `ob_ec_shipay` CHANGE `minAmount` `freeShippingFrom` DECIMAL(11,2) NOT NULL;
ALTER TABLE `ob_ec_items` ADD `isPreOrder` CHAR(1) NOT NULL AFTER `mrp`;
ALTER TABLE `ob_ec_ship_range` ADD `type` CHAR(1) NOT NULL AFTER `amount`;
ALTER TABLE `ob_ec_items` ADD `maxOrderQty` DECIMAL(9,3) NOT NULL AFTER `minOrderQty`;
CREATE TABLE `ob_ec_orders` (
  `id` int(8) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `tDate` date NOT NULL,
  `status` char(1) NOT NULL,
  `orderinfo` text NOT NULL,
  `totAmount` decimal(11,2) NOT NULL,
  `cId` int(11) NOT NULL,
  `transId` mediumint(8) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_orders` ADD `phone` VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `transId`, ADD `email` VARCHAR(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL AFTER `phone`;
ALTER TABLE `ob_ec_items` DROP `mrp`;
ALTER TABLE `ob_ec_orders` ADD `conId` MEDIUMINT(8) NOT NULL AFTER `email`;
CREATE TABLE `ob_ec_zones_region` (
  `zone_id` int(11) NOT NULL,
  `regCode` varchar(25) NOT NULL COMMENT 'countrycode:statecode / countrycode',
  `regType` char(2) NOT NULL,
  `exclude` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_zones_region` ADD KEY `zone_id` (`zone_id`,`regCode`);
  
CREATE TABLE `ob_ec_zones` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(60) NOT NULL,
  `cId` mediumint(8) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `ob_ec_discount` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_code` varchar(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  `config` text NOT NULL,
  `validFrom` timestamp NOT NULL DEFAULT current_timestamp(),
  `expiredOn` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `cId` mediumint(8) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_discount`
  ADD UNIQUE KEY `coupon_code` (`coupon_code`);
CREATE TABLE `ob_ec_ship_zones` (
  `ref_id` int(11) NOT NULL,
  `zone_id` int(11) NOT NULL,
  `refType` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_ship_zones` ADD KEY `ref_id` (`ref_id`);
CREATE TABLE `ob_trans_coupons` (
  `transId` mediumint(8) NOT NULL,
  `coupon_code` varchar(11) NOT NULL,
  `cId` mediumint(8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_pages` ADD `filePath` VARCHAR(250) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `pCode`;
ALTER TABLE `ob_ec_orders` ADD `bId` MEDIUMINT(8) UNSIGNED  NOT NULL DEFAULT '0' AFTER `conId`;
ALTER TABLE `ob_ec_shipay` ADD `bId` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '0' AFTER `sortNo`;
CREATE TABLE `ob_ec_images` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `refType` varchar(3) NOT NULL,
  `refId` int(12) NOT NULL,
  `icode` varchar(4) NOT NULL,
  `path` varchar(255) NOT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_category` ADD `customFields` VARCHAR(500) NOT NULL AFTER `friendlyUrl`;

INSERT INTO `ob_ec_module` (`modCode`, `modName`, `pageCodes`, `title`) VALUES ('PPE','PhonePe','PAY','PhonePe');
DROP TABLE IF EXISTS `ob_ec_rating`;
CREATE TABLE `ob_ec_rating` ( `id` int(10) NOT NULL,`reviewId` int(10) NOT NULL,`rating` int(1) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_rating` ADD PRIMARY KEY (`id`);
ALTER TABLE `ob_ec_rating` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT; 
DROP TABLE IF EXISTS `ob_ec_reviews`;
CREATE TABLE `ob_ec_reviews` (`reviewId` int(10) UNSIGNED NOT NULL,`itemId` int(10) UNSIGNED NOT NULL,`userId` int(10) NOT NULL,`userName` varchar(100) NOT NULL,`userEmail` varchar(255) NOT NULL,`deviceIP` varchar(40) NOT NULL,`reviewDate` timestamp NOT NULL DEFAULT current_timestamp(), `reviewContent` text NOT NULL,`isApprove` varchar(1) NOT NULL,`device_user_agent` varchar(500) NOT NULL, `approved_by` int(10) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
ALTER TABLE `ob_ec_reviews` ADD PRIMARY KEY (`reviewId`);
ALTER TABLE `ob_ec_reviews` CHANGE `reviewId` `reviewId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE `ob_item_images` ADD `videoUrl` VARCHAR(500) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL AFTER `filePath`;
INSERT INTO `ob_ec_module` (`modCode`, `modName`, `pageCodes`, `title`) VALUES ('HUB','Hubtel','PAY','Hubtel');
COMMIT;
