-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 172.20.0.2
-- Generation Time: Jul 20, 2023 at 03:01 PM
-- Server version: 10.4.28-MariaDB-1:10.4.28+maria~ubu2004
-- PHP Version: 8.1.15

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


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `minibase_materials`
--

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

--
-- Table structure for table `classification`
--

CREATE TABLE `classification` (
  `id` int(11) NOT NULL COMMENT 'not NULLable the auto-incremented values unique to each class.   ',
  `reference` int(11) DEFAULT NULL COMMENT 'Numerical reference if a custom referencing scheme should be used.',
  `name` varchar(75) NOT NULL COMMENT 'Name describing the class of this level',
  `description` varchar(500) NOT NULL COMMENT 'The description this class',
  `level` tinyint(4) NOT NULL COMMENT 'The hierarchical level of this class',
  `last_update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  `upload_date` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='This table is used to classisfy products on different levels';

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

--
-- Table structure for table `packaging`
--

CREATE TABLE `packaging` (
  `id` int(11) NOT NULL COMMENT 'unique id for this type of packaging',
  `name` varchar(50) NOT NULL COMMENT 'Name for the packaging, Cartons, tape, reel, paper, and so on',
  `unit` int(11) NOT NULL COMMENT 'How many units are in one package',
  `quantity` int(11) NOT NULL COMMENT 'How many products are in one unit',
  `total` int(11) NOT NULL COMMENT 'The total amount of products in one package',
  `last_update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(),
  `upload_date` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `physical`
--

CREATE TABLE `physical` (
  `id` int(11) NOT NULL COMMENT 'Unique ID for this physical form.',
  `name` varchar(50) NOT NULL COMMENT 'the name for this physical form',
  `type` varchar(75) NOT NULL COMMENT 'The type could be interpreted differently but it''s an extra level of specification.',
  `footprint` blob DEFAULT NULL COMMENT 'Footprint Blob. This is for a CAD model of the footprint of the physical form.',
  `symbol` blob DEFAULT NULL COMMENT 'Symbol Blob: This is for a CAD model of the symbol representing this physical form.',
  `image` mediumblob DEFAULT NULL COMMENT 'Image Blob, this is the image representing this physical form.',
  `icon` blob DEFAULT NULL COMMENT 'Icon Blob, this is the Icon representing this physical form.',
  `step` mediumblob DEFAULT NULL COMMENT 'STEP Blob. This is for th3 3D CAD model of the physical form.',
  `vrlm` mediumblob DEFAULT NULL COMMENT 'VRLM Blob. This is for the 3D CAD model of the physical form.',
  `last_update_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp() COMMENT 'Will update on each change of the physical form description',
  `upload_date` date NOT NULL DEFAULT current_timestamp() COMMENT 'First upload date',
  `size_x` mediumint(9) NOT NULL COMMENT 'Outline size for the x-axis in mm',
  `size_y` mediumint(9) NOT NULL COMMENT 'Outline size for the y-axis in mm',
  `size_z` mediumint(9) NOT NULL COMMENT 'Outline size for the z-axis in mm',
  `pitch_x` smallint(6) DEFAULT NULL COMMENT 'If the device has pins, this would be the separation pitch of those pins for the x-axis',
  `pitch_y` smallint(6) NOT NULL COMMENT 'If the device has pins, this would be the separation pitch of those pins for the y-axis'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `physical`
--

INSERT INTO `physical` (`id`, `name`, `type`, `footprint`, `symbol`, `image`, `icon`, `step`, `vrlm`, `last_update_date`, `upload_date`, `size_x`, `size_y`, `size_z`, `pitch_x`, `pitch_y`) VALUES
(1, '06_08', 'smd', NULL, NULL, NULL, NULL, [encoded data, 286568 characters], [encoded data, 72036 characters], '2022-11-14 20:25:26', '2022-11-14', 50, 50, 10, NULL, 5);

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

--
-- Table structure for table `product`
--

CREATE TABLE `product` (
  `id` int(11) NOT NULL COMMENT 'Primary Key for Database referencing',
  `manufacturer` varchar(50) NOT NULL COMMENT 'The mane of the manufacturer',
  `manufacturer_no` varchar(50) NOT NULL COMMENT 'The product No of the manufacturer ',
  `manufacturer_core_no` varchar(50) NOT NULL COMMENT 'Core product no of the manufacturer ',
  `ordering_code` varchar(50) NOT NULL COMMENT 'Ordering code from the manufacturer, if it is different than the product no',
  `description` varchar(300) NOT NULL COMMENT 'A brief and simple description of the article',
  `classification` int(11) NOT NULL,
  `status` varchar(50) NOT NULL COMMENT 'Actual status of the products life cycle',
  `buy_cost` float NOT NULL COMMENT 'our standard buy price.',
  `pricing_scheme` varchar(50) NOT NULL COMMENT 'If the manufacturer of the product allows a pricing scheme for this product',
  `price_change_flag` tinyint(1) NOT NULL COMMENT 'did the price changed since the last update ? this flag should be carefully monitored to be sure that every client was informed before resetting it !!!',
  `currency` varchar(6) NOT NULL COMMENT 'The currency with which the manufaturer sells',
  `lead_time_days` int(11) NOT NULL COMMENT 'Actual lead time on this product in days',
  `physical` int(11) NOT NULL COMMENT 'The physical form of the product. is it a flower ? or is it a box or is it tssop18 packaged IC ?',
  `packaging` int(11) NOT NULL COMMENT 'Available packing options for the product, plastic wrap, tray, bulk, reel and so on.',
  `minimum_order_quantity` int(11) NOT NULL COMMENT 'What is the minimum order quantity that the manufacturer allows. no one will send you only one flower seed. ',
  `minimum_awarding_quantity` int(11) NOT NULL COMMENT 'If we support an customer of ours and the manufacturer proposes a special pricing condition for this product. This is the quantity that must be reached before we can be awarded for our efforts in order to get the promised special price.   ',
  `minimum_quote_quantity` int(11) NOT NULL COMMENT 'If no special pricing conditions are available, this is the first price break quantity. for the rest a quotation should be asked.  ',
  `proposed_margin` int(11) NOT NULL COMMENT 'Margins defined/proposed by the organisation for this article ',
  `container` varchar(50) NOT NULL COMMENT 'Shipping container form of the packaged product.',
  `container_size` int(11) NOT NULL COMMENT 'How many items are there in one shipping container ? 5 baskets of 50 tomatoes for a total of 250 tomatoes.',
  `last_update_date` date NOT NULL COMMENT 'Last date that this product has had an updated field',
  `upload_date` date NOT NULL DEFAULT current_timestamp() COMMENT 'Upload date on the database',
  `last_time_buy_date` date NOT NULL COMMENT 'Last time that this product can be purchased',
  `obsolete_date` date NOT NULL COMMENT 'The date since/at which this product will be declared obsolete',
  `pollution_level` varchar(50) NOT NULL COMMENT 'Depending on the Product some pollution level requirement can be met or not.',
  `production_site` varchar(20) NOT NULL COMMENT 'The production site for this product. This should be linked to the manufacturer profile.',
  `comment_manufacturer` varchar(200) NOT NULL COMMENT 'Comments fomr the manufaturer',
  `comment` varchar(200) NOT NULL COMMENT 'Special comments. ',
  `favourite` tinyint(1) DEFAULT NULL COMMENT 'Is this component a favorite of yours?'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Base Table  of this database the prcodcut';

--
-- Indexes for dumped tables
--

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

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

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

--
-- Indexes for table `product`
--
ALTER TABLE `product`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_physical` (`physical`),
  ADD KEY `fk_packaging` (`packaging`),
  ADD KEY `fk_classification` (`classification`) USING BTREE;

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `classification`
--
ALTER TABLE `classification`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'not NULLable the auto-incremented values unique to each class.   ';

--
-- AUTO_INCREMENT for table `packaging`
--
ALTER TABLE `packaging`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'unique id for this type of packaging';

--
-- AUTO_INCREMENT for table `physical`
--
ALTER TABLE `physical`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Unique ID for this physical form.', AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `product`
--
ALTER TABLE `product`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key for Database referencing';

--
-- Constraints for dumped tables
--

--
-- Constraints for table `product`
--
ALTER TABLE `product`
  ADD CONSTRAINT `fk_classification` FOREIGN KEY (`classification`) REFERENCES `classification` (`id`),
  ADD CONSTRAINT `fk_packaging` FOREIGN KEY (`packaging`) REFERENCES `packaging` (`id`),
  ADD CONSTRAINT `fk_physical` FOREIGN KEY (`physical`) REFERENCES `physical` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
