-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Dec 16, 2025 at 12:30 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

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


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

--
-- Database: `od_cleans`
--

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

--
-- Table structure for table `additional_service_pricing`
--

CREATE TABLE `additional_service_pricing` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `name` varchar(50) NOT NULL,
  `service_id` int(11) NOT NULL,
  `base_price` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `unit` varchar(50) NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `is_optional` tinyint(1) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `additional_service_pricing`
--

INSERT INTO `additional_service_pricing` (`id`, `name`, `service_id`, `base_price`, `description`, `unit`, `is_active`, `is_optional`, `created_at`, `updated_at`) VALUES
(1, 'laundry', 1, 25.00, 'Washing, drying, and folding services', 'flat rate', 1, 0, '2025-09-03 09:39:58', '2025-09-03 09:39:58'),
(2, 'folding clothes', 1, 15.00, 'Professional folding and organization', 'flat rate', 1, 0, '2025-09-03 09:39:58', '2025-09-03 09:39:58'),
(3, 'fridge cleaning ', 1, 35.00, 'Deep cleaning of refrigerator interior ad arrangement of items ', 'flat rate', 1, 1, '2025-09-03 09:39:58', '2025-09-08 11:19:09'),
(4, 'Halimak E-Solutions ', 1, 20.00, 'Thorough cleaning of baseboards and trim', 'per square foot', 1, 0, '2025-09-03 09:39:58', '2025-11-19 18:40:17'),
(5, 'cabinet cleaning', 4, 30.00, 'Cleaning inside cabinets and drawers only', 'flat rate', 1, 0, '2025-09-03 09:39:58', '2025-09-08 10:17:08'),
(6, 'window cleanings', 4, 40.00, 'Cleaning of interior window surfaces', 'flat rate', 1, 0, '2025-09-03 09:39:58', '2025-09-08 12:21:02');

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

--
-- Table structure for table `admin_activity`
--

