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

START TRANSACTION;



--
-- Database: `sps`
--

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

--
-- Table structure for table `sps_barrier_opening`
--

CREATE TABLE `sps_barrier_opening` (
  `id` int(11) NOT NULL,
  `vehicle_no` varchar(100) NOT NULL,
  `vehicle_type` varchar(10) NOT NULL,
  `gate_id` varchar(100) NOT NULL,
  `reason` varchar(300) NOT NULL,
  `request_user_id` bigint(20) NOT NULL,
  `admin_approver_id` bigint(20) NOT NULL,
  `status` varchar(50) NOT NULL COMMENT 'Defaults : Approved, Completed, Failed, Pending, Rejected',
  `image_path` varchar(300) DEFAULT NULL,
  `note` varchar(500) DEFAULT NULL,
  `area_id` bigint(20) NOT NULL,
  `location_id` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT NULL,
  `sync_at` timestamp NULL DEFAULT NULL,
  `superadmin` bigint(20) NOT NULL DEFAULT 0,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `sps_barrier_opening`
--


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

--
-- Table structure for table `sps_barrier_opening_reasons`
--

CREATE TABLE `sps_barrier_opening_reasons` (
  `id` int(11) NOT NULL,
  `reason` varchar(255) NOT NULL,
  `created_by` bigint(20) NOT NULL,
  `updated_by` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `sps_barrier_opening_reasons`
--

INSERT INTO `sps_barrier_opening_reasons` (`id`, `reason`, `created_by`, `updated_by`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'reason 1', 1, 1, '2021-07-09 04:23:46', NULL, NULL),
(2, 'reason 2', 1, 1, '2021-07-09 04:24:27', NULL, NULL),
(3, 'reason 3', 1, 1, '2021-07-09 04:24:27', NULL, NULL);

--
-- Indexes for dumped tables
--

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

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

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `sps_barrier_opening_reasons`
--
ALTER TABLE `sps_barrier_opening_reasons`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
COMMIT;




INSERT INTO `sps_menu` (`menu_item`, `menu_url`, `menu_class`, `parent_menu_id`, `action`, `order`, `status`, `location_id`, `area_id`, `created_by`, `created_at`, `updated_at`, `deleted_at`, `updated_by`) VALUES
('gate_management', '', '', 0, '1', 12.00, 1, 0, 0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, 0),
('manual_opening_bg_request', 'admin/manual_opening_bg_request', 'menu', 0, '1,2,6', 12.02, 1, 0, 0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, NULL, 0)
;


UPDATE sps_menu t
    SET parent_menu_id = (SELECT id
                FROM sps_menu t2
                WHERE menu_item ='gate_management' AND
                      status = 1
               ) 
    WHERE status = 1 and (menu_item ='gate_monitoring' OR menu_item ='manual_opening_bg_request');
	
	
INSERT INTO sps_menu_action_mapping ( menu_id, action_id)
SELECT  sps_menu.id, 1
FROM    sps_menu
WHERE   sps_menu.menu_item = 'gate_management';

INSERT INTO sps_menu_action_mapping ( menu_id, action_id)
SELECT  sps_menu.id, 1
FROM    sps_menu
WHERE   sps_menu.menu_item = 'manual_opening_bg_request';

INSERT INTO sps_menu_action_mapping ( menu_id, action_id)
SELECT  sps_menu.id, 2
FROM    sps_menu
WHERE   sps_menu.menu_item = 'manual_opening_bg_request';

INSERT INTO sps_menu_action_mapping ( menu_id, action_id)
SELECT  sps_menu.id, 6
FROM    sps_menu
WHERE   sps_menu.menu_item = 'manual_opening_bg_request';


ALTER TABLE `sps_barrier_opening` CHANGE `request_user_id` `request_user_id` VARCHAR(300) NOT NULL;