-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Apr 12, 2025 at 08:13 PM
-- Server version: 10.4.11-MariaDB
-- PHP Version: 7.4.1

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `blue_diesel`
--

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

--
-- Table structure for table `car_details`
--

CREATE TABLE `car_details` (
  `car_id` int(11) NOT NULL,
  `car_name` varchar(50) NOT NULL,
  `i_range` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `cash_point`
--

CREATE TABLE `cash_point` (
  `cp_id` int(11) NOT NULL,
  `withdraw_amount` float(11,2) DEFAULT NULL,
  `deposit_amount` float(11,2) DEFAULT NULL,
  `details` varchar(100) NOT NULL,
  `emp_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `currency` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `customer`
--

CREATE TABLE `customer` (
  `cust_id` int(11) NOT NULL,
  `cust_name` varchar(50) NOT NULL,
  `cust_contact` int(11) NOT NULL,
  `cust_add` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `employee`
--

CREATE TABLE `employee` (
  `emp_id` int(11) NOT NULL,
  `emp_name` varchar(50) NOT NULL,
  `emp_type` varchar(20) NOT NULL,
  `emp_email` varchar(50) NOT NULL,
  `emp_password` varchar(70) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`emp_id`, `emp_name`, `emp_type`, `emp_email`, `emp_password`) VALUES
(3, 'Ramin Sultani', 'staff', 'ramin@gmail.com', '6b619c03996be45662ea51cbaaa13e6d'),
(4, 'Hameed', 'admin', 'hameed@gmail.com', 'b4a52707cf537f112e12418829513ab9'),
(5, 'Omair', 'admin', 'omair@gmail.com', '1bcecb60cf0c4ac3712c61f8b985d56e');

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

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `e_id` int(11) NOT NULL,
  `exp_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `expenses_details`
--

CREATE TABLE `expenses_details` (
  `ex_id` int(11) NOT NULL,
  `amount` int(11) NOT NULL,
  `details` varchar(100) NOT NULL,
  `cp_id` int(11) NOT NULL,
  `e_date` date NOT NULL,
  `emp_id` int(11) NOT NULL,
  `e_id` int(11) NOT NULL,
  `currency` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `invoice_customer`
--

CREATE TABLE `invoice_customer` (
  `inv_no_c` int(11) NOT NULL,
  `total` float(11,2) NOT NULL,
  `received` float(11,2) NOT NULL,
  `reminder` float(11,2) NOT NULL,
  `cust_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `invoice_sup`
--

CREATE TABLE `invoice_sup` (
  `inv_no_s` int(11) NOT NULL,
  `total_am` int(11) NOT NULL,
  `paid` int(11) NOT NULL,
  `borrow` int(11) NOT NULL,
  `sup_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `in_stock`
--

CREATE TABLE `in_stock` (
  `s_in_id` int(11) NOT NULL,
  `item_quantity` int(11) NOT NULL,
  `orgin` varchar(50) NOT NULL,
  `part_price` decimal(11,2) NOT NULL,
  `total_price` decimal(11,2) NOT NULL,
  `date_m` date NOT NULL,
  `p_id` int(11) NOT NULL,
  `inv_no_s` int(11) NOT NULL,
  `cp_id` int(11) NOT NULL,
  `currency` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Stand-in structure for view `item_details`
-- (See below for the actual view)
--
CREATE TABLE `item_details` (
`p_id` int(11)
,`p_item` varchar(100)
,`p_number` varchar(50)
,`p_total_qty` int(11)
,`p_avg_price` decimal(11,2)
,`p_total_avg` decimal(11,2)
,`s_price` int(11)
,`car_id` int(11)
,`car_name` varchar(50)
);

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

--
-- Table structure for table `loan`
--

CREATE TABLE `loan` (
  `l_id` int(11) NOT NULL,
  `total_price` float(11,2) NOT NULL,
  `received_amount` float(11,2) NOT NULL,
  `borrow_amount` float(11,2) NOT NULL,
  `cust_id` int(11) DEFAULT NULL,
  `sup_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `loan_r_details`
--

CREATE TABLE `loan_r_details` (
  `loan_id` int(11) NOT NULL,
  `past_paid` float NOT NULL,
  `past_borrow` float NOT NULL,
  `receipt` float NOT NULL,
  `remaining` float NOT NULL,
  `pay_amount` float NOT NULL,
  `invoice_no` varchar(100) NOT NULL,
  `l_date` date NOT NULL,
  `cust_id` int(11) DEFAULT NULL,
  `sup_id` int(11) DEFAULT NULL,
  `cp_id` int(11) NOT NULL,
  `currency` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `out_stock`
--

CREATE TABLE `out_stock` (
  `s_out_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `orgin` varchar(50) NOT NULL,
  `unit_price` float(11,2) NOT NULL,
  `total_amount` float(11,2) NOT NULL,
  `profit` int(11) NOT NULL,
  `date` date NOT NULL,
  `p_id` int(11) NOT NULL,
  `inv_no_c` int(11) NOT NULL,
  `cp_id` int(11) NOT NULL,
  `currency` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `parts_number`
--

CREATE TABLE `parts_number` (
  `p_id` int(11) NOT NULL,
  `p_item` varchar(100) NOT NULL,
  `p_number` varchar(50) NOT NULL,
  `p_total_qty` int(11) NOT NULL,
  `p_avg_price` decimal(11,2) NOT NULL,
  `p_total_avg` decimal(11,2) NOT NULL,
  `s_price` int(11) NOT NULL,
  `orgin` varchar(50) NOT NULL,
  `car_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `sarafi_details`
--

CREATE TABLE `sarafi_details` (
  `s_id` int(11) NOT NULL,
  `s_name` varchar(50) NOT NULL,
  `s_number` int(11) NOT NULL,
  `s_shop` varchar(100) NOT NULL,
  `s_deposit` float(11,3) DEFAULT NULL,
  `s_withdraw` float(11,3) DEFAULT NULL,
  `s_currency` varchar(10) NOT NULL,
  `cp_id` int(11) NOT NULL,
  `s_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Table structure for table `stock`
--

CREATE TABLE `stock` (
  `stk_id` int(11) NOT NULL,
  `stock_name` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `stock`
--

INSERT INTO `stock` (`stk_id`, `stock_name`) VALUES
(1, 'Kabul-Qawi-Markaz'),
(2, 'Dubai-Jabal-Ali');

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

--
-- Table structure for table `supplier`
--

CREATE TABLE `supplier` (
  `sup_id` int(11) NOT NULL,
  `sup_name` varchar(50) NOT NULL,
  `sup_contact` int(11) NOT NULL,
  `sup_add` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

--
-- Structure for view `item_details`
--
DROP TABLE IF EXISTS `item_details`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `item_details`  AS  select `parts_number`.`p_id` AS `p_id`,`parts_number`.`p_item` AS `p_item`,`parts_number`.`p_number` AS `p_number`,`parts_number`.`p_total_qty` AS `p_total_qty`,`parts_number`.`p_avg_price` AS `p_avg_price`,`parts_number`.`p_total_avg` AS `p_total_avg`,`parts_number`.`s_price` AS `s_price`,`parts_number`.`car_id` AS `car_id`,`car_details`.`car_name` AS `car_name` from (`parts_number` left join `car_details` on(`parts_number`.`car_id` = `car_details`.`car_id`)) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `car_details`
--
ALTER TABLE `car_details`
  ADD PRIMARY KEY (`car_id`);

--
-- Indexes for table `cash_point`
--
ALTER TABLE `cash_point`
  ADD PRIMARY KEY (`cp_id`),
  ADD KEY `emp_id` (`emp_id`);

--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
  ADD PRIMARY KEY (`cust_id`);

--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
  ADD PRIMARY KEY (`emp_id`);

--
-- Indexes for table `expenses`
--
ALTER TABLE `expenses`
  ADD PRIMARY KEY (`e_id`);

--
-- Indexes for table `expenses_details`
--
ALTER TABLE `expenses_details`
  ADD PRIMARY KEY (`ex_id`),
  ADD KEY `e_id` (`e_id`),
  ADD KEY `emp_id` (`emp_id`);

--
-- Indexes for table `invoice_customer`
--
ALTER TABLE `invoice_customer`
  ADD PRIMARY KEY (`inv_no_c`),
  ADD KEY `cust_id` (`cust_id`);

--
-- Indexes for table `invoice_sup`
--
ALTER TABLE `invoice_sup`
  ADD PRIMARY KEY (`inv_no_s`),
  ADD KEY `sup_id` (`sup_id`);

--
-- Indexes for table `in_stock`
--
ALTER TABLE `in_stock`
  ADD PRIMARY KEY (`s_in_id`),
  ADD KEY `inv_no_s` (`inv_no_s`),
  ADD KEY `item_id` (`p_id`);

--
-- Indexes for table `loan`
--
ALTER TABLE `loan`
  ADD PRIMARY KEY (`l_id`);

--
-- Indexes for table `loan_r_details`
--
ALTER TABLE `loan_r_details`
  ADD PRIMARY KEY (`loan_id`),
  ADD KEY `cust_id` (`cust_id`),
  ADD KEY `sup_id` (`sup_id`);

--
-- Indexes for table `out_stock`
--
ALTER TABLE `out_stock`
  ADD PRIMARY KEY (`s_out_id`),
  ADD KEY `inv_no_c` (`inv_no_c`),
  ADD KEY `item_id` (`p_id`);

--
-- Indexes for table `parts_number`
--
ALTER TABLE `parts_number`
  ADD PRIMARY KEY (`p_id`),
  ADD KEY `cat_id` (`car_id`);

--
-- Indexes for table `sarafi_details`
--
ALTER TABLE `sarafi_details`
  ADD PRIMARY KEY (`s_id`);

--
-- Indexes for table `stock`
--
ALTER TABLE `stock`
  ADD PRIMARY KEY (`stk_id`);

--
-- Indexes for table `supplier`
--
ALTER TABLE `supplier`
  ADD PRIMARY KEY (`sup_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `car_details`
--
ALTER TABLE `car_details`
  MODIFY `car_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `customer`
--
ALTER TABLE `customer`
  MODIFY `cust_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `employee`
--
ALTER TABLE `employee`
  MODIFY `emp_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `expenses`
--
ALTER TABLE `expenses`
  MODIFY `e_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `expenses_details`
--
ALTER TABLE `expenses_details`
  MODIFY `ex_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `in_stock`
--
ALTER TABLE `in_stock`
  MODIFY `s_in_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan`
--
ALTER TABLE `loan`
  MODIFY `l_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `loan_r_details`
--
ALTER TABLE `loan_r_details`
  MODIFY `loan_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `out_stock`
--
ALTER TABLE `out_stock`
  MODIFY `s_out_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `parts_number`
--
ALTER TABLE `parts_number`
  MODIFY `p_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `sarafi_details`
--
ALTER TABLE `sarafi_details`
  MODIFY `s_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock`
--
ALTER TABLE `stock`
  MODIFY `stk_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `supplier`
--
ALTER TABLE `supplier`
  MODIFY `sup_id` int(11) NOT NULL AUTO_INCREMENT;
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 */;
