-- phpMyAdmin SQL Dump
-- version 5.2.0-dev+20220109.19e814c65d
-- https://www.phpmyadmin.net/
--
-- Host: localhost
-- Generation Time: Jan 24, 2023 at 09:49 AM
-- Server version: 10.8.3-MariaDB
-- PHP Version: 7.4.33

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `frfrtiresnew`
--

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

--
-- Table structure for table `Advertising`
--

CREATE TABLE `Advertising` (
                               `id` int(10) UNSIGNED NOT NULL,
                               `retailer` varchar(32) DEFAULT NULL,
                               `name` varchar(64) NOT NULL,
                               `start` datetime NOT NULL,
                               `end` datetime NOT NULL,
                               `image` varchar(256) NOT NULL,
                               `image_tablet` varchar(256) DEFAULT NULL,
                               `image_phone` varchar(256) DEFAULT NULL,
                               `url` varchar(256) NOT NULL,
                               `type` enum('BANNER','BACKGROUND') NOT NULL,
                               `tracking` varchar(256) DEFAULT NULL,
                               `regex` varchar(256) DEFAULT NULL,
                               `css` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Advertising_Event`
--

CREATE TABLE `Advertising_Event` (
                                     `id` int(10) UNSIGNED NOT NULL,
                                     `idAdvertising` int(10) UNSIGNED NOT NULL,
                                     `userHash` char(32) NOT NULL,
                                     `type` enum('CLICK','VIEW') NOT NULL,
                                     `time` DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Blog_Post`
--

CREATE TABLE `Blog_Post` (
                             `id` int(10) UNSIGNED NOT NULL,
                             `url` varchar(240) NOT NULL,
                             `title` varchar(180) NOT NULL,
                             `metaTitle` varchar(80) NOT NULL,
                             `metaDescription` varchar(240) NOT NULL,
                             `content` text NOT NULL,
                             `cover` varchar(180) NOT NULL,
                             `thumbnail` varchar(180) NOT NULL,
                             `anchor` varchar(60) DEFAULT NULL,
                             `home` tinyint(1) NOT NULL,
                             `published` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Brand`
--

CREATE TABLE `Brand` (
                         `id` char(24) NOT NULL,
                         `name` varchar(64) NOT NULL,
                         `displayName` varchar(64) NOT NULL,
                         `category` enum('DISCOUNT','BUDGET','PREMIUM') NOT NULL DEFAULT 'DISCOUNT'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Brand_Alt`
--

CREATE TABLE `Brand_Alt` (
                             `idBrand` char(24) NOT NULL,
                             `altName` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Brand_Content`
--

CREATE TABLE `Brand_Content` (
                                 `idBrand` char(24) NOT NULL,
                                 `condition` varchar(32) NOT NULL,
                                 `content` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Content`
--

CREATE TABLE `Content` (
                           `id` int(11) NOT NULL,
                           `title` varchar(100) DEFAULT NULL,
                           `description` varchar(240) DEFAULT NULL,
                           `content` text NOT NULL,
                           `url` varchar(256) NOT NULL,
                           `published` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Discount`
--

CREATE TABLE `Discount` (
                            `id` int(10) UNSIGNED NOT NULL,
                            `retailer` varchar(32) NOT NULL,
                            `code` varchar(32) NOT NULL,
                            `type` enum('PERCENTAGE','REBATE') NOT NULL,
                            `amount` decimal(5,2) NOT NULL,
                            `start` datetime NOT NULL,
                            `end` datetime NOT NULL,
                            `description` varchar(255) DEFAULT NULL,
                            `paused` tinyint(1) NOT NULL,
                            `enabled` tinyint(1) NOT NULL,
                            `conditions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`conditions`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Model`
--

CREATE TABLE `Model` (
                         `id` char(24) NOT NULL,
                         `idBrand` char(24) NOT NULL,
                         `name` varchar(64) NOT NULL,
                         `cleanName` varchar(64) NOT NULL,
                         `vehicle` int(11) NOT NULL,
                         `season` int(11) NOT NULL,
                         `superseeded` char(24) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Model_Alt`
--

CREATE TABLE `Model_Alt` (
                             `idModel` char(24) NOT NULL,
                             `altName` varchar(62) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Product`
--

CREATE TABLE `Product` (
                           `id` char(32) NOT NULL,
                           `idModel` char(24) NOT NULL,
                           `width` double(7,2) NOT NULL,
  `height` double(5,2) NOT NULL,
  `diameter` double(5,2) NOT NULL,
  `speed` varchar(2) NOT NULL,
  `load` int(10) UNSIGNED NOT NULL,
  `rollingResistance` varchar(1) NOT NULL,
  `wetAdhesion` varchar(1) NOT NULL,
  `noiseDb` int(11) NOT NULL,
  `noiseLevel` varchar(1) NOT NULL,
  `ean` varchar(13) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Product_Click`
--

CREATE TABLE `Product_Click` (
                                 `id` int(11) UNSIGNED NOT NULL,
                                 `reference` varchar(32) DEFAULT NULL,
                                 `time` datetime NOT NULL,
                                 `userHash` char(32) NOT NULL,
                                 `idProduct` char(32) DEFAULT NULL,
                                 `retailer` varchar(32) DEFAULT NULL,
                                 `price` double(7,2) DEFAULT NULL,
  `invoiced` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Product_Offer`
--

CREATE TABLE `Product_Offer` (
                                 `id` int(10) UNSIGNED NOT NULL,
                                 `retailer` varchar(32) NOT NULL,
                                 `reference` varchar(32) DEFAULT NULL,
                                 `price` decimal(10,2) NOT NULL,
                                 `url` text NOT NULL,
                                 `runflat` int(10) UNSIGNED NOT NULL,
                                 `reinforced` int(10) UNSIGNED NOT NULL,
                                 `idDiscount` int(10) UNSIGNED DEFAULT NULL,
                                 `ean` varchar(13) DEFAULT NULL,
                                 `idBrand` char(24) NOT NULL,
                                 `width` double(7,2) NOT NULL,
                                 `height` double(5,2) NOT NULL,
                                 `diameter` double(5,2) NOT NULL,
                                 `speed` varchar(2) NOT NULL,
                                 `load` int(10) UNSIGNED NOT NULL,
                                 `retailerModelName` varchar(64) NOT NULL,
                                 `rollingResistance` varchar(1) NOT NULL,
                                 `wetAdhesion` varchar(1) NOT NULL,
                                 `noiseDb` int(11) NOT NULL,
                                 `noiseLevel` varchar(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Product_Test`
--

CREATE TABLE `Product_Test` (
                                `id` int(11) NOT NULL,
                                `idProduct` char(32) NOT NULL,
                                `tester` varchar(32) NOT NULL,
                                `formattedScore` int(11) NOT NULL,
                                `originalScore` varchar(32) NOT NULL,
                                `link` varchar(256) NOT NULL,
                                `content` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Product_View`
--

CREATE TABLE `Product_View` (
                                `id` int(11) NOT NULL,
                                `idProduct` char(32) NOT NULL,
                                `retailer` varchar(32) NOT NULL,
                                `reference` varchar(32) NOT NULL,
                                `userhash` varchar(32) NOT NULL,
                                `time` datetime NOT NULL,
                                `price` double(7,2) NOT NULL,
  `rank` int(11) NOT NULL,
  `searchId` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Retailer`
--

CREATE TABLE `Retailer` (
                            `name` varchar(32) NOT NULL,
                            `displayName` varchar(64) NOT NULL,
                            `enabled` tinyint(1) NOT NULL,
                            `invoicingType` enum('CPA','CPC') NOT NULL,
                            `invoicingValue` double(4,2) NOT NULL,
  `invoicingLimit` int(11) DEFAULT NULL,
  `comment` text DEFAULT NULL,
  `country` varchar(64) NOT NULL,
  `ecotax` tinyint(1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `Retailer_Shipping`
--

CREATE TABLE `Retailer_Shipping` (
                                     `retailer` varchar(32) NOT NULL,
                                     `nbrProduct` int(10) UNSIGNED NOT NULL,
                                     `cost` double(4,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Advertising`
--
ALTER TABLE `Advertising`
    ADD PRIMARY KEY (`id`),
  ADD KEY `retailer` (`retailer`),
  ADD KEY `start` (`start`,`end`);

--
-- Indexes for table `Advertising_Event`
--
ALTER TABLE `Advertising_Event`
    ADD PRIMARY KEY (`id`),
  ADD KEY `idAdvertising` (`idAdvertising`);

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

--
-- Indexes for table `Brand`
--
ALTER TABLE `Brand`
    ADD PRIMARY KEY (`id`),
  ADD KEY `name` (`name`);

--
-- Indexes for table `Brand_Alt`
--
ALTER TABLE `Brand_Alt`
    ADD PRIMARY KEY (`idBrand`,`altName`),
  ADD KEY `idBrand` (`idBrand`);

--
-- Indexes for table `Brand_Content`
--
ALTER TABLE `Brand_Content`
    ADD KEY `idBrand` (`idBrand`);

--
-- Indexes for table `Content`
--
ALTER TABLE `Content`
    ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `url` (`url`);

--
-- Indexes for table `Discount`
--
ALTER TABLE `Discount`
    ADD PRIMARY KEY (`id`),
  ADD KEY `retailer` (`retailer`);

--
-- Indexes for table `Model`
--
ALTER TABLE `Model`
    ADD PRIMARY KEY (`id`),
  ADD KEY `idBrand` (`idBrand`),
  ADD KEY `superseeded` (`superseeded`);
ALTER TABLE `Model` ADD FULLTEXT KEY `name` (`name`);
ALTER TABLE `Model` ADD FULLTEXT KEY `cleanName` (`cleanName`);

--
-- Indexes for table `Model_Alt`
--
ALTER TABLE `Model_Alt`
    ADD PRIMARY KEY (`idModel`,`altName`);
ALTER TABLE `Model_Alt` ADD FULLTEXT KEY `altName` (`altName`);

--
-- Indexes for table `Product`
--
ALTER TABLE `Product`
    ADD PRIMARY KEY (`id`),
  ADD KEY `width` (`width`,`height`,`diameter`),
  ADD KEY `width_2` (`width`,`height`,`diameter`,`speed`,`load`),
  ADD KEY `idModel` (`idModel`);

--
-- Indexes for table `Product_Click`
--
ALTER TABLE `Product_Click`
    ADD PRIMARY KEY (`id`),
  ADD KEY `time` (`time`),
  ADD KEY `retailer` (`retailer`),
  ADD KEY `idProduct` (`idProduct`);

--
-- Indexes for table `Product_Offer`
--
ALTER TABLE `Product_Offer`
    ADD PRIMARY KEY (`id`),
  ADD KEY `idRetailer` (`retailer`),
  ADD KEY `idDiscount` (`idDiscount`),
  ADD KEY `idProduct` (`idProduct`) USING BTREE;

--
-- Indexes for table `Product_Test`
--
ALTER TABLE `Product_Test`
    ADD PRIMARY KEY (`id`),
  ADD KEY `idProduct` (`idProduct`);

--
-- Indexes for table `Product_View`
--
ALTER TABLE `Product_View`
    ADD PRIMARY KEY (`id`),
  ADD KEY `retailer` (`retailer`),
  ADD KEY `idProduct` (`idProduct`);

--
-- Indexes for table `Retailer`
--
ALTER TABLE `Retailer`
    ADD PRIMARY KEY (`name`);
ALTER TABLE `Retailer` ADD FULLTEXT KEY `name` (`name`);

--
-- Indexes for table `Retailer_Shipping`
--
ALTER TABLE `Retailer_Shipping`
    ADD PRIMARY KEY (`retailer`,`nbrProduct`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Advertising`
--
ALTER TABLE `Advertising`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Advertising_Event`
--
ALTER TABLE `Advertising_Event`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Blog_Post`
--
ALTER TABLE `Blog_Post`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Content`
--
ALTER TABLE `Content`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Product_Click`
--
ALTER TABLE `Product_Click`
    MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Product_Offer`
--
ALTER TABLE `Product_Offer`
    MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Product_Test`
--
ALTER TABLE `Product_Test`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `Product_View`
--
ALTER TABLE `Product_View`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Advertising`
--
ALTER TABLE `Advertising`
    ADD CONSTRAINT `advertising_ibfk_1` FOREIGN KEY (`retailer`) REFERENCES `Retailer` (`name`);

--
-- Constraints for table `Advertising_Event`
--
ALTER TABLE `Advertising_Event`
    ADD CONSTRAINT `advertising_event_ibfk_1` FOREIGN KEY (`idAdvertising`) REFERENCES `Advertising` (`id`);

--
-- Constraints for table `Brand_Alt`
--
ALTER TABLE `Brand_Alt`
    ADD CONSTRAINT `brand_alt_ibfk_1` FOREIGN KEY (`idBrand`) REFERENCES `Brand` (`id`);

--
-- Constraints for table `Brand_Content`
--
ALTER TABLE `Brand_Content`
    ADD CONSTRAINT `brand_content_ibfk_1` FOREIGN KEY (`idBrand`) REFERENCES `Brand` (`id`);

--
-- Constraints for table `Discount`
--
ALTER TABLE `Discount`
    ADD CONSTRAINT `discount_ibfk_1` FOREIGN KEY (`retailer`) REFERENCES `Retailer` (`name`),
  ADD CONSTRAINT `discount_ibfk_2` FOREIGN KEY (`retailer`) REFERENCES `Retailer` (`name`);

--
-- Constraints for table `Model`
--
ALTER TABLE `Model`
    ADD CONSTRAINT `model_ibfk_3` FOREIGN KEY (`idBrand`) REFERENCES `Brand` (`id`),
  ADD CONSTRAINT `model_ibfk_4` FOREIGN KEY (`superseeded`) REFERENCES `Model` (`id`);

--
-- Constraints for table `Model_Alt`
--
ALTER TABLE `Model_Alt`
    ADD CONSTRAINT `model_alt_ibfk_1` FOREIGN KEY (`idModel`) REFERENCES `Model` (`id`);

--
-- Constraints for table `Product`
--
ALTER TABLE `Product`
    ADD CONSTRAINT `product_ibfk_1` FOREIGN KEY (`idModel`) REFERENCES `Model` (`id`);

--
-- Constraints for table `Product_Click`
--
ALTER TABLE `Product_Click`
    ADD CONSTRAINT `product_click_ibfk_6` FOREIGN KEY (`retailer`) REFERENCES `Retailer` (`name`),
  ADD CONSTRAINT `product_click_ibfk_7` FOREIGN KEY (`idProduct`) REFERENCES `Product` (`id`);

--
-- Constraints for table `Product_Offer`
--
ALTER TABLE `Product_Offer`
    ADD CONSTRAINT `product_offer_ibfk_4` FOREIGN KEY (`retailer`) REFERENCES `Retailer` (`name`),
  ADD CONSTRAINT `product_offer_ibfk_5` FOREIGN KEY (`idDiscount`) REFERENCES `Discount` (`id`);

--
-- Constraints for table `Product_View`
--
ALTER TABLE `Product_View`
    ADD CONSTRAINT `product_view_ibfk_2` FOREIGN KEY (`retailer`) REFERENCES `Retailer` (`name`),
  ADD CONSTRAINT `product_view_ibfk_3` FOREIGN KEY (`idProduct`) REFERENCES `Product` (`id`);

--
-- Constraints for table `Retailer_Shipping`
--
ALTER TABLE `Retailer_Shipping`
    ADD CONSTRAINT `retailer_shipping_ibfk_1` FOREIGN KEY (`retailer`) REFERENCES `Retailer` (`name`);
COMMIT;
