-- XAMPP-Lite
-- https://xampplite.sf.net/
--
-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `project_management`
--

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

--
-- Table structure for table `activity_logs`
--

CREATE TABLE `activity_logs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED DEFAULT NULL,
  `user_name` varchar(255) NOT NULL,
  `action` varchar(255) NOT NULL,
  `subject_type` varchar(255) DEFAULT NULL,
  `subject_id` bigint(20) UNSIGNED DEFAULT NULL,
  `description` text DEFAULT NULL,
  `properties` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`properties`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `activity_logs`
--

INSERT INTO `activity_logs` (`id`, `project_id`, `user_name`, `action`, `subject_type`, `subject_id`, `description`, `properties`, `created_at`, `updated_at`) VALUES
(1, 6, 'Admin User', 'created', NULL, NULL, 'Created project: Aaradhya Residential Tower', NULL, '2026-03-11 06:15:33', '2026-03-11 07:15:33'),
(2, 4, 'Rajesh Sharma', 'updated', NULL, NULL, 'Updated task: Concrete slab pouring progress to 75%', NULL, '2026-03-11 03:15:33', '2026-03-11 07:15:33'),
(3, 4, 'Priya Patel', 'created', NULL, NULL, 'Added daily report for GreenPark Complex', NULL, '2026-03-11 01:15:33', '2026-03-11 07:15:33'),
(4, 3, 'Sunita Devi', 'completed', NULL, NULL, 'Completed inspection: Steel Reinforcement Check - PASS', NULL, '2026-03-10 21:15:34', '2026-03-11 07:15:34'),
(5, 5, 'AI System', 'alert', NULL, NULL, 'AI detected potential 18-day delay in structural work', NULL, '2026-03-10 19:15:34', '2026-03-11 07:15:34'),
(6, 2, 'Vikram Singh', 'created', NULL, NULL, 'Raised risk: Monsoon Delay Risk (High Severity)', NULL, '2026-03-10 14:15:34', '2026-03-11 07:15:34'),
(7, 6, 'Neha Gupta', 'updated', NULL, NULL, 'Updated budget: Steel costs increased by ₹5.2L', NULL, '2026-03-10 12:15:34', '2026-03-11 07:15:34'),
(8, 3, 'Admin User', 'created', NULL, NULL, 'Added new contractor: SteelTech Engineering', NULL, '2026-03-10 08:15:34', '2026-03-11 07:15:34'),
(9, 6, 'Rohit Verma', 'consumed', NULL, NULL, 'Material consumed: 200 bags OPC Cement for Tower A', NULL, '2026-03-10 07:15:34', '2026-03-11 07:15:34'),
(10, 1, 'Rajesh Sharma', 'updated', NULL, NULL, 'Phase 3 Structure marked as In Progress', NULL, '2026-03-10 03:15:34', '2026-03-11 07:15:34');

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

--
-- Table structure for table `ai_predictions`
--

CREATE TABLE `ai_predictions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `type` enum('delay','cost_overrun','quality','safety','weather','resource') NOT NULL DEFAULT 'delay',
  `title` varchar(255) NOT NULL,
  `prediction` text NOT NULL,
  `confidence` decimal(5,2) NOT NULL DEFAULT 0.00,
  `severity` enum('info','warning','critical') NOT NULL DEFAULT 'info',
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`data`)),
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `is_resolved` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_predictions`
--

INSERT INTO `ai_predictions` (`id`, `project_id`, `type`, `title`, `prediction`, `confidence`, `severity`, `data`, `is_read`, `is_resolved`, `created_at`, `updated_at`) VALUES
(1, 3, 'delay', 'Schedule Slippage Detected', 'Project \"Aaradhya Residential Tower\" structural work is 12% behind schedule. AI analysis of daily progress data indicates a potential 18-day delay in Phase 3 completion. Recommended: Deploy additional crew and extend work hours.', 87.00, 'warning', NULL, 0, 0, '2026-03-08 08:15:33', '2026-03-11 07:15:33'),
(2, 2, 'cost_overrun', 'Budget Overrun Forecast', 'Based on current spending trajectory, \"GreenPark Commercial Complex\" is projected to exceed budget by ₹4.2 Cr (5.4%). Major variance in steel and labor costs. Immediate procurement review recommended.', 92.00, 'critical', NULL, 0, 0, '2026-03-09 18:15:33', '2026-03-11 07:15:33'),
(3, 3, 'safety', 'Safety Compliance Alert', 'Analysis of 15 daily reports from \"Sunrise Highway Extension\" shows declining PPE compliance trend (94% → 81% over 2 weeks). Historical data suggests increased incident probability. Immediate safety audit recommended.', 78.00, 'critical', NULL, 0, 0, '2026-03-10 05:15:33', '2026-03-11 07:15:33'),
(4, 1, 'resource', 'Material Shortage Warning', '3 critical materials (PPC Cement, TMT 16mm, AAC Blocks) are below minimum stock levels. Based on consumption rate, stockout expected within 5-8 working days. Urgent procurement needed.', 95.00, 'warning', NULL, 0, 0, '2026-03-10 16:15:33', '2026-03-11 07:15:33'),
(5, 3, 'quality', 'Quality Trend Analysis', 'Inspection pass rate has improved from 72% to 89% over the last 30 days. Concrete quality issues in Tower B resolved after supplier change. Recommend continuing current QC protocols.', 91.00, 'info', NULL, 1, 0, '2026-03-10 02:15:33', '2026-03-11 07:15:33'),
(6, 3, 'weather', 'Weather Impact Forecast', 'Meteorological data indicates 70% probability of heavy rainfall (>50mm) in the next 7 days for Mumbai region. Projects at Andheri West may face 2-3 day work stoppage. Pre-position waterproofing materials.', 72.00, 'warning', NULL, 1, 0, '2026-03-09 03:15:33', '2026-03-11 07:15:33'),
(7, 1, 'resource', 'Material Shortage Warning', '3 materials are running low. Immediate procurement recommended to avoid work stoppage.', 88.00, 'warning', NULL, 0, 0, '2026-03-11 08:00:25', '2026-03-11 08:00:25'),
(8, 1, 'safety', 'Unresolved Critical Risks', '3 critical risks remain unresolved across active projects. Immediate attention required.', 85.00, 'critical', NULL, 0, 0, '2026-03-11 08:00:25', '2026-03-11 08:00:25');

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

--
-- Table structure for table `attendance_records`
--

CREATE TABLE `attendance_records` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `date` date NOT NULL,
  `worker_name` varchar(255) NOT NULL,
  `worker_type` enum('skilled','unskilled','supervisor','engineer','contractor') NOT NULL DEFAULT 'skilled',
  `contractor_name` varchar(255) DEFAULT NULL,
  `check_in` time DEFAULT NULL,
  `check_out` time DEFAULT NULL,
  `hours_worked` decimal(5,2) NOT NULL DEFAULT 0.00,
  `overtime_hours` decimal(5,2) NOT NULL DEFAULT 0.00,
  `status` enum('present','absent','half_day','overtime') NOT NULL DEFAULT 'present',
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `attendance_records`
--

INSERT INTO `attendance_records` (`id`, `project_id`, `date`, `worker_name`, `worker_type`, `contractor_name`, `check_in`, `check_out`, `hours_worked`, `overtime_hours`, `status`, `notes`, `created_at`, `updated_at`) VALUES
(1, 1, '2026-03-11', 'Worker A - Site 1', 'skilled', 'ABC Contractors', '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 1, '2026-03-11', 'Worker B - Site 1', 'unskilled', 'ABC Contractors', '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 1, '2026-03-11', 'Worker C - Site 1', 'supervisor', NULL, '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(4, 1, '2026-03-11', 'Worker D - Site 1', 'engineer', NULL, '08:00:00', '19:00:00', 8.00, 3.00, 'overtime', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(5, 2, '2026-03-11', 'Worker A - Site 2', 'skilled', 'ABC Contractors', '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(6, 2, '2026-03-11', 'Worker B - Site 2', 'unskilled', 'ABC Contractors', '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(7, 2, '2026-03-11', 'Worker C - Site 2', 'supervisor', NULL, '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(8, 2, '2026-03-11', 'Worker D - Site 2', 'engineer', NULL, '08:00:00', '19:00:00', 8.00, 3.00, 'overtime', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(9, 6, '2026-03-11', 'Worker A - Site 6', 'skilled', 'ABC Contractors', '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(10, 6, '2026-03-11', 'Worker B - Site 6', 'unskilled', 'ABC Contractors', '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(11, 6, '2026-03-11', 'Worker C - Site 6', 'supervisor', NULL, '08:00:00', '17:00:00', 8.00, 0.00, 'present', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(12, 6, '2026-03-11', 'Worker D - Site 6', 'engineer', NULL, '08:00:00', '19:00:00', 8.00, 3.00, 'overtime', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `budget_items`
--

CREATE TABLE `budget_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `category` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `planned_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `actual_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `budget_items`
--

