Gas

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


CREATE TABLE `invoice` (
  `iid` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `username` varchar(100) NOT NULL,
  `name` varchar(40) NOT NULL,
  `service` varchar(40) NOT NULL,
  `date` date NOT NULL,
  `amount` int(11) NOT NULL,
  `status` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



INSERT INTO `invoice` (`iid`, `sid`, `username`, `name`, `service`, `date`, `amount`, `status`) VALUES
(1, 30, 'utkarsh', 'kumar', 'chassis', '2022-12-29', 20000, 'unpaid'),
(3, 31, 'utkarsh', 'harsh', 'chassis', '2023-01-10', 50000, 'unpaid'),
(4, 27, 'utkarsh', 'Sagotia', 'chassis', '2022-12-28', 60000, 'unpaid'),
(6, 36, 'tanay', 'Jack', 'headlight', '2023-01-03', 50000, 'unpaid'),
(8, 27, '', 'Sagotia', 'chassis', '2022-12-28', 50000, 'unpaid'),
(10, 37, 'tanay', 'Tunna Zii', 'puncture', '2023-01-03', 1000, 'unpaid');



CREATE TABLE `service` (
  `id` int(100) NOT NULL,
  `car_no` varchar(100) NOT NULL,
  `username` varchar(20) NOT NULL,
  `owner_name` varchar(100) NOT NULL,
  `category` varchar(100) NOT NULL,
  `service_type` varchar(1000) NOT NULL,
  `date` date NOT NULL,
  `status` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



INSERT INTO `service` (`id`, `car_no`, `username`, `owner_name`, `category`, `service_type`, `date`, `status`) VALUES
(27, 'KA26MA6789', 'utkarsh', 'Utkarsh', 'mcwg', 'chassis', '2022-12-28', 'completed'),
(29, 'BR06', 'utkarsh', 'Utkarsh', '4', 'servicing', '2022-12-29', 'pending'),
(30, 'RJ01CC123', 'utkarsh', 'Sam', '1', 'chassis', '2022-12-29', 'pending'),
(31, '12345689', 'utkarsh', 'Pintu', '3', 'engine', '2023-01-10', 'pending'),
(34, 'RJ01CC0001', 'utkarsh', 'Sohail', '4', 'ppf', '2023-01-19', 'pending'),
(35, 'KL08AF6270', 'utkarsh', 'Sunny', '2', 'piston', '2022-12-27', 'pending'),
(36, 'BR01DH0001', 'tanay', 'Jack', '4', 'headlight', '2023-01-03', 'completed'),
(37, 'BR01DH0001', 'tanay', 'Tunna zii', '4', 'puncture', '2023-01-03', 'completed');



CREATE TABLE `staff` (
  `sid` int(11) NOT NULL,
  `name` varchar(20) NOT NULL,
  `domain` varchar(20) NOT NULL,
  `current_status` varchar(20) NOT NULL,
  `start_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


INSERT INTO `staff` (`sid`, `name`, `domain`, `current_status`, `start_date`) VALUES
(1, 'Tunna Zii', 'Tyre work', 'free', '2022-12-14'),
(2, 'Sahil', 'Wash', 'free', '2022-12-28'),
(3, 'Tauqueer', 'Engine', 'free', '2022-12-28'),
(4, 'Tanmay', 'Paint', 'free', '2022-12-07');



CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(100) NOT NULL,
  `name` varchar(100) NOT NULL,
  `password` varchar(50) NOT NULL,
  `role` varchar(20) NOT NULL DEFAULT 'customer',
  `idproof` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



INSERT INTO `users` (`id`, `username`, `name`, `password`, `role`, `idproof`) VALUES
(1, 'utkarsh', 'Utkarsh', 'utkarsh@123', 'customer', '1234'),
(4, 'admin', 'admin', 'admin123', 'admin', '09912'),
(7, 'tanay', 'Tanay', 'tanay@123', 'customer', '1234');



CREATE TABLE `works` (
  `serviceid` int(11) NOT NULL,
  `staffid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `invoice`
  ADD PRIMARY KEY (`iid`);


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


ALTER TABLE `staff`
  ADD PRIMARY KEY (`sid`);


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


ALTER TABLE `works`
  ADD PRIMARY KEY (`serviceid`,`staffid`);


ALTER TABLE `invoice`
  MODIFY `iid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;


ALTER TABLE `service`
  MODIFY `id` int(100) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=38;


ALTER TABLE `staff`
  MODIFY `sid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
COMMIT;