CREATE TABLE `admin_activity` (
  `id` int(11) NOT NULL,
  `admin_id` int(11) NOT NULL,
  `action` varchar(255) NOT NULL,
  `type` varchar(50) NOT NULL,
  `details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`details`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `admin_users`
--

CREATE TABLE `admin_users` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `role` enum('admin','manager') DEFAULT 'admin',
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `session_token` varchar(255) NOT NULL,
  `session_expires` varchar(255) NOT NULL,
  `reset_token` varchar(500) NOT NULL,
  `reset_token_expiry` varchar(500) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admin_users`
--

INSERT INTO `admin_users` (`id`, `email`, `name`, `password_hash`, `role`, `is_active`, `created_at`, `updated_at`, `session_token`, `session_expires`, `reset_token`, `reset_token_expiry`) VALUES
(1, 'mubraksak@gmail.com', 'Admin User', '$2b$12$8c76USAKwYQjSdPLPxSna.yHbY7Ydpy.AFUNEW6c6NP5HBO8sx1TC', 'admin', 1, '2025-08-24 09:23:08', '2025-12-15 15:33:05', '544a3535ddb105fab28e5cbbab5241129a96c3e567289da6f721f5e1d0ecfb83', '2025-12-16 00:33:05.465000', '', ''),
(2, 'admin@example.com', 'Administrator', '$2b$12$HMzWtQe.bR4odV8tLjCoTOSb14QF6OXBObPi9Cl08vtnqUrQvJ87y', 'admin', 1, '2025-08-26 10:05:20', '2025-08-26 10:05:20', '', '', '', '');

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

--
-- Table structure for table `bookings`
--

CREATE TABLE `bookings` (
  `id` int(11) NOT NULL,
  `quote_request_id` int(11) NOT NULL,
  `scheduled_date` datetime NOT NULL,
  `status` enum('pending_schedule','scheduled','confirmed','completed','cancelled') NOT NULL DEFAULT 'pending_schedule',
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `bookings`
--

INSERT INTO `bookings` (`id`, `quote_request_id`, `scheduled_date`, `status`, `completed_at`, `created_at`, `updated_at`) VALUES
(16, 14, '2025-12-25 23:00:00', 'completed', NULL, '2025-12-14 14:10:56', '2025-12-15 14:52:47'),
(17, 13, '2025-12-16 00:00:00', 'scheduled', NULL, '2025-12-14 14:27:03', '2025-12-14 14:27:52');

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

--
-- Table structure for table `cleaners`
--

CREATE TABLE `cleaners` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `business_name` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `service_areas` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`service_areas`)),
  `services_offered` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`services_offered`)),
  `experience_years` int(11) DEFAULT NULL,
  `hourly_rate` decimal(10,2) DEFAULT NULL,
  `status` enum('pending','approved','rejected','suspended') DEFAULT 'pending',
  `is_available` tinyint(1) DEFAULT 1,
  `rating` decimal(3,2) DEFAULT 0.00,
  `admin_notes` text NOT NULL,
  `total_jobs` int(11) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cleaners`
--

INSERT INTO `cleaners` (`id`, `user_id`, `business_name`, `phone`, `address`, `service_areas`, `services_offered`, `experience_years`, `hourly_rate`, `status`, `is_available`, `rating`, `admin_notes`, `total_jobs`, `created_at`, `updated_at`) VALUES
(2, 1, '', '', '', '[]', '[]', 0, 0.00, 'approved', 0, 5.00, 'welcome to OD clean service', 0, '2025-11-27 11:59:10', '2025-12-07 14:20:56'),
(3, 3, 'OD cleaner', '08138018194', 'houston texas ', '[\"Downtown\",\"West End\"]', '[\"Residential Cleaning\",\"Deep Cleaning\"]', 2, 20.00, 'approved', 1, 0.00, 'welcome to OD Cleaning services ', 0, '2025-11-30 17:07:45', '2025-12-15 14:52:47');

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

--
-- Table structure for table `cleaner_assignments`
--

CREATE TABLE `cleaner_assignments` (
  `id` int(11) NOT NULL,
  `quote_request_id` int(11) NOT NULL,
  `cleaner_id` int(11) NOT NULL,
  `assigned_by` int(11) NOT NULL,
  `status` enum('pending','accepted','rejected','completed','cancelled') DEFAULT 'pending',
  `assigned_date` timestamp NOT NULL DEFAULT current_timestamp(),
  `accepted_at` timestamp NULL DEFAULT NULL,
  `completed_at` timestamp NULL DEFAULT NULL,
  `cleaner_notes` text DEFAULT NULL,
  `admin_notes` text DEFAULT NULL,
  `payment_status` enum('pending','paid','processing') DEFAULT 'pending',
  `payment_amount` decimal(10,2) DEFAULT NULL,
  `payment_date` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `cleaner_assignments`
--

INSERT INTO `cleaner_assignments` (`id`, `quote_request_id`, `cleaner_id`, `assigned_by`, `status`, `assigned_date`, `accepted_at`, `completed_at`, `cleaner_notes`, `admin_notes`, `payment_status`, `payment_amount`, `payment_date`) VALUES
(12, 14, 3, 1, 'completed', '2025-12-14 15:04:20', '2025-12-14 15:05:23', '2025-12-15 14:52:46', NULL, NULL, 'pending', 100.00, NULL),
(13, 13, 3, 3, 'rejected', '2025-12-15 14:30:18', NULL, NULL, NULL, NULL, 'pending', NULL, NULL);

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

--
-- Table structure for table `cleaner_availability`
--

CREATE TABLE `cleaner_availability` (
  `id` int(11) NOT NULL,
  `cleaner_id` int(11) NOT NULL,
  `day_of_week` int(11) NOT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  `is_available` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `cleaner_reviews`
--

CREATE TABLE `cleaner_reviews` (
  `id` int(11) NOT NULL,
  `cleaner_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `booking_id` int(11) NOT NULL,
  `rating` int(11) NOT NULL CHECK (`rating` >= 1 and `rating` <= 5),
  `comment` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `type` enum('booking_reminder','new_quote','quote_accepted') NOT NULL,
  `title` varchar(255) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `related_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quote_additional_services`
--