INSERT INTO `budget_items` (`id`, `project_id`, `category`, `description`, `planned_amount`, `actual_amount`, `created_at`, `updated_at`) VALUES
(1, 1, 'Land & Approvals', 'Land & Approvals costs for Aaradhya Residential Tower', 7718287.00, 8069932.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(2, 1, 'Foundation', 'Foundation costs for Aaradhya Residential Tower', 3878623.00, 3973892.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(3, 1, 'Structure & RCC', 'Structure & RCC costs for Aaradhya Residential Tower', 12858938.00, 13611858.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(4, 1, 'Brickwork & Masonry', 'Brickwork & Masonry costs for Aaradhya Residential Tower', 2967156.00, 2933417.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(5, 1, 'Electrical', 'Electrical costs for Aaradhya Residential Tower', 3142453.00, 3379694.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(6, 1, 'Plumbing & Sanitary', 'Plumbing & Sanitary costs for Aaradhya Residential Tower', 1923265.00, 1995055.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(7, 1, 'Finishing & Interiors', 'Finishing & Interiors costs for Aaradhya Residential Tower', 6132717.00, 6040029.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(8, 1, 'Labour Costs', 'Labour Costs costs for Aaradhya Residential Tower', 4456322.00, 5195797.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(9, 1, 'Equipment Rental', 'Equipment Rental costs for Aaradhya Residential Tower', 2195289.00, 2149803.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(10, 1, 'Miscellaneous', 'Miscellaneous costs for Aaradhya Residential Tower', 1472105.00, 1509434.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(11, 2, 'Land & Approvals', 'Land & Approvals costs for GreenPark Commercial Complex', 7610481.00, 7430051.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(12, 2, 'Foundation', 'Foundation costs for GreenPark Commercial Complex', 4287359.00, 4314972.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(13, 2, 'Structure & RCC', 'Structure & RCC costs for GreenPark Commercial Complex', 12703043.00, 12679441.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(14, 2, 'Brickwork & Masonry', 'Brickwork & Masonry costs for GreenPark Commercial Complex', 3783498.00, 3974274.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(15, 2, 'Electrical', 'Electrical costs for GreenPark Commercial Complex', 2811641.00, 2962745.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(16, 2, 'Plumbing & Sanitary', 'Plumbing & Sanitary costs for GreenPark Commercial Complex', 2163329.00, 2120289.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(17, 2, 'Finishing & Interiors', 'Finishing & Interiors costs for GreenPark Commercial Complex', 5138752.00, 5023387.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(18, 2, 'Labour Costs', 'Labour Costs costs for GreenPark Commercial Complex', 8186220.00, 8817518.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(19, 2, 'Equipment Rental', 'Equipment Rental costs for GreenPark Commercial Complex', 2579891.00, 2756541.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(20, 2, 'Miscellaneous', 'Miscellaneous costs for GreenPark Commercial Complex', 1405865.00, 1607872.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(21, 3, 'Land & Approvals', 'Land & Approvals costs for Sunrise Highway Extension', 6426862.00, 6393503.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(22, 3, 'Foundation', 'Foundation costs for Sunrise Highway Extension', 5395101.00, 5341879.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(23, 3, 'Structure & RCC', 'Structure & RCC costs for Sunrise Highway Extension', 14257026.00, 14599241.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(24, 3, 'Brickwork & Masonry', 'Brickwork & Masonry costs for Sunrise Highway Extension', 3319568.00, 3361822.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(25, 3, 'Electrical', 'Electrical costs for Sunrise Highway Extension', 2225629.00, 2162940.00, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(26, 3, 'Plumbing & Sanitary', 'Plumbing & Sanitary costs for Sunrise Highway Extension', 2624074.00, 2635717.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(27, 3, 'Finishing & Interiors', 'Finishing & Interiors costs for Sunrise Highway Extension', 5670788.00, 6224334.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(28, 3, 'Labour Costs', 'Labour Costs costs for Sunrise Highway Extension', 9262288.00, 9356372.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(29, 3, 'Equipment Rental', 'Equipment Rental costs for Sunrise Highway Extension', 1489941.00, 1578590.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(30, 3, 'Miscellaneous', 'Miscellaneous costs for Sunrise Highway Extension', 929460.00, 1171848.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(31, 4, 'Land & Approvals', 'Land & Approvals costs for Vedanta Industrial Park', 7455253.00, 7266609.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(32, 4, 'Foundation', 'Foundation costs for Vedanta Industrial Park', 3901014.00, 4074914.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(33, 4, 'Structure & RCC', 'Structure & RCC costs for Vedanta Industrial Park', 11445212.00, 12136615.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(34, 4, 'Brickwork & Masonry', 'Brickwork & Masonry costs for Vedanta Industrial Park', 2208652.00, 2218543.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(35, 4, 'Electrical', 'Electrical costs for Vedanta Industrial Park', 3206557.00, 3131144.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(36, 4, 'Plumbing & Sanitary', 'Plumbing & Sanitary costs for Vedanta Industrial Park', 2573842.00, 2572671.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(37, 4, 'Finishing & Interiors', 'Finishing & Interiors costs for Vedanta Industrial Park', 4490129.00, 4586288.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(38, 4, 'Labour Costs', 'Labour Costs costs for Vedanta Industrial Park', 6005003.00, 6749668.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(39, 4, 'Equipment Rental', 'Equipment Rental costs for Vedanta Industrial Park', 2750531.00, 2765726.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(40, 4, 'Miscellaneous', 'Miscellaneous costs for Vedanta Industrial Park', 775702.00, 814139.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(41, 5, 'Land & Approvals', 'Land & Approvals costs for Royal Palms Villa Community', 6549603.00, 6616163.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(42, 5, 'Foundation', 'Foundation costs for Royal Palms Villa Community', 4199380.00, 4430894.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(43, 5, 'Structure & RCC', 'Structure & RCC costs for Royal Palms Villa Community', 8271727.00, 8035922.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(44, 5, 'Brickwork & Masonry', 'Brickwork & Masonry costs for Royal Palms Villa Community', 3340656.00, 3478815.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(45, 5, 'Electrical', 'Electrical costs for Royal Palms Villa Community', 2289278.00, 2520918.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(46, 5, 'Plumbing & Sanitary', 'Plumbing & Sanitary costs for Royal Palms Villa Community', 2693572.00, 2646311.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(47, 5, 'Finishing & Interiors', 'Finishing & Interiors costs for Royal Palms Villa Community', 5822021.00, 6071894.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(48, 5, 'Labour Costs', 'Labour Costs costs for Royal Palms Villa Community', 7135064.00, 7557836.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(49, 5, 'Equipment Rental', 'Equipment Rental costs for Royal Palms Villa Community', 1713499.00, 1726134.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(50, 5, 'Miscellaneous', 'Miscellaneous costs for Royal Palms Villa Community', 1411458.00, 1629588.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(51, 6, 'Land & Approvals', 'Land & Approvals costs for Metro City Mall Renovation', 7568130.00, 7401801.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(52, 6, 'Foundation', 'Foundation costs for Metro City Mall Renovation', 4881876.00, 4893100.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(53, 6, 'Structure & RCC', 'Structure & RCC costs for Metro City Mall Renovation', 10931291.00, 11341889.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(54, 6, 'Brickwork & Masonry', 'Brickwork & Masonry costs for Metro City Mall Renovation', 2218503.00, 2350132.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(55, 6, 'Electrical', 'Electrical costs for Metro City Mall Renovation', 4617410.00, 4727363.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(56, 6, 'Plumbing & Sanitary', 'Plumbing & Sanitary costs for Metro City Mall Renovation', 2060291.00, 2071703.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(57, 6, 'Finishing & Interiors', 'Finishing & Interiors costs for Metro City Mall Renovation', 6394559.00, 6941007.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(58, 6, 'Labour Costs', 'Labour Costs costs for Metro City Mall Renovation', 9759498.00, 10138662.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(59, 6, 'Equipment Rental', 'Equipment Rental costs for Metro City Mall Renovation', 2031281.00, 2120755.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(60, 6, 'Miscellaneous', 'Miscellaneous costs for Metro City Mall Renovation', 973332.00, 1176257.00, '2026-03-11 07:15:32', '2026-03-11 07:15:32');

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

--
-- Table structure for table `change_orders`
--

CREATE TABLE `change_orders` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `requested_by` varchar(255) NOT NULL,
  `type` enum('scope','design','material','schedule','cost','other') NOT NULL DEFAULT 'scope',
  `status` enum('draft','submitted','under_review','approved','rejected','implemented') NOT NULL DEFAULT 'draft',
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `cost_impact` decimal(15,2) NOT NULL DEFAULT 0.00,
  `schedule_impact_days` int(11) NOT NULL DEFAULT 0,
  `justification` text DEFAULT NULL,
  `approved_by` varchar(255) DEFAULT NULL,
  `submitted_date` date DEFAULT NULL,
  `decision_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `change_orders`
--

INSERT INTO `change_orders` (`id`, `project_id`, `title`, `description`, `requested_by`, `type`, `status`, `priority`, `cost_impact`, `schedule_impact_days`, `justification`, `approved_by`, `submitted_date`, `decision_date`, `created_at`, `updated_at`) VALUES
(1, 1, 'Additional Floor Requested', 'Client requested an additional floor', 'Client', 'scope', 'approved', 'high', 1500000.00, 30, 'Detailed justification provided in attached document', 'Project Director', '2026-02-15', '2026-03-06', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 2, 'Design Modification - Lobby', 'Lobby design changed per architect', 'Architect', 'design', 'submitted', 'medium', -50000.00, 0, 'Detailed justification provided in attached document', NULL, '2026-02-19', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 6, 'Material Upgrade - Tiles', 'Upgrade floor tiles to premium grade', 'Interior Designer', 'material', 'draft', 'low', 280000.00, 7, 'Detailed justification provided in attached document', NULL, '2026-02-20', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `commentable_type` varchar(255) NOT NULL,
  `commentable_id` bigint(20) UNSIGNED NOT NULL,
  `admin_id` bigint(20) UNSIGNED NOT NULL,
  `body` text NOT NULL,
  `parent_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `compliance_items`
--

CREATE TABLE `compliance_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `type` enum('building_permit','environmental','fire_safety','structural','electrical','plumbing','occupancy','rera','labor','insurance','other') NOT NULL DEFAULT 'other',
  `status` enum('pending','applied','approved','expired','rejected','renewal_due') NOT NULL DEFAULT 'pending',
  `authority` varchar(255) NOT NULL,
  `reference_number` varchar(255) DEFAULT NULL,
  `applied_date` date DEFAULT NULL,
  `approval_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `document_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compliance_items`
--

INSERT INTO `compliance_items` (`id`, `project_id`, `title`, `type`, `status`, `authority`, `reference_number`, `applied_date`, `approval_date`, `expiry_date`, `notes`, `document_path`, `created_at`, `updated_at`) VALUES
(1, 1, 'Building permit Certificate', 'building_permit', 'approved', 'Municipal Corp', 'REF-BUI-2026-001', '2025-12-31', '2026-02-05', '2026-06-08', 'Compliance documentation maintained', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 2, 'Environmental Certificate', 'environmental', 'approved', 'State PCB', 'REF-ENV-2026-002', '2025-11-27', '2026-02-17', '2026-06-25', 'Compliance documentation maintained', NULL, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 6, 'Fire safety Certificate', 'fire_safety', 'pending', 'Fire Dept', 'REF-FIR-2026-003', '2025-12-16', NULL, '2026-10-29', 'Compliance documentation maintained', NULL, '2026-03-11 09:18:15', '2026-03-11 09:18:15'),
(4, 5, 'Rera Certificate', 'rera', 'approved', 'RERA Authority', 'REF-RER-2026-004', '2025-12-31', '2026-01-24', '2026-06-26', 'Compliance documentation maintained', NULL, '2026-03-11 09:18:15', '2026-03-11 09:18:15'),
(5, 3, 'Labor Certificate', 'labor', 'renewal_due', 'Labor Dept', 'REF-LAB-2026-005', '2026-01-05', NULL, '2026-03-26', 'Compliance documentation maintained', NULL, '2026-03-11 09:18:15', '2026-03-11 09:18:15'),
(6, 4, 'Insurance Certificate', 'insurance', 'approved', 'Insurance Co', 'REF-INS-2026-006', '2025-11-30', '2026-02-12', '2026-12-06', 'Compliance documentation maintained', NULL, '2026-03-11 09:18:15', '2026-03-11 09:18:15');

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

--
-- Table structure for table `contractors`
--

CREATE TABLE `contractors` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `company` varchar(255) NOT NULL,
  `type` enum('civil','electrical','plumbing','interior','painting','hvac','structural','other') NOT NULL DEFAULT 'civil',
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `license_number` varchar(255) DEFAULT NULL,
  `rating` decimal(3,1) NOT NULL DEFAULT 0.0,
  `status` enum('active','inactive','blacklisted') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `contractors`
--

INSERT INTO `contractors` (`id`, `name`, `company`, `type`, `email`, `phone`, `address`, `license_number`, `rating`, `status`, `created_at`, `updated_at`) VALUES
(1, 'Ravi Construction', 'Ravi & Sons Builders', 'civil', 'ravi@builders.com', '9988776655', NULL, 'CL-MH-2023-001', 4.2, 'active', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(2, 'Spark Electricals', 'Spark Power Systems', 'electrical', 'spark@power.com', '9988776656', NULL, 'EL-MH-2023-045', 4.5, 'active', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(3, 'Aquaflow Plumbing', 'Aquaflow Solutions Pvt Ltd', 'plumbing', 'aqua@plumbing.com', '9988776657', NULL, 'PL-MH-2022-112', 3.8, 'active', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(4, 'Elegant Interiors', 'Elegant Design Studio', 'interior', 'elegant@interiors.com', '9988776658', NULL, 'IN-MH-2024-033', 4.7, 'active', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(5, 'ColorMax Painters', 'ColorMax Services', 'painting', 'color@painters.com', '9988776659', NULL, 'PT-MH-2023-078', 4.0, 'active', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(6, 'CoolAir HVAC', 'CoolAir Conditioning Pvt Ltd', 'hvac', 'cool@hvac.com', '9988776660', NULL, 'HV-MH-2023-015', 4.3, 'active', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(7, 'SteelTech Structural', 'SteelTech Engineering', 'structural', 'steel@tech.com', '9988776661', NULL, 'ST-MH-2022-089', 4.6, 'active', '2026-03-11 07:15:30', '2026-03-11 07:15:30');

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

--
-- Table structure for table `contracts`
--

CREATE TABLE `contracts` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `contractor_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `scope` text DEFAULT NULL,
  `value` decimal(15,2) NOT NULL DEFAULT 0.00,
  `paid` decimal(15,2) NOT NULL DEFAULT 0.00,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `status` enum('draft','active','completed','terminated') NOT NULL DEFAULT 'draft',
  `penalty_per_day` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `contracts`
--

INSERT INTO `contracts` (`id`, `project_id`, `contractor_id`, `title`, `scope`, `value`, `paid`, `start_date`, `end_date`, `status`, `penalty_per_day`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 'painting work for Aaradhya Residential Tower', 'Complete painting work as per approved drawings and specifications.', 515467.00, 441285.00, '2025-06-15', '2027-12-31', 'active', 6450.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(2, 1, 7, 'structural work for Aaradhya Residential Tower', 'Complete structural work as per approved drawings and specifications.', 4849083.00, 2165214.00, '2025-06-15', '2027-12-31', 'active', 22382.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(3, 1, 2, 'electrical work for Aaradhya Residential Tower', 'Complete electrical work as per approved drawings and specifications.', 4028064.00, 2581772.00, '2025-06-15', '2027-12-31', 'active', 7884.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(4, 2, 3, 'plumbing work for GreenPark Commercial Complex', 'Complete plumbing work as per approved drawings and specifications.', 4228487.00, 567119.00, '2025-03-01', '2027-06-30', 'active', 17305.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(5, 2, 6, 'hvac work for GreenPark Commercial Complex', 'Complete hvac work as per approved drawings and specifications.', 2867030.00, 2340727.00, '2025-03-01', '2027-06-30', 'active', 18864.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(6, 2, 4, 'interior work for GreenPark Commercial Complex', 'Complete interior work as per approved drawings and specifications.', 1479938.00, 1217657.00, '2025-03-01', '2027-06-30', 'active', 11994.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(7, 3, 7, 'structural work for Sunrise Highway Extension', 'Complete structural work as per approved drawings and specifications.', 3505363.00, 2746081.00, '2025-01-10', '2026-12-31', 'active', 19457.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(8, 3, 2, 'electrical work for Sunrise Highway Extension', 'Complete electrical work as per approved drawings and specifications.', 3096092.00, 408733.00, '2025-01-10', '2026-12-31', 'active', 6618.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(9, 3, 5, 'painting work for Sunrise Highway Extension', 'Complete painting work as per approved drawings and specifications.', 4235872.00, 1970499.00, '2025-01-10', '2026-12-31', 'active', 22153.00, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(10, 1, 1, 'Main Building Contract', 'Complete scope as per BOQ and specifications', 5000000.00, 2000000.00, '2026-01-22', '2026-08-04', 'active', 5000.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(11, 2, 2, 'Electrical Sub-Contract', 'Complete scope as per BOQ and specifications', 1200000.00, 600000.00, '2025-12-30', '2026-06-12', 'active', 2000.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(12, 6, 3, 'Plumbing Works Contract', 'Complete scope as per BOQ and specifications', 800000.00, 0.00, '2025-12-17', '2026-05-26', 'draft', 1500.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(13, 5, 4, 'Finishing Works Contract', 'Complete scope as per BOQ and specifications', 1500000.00, 500000.00, '2025-12-31', '2026-05-20', 'active', 3000.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `daily_reports`
--

CREATE TABLE `daily_reports` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `report_date` date NOT NULL,
  `weather` varchar(255) DEFAULT NULL,
  `temperature` varchar(255) DEFAULT NULL,
  `workers_count` int(11) NOT NULL DEFAULT 0,
  `machines_count` int(11) NOT NULL DEFAULT 0,
  `work_completed` text DEFAULT NULL,
  `work_planned` text DEFAULT NULL,
  `issues` text DEFAULT NULL,
  `safety_notes` text DEFAULT NULL,
  `reported_by` varchar(255) DEFAULT NULL,
  `photos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`photos`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `daily_reports`
--

INSERT INTO `daily_reports` (`id`, `project_id`, `report_date`, `weather`, `temperature`, `workers_count`, `machines_count`, `work_completed`, `work_planned`, `issues`, `safety_notes`, `reported_by`, `photos`, `created_at`, `updated_at`) VALUES
(1, 1, '2026-03-11', 'Clear', '33°C', 108, 6, 'Electrical conduit installation in progress', 'Install window frames on 2nd floor', NULL, NULL, 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(2, 1, '2026-03-10', 'Hot & Humid', '37°C', 100, 5, 'Brickwork completed for units 201-208', 'Complete plumbing rough-in', NULL, NULL, 'Priya Patel', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(3, 1, '2026-03-09', 'Sunny', '35°C', 92, 7, 'Concrete pouring completed for 3rd floor slab', 'Install window frames on 2nd floor', NULL, 'Tool box talk conducted', 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(4, 1, '2026-03-08', 'Sunny', '29°C', 61, 2, 'Brickwork completed for units 201-208', 'Begin waterproofing for terrace', 'Rain caused half-day stoppage', 'Fire extinguishers checked', 'Priya Patel', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(5, 1, '2026-03-07', 'Sunny', '25°C', 46, 3, 'Column casting for Tower B level 5', 'Begin waterproofing for terrace', 'Short of skilled masons', 'All workers wearing PPE', 'Priya Patel', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(6, 2, '2026-03-11', 'Hot & Humid', '28°C', 79, 2, 'Brickwork completed for units 201-208', 'Continue slab work on 4th floor', NULL, 'All workers wearing PPE', 'Priya Patel', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(7, 2, '2026-03-10', 'Rainy', '26°C', 113, 3, 'Electrical conduit installation in progress', 'Complete plumbing rough-in', NULL, 'Fire extinguishers checked', 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(8, 2, '2026-03-09', 'Cloudy', '36°C', 52, 4, 'Brickwork completed for units 201-208', 'Install window frames on 2nd floor', NULL, NULL, 'Priya Patel', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(9, 2, '2026-03-08', 'Rainy', '30°C', 89, 2, 'Electrical conduit installation in progress', 'Install window frames on 2nd floor', 'Cement delivery delayed by 2 days', 'Fire extinguishers checked', 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(10, 2, '2026-03-07', 'Rainy', '25°C', 55, 2, 'Steel reinforcement work for basement', 'Install window frames on 2nd floor', NULL, NULL, 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(11, 3, '2026-03-11', 'Rainy', '37°C', 109, 4, 'Electrical conduit installation in progress', 'Continue slab work on 4th floor', NULL, 'Fire extinguishers checked', 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(12, 3, '2026-03-10', 'Sunny', '36°C', 56, 7, 'Brickwork completed for units 201-208', 'Start brickwork on level 6', 'Rain caused half-day stoppage', 'Fire extinguishers checked', 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(13, 3, '2026-03-09', 'Sunny', '37°C', 97, 5, 'Steel reinforcement work for basement', 'Begin waterproofing for terrace', 'Cement delivery delayed by 2 days', 'Safety net installed on 5th floor', 'Priya Patel', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(14, 3, '2026-03-08', 'Cloudy', '27°C', 99, 5, 'Electrical conduit installation in progress', 'Begin waterproofing for terrace', NULL, NULL, 'Rohit Verma', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(15, 3, '2026-03-07', 'Hot & Humid', '27°C', 90, 8, 'Steel reinforcement work for basement', 'Install window frames on 2nd floor', 'Short of skilled masons', 'Fire extinguishers checked', 'Priya Patel', NULL, '2026-03-11 07:15:31', '2026-03-11 07:15:31');

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

--
-- Table structure for table `documents`
--

CREATE TABLE `documents` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `type` enum('drawing','approval','contract','report','specification','photo','other') NOT NULL DEFAULT 'other',
  `file_path` varchar(255) DEFAULT NULL,
  `file_size` varchar(255) DEFAULT NULL,
  `uploaded_by` varchar(255) DEFAULT NULL,
  `version` int(11) NOT NULL DEFAULT 1,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `documents`
--

INSERT INTO `documents` (`id`, `project_id`, `name`, `type`, `file_path`, `file_size`, `uploaded_by`, `version`, `created_at`, `updated_at`) VALUES
(1, 1, 'Document 1 - Drawing', 'drawing', 'documents/sample_1.pdf', '2701823', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 1, 'Document 2 - Approval', 'approval', 'documents/sample_2.pdf', '1042877', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 1, 'Document 3 - Contract', 'contract', 'documents/sample_3.pdf', '234198', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(4, 2, 'Document 4 - Approval', 'approval', 'documents/sample_4.pdf', '556082', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(5, 2, 'Document 5 - Contract', 'contract', 'documents/sample_5.pdf', '3144692', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(6, 2, 'Document 6 - Report', 'report', 'documents/sample_6.pdf', '2751022', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(7, 6, 'Document 7 - Contract', 'contract', 'documents/sample_7.pdf', '3591776', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(8, 6, 'Document 8 - Report', 'report', 'documents/sample_8.pdf', '3673491', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(9, 6, 'Document 9 - Specification', 'specification', 'documents/sample_9.pdf', '888325', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(10, 5, 'Document 10 - Report', 'report', 'documents/sample_10.pdf', '392263', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(11, 5, 'Document 11 - Specification', 'specification', 'documents/sample_11.pdf', '1905172', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(12, 5, 'Document 12 - Photo', 'photo', 'documents/sample_12.pdf', '4325609', 'Admin', 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `equipment`
--

CREATE TABLE `equipment` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `equipment_id` varchar(255) NOT NULL,
  `type` enum('crane','excavator','bulldozer','loader','mixer','pump','generator','scaffolding','compactor','vehicle','other') NOT NULL DEFAULT 'other',
  `ownership` enum('owned','rented','leased') NOT NULL DEFAULT 'rented',
  `vendor` varchar(255) DEFAULT NULL,
  `daily_rate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `status` enum('available','in_use','maintenance','broken','returned') NOT NULL DEFAULT 'available',
  `project_id` bigint(20) UNSIGNED DEFAULT NULL,
  `assigned_date` date DEFAULT NULL,
  `return_date` date DEFAULT NULL,
  `last_maintenance` date DEFAULT NULL,
  `next_maintenance` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `equipment`
--

INSERT INTO `equipment` (`id`, `name`, `equipment_id`, `type`, `ownership`, `vendor`, `daily_rate`, `status`, `project_id`, `assigned_date`, `return_date`, `last_maintenance`, `next_maintenance`, `notes`, `created_at`, `updated_at`) VALUES
(1, 'CAT 320 Excavator', 'EQ-EXC-001', 'excavator', 'rented', 'CAT Rentals', 8500.00, 'in_use', 1, '2026-02-20', NULL, '2026-02-18', '2026-04-18', 'Regular maintenance scheduled', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 'Liebherr Tower Crane', 'EQ-CRN-002', 'crane', 'rented', 'Liebherr India', 15000.00, 'in_use', 2, '2026-02-13', NULL, '2026-02-21', '2026-03-24', 'Regular maintenance scheduled', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 'JCB 3CX Loader', 'EQ-LDR-003', 'loader', 'owned', NULL, 5500.00, 'available', NULL, NULL, NULL, '2026-02-02', '2026-04-12', 'Regular maintenance scheduled', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(4, 'Schwing Stetter Mixer', 'EQ-MXR-004', 'mixer', 'owned', NULL, 3500.00, 'in_use', 5, '2026-02-26', NULL, '2026-02-04', '2026-04-19', 'Regular maintenance scheduled', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(5, 'Kirloskar 125KVA Generator', 'EQ-GEN-005', 'generator', 'owned', NULL, 4000.00, 'available', NULL, NULL, NULL, '2026-01-25', '2026-03-27', 'Regular maintenance scheduled', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(6, 'Steel Scaffolding Set', 'EQ-SCF-006', 'scaffolding', 'owned', NULL, 2000.00, 'maintenance', NULL, NULL, NULL, '2026-02-19', '2026-04-21', 'Regular maintenance scheduled', '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `failed_jobs`
--

CREATE TABLE `failed_jobs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `uuid` varchar(255) NOT NULL,
  `connection` text NOT NULL,
  `queue` text NOT NULL,
  `payload` longtext NOT NULL,
  `exception` longtext NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `inspections`
--

CREATE TABLE `inspections` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `task_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `type` enum('concrete','steel','electrical','plumbing','structural','safety','finishing','other') NOT NULL DEFAULT 'other',
  `inspection_date` date NOT NULL,
  `inspector` varchar(255) NOT NULL,
  `result` enum('pass','fail','conditional','pending') NOT NULL DEFAULT 'pending',
  `criteria` text DEFAULT NULL,
  `findings` text DEFAULT NULL,
  `recommendations` text DEFAULT NULL,
  `photos` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`photos`)),
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `inspections`
--

INSERT INTO `inspections` (`id`, `project_id`, `task_id`, `title`, `type`, `inspection_date`, `inspector`, `result`, `criteria`, `findings`, `recommendations`, `photos`, `created_at`, `updated_at`) VALUES
(1, 1, NULL, 'Concrete Cube Test - M30', 'concrete', '2026-02-10', 'Sunita Devi', 'pass', 'Required: M30 (30 N/mm²)', 'Partial compliance observed', 'Continue monitoring', NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(2, 1, NULL, 'Steel Reinforcement Check', 'steel', '2026-02-10', 'Sunita Devi', 'pass', 'Verify bar spacing, cover, and grade as per drawing', 'All parameters satisfactory', 'Continue monitoring', NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(3, 1, NULL, 'Electrical Earthing Test', 'electrical', '2026-02-13', 'Sunita Devi', 'pass', 'Earth resistance below 5 ohms', 'All parameters satisfactory', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(4, 1, NULL, 'Plumbing Pressure Test', 'plumbing', '2026-03-05', 'Sunita Devi', 'conditional', 'No leakage at 6 bar pressure for 30 min', 'Partial compliance observed', 'Continue monitoring', NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(5, 1, NULL, 'Column Alignment Check', 'structural', '2026-02-28', 'Sunita Devi', 'pass', 'Deviation less than 5mm per floor height', 'All parameters satisfactory', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(6, 1, NULL, 'Fire Safety Equipment Audit', 'safety', '2026-02-28', 'Sunita Devi', 'pass', 'All fire extinguishers present and charged', 'Partial compliance observed', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(7, 2, NULL, 'Concrete Cube Test - M30', 'concrete', '2026-02-12', 'Sunita Devi', 'fail', 'Required: M30 (30 N/mm²)', 'Results within acceptable range', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(8, 2, NULL, 'Steel Reinforcement Check', 'steel', '2026-02-20', 'Sunita Devi', 'pass', 'Verify bar spacing, cover, and grade as per drawing', 'Minor deviation noted', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(9, 2, NULL, 'Electrical Earthing Test', 'electrical', '2026-03-06', 'Sunita Devi', 'pending', 'Earth resistance below 5 ohms', 'Partial compliance observed', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(10, 2, NULL, 'Plumbing Pressure Test', 'plumbing', '2026-02-15', 'Sunita Devi', 'fail', 'No leakage at 6 bar pressure for 30 min', 'All parameters satisfactory', 'Continue monitoring', NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(11, 2, NULL, 'Column Alignment Check', 'structural', '2026-03-09', 'Sunita Devi', 'conditional', 'Deviation less than 5mm per floor height', 'All parameters satisfactory', 'Continue monitoring', NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(12, 3, NULL, 'Concrete Cube Test - M30', 'concrete', '2026-02-09', 'Sunita Devi', 'pass', 'Required: M30 (30 N/mm²)', 'Requires re-inspection', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(13, 3, NULL, 'Steel Reinforcement Check', 'steel', '2026-02-11', 'Sunita Devi', 'pass', 'Verify bar spacing, cover, and grade as per drawing', 'Results within acceptable range', NULL, NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(14, 3, NULL, 'Electrical Earthing Test', 'electrical', '2026-02-14', 'Sunita Devi', 'pass', 'Earth resistance below 5 ohms', 'Requires re-inspection', 'Continue monitoring', NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(15, 3, NULL, 'Plumbing Pressure Test', 'plumbing', '2026-03-01', 'Sunita Devi', 'pass', 'No leakage at 6 bar pressure for 30 min', 'Minor deviation noted', 'Continue monitoring', NULL, '2026-03-11 07:15:32', '2026-03-11 07:15:32');

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

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `queue` varchar(255) NOT NULL,
  `payload` longtext NOT NULL,
  `attempts` tinyint(3) UNSIGNED NOT NULL,
  `reserved_at` int(10) UNSIGNED DEFAULT NULL,
  `available_at` int(10) UNSIGNED NOT NULL,
  `created_at` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `job_batches`
--

CREATE TABLE `job_batches` (
  `id` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext NOT NULL,
  `options` mediumtext DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `materials`
--

CREATE TABLE `materials` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` enum('cement','steel','sand','bricks','electrical','plumbing','wood','paint','tiles','glass','other') NOT NULL DEFAULT 'other',
  `unit` varchar(255) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `stock_quantity` decimal(10,2) NOT NULL DEFAULT 0.00,
  `min_stock_level` decimal(10,2) NOT NULL DEFAULT 0.00,
  `supplier` varchar(255) DEFAULT NULL,
  `status` enum('available','low_stock','out_of_stock') NOT NULL DEFAULT 'available',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `materials`
--

INSERT INTO `materials` (`id`, `name`, `category`, `unit`, `unit_price`, `stock_quantity`, `min_stock_level`, `supplier`, `status`, `created_at`, `updated_at`) VALUES
(1, 'OPC Cement 53 Grade', 'cement', 'bags', 380.00, 2500.00, 500.00, 'UltraTech Cement', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(2, 'PPC Cement', 'cement', 'bags', 350.00, 180.00, 300.00, 'ACC Cement', 'low_stock', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(3, 'TMT Steel Bars 12mm', 'steel', 'tonnes', 52000.00, 45.00, 20.00, 'Tata Steel', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(4, 'TMT Steel Bars 16mm', 'steel', 'tonnes', 54000.00, 8.00, 15.00, 'JSW Steel', 'low_stock', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(5, 'River Sand (Fine)', 'sand', 'cubic meters', 2800.00, 120.00, 50.00, 'Local Quarry', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(6, 'M-Sand (Manufactured)', 'sand', 'cubic meters', 1800.00, 200.00, 80.00, 'CrushStone Industries', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(7, 'Red Clay Bricks', 'bricks', 'pieces', 8.00, 50000.00, 10000.00, 'Bharat Bricks', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(8, 'AAC Blocks 600x200x150', 'bricks', 'pieces', 55.00, 3000.00, 5000.00, 'Magicrete', 'low_stock', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(9, 'Copper Wire 2.5mm', 'electrical', 'meters', 45.00, 5000.00, 2000.00, 'Finolex Cables', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(10, 'CPVC Pipe 1 inch', 'plumbing', 'meters', 120.00, 800.00, 300.00, 'Astral Pipes', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(11, 'Teak Wood Planks', 'wood', 'cubic feet', 3500.00, 60.00, 25.00, 'Timber Mart', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(12, 'Exterior Emulsion Paint', 'paint', 'litres', 280.00, 400.00, 200.00, 'Asian Paints', 'available', '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(13, 'Vitrified Floor Tiles 2x2', 'tiles', 'boxes', 650.00, 0.00, 100.00, 'Kajaria Tiles', 'out_of_stock', '2026-03-11 07:15:31', '2026-03-11 07:15:31'),
(14, 'Tempered Glass 10mm', 'glass', 'sq feet', 180.00, 350.00, 150.00, 'Saint Gobain', 'available', '2026-03-11 07:15:31', '2026-03-11 07:15:31');

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

--
-- Table structure for table `material_transactions`
--

CREATE TABLE `material_transactions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `material_id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED DEFAULT NULL,
  `type` enum('purchase','consumption','return','adjustment') NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL DEFAULT 0.00,
  `reference` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `transaction_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `migrations`
--

CREATE TABLE `migrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `migration` varchar(255) NOT NULL,
  `batch` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `migrations`
--

INSERT INTO `migrations` (`id`, `migration`, `batch`) VALUES
(1, '0001_01_01_000000_create_users_table', 1),
(2, '0001_01_01_000001_create_cache_table', 1),
(3, '0001_01_01_000002_create_jobs_table', 1),
(4, '2026_03_11_121155_create_admins_table', 1),
(5, '2026_03_11_130000_create_cpms_tables', 1),
(6, '2026_03_11_140000_create_roles_permissions_tables', 2),
(7, '2026_03_11_150000_create_advanced_modules_tables', 3),
(8, '2026_03_11_160000_create_settings_table', 4),
(9, '2019_12_14_000001_create_personal_access_tokens_table', 5);

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `admin_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `type` enum('info','warning','danger','success') NOT NULL DEFAULT 'info',
  `module` varchar(255) DEFAULT NULL,
  `link` varchar(255) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `admin_id`, `title`, `message`, `type`, `module`, `link`, `is_read`, `created_at`, `updated_at`) VALUES
(1, 1, 'Task Assigned: Review Project Plans', 'You have been assigned a new task for Skyline Tower project.', 'info', 'tasks', NULL, 0, '2026-03-11 09:18:58', '2026-03-11 09:18:58'),
(2, 1, 'Inspection Due Tomorrow', 'Quality inspection for Green Valley Villas is scheduled for tomorrow.', 'warning', 'inspections', NULL, 0, '2026-03-11 09:18:58', '2026-03-11 09:18:58'),
(3, 1, 'Budget Alert: Over Spending', 'Metro Mall project has exceeded 90% of allocated budget.', 'danger', 'budget', NULL, 0, '2026-03-11 09:18:58', '2026-03-11 09:18:58'),
(4, 1, 'Safety Incident Reported', 'A near-miss incident was reported at Riverside Complex site.', 'danger', 'safety', NULL, 1, '2026-03-11 09:18:58', '2026-03-11 09:18:58'),
(5, 1, 'Compliance Expiry Notice', 'Building permit for Tech Park project expires in 15 days.', 'warning', 'compliance', NULL, 1, '2026-03-11 09:18:58', '2026-03-11 09:18:58');

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

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `module` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` (`id`, `name`, `slug`, `module`, `description`, `created_at`, `updated_at`) VALUES
(1, 'View Projects', 'projects.view', 'projects', 'View Projects', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(2, 'Create Projects', 'projects.create', 'projects', 'Create Projects', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(3, 'Edit Projects', 'projects.edit', 'projects', 'Edit Projects', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(4, 'Delete Projects', 'projects.delete', 'projects', 'Delete Projects', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(5, 'View Tasks', 'tasks.view', 'tasks', 'View Tasks', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(6, 'Create Tasks', 'tasks.create', 'tasks', 'Create Tasks', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(7, 'Edit Tasks', 'tasks.edit', 'tasks', 'Edit Tasks', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(8, 'Delete Tasks', 'tasks.delete', 'tasks', 'Delete Tasks', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(9, 'View Contractors', 'contractors.view', 'contractors', 'View Contractors', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(10, 'Create Contractors', 'contractors.create', 'contractors', 'Create Contractors', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(11, 'Edit Contractors', 'contractors.edit', 'contractors', 'Edit Contractors', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(12, 'Delete Contractors', 'contractors.delete', 'contractors', 'Delete Contractors', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(13, 'View Materials', 'materials.view', 'materials', 'View Materials', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(14, 'Create Materials', 'materials.create', 'materials', 'Create Materials', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(15, 'Edit Materials', 'materials.edit', 'materials', 'Edit Materials', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(16, 'Delete Materials', 'materials.delete', 'materials', 'Delete Materials', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(17, 'Record Transactions', 'materials.transaction', 'materials', 'Record Transactions', '2026-03-11 08:55:12', '2026-03-11 08:55:12'),
(18, 'View Budget', 'budget.view', 'budget', 'View Budget', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(19, 'Create Budget Items', 'budget.create', 'budget', 'Create Budget Items', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(20, 'Edit Budget Items', 'budget.edit', 'budget', 'Edit Budget Items', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(21, 'Delete Budget Items', 'budget.delete', 'budget', 'Delete Budget Items', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(22, 'View Inspections', 'inspections.view', 'inspections', 'View Inspections', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(23, 'Create Inspections', 'inspections.create', 'inspections', 'Create Inspections', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(24, 'Edit Inspections', 'inspections.edit', 'inspections', 'Edit Inspections', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(25, 'Delete Inspections', 'inspections.delete', 'inspections', 'Delete Inspections', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(26, 'View Risks', 'risks.view', 'risks', 'View Risks', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(27, 'Create Risks', 'risks.create', 'risks', 'Create Risks', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(28, 'Edit Risks', 'risks.edit', 'risks', 'Edit Risks', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(29, 'Delete Risks', 'risks.delete', 'risks', 'Delete Risks', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(30, 'View Daily Reports', 'daily-reports.view', 'daily-reports', 'View Daily Reports', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(31, 'Create Daily Reports', 'daily-reports.create', 'daily-reports', 'Create Daily Reports', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(32, 'Delete Daily Reports', 'daily-reports.delete', 'daily-reports', 'Delete Daily Reports', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(33, 'View Team Members', 'team.view', 'team', 'View Team Members', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(34, 'Create Team Members', 'team.create', 'team', 'Create Team Members', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(35, 'Edit Team Members', 'team.edit', 'team', 'Edit Team Members', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(36, 'Delete Team Members', 'team.delete', 'team', 'Delete Team Members', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(37, 'View AI Predictions', 'ai-predictions.view', 'ai-predictions', 'View AI Predictions', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(38, 'Generate Predictions', 'ai-predictions.generate', 'ai-predictions', 'Generate Predictions', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(39, 'Manage Predictions', 'ai-predictions.manage', 'ai-predictions', 'Manage Predictions', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(40, 'View Reports & Analytics', 'reports.view', 'reports', 'View Reports & Analytics', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(41, 'View Users', 'users.view', 'users', 'View Users', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(42, 'Create Users', 'users.create', 'users', 'Create Users', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(43, 'Edit Users', 'users.edit', 'users', 'Edit Users', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(44, 'Delete Users', 'users.delete', 'users', 'Delete Users', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(45, 'View Roles', 'roles.view', 'roles', 'View Roles', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(46, 'Create Roles', 'roles.create', 'roles', 'Create Roles', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(47, 'Edit Roles', 'roles.edit', 'roles', 'Edit Roles', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(48, 'Delete Roles', 'roles.delete', 'roles', 'Delete Roles', '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(49, 'View Documents', 'documents.view', 'documents', 'View Documents', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(50, 'Upload Documents', 'documents.create', 'documents', 'Upload Documents', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(51, 'Edit Documents', 'documents.edit', 'documents', 'Edit Documents', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(52, 'Delete Documents', 'documents.delete', 'documents', 'Delete Documents', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(53, 'View Purchase Orders', 'purchase-orders.view', 'purchase-orders', 'View Purchase Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(54, 'Create Purchase Orders', 'purchase-orders.create', 'purchase-orders', 'Create Purchase Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(55, 'Edit Purchase Orders', 'purchase-orders.edit', 'purchase-orders', 'Edit Purchase Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(56, 'Delete Purchase Orders', 'purchase-orders.delete', 'purchase-orders', 'Delete Purchase Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(57, 'View Contracts', 'contracts.view', 'contracts', 'View Contracts', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(58, 'Create Contracts', 'contracts.create', 'contracts', 'Create Contracts', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(59, 'Edit Contracts', 'contracts.edit', 'contracts', 'Edit Contracts', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(60, 'Delete Contracts', 'contracts.delete', 'contracts', 'Delete Contracts', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(61, 'View Change Orders', 'change-orders.view', 'change-orders', 'View Change Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(62, 'Create Change Orders', 'change-orders.create', 'change-orders', 'Create Change Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(63, 'Edit Change Orders', 'change-orders.edit', 'change-orders', 'Edit Change Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(64, 'Delete Change Orders', 'change-orders.delete', 'change-orders', 'Delete Change Orders', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(65, 'View Equipment', 'equipment.view', 'equipment', 'View Equipment', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(66, 'Create Equipment', 'equipment.create', 'equipment', 'Create Equipment', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(67, 'Edit Equipment', 'equipment.edit', 'equipment', 'Edit Equipment', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(68, 'Delete Equipment', 'equipment.delete', 'equipment', 'Delete Equipment', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(69, 'View Safety Records', 'safety.view', 'safety', 'View Safety Records', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(70, 'Create Safety Records', 'safety.create', 'safety', 'Create Safety Records', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(71, 'Edit Safety Records', 'safety.edit', 'safety', 'Edit Safety Records', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(72, 'Delete Safety Records', 'safety.delete', 'safety', 'Delete Safety Records', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(73, 'View Attendance', 'attendance.view', 'attendance', 'View Attendance', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(74, 'Record Attendance', 'attendance.create', 'attendance', 'Record Attendance', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(75, 'Delete Attendance', 'attendance.delete', 'attendance', 'Delete Attendance', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(76, 'View Notifications', 'notifications.view', 'notifications', 'View Notifications', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(77, 'Manage Notifications', 'notifications.manage', 'notifications', 'Manage Notifications', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(78, 'View Compliance Items', 'compliance.view', 'compliance', 'View Compliance Items', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(79, 'Create Compliance Items', 'compliance.create', 'compliance', 'Create Compliance Items', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(80, 'Edit Compliance Items', 'compliance.edit', 'compliance', 'Edit Compliance Items', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(81, 'Delete Compliance Items', 'compliance.delete', 'compliance', 'Delete Compliance Items', '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(82, 'Use AI Assistant', 'ai-chat.view', 'ai-chat', 'Use AI Assistant', '2026-03-11 10:47:42', '2026-03-11 10:47:42'),
(83, 'View Settings', 'settings.view', 'settings', 'View Settings', '2026-03-11 10:47:42', '2026-03-11 10:47:42'),
(84, 'Edit Settings', 'settings.edit', 'settings', 'Edit Settings', '2026-03-11 10:47:42', '2026-03-11 10:47:42');

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

--
-- Table structure for table `projects`
--

CREATE TABLE `projects` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `code` varchar(255) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `client_name` varchar(255) DEFAULT NULL,
  `type` enum('residential','commercial','infrastructure','industrial','mixed_use') NOT NULL DEFAULT 'residential',
  `status` enum('planning','in_progress','on_hold','completed','cancelled') NOT NULL DEFAULT 'planning',
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `actual_end_date` date DEFAULT NULL,
  `budget` decimal(15,2) NOT NULL DEFAULT 0.00,
  `spent` decimal(15,2) NOT NULL DEFAULT 0.00,
  `progress` int(11) NOT NULL DEFAULT 0,
  `image` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `projects`
--

INSERT INTO `projects` (`id`, `name`, `description`, `code`, `location`, `client_name`, `type`, `status`, `priority`, `start_date`, `end_date`, `actual_end_date`, `budget`, `spent`, `progress`, `image`, `created_at`, `updated_at`) VALUES
(1, 'Aaradhya Residential Tower', 'Premium 25-floor residential tower with modern amenities, swimming pool, clubhouse, and landscaped gardens in the heart of Mumbai.', 'ART-001', 'Andheri West, Mumbai', 'Aaradhya Developers Pvt Ltd', 'residential', 'in_progress', 'high', '2025-06-15', '2027-12-31', NULL, 45000000.00, 18500000.00, 42, NULL, '2026-03-11 07:15:16', '2026-03-11 07:15:16'),
(2, 'GreenPark Commercial Complex', 'State-of-the-art commercial complex with 8 floors of office space, retail outlets, food court, and underground parking.', 'GPC-002', 'Whitefield, Bangalore', 'GreenPark Infra Ltd', 'commercial', 'in_progress', 'critical', '2025-03-01', '2027-06-30', NULL, 78000000.00, 42000000.00, 58, NULL, '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(3, 'Sunrise Highway Extension', '12 km highway extension project connecting the industrial belt to the national highway with 4 flyovers.', 'SHE-003', 'Pune-Nashik Highway, Maharashtra', 'Maharashtra State Road Dev Corp', 'infrastructure', 'in_progress', 'high', '2025-01-10', '2026-12-31', NULL, 120000000.00, 85000000.00, 72, NULL, '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(4, 'Vedanta Industrial Park', 'Large-scale industrial park with 20 factory units, warehouses, worker amenities, and waste treatment plant.', 'VIP-004', 'MIDC Chakan, Pune', 'Vedanta Industries Group', 'industrial', 'planning', 'medium', '2026-04-01', '2028-03-31', NULL, 95000000.00, 2500000.00, 5, NULL, '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(5, 'Royal Palms Villa Community', 'Luxury villa community with 50 independent villas, central park, sports facilities, and community center.', 'RPV-005', 'Lonavala, Maharashtra', 'Royal Palms Estates', 'residential', 'on_hold', 'medium', '2025-09-01', '2027-08-31', NULL, 65000000.00, 12000000.00, 18, NULL, '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(6, 'Metro City Mall Renovation', 'Complete renovation of existing mall including structural reinforcement, new facade, modern interiors, and green building upgrades.', 'MCM-006', 'MG Road, Delhi', 'Metro City Properties', 'commercial', 'completed', 'low', '2024-06-01', '2025-11-30', '2026-01-15', 28000000.00, 31500000.00, 100, NULL, '2026-03-11 07:15:23', '2026-03-11 07:15:23');

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

--
-- Table structure for table `project_phases`
--

CREATE TABLE `project_phases` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `sort_order` int(11) NOT NULL DEFAULT 0,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `progress` int(11) NOT NULL DEFAULT 0,
  `status` enum('pending','in_progress','completed','delayed') NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `project_phases`
--

INSERT INTO `project_phases` (`id`, `project_id`, `name`, `description`, `sort_order`, `start_date`, `end_date`, `progress`, `status`, `created_at`, `updated_at`) VALUES
(1, 1, 'Land Preparation', NULL, 0, '2025-06-15', '2025-09-15', 100, 'completed', '2026-03-11 07:15:16', '2026-03-11 07:15:16'),
(2, 1, 'Foundation', NULL, 1, '2025-09-15', '2025-12-15', 100, 'completed', '2026-03-11 07:15:16', '2026-03-11 07:15:16'),
(3, 1, 'Structure', NULL, 2, '2025-12-15', '2026-03-15', 66, 'in_progress', '2026-03-11 07:15:17', '2026-03-11 07:15:17'),
(4, 1, 'Brickwork', NULL, 3, '2026-03-15', '2026-06-15', 0, 'pending', '2026-03-11 07:15:17', '2026-03-11 07:15:17'),
(5, 1, 'Electrical & Plumbing', NULL, 4, '2026-06-15', '2026-09-15', 0, 'pending', '2026-03-11 07:15:17', '2026-03-11 07:15:17'),
(6, 1, 'Finishing', NULL, 5, '2026-09-15', '2026-12-15', 0, 'pending', '2026-03-11 07:15:17', '2026-03-11 07:15:17'),
(7, 1, 'Handover', NULL, 6, '2026-12-15', '2027-03-15', 0, 'pending', '2026-03-11 07:15:17', '2026-03-11 07:15:17'),
(8, 2, 'Land Preparation', NULL, 0, '2025-03-01', '2025-06-01', 100, 'completed', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(9, 2, 'Foundation', NULL, 1, '2025-06-01', '2025-09-01', 100, 'completed', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(10, 2, 'Structure', NULL, 2, '2025-09-01', '2025-12-01', 41, 'in_progress', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(11, 2, 'Brickwork', NULL, 3, '2025-12-01', '2026-03-01', 0, 'pending', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(12, 2, 'Electrical & Plumbing', NULL, 4, '2026-03-01', '2026-06-01', 0, 'pending', '2026-03-11 07:15:19', '2026-03-11 07:15:19'),
(13, 2, 'Finishing', NULL, 5, '2026-06-01', '2026-09-01', 0, 'pending', '2026-03-11 07:15:19', '2026-03-11 07:15:19'),
(14, 2, 'Handover', NULL, 6, '2026-09-01', '2026-12-01', 0, 'pending', '2026-03-11 07:15:19', '2026-03-11 07:15:19'),
(15, 3, 'Land Preparation', NULL, 0, '2025-01-10', '2025-04-10', 100, 'completed', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(16, 3, 'Foundation', NULL, 1, '2025-04-10', '2025-07-10', 100, 'completed', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(17, 3, 'Structure', NULL, 2, '2025-07-10', '2025-10-10', 100, 'completed', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(18, 3, 'Brickwork', NULL, 3, '2025-10-10', '2026-01-10', 100, 'completed', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(19, 3, 'Electrical & Plumbing', NULL, 4, '2026-01-10', '2026-04-10', 63, 'in_progress', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(20, 3, 'Finishing', NULL, 5, '2026-04-10', '2026-07-10', 0, 'pending', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(21, 3, 'Handover', NULL, 6, '2026-07-10', '2026-10-10', 0, 'pending', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(22, 4, 'Land Preparation', NULL, 0, '2026-04-01', '2026-07-01', 0, 'pending', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(23, 4, 'Foundation', NULL, 1, '2026-07-01', '2026-10-01', 0, 'pending', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(24, 4, 'Structure', NULL, 2, '2026-10-01', '2027-01-01', 0, 'pending', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(25, 4, 'Brickwork', NULL, 3, '2027-01-01', '2027-04-01', 0, 'pending', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(26, 4, 'Electrical & Plumbing', NULL, 4, '2027-04-01', '2027-07-01', 0, 'pending', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(27, 4, 'Finishing', NULL, 5, '2027-07-01', '2027-10-01', 0, 'pending', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(28, 4, 'Handover', NULL, 6, '2027-10-01', '2028-01-01', 0, 'pending', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(29, 5, 'Land Preparation', NULL, 0, '2025-09-01', '2025-12-01', 100, 'completed', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(30, 5, 'Foundation', NULL, 1, '2025-12-01', '2026-03-01', 100, 'completed', '2026-03-11 07:15:22', '2026-03-11 07:15:22'),
(31, 5, 'Structure', NULL, 2, '2026-03-01', '2026-06-01', 53, 'in_progress', '2026-03-11 07:15:22', '2026-03-11 07:15:22'),
(32, 5, 'Brickwork', NULL, 3, '2026-06-01', '2026-09-01', 0, 'pending', '2026-03-11 07:15:22', '2026-03-11 07:15:22'),
(33, 5, 'Electrical & Plumbing', NULL, 4, '2026-09-01', '2026-12-01', 0, 'pending', '2026-03-11 07:15:22', '2026-03-11 07:15:22'),
(34, 5, 'Finishing', NULL, 5, '2026-12-01', '2027-03-01', 0, 'pending', '2026-03-11 07:15:22', '2026-03-11 07:15:22'),
(35, 5, 'Handover', NULL, 6, '2027-03-01', '2027-06-01', 0, 'pending', '2026-03-11 07:15:22', '2026-03-11 07:15:22'),
(36, 6, 'Land Preparation', NULL, 0, '2024-06-01', '2024-09-01', 100, 'completed', '2026-03-11 07:15:23', '2026-03-11 07:15:23'),
(37, 6, 'Foundation', NULL, 1, '2024-09-01', '2024-12-01', 100, 'completed', '2026-03-11 07:15:24', '2026-03-11 07:15:24'),
(38, 6, 'Structure', NULL, 2, '2024-12-01', '2025-03-01', 100, 'completed', '2026-03-11 07:15:24', '2026-03-11 07:15:24'),
(39, 6, 'Brickwork', NULL, 3, '2025-03-01', '2025-06-01', 100, 'completed', '2026-03-11 07:15:24', '2026-03-11 07:15:24'),
(40, 6, 'Electrical & Plumbing', NULL, 4, '2025-06-01', '2025-09-01', 100, 'completed', '2026-03-11 07:15:24', '2026-03-11 07:15:24'),
(41, 6, 'Finishing', NULL, 5, '2025-09-01', '2025-12-01', 100, 'completed', '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(42, 6, 'Handover', NULL, 6, '2025-12-01', '2026-03-01', 100, 'completed', '2026-03-11 07:15:25', '2026-03-11 07:15:25');

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

--
-- Table structure for table `project_team`
--

CREATE TABLE `project_team` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `team_member_id` bigint(20) UNSIGNED NOT NULL,
  `role` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `project_team`
--

INSERT INTO `project_team` (`id`, `project_id`, `team_member_id`, `role`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 'member', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(2, 1, 1, 'member', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(3, 1, 2, 'member', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(4, 1, 7, 'member', '2026-03-11 07:15:18', '2026-03-11 07:15:18'),
(5, 2, 2, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(6, 2, 3, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(7, 2, 1, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(8, 3, 7, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(9, 3, 2, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(10, 3, 8, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(11, 3, 4, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(12, 3, 5, 'member', '2026-03-11 07:15:20', '2026-03-11 07:15:20'),
(13, 4, 3, 'member', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(14, 4, 1, 'member', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(15, 4, 6, 'member', '2026-03-11 07:15:21', '2026-03-11 07:15:21'),
(16, 5, 1, 'member', '2026-03-11 07:15:23', '2026-03-11 07:15:23'),
(17, 5, 6, 'member', '2026-03-11 07:15:23', '2026-03-11 07:15:23'),
(18, 5, 8, 'member', '2026-03-11 07:15:23', '2026-03-11 07:15:23'),
(19, 5, 5, 'member', '2026-03-11 07:15:23', '2026-03-11 07:15:23'),
(20, 5, 7, 'member', '2026-03-11 07:15:23', '2026-03-11 07:15:23'),
(21, 6, 4, 'member', '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(22, 6, 7, 'member', '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(23, 6, 2, 'member', '2026-03-11 07:15:25', '2026-03-11 07:15:25');

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

--
-- Table structure for table `purchase_orders`
--

CREATE TABLE `purchase_orders` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `po_number` varchar(255) NOT NULL,
  `vendor` varchar(255) NOT NULL,
  `total_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` enum('draft','approved','ordered','delivered','cancelled') NOT NULL DEFAULT 'draft',
  `order_date` date DEFAULT NULL,
  `delivery_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_orders`
--

INSERT INTO `purchase_orders` (`id`, `project_id`, `po_number`, `vendor`, `total_amount`, `status`, `order_date`, `delivery_date`, `notes`, `created_at`, `updated_at`) VALUES
(1, 1, 'PO-202603-0001', 'Ambuja Supplies', 372260.00, 'draft', '2026-02-24', '2026-04-12', 'Purchase order for construction materials', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 2, 'PO-202603-0002', 'Tata Steel Dealer', 496787.00, 'approved', '2026-02-27', '2026-03-27', 'Purchase order for construction materials', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 6, 'PO-202603-0003', 'ACC Materials', 384056.00, 'ordered', '2026-02-14', '2026-05-03', 'Purchase order for construction materials', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(4, 5, 'PO-202603-0004', 'Ultratech Traders', 666391.00, 'delivered', '2026-02-18', '2026-04-19', 'Purchase order for construction materials', '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `purchase_order_items`
--

CREATE TABLE `purchase_order_items` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `purchase_order_id` bigint(20) UNSIGNED NOT NULL,
  `material_id` bigint(20) UNSIGNED NOT NULL,
  `quantity` decimal(10,2) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total` decimal(15,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `purchase_order_items`
--

INSERT INTO `purchase_order_items` (`id`, `purchase_order_id`, `material_id`, `quantity`, `unit_price`, `total`, `created_at`, `updated_at`) VALUES
(1, 1, 1, 72.00, 1450.00, 104400.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 1, 2, 89.00, 2770.00, 246530.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 1, 3, 10.00, 2133.00, 21330.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(4, 2, 1, 50.00, 1407.00, 70350.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(5, 2, 2, 77.00, 3641.00, 280357.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(6, 2, 3, 44.00, 3320.00, 146080.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(7, 3, 1, 24.00, 4595.00, 110280.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(8, 3, 2, 20.00, 4718.00, 94360.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(9, 3, 3, 82.00, 2188.00, 179416.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(10, 4, 1, 59.00, 3357.00, 198063.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(11, 4, 2, 63.00, 4538.00, 285894.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(12, 4, 3, 83.00, 2198.00, 182434.00, '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `risks`
--

CREATE TABLE `risks` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `type` enum('risk','issue') NOT NULL DEFAULT 'risk',
  `severity` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `probability` enum('low','medium','high') NOT NULL DEFAULT 'medium',
  `status` enum('identified','mitigating','resolved','accepted','escalated') NOT NULL DEFAULT 'identified',
  `mitigation` text DEFAULT NULL,
  `assigned_to` varchar(255) DEFAULT NULL,
  `identified_date` date DEFAULT NULL,
  `resolved_date` date DEFAULT NULL,
  `impact_cost` decimal(15,2) NOT NULL DEFAULT 0.00,
  `impact_days` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `risks`
--

INSERT INTO `risks` (`id`, `project_id`, `title`, `description`, `type`, `severity`, `probability`, `status`, `mitigation`, `assigned_to`, `identified_date`, `resolved_date`, `impact_cost`, `impact_days`, `created_at`, `updated_at`) VALUES
(1, 1, 'Monsoon Delay Risk', 'Heavy monsoon expected July-September may halt outdoor construction work for 30-45 days.', 'risk', 'high', 'high', 'identified', 'Schedule critical outdoor work before monsoon. Prepare indoor work packages for rainy season.', 'Rajesh Sharma', '2026-03-04', NULL, 2500000.00, 35, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(2, 3, 'Steel Price Escalation', 'Global steel prices trending upward, may increase procurement costs by 8-12%.', 'risk', 'medium', 'medium', 'identified', 'Lock prices with advance purchase orders. Explore alternative suppliers.', 'Anita Joshi', '2026-02-14', NULL, 1800000.00, 0, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(3, 2, 'Skilled Labour Shortage', 'Shortage of skilled masons and bar benders affecting structural work pace.', 'issue', 'critical', 'high', 'mitigating', 'Engage additional contractors. Offer overtime incentives to existing crew.', 'Rajesh Sharma', '2026-02-14', NULL, 500000.00, 15, '2026-03-11 07:15:32', '2026-03-11 07:15:32'),
(4, 3, 'Environmental Clearance Pending', 'Phase 2 environmental clearance application still under review.', 'risk', 'critical', 'medium', 'escalated', 'Follow up with authorities weekly. Prepare all supporting documentation.', 'Sunita Devi', '2026-01-30', NULL, 5000000.00, 60, '2026-03-11 07:15:33', '2026-03-11 07:15:33'),
(5, 2, 'Concrete Quality Issue - Batch 47', 'Cube test results for batch 47 showed strength below M30 specification.', 'issue', 'high', 'high', 'mitigating', 'Core testing ordered. Ready demolition plan if results confirm weakness.', 'Amit Kumar', '2026-02-27', NULL, 350000.00, 7, '2026-03-11 07:15:33', '2026-03-11 07:15:33'),
(6, 2, 'Design Change Request from Client', 'Client requesting changes to lobby design after structural work started.', 'issue', 'medium', 'high', 'identified', 'Evaluate feasibility with architect. Submit revised cost estimate to client.', 'Rohit Verma', '2026-02-08', NULL, 800000.00, 12, '2026-03-11 07:15:33', '2026-03-11 07:15:33'),
(7, 3, 'Adjacent Building Settlement', 'Deep excavation near neighboring structure could cause settlement cracks.', 'risk', 'critical', 'low', 'identified', 'Install settlement monitors. Use sheet piling and controlled excavation.', 'Rohit Verma', '2026-02-15', NULL, 10000000.00, 90, '2026-03-11 07:15:33', '2026-03-11 07:15:33');

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

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `slug` varchar(255) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  `is_super_admin` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` (`id`, `name`, `slug`, `description`, `is_super_admin`, `created_at`, `updated_at`) VALUES
(1, 'Super Admin', 'super-admin', 'Full system access with all permissions', 1, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(2, 'Project Manager', 'project-manager', 'Can manage projects, tasks, team, and view reports', 0, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(3, 'Site Engineer', 'site-engineer', 'Can view projects, manage tasks, daily reports, and inspections', 0, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(4, 'Viewer', 'viewer', 'Read-only access to all modules', 0, '2026-03-11 08:55:13', '2026-03-11 08:55:13');

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

--
-- Table structure for table `role_permission`
--

CREATE TABLE `role_permission` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `role_id` bigint(20) UNSIGNED NOT NULL,
  `permission_id` bigint(20) UNSIGNED NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `role_permission`
--

INSERT INTO `role_permission` (`id`, `role_id`, `permission_id`, `created_at`, `updated_at`) VALUES
(1, 2, 1, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(2, 2, 2, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(3, 2, 3, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(4, 2, 4, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(5, 2, 5, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(6, 2, 6, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(7, 2, 7, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(8, 2, 8, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(9, 2, 9, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(10, 2, 10, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(11, 2, 11, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(12, 2, 12, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(13, 2, 13, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(14, 2, 14, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(15, 2, 15, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(16, 2, 16, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(17, 2, 17, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(18, 2, 18, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(19, 2, 19, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(20, 2, 20, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(21, 2, 21, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(22, 2, 22, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(23, 2, 23, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(24, 2, 24, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(25, 2, 25, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(26, 2, 26, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(27, 2, 27, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(28, 2, 28, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(29, 2, 29, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(30, 2, 30, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(31, 2, 31, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(32, 2, 32, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(33, 2, 33, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(34, 2, 34, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(35, 2, 35, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(36, 2, 36, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(37, 2, 37, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(38, 2, 38, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(39, 2, 39, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(40, 2, 40, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(41, 3, 1, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(42, 3, 5, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(43, 3, 6, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(44, 3, 7, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(45, 3, 13, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(46, 3, 17, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(47, 3, 22, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(48, 3, 23, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(49, 3, 24, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(50, 3, 25, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(51, 3, 26, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(52, 3, 27, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(53, 3, 30, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(54, 3, 31, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(55, 3, 32, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(56, 4, 37, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(57, 4, 18, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(58, 4, 9, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(59, 4, 30, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(60, 4, 22, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(61, 4, 13, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(62, 4, 1, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(63, 4, 40, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(64, 4, 26, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(65, 4, 45, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(66, 4, 5, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(67, 4, 33, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(68, 4, 41, '2026-03-11 08:55:13', '2026-03-11 08:55:13'),
(69, 2, 49, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(70, 2, 50, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(71, 2, 51, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(72, 2, 52, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(73, 2, 53, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(74, 2, 54, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(75, 2, 55, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(76, 2, 56, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(77, 2, 57, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(78, 2, 58, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(79, 2, 59, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(80, 2, 60, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(81, 2, 61, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(82, 2, 62, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(83, 2, 63, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(84, 2, 64, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(85, 2, 65, '2026-03-11 09:15:50', '2026-03-11 09:15:50'),
(86, 2, 66, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(87, 2, 67, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(88, 2, 68, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(89, 2, 69, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(90, 2, 70, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(91, 2, 71, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(92, 2, 72, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(93, 2, 73, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(94, 2, 74, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(95, 2, 75, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(96, 2, 76, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(97, 2, 77, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(98, 2, 78, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(99, 2, 79, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(100, 2, 80, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(101, 2, 81, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(102, 4, 73, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(103, 4, 61, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(104, 4, 78, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(105, 4, 57, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(106, 4, 49, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(107, 4, 65, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(108, 4, 76, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(109, 4, 53, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(110, 4, 69, '2026-03-11 09:15:51', '2026-03-11 09:15:51'),
(111, 2, 82, '2026-03-11 10:47:43', '2026-03-11 10:47:43'),
(112, 2, 83, '2026-03-11 10:47:43', '2026-03-11 10:47:43'),
(113, 2, 84, '2026-03-11 10:47:43', '2026-03-11 10:47:43'),
(114, 4, 82, '2026-03-11 10:47:43', '2026-03-11 10:47:43'),
(115, 4, 83, '2026-03-11 10:47:43', '2026-03-11 10:47:43');

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

--
-- Table structure for table `safety_checklists`
--

CREATE TABLE `safety_checklists` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `category` enum('ppe','fall_protection','electrical','fire','excavation','scaffolding','housekeeping','general') NOT NULL DEFAULT 'general',
  `items` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`items`)),
  `inspector` varchar(255) NOT NULL,
  `inspection_date` date NOT NULL,
  `score` decimal(5,2) NOT NULL DEFAULT 0.00,
  `result` enum('pass','fail','needs_improvement') NOT NULL DEFAULT 'pass',
  `remarks` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `safety_checklists`
--

INSERT INTO `safety_checklists` (`id`, `project_id`, `title`, `category`, `items`, `inspector`, `inspection_date`, `score`, `result`, `remarks`, `created_at`, `updated_at`) VALUES
(1, 1, 'Ppe Safety Check', 'ppe', '[{\"item\":\"Hard hats worn\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Safety boots worn\",\"checked\":true,\"notes\":\"\"},{\"item\":\"High-vis vests\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Gloves available\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Eye protection\",\"checked\":false,\"notes\":\"\"}]', 'Anil Sharma', '2026-03-03', 80.00, 'pass', 'Overall compliance is satisfactory', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 2, 'Fall protection Safety Check', 'fall_protection', '[{\"item\":\"Guardrails installed\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Safety nets in place\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Harnesses available\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Anchor points secure\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Warning signs\",\"checked\":false,\"notes\":\"\"}]', 'Vikash Gupta', '2026-02-26', 80.00, 'pass', 'Overall compliance is satisfactory', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 6, 'Electrical Safety Check', 'electrical', '[{\"item\":\"GFCI in use\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Cables inspected\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Lockout\\/tagout\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Grounding verified\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Panel covers on\",\"checked\":false,\"notes\":\"\"}]', 'Priya Nair', '2026-02-27', 80.00, 'pass', 'Overall compliance is satisfactory', '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(4, 5, 'Fire Safety Check', 'fire', '[{\"item\":\"Extinguishers accessible\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Exit routes clear\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Alarm system working\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Hot work permits\",\"checked\":true,\"notes\":\"\"},{\"item\":\"Flammables stored properly\",\"checked\":false,\"notes\":\"\"}]', 'Deepak Joshi', '2026-02-25', 80.00, 'pass', 'Overall compliance is satisfactory', '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `safety_incidents`
--

CREATE TABLE `safety_incidents` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `type` enum('near_miss','first_aid','minor_injury','major_injury','fatality','property_damage','environmental','fire','other') NOT NULL DEFAULT 'near_miss',
  `severity` enum('low','medium','high','critical') NOT NULL DEFAULT 'low',
  `status` enum('reported','investigating','resolved','closed') NOT NULL DEFAULT 'reported',
  `incident_datetime` datetime NOT NULL,
  `location` varchar(255) NOT NULL,
  `reported_by` varchar(255) NOT NULL,
  `injured_person` varchar(255) DEFAULT NULL,
  `root_cause` text DEFAULT NULL,
  `corrective_action` text DEFAULT NULL,
  `preventive_action` text DEFAULT NULL,
  `lost_work_days` int(11) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `safety_incidents`
--

INSERT INTO `safety_incidents` (`id`, `project_id`, `title`, `description`, `type`, `severity`, `status`, `incident_datetime`, `location`, `reported_by`, `injured_person`, `root_cause`, `corrective_action`, `preventive_action`, `lost_work_days`, `created_at`, `updated_at`) VALUES
(1, 1, 'Incident: Near miss', 'Safety incident reported at site', 'near_miss', 'low', 'closed', '2026-03-05 14:48:14', 'Block A Ground Floor', 'Rajesh Verma', NULL, 'Under investigation', 'Corrective measures implemented', NULL, 0, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(2, 2, 'Incident: First aid', 'Safety incident reported at site', 'first_aid', 'medium', 'resolved', '2026-02-28 14:48:14', 'Tower 2 Level 5', 'Anil Kumar', 'Worker A', 'Under investigation', 'Corrective measures implemented', NULL, 1, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(3, 6, 'Incident: Minor injury', 'Safety incident reported at site', 'minor_injury', 'high', 'investigating', '2026-02-20 14:48:14', 'Excavation Area', 'Suresh Patel', 'Worker B', 'Under investigation', 'Corrective measures implemented', NULL, 3, '2026-03-11 09:18:14', '2026-03-11 09:18:14'),
(4, 5, 'Incident: Property damage', 'Safety incident reported at site', 'property_damage', 'low', 'reported', '2026-03-03 14:48:14', 'Material Storage', 'Manoj Singh', NULL, 'Under investigation', 'Corrective measures implemented', NULL, 0, '2026-03-11 09:18:14', '2026-03-11 09:18:14');

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

--
-- Table structure for table `sessions`
--

CREATE TABLE `sessions` (
  `id` varchar(255) NOT NULL,
  `user_id` bigint(20) UNSIGNED DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `payload` longtext NOT NULL,
  `last_activity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `sessions`
--

INSERT INTO `sessions` (`id`, `user_id`, `ip_address`, `user_agent`, `payload`, `last_activity`) VALUES
('5ZOO7Xi5ViFOjeQSGaCgTj7HLu4d1PQfp9ebpD2P', NULL, '127.0.0.1', 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36', 'YToyOntzOjY6Il90b2tlbiI7czo0MDoick5acDRNYW1DOG55bWpnVkFnRVYwM010NE9XTUlyckY5S2U3VDl6TyI7czo2OiJfZmxhc2giO2E6Mjp7czozOiJvbGQiO2E6MDp7fXM6MzoibmV3IjthOjA6e319fQ==', 1773234181);

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

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `project_id` bigint(20) UNSIGNED NOT NULL,
  `phase_id` bigint(20) UNSIGNED DEFAULT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('todo','in_progress','review','completed','blocked') NOT NULL DEFAULT 'todo',
  `priority` enum('low','medium','high','critical') NOT NULL DEFAULT 'medium',
  `assigned_to` varchar(255) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `completed_date` date DEFAULT NULL,
  `progress` int(11) NOT NULL DEFAULT 0,
  `estimated_hours` int(11) NOT NULL DEFAULT 0,
  `actual_hours` int(11) NOT NULL DEFAULT 0,
  `dependency_ids` varchar(255) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tasks`
--

INSERT INTO `tasks` (`id`, `project_id`, `phase_id`, `title`, `description`, `status`, `priority`, `assigned_to`, `start_date`, `due_date`, `completed_date`, `progress`, `estimated_hours`, `actual_hours`, `dependency_ids`, `created_at`, `updated_at`) VALUES
(1, 1, 5, 'Soil testing and survey', 'Task for Aaradhya Residential Tower: Soil testing and survey', 'completed', 'high', 'Neha Gupta', '2026-02-10', '2026-03-27', '2026-03-08', 100, 91, 138, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(2, 1, 7, 'Excavation for foundation', 'Task for Aaradhya Residential Tower: Excavation for foundation', 'in_progress', 'medium', 'Anita Joshi', '2026-02-11', '2026-05-09', NULL, 61, 95, 58, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(3, 1, 1, 'RCC column casting', 'Task for Aaradhya Residential Tower: RCC column casting', 'in_progress', 'high', 'Neha Gupta', '2026-03-06', '2026-03-16', NULL, 10, 20, 5, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(4, 1, 4, 'Concrete slab pouring - Floor 3', 'Task for Aaradhya Residential Tower: Concrete slab pouring - Floor 3', 'in_progress', 'high', 'Rajesh Sharma', '2026-02-18', '2026-05-06', NULL, 59, 23, 4, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(5, 1, 2, 'Steel reinforcement installation', 'Task for Aaradhya Residential Tower: Steel reinforcement installation', 'todo', 'critical', 'Rohit Verma', '2026-02-15', '2026-05-02', NULL, 28, 34, 23, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(6, 1, 3, 'Brickwork - Ground floor', 'Task for Aaradhya Residential Tower: Brickwork - Ground floor', 'todo', 'high', 'Sunita Devi', '2026-03-06', '2026-04-27', NULL, 26, 114, 5, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(7, 1, 5, 'Electrical conduit installation', 'Task for Aaradhya Residential Tower: Electrical conduit installation', 'review', 'low', 'Sunita Devi', '2026-02-21', '2026-04-20', NULL, 19, 79, 51, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(8, 1, 1, 'Plumbing rough-in', 'Task for Aaradhya Residential Tower: Plumbing rough-in', 'completed', 'medium', 'Rohit Verma', '2026-02-19', '2026-03-20', '2026-03-05', 100, 56, 114, NULL, '2026-03-11 07:15:25', '2026-03-11 07:15:25'),
(9, 1, 6, 'Window frame fitting', 'Task for Aaradhya Residential Tower: Window frame fitting', 'completed', 'critical', 'Rajesh Sharma', '2026-03-04', '2026-04-15', '2026-03-07', 100, 66, 54, NULL, '2026-03-11 07:15:26', '2026-03-11 07:15:26'),
(10, 1, 3, 'External plastering', 'Task for Aaradhya Residential Tower: External plastering', 'completed', 'medium', 'Neha Gupta', '2026-03-01', '2026-03-19', '2026-03-09', 100, 71, 85, NULL, '2026-03-11 07:15:26', '2026-03-11 07:15:26'),
(11, 2, 14, 'Soil testing and survey', 'Task for GreenPark Commercial Complex: Soil testing and survey', 'completed', 'low', 'Anita Joshi', '2026-02-10', '2026-03-17', '2026-03-04', 100, 29, 131, NULL, '2026-03-11 07:15:26', '2026-03-11 07:15:26'),
(12, 2, 11, 'Excavation for foundation', 'Task for GreenPark Commercial Complex: Excavation for foundation', 'todo', 'critical', 'Vikram Singh', '2026-03-04', '2026-03-18', NULL, 28, 56, 6, NULL, '2026-03-11 07:15:26', '2026-03-11 07:15:26'),
(13, 2, 8, 'RCC column casting', 'Task for GreenPark Commercial Complex: RCC column casting', 'todo', 'high', 'Rohit Verma', '2026-02-25', '2026-03-28', NULL, 42, 47, 50, NULL, '2026-03-11 07:15:26', '2026-03-11 07:15:26'),
(14, 2, 12, 'Concrete slab pouring - Floor 3', 'Task for GreenPark Commercial Complex: Concrete slab pouring - Floor 3', 'in_progress', 'critical', 'Priya Patel', '2026-02-09', '2026-03-19', NULL, 36, 104, 55, NULL, '2026-03-11 07:15:27', '2026-03-11 07:15:27'),
(15, 2, 8, 'Steel reinforcement installation', 'Task for GreenPark Commercial Complex: Steel reinforcement installation', 'in_progress', 'high', 'Sunita Devi', '2026-03-05', '2026-05-04', NULL, 30, 28, 39, NULL, '2026-03-11 07:15:27', '2026-03-11 07:15:27'),
(16, 2, 12, 'Brickwork - Ground floor', 'Task for GreenPark Commercial Complex: Brickwork - Ground floor', 'completed', 'medium', 'Amit Kumar', '2026-02-22', '2026-04-23', '2026-03-03', 100, 105, 16, NULL, '2026-03-11 07:15:27', '2026-03-11 07:15:27'),
(17, 2, 14, 'Electrical conduit installation', 'Task for GreenPark Commercial Complex: Electrical conduit installation', 'completed', 'medium', 'Neha Gupta', '2026-02-26', '2026-05-09', '2026-03-02', 100, 113, 84, NULL, '2026-03-11 07:15:28', '2026-03-11 07:15:28'),
(18, 2, 13, 'Plumbing rough-in', 'Task for GreenPark Commercial Complex: Plumbing rough-in', 'completed', 'critical', 'Amit Kumar', '2026-02-23', '2026-03-26', '2026-03-07', 100, 94, 133, NULL, '2026-03-11 07:15:28', '2026-03-11 07:15:28'),
(19, 2, 12, 'Window frame fitting', 'Task for GreenPark Commercial Complex: Window frame fitting', 'review', 'critical', 'Sunita Devi', '2026-02-22', '2026-04-05', NULL, 47, 60, 55, NULL, '2026-03-11 07:15:29', '2026-03-11 07:15:29'),
(20, 2, 12, 'External plastering', 'Task for GreenPark Commercial Complex: External plastering', 'todo', 'high', 'Rajesh Sharma', '2026-02-11', '2026-03-17', NULL, 12, 80, 7, NULL, '2026-03-11 07:15:29', '2026-03-11 07:15:29'),
(21, 3, 19, 'Soil testing and survey', 'Task for Sunrise Highway Extension: Soil testing and survey', 'in_progress', 'medium', 'Anita Joshi', '2026-03-02', '2026-03-26', NULL, 52, 94, 54, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(22, 3, 17, 'Excavation for foundation', 'Task for Sunrise Highway Extension: Excavation for foundation', 'completed', 'medium', 'Amit Kumar', '2026-02-13', '2026-04-20', '2026-03-09', 100, 69, 66, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(23, 3, 18, 'RCC column casting', 'Task for Sunrise Highway Extension: RCC column casting', 'in_progress', 'medium', 'Rohit Verma', '2026-02-09', '2026-05-06', NULL, 15, 56, 7, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(24, 3, 17, 'Concrete slab pouring - Floor 3', 'Task for Sunrise Highway Extension: Concrete slab pouring - Floor 3', 'completed', 'medium', 'Priya Patel', '2026-02-13', '2026-04-30', '2026-03-03', 100, 71, 73, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(25, 3, 18, 'Steel reinforcement installation', 'Task for Sunrise Highway Extension: Steel reinforcement installation', 'todo', 'medium', 'Amit Kumar', '2026-02-10', '2026-04-25', NULL, 25, 9, 3, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(26, 3, 16, 'Brickwork - Ground floor', 'Task for Sunrise Highway Extension: Brickwork - Ground floor', 'completed', 'critical', 'Neha Gupta', '2026-02-18', '2026-04-17', '2026-03-07', 100, 96, 127, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(27, 3, 16, 'Electrical conduit installation', 'Task for Sunrise Highway Extension: Electrical conduit installation', 'completed', 'medium', 'Vikram Singh', '2026-02-10', '2026-05-05', '2026-03-06', 100, 113, 137, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(28, 3, 21, 'Plumbing rough-in', 'Task for Sunrise Highway Extension: Plumbing rough-in', 'in_progress', 'medium', 'Anita Joshi', '2026-02-22', '2026-03-28', NULL, 30, 74, 36, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(29, 3, 21, 'Window frame fitting', 'Task for Sunrise Highway Extension: Window frame fitting', 'in_progress', 'medium', 'Amit Kumar', '2026-02-10', '2026-04-25', NULL, 78, 45, 17, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30'),
(30, 3, 15, 'External plastering', 'Task for Sunrise Highway Extension: External plastering', 'todo', 'low', 'Rohit Verma', '2026-02-26', '2026-03-17', NULL, 27, 72, 42, NULL, '2026-03-11 07:15:30', '2026-03-11 07:15:30');

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

--
-- Table structure for table `team_members`
--

CREATE TABLE `team_members` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `role` varchar(255) NOT NULL,
  `department` varchar(255) DEFAULT NULL,
  `designation` varchar(255) DEFAULT NULL,
  `avatar` varchar(255) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `hourly_rate` decimal(10,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `team_members`
--

INSERT INTO `team_members` (`id`, `name`, `email`, `phone`, `role`, `department`, `designation`, `avatar`, `status`, `hourly_rate`, `created_at`, `updated_at`) VALUES
(1, 'Rajesh Sharma', 'rajesh@cpms.com', '9876543210', 'Project Manager', 'Management', 'Senior PM', NULL, 'active', 2500.00, '2026-03-11 07:15:15', '2026-03-11 07:15:15'),
(2, 'Priya Patel', 'priya@cpms.com', '9876543211', 'Site Engineer', 'Engineering', 'Lead Engineer', NULL, 'active', 1800.00, '2026-03-11 07:15:15', '2026-03-11 07:15:15'),
(3, 'Amit Kumar', 'amit@cpms.com', '9876543212', 'Architect', 'Design', 'Chief Architect', NULL, 'active', 3000.00, '2026-03-11 07:15:15', '2026-03-11 07:15:15'),
(4, 'Sunita Devi', 'sunita@cpms.com', '9876543213', 'Quality Inspector', 'Quality', 'QC Lead', NULL, 'active', 1500.00, '2026-03-11 07:15:15', '2026-03-11 07:15:15'),
(5, 'Vikram Singh', 'vikram@cpms.com', '9876543214', 'Safety Officer', 'Safety', 'Safety Head', NULL, 'active', 1600.00, '2026-03-11 07:15:15', '2026-03-11 07:15:15'),
(6, 'Neha Gupta', 'neha@cpms.com', '9876543215', 'Cost Estimator', 'Finance', 'Sr. Estimator', NULL, 'active', 2000.00, '2026-03-11 07:15:16', '2026-03-11 07:15:16'),
(7, 'Rohit Verma', 'rohit@cpms.com', '9876543216', 'Site Supervisor', 'Engineering', 'Supervisor', NULL, 'active', 1200.00, '2026-03-11 07:15:16', '2026-03-11 07:15:16'),
(8, 'Anita Joshi', 'anita@cpms.com', '9876543217', 'MEP Engineer', 'Engineering', 'MEP Lead', NULL, 'active', 2200.00, '2026-03-11 07:15:16', '2026-03-11 07:15:16');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `activity_logs_project_id_foreign` (`project_id`);

--
-- Indexes for table `ai_predictions`
--
ALTER TABLE `ai_predictions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ai_predictions_project_id_foreign` (`project_id`);

--
-- Indexes for table `attendance_records`
--
ALTER TABLE `attendance_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `attendance_records_project_id_foreign` (`project_id`);

--
-- Indexes for table `budget_items`
--
ALTER TABLE `budget_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `budget_items_project_id_foreign` (`project_id`);

--
-- Indexes for table `change_orders`
--
ALTER TABLE `change_orders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `change_orders_project_id_foreign` (`project_id`);

--
-- Indexes for table `comments`
--
ALTER TABLE `comments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `comments_commentable_type_commentable_id_index` (`commentable_type`,`commentable_id`),
  ADD KEY `comments_admin_id_foreign` (`admin_id`),
  ADD KEY `comments_parent_id_foreign` (`parent_id`);

--
-- Indexes for table `compliance_items`
--
ALTER TABLE `compliance_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `compliance_items_project_id_foreign` (`project_id`);

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

--
-- Indexes for table `contracts`
--
ALTER TABLE `contracts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `contracts_project_id_foreign` (`project_id`),
  ADD KEY `contracts_contractor_id_foreign` (`contractor_id`);

--
-- Indexes for table `daily_reports`
--
ALTER TABLE `daily_reports`
  ADD PRIMARY KEY (`id`),
  ADD KEY `daily_reports_project_id_foreign` (`project_id`);

--
-- Indexes for table `documents`
--
ALTER TABLE `documents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `documents_project_id_foreign` (`project_id`);

--
-- Indexes for table `equipment`
--
ALTER TABLE `equipment`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `equipment_equipment_id_unique` (`equipment_id`),
  ADD KEY `equipment_project_id_foreign` (`project_id`);

--
-- Indexes for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`);

--
-- Indexes for table `inspections`
--
ALTER TABLE `inspections`
  ADD PRIMARY KEY (`id`),
  ADD KEY `inspections_project_id_foreign` (`project_id`),
  ADD KEY `inspections_task_id_foreign` (`task_id`);

--
-- Indexes for table `jobs`
--
ALTER TABLE `jobs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `jobs_queue_reserved_at_available_at_index` (`queue`,`reserved_at`,`available_at`);

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

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

--
-- Indexes for table `material_transactions`
--
ALTER TABLE `material_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `material_transactions_material_id_foreign` (`material_id`),
  ADD KEY `material_transactions_project_id_foreign` (`project_id`);

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

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD PRIMARY KEY (`id`),
  ADD KEY `notifications_admin_id_foreign` (`admin_id`);

--
-- Indexes for table `permissions`
--
ALTER TABLE `permissions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `permissions_slug_unique` (`slug`);

--
-- Indexes for table `projects`
--
ALTER TABLE `projects`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `projects_code_unique` (`code`);

--
-- Indexes for table `project_phases`
--
ALTER TABLE `project_phases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `project_phases_project_id_foreign` (`project_id`);

--
-- Indexes for table `project_team`
--
ALTER TABLE `project_team`
  ADD PRIMARY KEY (`id`),
  ADD KEY `project_team_project_id_foreign` (`project_id`),
  ADD KEY `project_team_team_member_id_foreign` (`team_member_id`);

--
-- Indexes for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `purchase_orders_po_number_unique` (`po_number`),
  ADD KEY `purchase_orders_project_id_foreign` (`project_id`);

--
-- Indexes for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `purchase_order_items_purchase_order_id_foreign` (`purchase_order_id`),
  ADD KEY `purchase_order_items_material_id_foreign` (`material_id`);

--
-- Indexes for table `risks`
--
ALTER TABLE `risks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `risks_project_id_foreign` (`project_id`);

--
-- Indexes for table `roles`
--
ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `roles_name_unique` (`name`),
  ADD UNIQUE KEY `roles_slug_unique` (`slug`);

--
-- Indexes for table `role_permission`
--
ALTER TABLE `role_permission`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `role_permission_role_id_permission_id_unique` (`role_id`,`permission_id`),
  ADD KEY `role_permission_permission_id_foreign` (`permission_id`);

--
-- Indexes for table `safety_checklists`
--
ALTER TABLE `safety_checklists`
  ADD PRIMARY KEY (`id`),
  ADD KEY `safety_checklists_project_id_foreign` (`project_id`);

--
-- Indexes for table `safety_incidents`
--
ALTER TABLE `safety_incidents`
  ADD PRIMARY KEY (`id`),
  ADD KEY `safety_incidents_project_id_foreign` (`project_id`);

--
-- Indexes for table `sessions`
--
ALTER TABLE `sessions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sessions_user_id_index` (`user_id`),
  ADD KEY `sessions_last_activity_index` (`last_activity`);

--
-- Indexes for table `tasks`
--
ALTER TABLE `tasks`
  ADD PRIMARY KEY (`id`),
  ADD KEY `tasks_project_id_foreign` (`project_id`),
  ADD KEY `tasks_phase_id_foreign` (`phase_id`);

--
-- Indexes for table `team_members`
--
ALTER TABLE `team_members`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `team_members_email_unique` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_logs`
--
ALTER TABLE `activity_logs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `ai_predictions`
--
ALTER TABLE `ai_predictions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- AUTO_INCREMENT for table `attendance_records`
--
ALTER TABLE `attendance_records`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `budget_items`
--
ALTER TABLE `budget_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=61;

--
-- AUTO_INCREMENT for table `change_orders`
--
ALTER TABLE `change_orders`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `comments`
--
ALTER TABLE `comments`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `compliance_items`
--
ALTER TABLE `compliance_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `contractors`
--
ALTER TABLE `contractors`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `contracts`
--
ALTER TABLE `contracts`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=14;

--
-- AUTO_INCREMENT for table `daily_reports`
--
ALTER TABLE `daily_reports`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `documents`
--
ALTER TABLE `documents`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `equipment`
--
ALTER TABLE `equipment`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `failed_jobs`
--
ALTER TABLE `failed_jobs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `inspections`
--
ALTER TABLE `inspections`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `jobs`
--
ALTER TABLE `jobs`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `materials`
--
ALTER TABLE `materials`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `material_transactions`
--
ALTER TABLE `material_transactions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `migrations`
--
ALTER TABLE `migrations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=10;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `permissions`
--
ALTER TABLE `permissions`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=85;

--
-- AUTO_INCREMENT for table `projects`
--
ALTER TABLE `projects`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `project_phases`
--
ALTER TABLE `project_phases`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;

--
-- AUTO_INCREMENT for table `project_team`
--
ALTER TABLE `project_team`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `risks`
--
ALTER TABLE `risks`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

--
-- AUTO_INCREMENT for table `roles`
--
ALTER TABLE `roles`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `role_permission`
--
ALTER TABLE `role_permission`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=116;

--
-- AUTO_INCREMENT for table `safety_checklists`
--
ALTER TABLE `safety_checklists`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `safety_incidents`
--
ALTER TABLE `safety_incidents`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `tasks`
--
ALTER TABLE `tasks`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=31;

--
-- AUTO_INCREMENT for table `team_members`
--
ALTER TABLE `team_members`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD CONSTRAINT `activity_logs_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `ai_predictions`
--
ALTER TABLE `ai_predictions`
  ADD CONSTRAINT `ai_predictions_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `attendance_records`
--
ALTER TABLE `attendance_records`
  ADD CONSTRAINT `attendance_records_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `budget_items`
--
ALTER TABLE `budget_items`
  ADD CONSTRAINT `budget_items_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `change_orders`
--
ALTER TABLE `change_orders`
  ADD CONSTRAINT `change_orders_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `comments`
--
ALTER TABLE `comments`
  ADD CONSTRAINT `comments_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `comments_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `comments` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `compliance_items`
--
ALTER TABLE `compliance_items`
  ADD CONSTRAINT `compliance_items_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `contracts`
--
ALTER TABLE `contracts`
  ADD CONSTRAINT `contracts_contractor_id_foreign` FOREIGN KEY (`contractor_id`) REFERENCES `contractors` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `contracts_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `daily_reports`
--
ALTER TABLE `daily_reports`
  ADD CONSTRAINT `daily_reports_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `documents`
--
ALTER TABLE `documents`
  ADD CONSTRAINT `documents_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `equipment`
--
ALTER TABLE `equipment`
  ADD CONSTRAINT `equipment_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `inspections`
--
ALTER TABLE `inspections`
  ADD CONSTRAINT `inspections_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `inspections_task_id_foreign` FOREIGN KEY (`task_id`) REFERENCES `tasks` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `material_transactions`
--
ALTER TABLE `material_transactions`
  ADD CONSTRAINT `material_transactions_material_id_foreign` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `material_transactions_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `notifications`
--
ALTER TABLE `notifications`
  ADD CONSTRAINT `notifications_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `project_phases`
--
ALTER TABLE `project_phases`
  ADD CONSTRAINT `project_phases_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `project_team`
--
ALTER TABLE `project_team`
  ADD CONSTRAINT `project_team_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `project_team_team_member_id_foreign` FOREIGN KEY (`team_member_id`) REFERENCES `team_members` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `purchase_orders`
--
ALTER TABLE `purchase_orders`
  ADD CONSTRAINT `purchase_orders_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `purchase_order_items`
--
ALTER TABLE `purchase_order_items`
  ADD CONSTRAINT `purchase_order_items_material_id_foreign` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `purchase_order_items_purchase_order_id_foreign` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `risks`
--
ALTER TABLE `risks`
  ADD CONSTRAINT `risks_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `role_permission`
--
ALTER TABLE `role_permission`
  ADD CONSTRAINT `role_permission_permission_id_foreign` FOREIGN KEY (`permission_id`) REFERENCES `permissions` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `role_permission_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `safety_checklists`
--
ALTER TABLE `safety_checklists`
  ADD CONSTRAINT `safety_checklists_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `safety_incidents`
--
ALTER TABLE `safety_incidents`
  ADD CONSTRAINT `safety_incidents_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `tasks`
--
ALTER TABLE `tasks`
  ADD CONSTRAINT `tasks_phase_id_foreign` FOREIGN KEY (`phase_id`) REFERENCES `project_phases` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `tasks_project_id_foreign` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE;
COMMIT;

