-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `pride_db`
--
CREATE DATABASE IF NOT EXISTS `pride_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `pride_db`;

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

--
-- Table structure for table `dealer`
--

CREATE TABLE `dealer` (
  `id_dealer` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `unmapped_name` varchar(255) DEFAULT NULL,
  `status` varchar(1) NOT NULL DEFAULT '1',
  `date_start` date DEFAULT NULL,
  `date_end` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

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

--
-- Table structure for table `invoice_line_item`
--

CREATE TABLE `invoice_line_item` (
  `id_invoice_line_item` int(11) NOT NULL,
  `invoice_date` varchar(45) NOT NULL,
  `amount` float NOT NULL,
  `date_paid` date NOT NULL,
  `dealer_name` varchar(45) NOT NULL,
  `extended_line_cost` float NOT NULL,
  `rebateable_flag` bit(1) DEFAULT NULL,
  `vendor_id` varchar(100) NOT NULL,
  `invoice_line_type` varchar(45) NOT NULL,
  `invoice_number` varchar(150) NOT NULL,
  `management_fee_percent` float NOT NULL,
  `marketing_allowance_percent` float NOT NULL,
  `marketing_fee` float DEFAULT NULL,
  `note` varchar(45) NOT NULL,
  `po_number` varchar(45) NOT NULL,
  `product_category` varchar(100) NOT NULL,
  `product_cost` float DEFAULT NULL,
  `product_description` varchar(255) NOT NULL,
  `product_qty` int(11) DEFAULT NULL,
  `product_sku` varchar(45) DEFAULT NULL,
  `rebate` float DEFAULT NULL,
  `rebate_month` varchar(45) NOT NULL,
  `rebate_percent` float NOT NULL,
  `rebate_year` int(11) NOT NULL,
  `vendor_name` varchar(45) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `id_submission` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `submission`
--

CREATE TABLE `submission` (
  `id_submission` int(11) NOT NULL,
  `status` varchar(45) NOT NULL,
  `result` varchar(500) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `id_user` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

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

--
-- Table structure for table `submission_error_log`
--

CREATE TABLE `submission_error_log` (
  `id_submission_error_log` int(11) NOT NULL,
  `error_type` varchar(50) NOT NULL,
  `error_description` varchar(500) NOT NULL,
  `field` varchar(150) NOT NULL,
  `row_number` int(11) NOT NULL,
  `created` datetime NOT NULL DEFAULT current_timestamp(),
  `id_submission` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `dealer`
--
ALTER TABLE `dealer`
  ADD PRIMARY KEY (`id_dealer`) USING BTREE;

--
-- Indexes for table `invoice_line_item`
--
ALTER TABLE `invoice_line_item`
  ADD PRIMARY KEY (`id_invoice_line_item`) USING BTREE,
  ADD KEY `fk_invoice_line_item_submission1_idx` (`id_submission`);

--
-- Indexes for table `submission`
--
ALTER TABLE `submission`
  ADD PRIMARY KEY (`id_submission`) USING BTREE,
  ADD KEY `fk_log_user1_idx` (`id_user`);

--
-- Indexes for table `submission_error_log`
--
ALTER TABLE `submission_error_log`
  ADD PRIMARY KEY (`id_submission_error_log`) USING BTREE,
  ADD KEY `fk_submission_error_log_submission1_idx` (`id_submission`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `dealer`
--
ALTER TABLE `dealer`
  MODIFY `id_dealer` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoice_line_item`
--
ALTER TABLE `invoice_line_item`
  MODIFY `id_invoice_line_item` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=102;

--
-- AUTO_INCREMENT for table `submission`
--
ALTER TABLE `submission`
  MODIFY `id_submission` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=66;

--
-- AUTO_INCREMENT for table `submission_error_log`
--
ALTER TABLE `submission_error_log`
  MODIFY `id_submission_error_log` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=84;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `invoice_line_item`
--
ALTER TABLE `invoice_line_item`
  ADD CONSTRAINT `fk_invoice_line_item_submission1` FOREIGN KEY (`id_submission`) REFERENCES `submission` (`id_submission`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `submission`
--
ALTER TABLE `submission`
  ADD CONSTRAINT `fk_log_user1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `submission_error_log`
--
ALTER TABLE `submission_error_log`
  ADD CONSTRAINT `fk_submission_error_log_submission1` FOREIGN KEY (`id_submission`) REFERENCES `submission` (`id_submission`) ON DELETE NO ACTION ON UPDATE NO ACTION;
COMMIT;