CREATE TABLE `quote_additional_services` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `quote_id` int(11) DEFAULT NULL,
  `service_type` varchar(50) NOT NULL CHECK (`service_type` in ('laundry','folding_clothes','fridge_cleaning','baseboard_cleaning','cabinet_cleaning','window_cleaning')),
  `price` decimal(10,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quote_history`
--

CREATE TABLE `quote_history` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `quote_id` int(11) DEFAULT NULL,
  `field_name` varchar(100) NOT NULL,
  `old_value` text DEFAULT NULL,
  `new_value` text DEFAULT NULL,
  `changed_by` varchar(100) DEFAULT 'system',
  `changed_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quote_images`
--

CREATE TABLE `quote_images` (
  `id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `image_url` varchar(500) NOT NULL,
  `image_name` varchar(255) NOT NULL,
  `image_size` int(11) NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `quote_requests`
--

CREATE TABLE `quote_requests` (
  `id` int(20) UNSIGNED NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `status` enum('pending','quoted','accepted','declined','scheduled','completed','counter_offer','paid') NOT NULL DEFAULT 'pending',
  `service_type` varchar(50) NOT NULL CHECK (`service_type` in ('residential','commercial','deep','post_construction','move_in_out')),
  `property_type` varchar(50) NOT NULL CHECK (`property_type` in ('house','apartment','condo','office','retail','other')),
  `bedrooms` int(11) NOT NULL CHECK (`bedrooms` >= 0),
  `bathrooms` int(11) NOT NULL CHECK (`bathrooms` >= 0),
  `square_footage` int(11) DEFAULT NULL,
  `cleaning_type` varchar(50) NOT NULL CHECK (`cleaning_type` in ('standard','deep','post_construction')),
  `cleaning_frequency` varchar(20) DEFAULT NULL CHECK (`cleaning_frequency` in ('one_time','weekly','bi_weekly','monthly')),
  `has_pets` tinyint(1) DEFAULT 0,
  `desired_date` date DEFAULT NULL,
  `desired_date1` datetime DEFAULT NULL,
  `desired_date2` datetime DEFAULT NULL,
  `desired_date3` datetime DEFAULT NULL,
  `contact_name` varchar(255) NOT NULL,
  `contact_email` varchar(255) NOT NULL,
  `contact_phone` varchar(50) NOT NULL,
  `street_address` text NOT NULL,
  `city` varchar(100) NOT NULL,
  `state` char(2) NOT NULL,
  `zip_code` varchar(10) NOT NULL,
  `special_instructions` text DEFAULT NULL,
  `additional_details` text DEFAULT NULL,
  `base_price` decimal(10,2) DEFAULT NULL,
  `additional_services_price` decimal(10,2) DEFAULT NULL,
  `suggested_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) DEFAULT NULL,
  `discount` decimal(10,2) DEFAULT 0.00,
  `final_price` decimal(10,2) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `admin_notes` text NOT NULL,
  `user_notes` text NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `quoted_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `expires_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `quote_requests`
--

INSERT INTO `quote_requests` (`id`, `user_id`, `status`, `service_type`, `property_type`, `bedrooms`, `bathrooms`, `square_footage`, `cleaning_type`, `cleaning_frequency`, `has_pets`, `desired_date`, `desired_date1`, `desired_date2`, `desired_date3`, `contact_name`, `contact_email`, `contact_phone`, `street_address`, `city`, `state`, `zip_code`, `special_instructions`, `additional_details`, `base_price`, `additional_services_price`, `suggested_price`, `total_price`, `discount`, `final_price`, `created_at`, `admin_notes`, `user_notes`, `updated_at`, `quoted_at`, `expires_at`) VALUES
(11, 2, 'quoted', 'residential', 'house', 2, 1, 200, 'standard', 'bi_weekly', 1, NULL, '2025-12-05 00:00:00', '2025-12-06 00:00:00', '2025-12-07 00:00:00', 'mubarak abdulkadir', 'mubraksak@gmail.com', '08138018194', '19, Olubunmi Rotimi', 'Lekki', 'AR', '700321', NULL, 'hello ', 200.00, 25.00, 0.00, 200.00, 0.00, 25.00, '2025-11-28 09:39:34', 'all services', '', '2025-11-28 10:55:29', '2025-11-28 09:39:34', '2025-11-28 09:39:34'),
(12, 2, 'quoted', 'residential', 'house', 2, 1, 200, 'deep', 'one_time', 0, NULL, '2025-12-06 00:00:00', '2025-12-09 00:00:00', '2025-12-12 00:00:00', 'mubarak abdulkadir', 'mubraksak@gmail.com', '08138018194', '19, Olubunmi Rotimi', 'Lekki', 'AK', '700321', NULL, NULL, 500.00, 25.00, 0.00, 500.00, 0.00, 25.00, '2025-12-01 12:51:57', '', '', '2025-12-01 12:59:46', '2025-12-01 12:51:57', '2025-12-01 12:51:57'),
(13, 2, 'scheduled', 'residential', 'apartment', 1, 1, 200, 'standard', 'one_time', 0, NULL, '2025-12-10 00:00:00', '2025-12-17 00:00:00', '2025-12-25 00:00:00', 'mubarak abdulkadir', 'mubraksak@gmail.com', '08138018194', '19, Olubunmi Rotimi', 'Lekki', 'FL', '700321', 'HELLO', NULL, 200.00, 0.00, 285.00, 285.00, 0.00, 0.00, '2025-12-07 12:24:24', '', '', '2025-12-14 14:27:02', '2025-12-07 12:24:24', '2025-12-07 12:24:24'),
(14, 2, 'completed', 'residential', 'apartment', 3, 2, 2800, 'standard', 'one_time', 1, NULL, '2025-12-24 00:00:00', '2025-12-26 00:00:00', '2025-12-28 00:00:00', 'mubarak abdulkadir', 'mubraksak@gmail.com', '08138018194', '19, Olubunmi Rotimi', 'Lekki', 'HI', '700321', 'qwertyuiop', NULL, 350.00, 25.00, 0.00, 350.00, 0.00, 25.00, '2025-12-14 12:52:18', '', '', '2025-12-14 14:10:54', '2025-12-14 12:52:18', '2025-12-14 12:52:18');

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

--
-- Table structure for table `review_tokens`
--

CREATE TABLE `review_tokens` (
  `id` int(11) NOT NULL,
  `token` varchar(64) NOT NULL,
  `user_id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `expires_at` datetime NOT NULL,
  `used` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `review_tokens`
--

INSERT INTO `review_tokens` (`id`, `token`, `user_id`, `quote_id`, `email`, `expires_at`, `used`, `created_at`) VALUES
(1, '51003454-9ab1-46ef-8e1d-8798113b7caa', 2, 11, 'mubraksak@gmail.com', '2025-12-14 14:59:28', 1, '2025-12-07 13:59:28'),
(2, '0dfb0755-629b-4ddf-bc33-0c8284272b84', 2, 11, 'mubraksak@gmail.com', '2025-12-16 15:49:18', 0, '2025-12-09 14:49:18'),
(3, 'a386a1d5-5806-4424-88d9-d5198603298f', 2, 12, 'mubraksak@gmail.com', '2025-12-16 16:01:31', 0, '2025-12-09 15:01:31');

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

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `image_url` varchar(500) NOT NULL,
  `display_order` int(11) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `services`
--

INSERT INTO `services` (`id`, `name`, `description`, `image_url`, `display_order`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'RESIDENTIAL CLEANING', 'Regular maintenance cleaning including dusting, vacuuming, mopping, and bathroom sanitization.', 'https://images.unsplash.com/photo-1648475237029-7f853809ca14?q=80&w=1171&auto=format&fit=crop&ixlib=rb-4.1.0&ixid=M3wxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8fA%3D%3D', 1, 1, '2025-08-24 09:23:07', '2025-09-16 09:02:01'),
(4, 'COMMERCIAL CLEANING', 'Professional commercial cleaning services to maintain a clean and productive work environment.', 'https://plus.unsplash.com/premium_photo-1661962229971-d4531b1db854?q=80&w=1255&auto=format&fit=crop&ixlib=rb-4.1.0&ixid=M3wxMjA3fDB8MHxwaG90by1wYWdlfHx8fGVufDB8fHx8fA%3D%3D', 4, 1, '2025-08-24 09:23:07', '2025-09-16 09:01:41');

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

--
-- Table structure for table `service_pricing`
--

CREATE TABLE `service_pricing` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `service_type` varchar(50) NOT NULL,
  `base_price` decimal(10,2) NOT NULL,
  `price_per_bedroom` decimal(10,2) DEFAULT 0.00,
  `price_per_bathroom` decimal(10,2) DEFAULT 0.00,
  `price_per_sqft` decimal(10,6) DEFAULT 0.000000,
  `min_price` decimal(10,2) DEFAULT NULL,
  `max_price` decimal(10,2) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `service_pricing`
--

INSERT INTO `service_pricing` (`id`, `service_type`, `base_price`, `price_per_bedroom`, `price_per_bathroom`, `price_per_sqft`, `min_price`, `max_price`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 'standard', 80.00, 10.00, 15.00, 0.050000, 50.00, 300.00, 1, '2025-09-03 09:39:41', '2025-09-03 09:39:41'),
(2, 'deep', 150.00, 20.00, 25.00, 0.080000, 100.00, 500.00, 1, '2025-09-03 09:39:41', '2025-09-03 09:39:41'),
(3, 'post_construction', 200.00, 30.00, 35.00, 0.120000, 150.00, 800.00, 1, '2025-09-03 09:39:41', '2025-09-03 09:39:41');

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

--
-- Table structure for table `site_config`
--

CREATE TABLE `site_config` (
  `id` int(11) NOT NULL,
  `hero_title` varchar(500) NOT NULL,
  `hero_subtitle` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `site_config`
--

INSERT INTO `site_config` (`id`, `hero_title`, `hero_subtitle`, `created_at`, `updated_at`) VALUES
(1, 'OD cleaning services', 'let clean your home extra', '2025-08-24 09:23:07', '2025-09-07 18:18:09');

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

--
-- Table structure for table `testimonials`
--

CREATE TABLE `testimonials` (
  `id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `client_name` varchar(255) NOT NULL,
  `quote` text NOT NULL,
  `rating` int(11) NOT NULL,
  `service_type` varchar(225) NOT NULL,
  `image_url` varchar(500) DEFAULT NULL,
  `display_order` int(11) DEFAULT 0,
  `token` int(11) NOT NULL,
  `review_date` date NOT NULL DEFAULT current_timestamp(),
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `testimonials`
--

INSERT INTO `testimonials` (`id`, `quote_id`, `user_id`, `client_name`, `quote`, `rating`, `service_type`, `image_url`, `display_order`, `token`, `review_date`, `is_active`, `created_at`, `updated_at`) VALUES
(1, 0, 0, 'Sarah Johnson', 'Od Cleaning Services transformed my home! Their attention to detail is incredible and the team is so professional.', 0, '', 'https://media.istockphoto.com/id/1406197730/photo/portrait-of-a-young-handsome-indian-man.jpg?s=1024x1024&w=is&k=20&c=VruKKTu4jBF2xPEEQUMWwd4bwJPysSsqLuZ7h1OyD8M=', 1, 0, '2025-12-07', 1, '2025-08-24 09:23:08', '2025-09-06 13:54:34'),
(2, 0, 0, 'Michael Chen', 'Best cleaning service in the city. They consistently deliver exceptional results for our office space.', 0, '', 'https://media.istockphoto.com/id/1406197730/photo/portrait-of-a-young-handsome-indian-man.jpg?s=1024x1024&w=is&k=20&c=VruKKTu4jBF2xPEEQUMWwd4bwJPysSsqLuZ7h1OyD8M=', 2, 0, '2025-12-07', 1, '2025-08-24 09:23:08', '2025-12-08 12:43:36'),
(3, 0, 0, 'Emily Rodriguez', 'I trust Od Cleaning with my home completely. They are reliable, thorough, and always exceed my thinking.', 0, '', 'https://media.istockphoto.com/id/1406197730/photo/portrait-of-a-young-handsome-indian-man.jpg?s=1024x1024&w=is&k=20&c=VruKKTu4jBF2xPEEQUMWwd4bwJPysSsqLuZ7h1OyD8M=', 3, 0, '2025-12-07', 1, '2025-08-24 09:23:08', '2025-09-06 13:55:00'),
(4, 0, 0, 'salman dauda', 'i love your cleaning service smoth and clean ', 0, '', NULL, 0, 0, '2025-12-07', 1, '2025-09-16 10:19:22', '2025-09-16 10:19:22'),
(5, 11, 2, 'mubarak abdulkadir', 'i love your services nice work', 5, 'standard cleaning ', NULL, 0, 51003454, '2025-12-07', 0, '2025-12-07 14:20:56', '2025-12-15 19:16:25');

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

--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL,
  `quote_id` int(11) NOT NULL,
  `stripe_payment_intent_id` varchar(255) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `currency` varchar(3) NOT NULL DEFAULT 'usd',
  `status` enum('pending','succeeded','failed','refunded') NOT NULL,
  `customer_email` varchar(255) NOT NULL,
  `customer_name` varchar(255) DEFAULT NULL,
  `failure_message` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `transactions`
--

INSERT INTO `transactions` (`id`, `quote_id`, `stripe_payment_intent_id`, `amount`, `currency`, `status`, `customer_email`, `customer_name`, `failure_message`, `created_at`, `updated_at`) VALUES
(12, 14, 'pi_3SeG0J5ODYdZSy5G1AI3iUiu', 350.00, 'usd', 'succeeded', 'mubraksak@gmail.com', 'mubarak abdulkadir', NULL, '2025-12-14 14:10:55', '2025-12-14 14:10:55'),
(13, 13, 'pi_3SeGFx5ODYdZSy5G14hmy5xo', 285.00, 'usd', 'succeeded', 'mubraksak@gmail.com', 'mubarak abdulkadir', NULL, '2025-12-14 14:27:02', '2025-12-14 14:27:02');

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password` varchar(500) NOT NULL,
  `name` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email_verified` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `role` varchar(20) NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `magic_token` varchar(255) NOT NULL,
  `magic_token_expires` varchar(255) NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `session_expires` varchar(255) NOT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT '["read", "write", "delete"]' CHECK (json_valid(`permissions`)),
  `last_ip` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `email`, `password`, `name`, `phone`, `email_verified`, `created_at`, `role`, `updated_at`, `magic_token`, `magic_token_expires`, `session_token`, `session_expires`, `permissions`, `last_ip`, `user_agent`) VALUES
(1, 'cleaner@gmail.com', '$2a$12$HM1oH7AAy.LanGEGnxqwN.yfOwhnsjT5xhk1SesVNFcJtS3fEIsVW', 'cleaner ', '', 1, '2025-11-27 11:59:10', 'cleaner', '2025-11-27 12:20:33', '', '', 'e6eb798a-b3ff-404f-b57b-9c35e01d5d5b', '2025-12-04 13:07:55.614000', '[\"read\", \"write\", \"delete\"]', NULL, NULL),
(2, 'mubraksak@gmail.com', '', 'Mubarak Abdulkadir', '09091494766', 1, '2025-11-28 09:39:33', 'customer', '2025-12-16 11:25:19', '', '', '614be3ad6c49bddbe083779850d66e5d56d8f7b84b86f6bb86e97eb2d245ec1c', '2025-12-22 15:15:01.999000', '[\"read\", \"write\", \"delete\"]', NULL, NULL),
(3, 'mubarakihalim@gmail.com', '$2b$12$5kvaDrWaGocKh9JkOtiAf.dgsxaMeirGS3GruINFwf/tA5GsufZ1W', 'OD clenear ', '08138018194', 0, '2025-11-30 17:07:45', 'cleaner', '2025-12-15 14:12:06', '', '', '15c9c043-1376-4241-bf5b-efe80a435eb4', '2025-12-22 15:12:05.953000', '[\"read\", \"write\", \"delete\"]', NULL, NULL);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `additional_service_pricing`
--
ALTER TABLE `additional_service_pricing`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `service_type` (`name`);

--
-- Indexes for table `admin_activity`
--
ALTER TABLE `admin_activity`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_admin_activity_admin_id` (`admin_id`),
  ADD KEY `idx_admin_activity_created_at` (`created_at`);

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

--
-- Indexes for table `bookings`
--
ALTER TABLE `bookings`
  ADD PRIMARY KEY (`id`),
  ADD KEY `quote_request_id` (`quote_request_id`),
  ADD KEY `idx_bookings_status` (`status`),
  ADD KEY `idx_bookings_scheduled_date` (`scheduled_date`),
  ADD KEY `idx_bookings_quote_id` (`quote_request_id`);

--
-- Indexes for table `cleaners`
--
ALTER TABLE `cleaners`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_user_cleaner` (`user_id`),
  ADD KEY `idx_cleaners_status` (`status`);
ALTER TABLE `cleaners` ADD FULLTEXT KEY `business_name` (`business_name`);
ALTER TABLE `cleaners` ADD FULLTEXT KEY `business_name_2` (`business_name`);

--
-- Indexes for table `cleaner_assignments`
--
ALTER TABLE `cleaner_assignments`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cleaner_assignments_status` (`status`),
  ADD KEY `idx_cleaner_assignments_cleaner_id` (`cleaner_id`),
  ADD KEY `idx_cleaner_assignments_payment_status` (`payment_status`);

--
-- Indexes for table `cleaner_availability`
--
ALTER TABLE `cleaner_availability`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_cleaner_availability_cleaner_day` (`cleaner_id`,`day_of_week`);

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

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

--
-- Indexes for table `quote_additional_services`
--
ALTER TABLE `quote_additional_services`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `quote_id` (`quote_id`,`service_type`),
  ADD KEY `idx_quote_services` (`quote_id`);

--
-- Indexes for table `quote_history`
--
ALTER TABLE `quote_history`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_quote_history` (`quote_id`,`changed_at`);

--
-- Indexes for table `quote_images`
--
ALTER TABLE `quote_images`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_quote_images_quote_id` (`quote_id`);

--
-- Indexes for table `quote_requests`
--
ALTER TABLE `quote_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_quote_user` (`user_id`),
  ADD KEY `idx_quote_created` (`created_at`),
  ADD KEY `idx_quotes_status` (`status`),
  ADD KEY `idx_quotes_user_id` (`user_id`),
  ADD KEY `idx_quotes_created_at` (`created_at`),
  ADD KEY `idx_quotes_desired_date` (`desired_date`),
  ADD KEY `idx_quote_requests_status` (`status`),
  ADD KEY `idx_quote_requests_created_at` (`created_at`);
ALTER TABLE `quote_requests` ADD FULLTEXT KEY `idx_quotes_search` (`special_instructions`,`admin_notes`);

--
-- Indexes for table `review_tokens`
--
ALTER TABLE `review_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `token` (`token`);

--
-- Indexes for table `services`
--
ALTER TABLE `services`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_services_active` (`is_active`);
ALTER TABLE `services` ADD FULLTEXT KEY `idx_services_search` (`name`,`description`);

--
-- Indexes for table `service_pricing`
--
ALTER TABLE `service_pricing`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `service_type` (`service_type`);

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

--
-- Indexes for table `testimonials`
--
ALTER TABLE `testimonials`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_testimonials_active` (`is_active`);
ALTER TABLE `testimonials` ADD FULLTEXT KEY `idx_testimonials_search` (`client_name`,`quote`);

--
-- Indexes for table `transactions`
--
ALTER TABLE `transactions`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `stripe_payment_intent_id` (`stripe_payment_intent_id`),
  ADD KEY `idx_quote_id` (`quote_id`),
  ADD KEY `idx_stripe_payment_intent_id` (`stripe_payment_intent_id`),
  ADD KEY `idx_customer_email` (`customer_email`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD KEY `idx_users_email` (`email`),
  ADD KEY `idx_users_name` (`name`),
  ADD KEY `idx_users_phone` (`phone`),
  ADD KEY `idx_users_role` (`role`),
  ADD KEY `idx_users_session_expires` (`session_expires`);
ALTER TABLE `users` ADD FULLTEXT KEY `idx_users_search` (`name`,`email`);
ALTER TABLE `users` ADD FULLTEXT KEY `name` (`name`,`email`);
ALTER TABLE `users` ADD FULLTEXT KEY `name_2` (`name`,`email`);

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `admin_activity`
--
ALTER TABLE `admin_activity`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

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

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

--
-- AUTO_INCREMENT for table `cleaner_availability`
--
ALTER TABLE `cleaner_availability`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `cleaner_reviews`
--
ALTER TABLE `cleaner_reviews`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `notifications`
--
ALTER TABLE `notifications`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

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

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

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

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

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `admin_activity`
--
ALTER TABLE `admin_activity`
  ADD CONSTRAINT `admin_activity_ibfk_1` FOREIGN KEY (`admin_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `cleaners`
--
ALTER TABLE `cleaners`
  ADD CONSTRAINT `cleaners_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `cleaner_assignments`
--
ALTER TABLE `cleaner_assignments`
  ADD CONSTRAINT `fk_cleaner_assignments_cleaner` FOREIGN KEY (`cleaner_id`) REFERENCES `cleaners` (`id`) ON DELETE CASCADE;
COMMIT;

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