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

START TRANSACTION;



--
-- Database: `amanarac_almanara`
--

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

--
-- Table structure for table `auth_tokens`
--

CREATE TABLE `auth_tokens` (
  `id` int NOT NULL,
  `selector` varchar(255) NOT NULL,
  `hashed_token` varchar(255) NOT NULL,
  `user_id` int NOT NULL,
  `expires` datetime NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `devices`
--

CREATE TABLE `devices` (
  `id` int NOT NULL,
  `device_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_seen` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `last_seen` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('active','inactive') COLLATE utf8mb4_unicode_ci DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `device_commands`
--

CREATE TABLE `device_commands` (
  `id` int NOT NULL,
  `device_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `command` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('PENDING','PROCESSING','COMPLETED','FAILED') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PENDING',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `device_contacts`
--

CREATE TABLE `device_contacts` (
  `id` int NOT NULL,
  `device_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `organization` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `device_groups`
--

CREATE TABLE `device_groups` (
  `id` int UNSIGNED NOT NULL,
  `group_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `device_group_members`
--

CREATE TABLE `device_group_members` (
  `id` int NOT NULL,
  `group_id` int NOT NULL,
  `device_id` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `device_motion_events`
--

CREATE TABLE `device_motion_events` (
  `id` bigint UNSIGNED NOT NULL,
  `device_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `event_type` enum('stop','move') COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `start_latitude` decimal(10,7) DEFAULT NULL,
  `start_longitude` decimal(10,7) DEFAULT NULL,
  `end_latitude` decimal(10,7) DEFAULT NULL,
  `end_longitude` decimal(10,7) DEFAULT NULL,
  `duration_seconds` int UNSIGNED NOT NULL,
  `distance_meters` double NOT NULL DEFAULT '0',
  `points_count` int UNSIGNED NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `device_motion_state`
--

CREATE TABLE `device_motion_state` (
  `device_id` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `current_state` enum('idle','moving','stopped') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'idle',
  `last_latitude` decimal(10,7) DEFAULT NULL,
  `last_longitude` decimal(10,7) DEFAULT NULL,
  `last_timestamp` datetime DEFAULT NULL,
  `segment_start_time` datetime DEFAULT NULL,
  `segment_start_latitude` decimal(10,7) DEFAULT NULL,
  `segment_start_longitude` decimal(10,7) DEFAULT NULL,
  `segment_points` int UNSIGNED NOT NULL DEFAULT '0',
  `segment_distance` double NOT NULL DEFAULT '0',
  `stationary_duration` int UNSIGNED NOT NULL DEFAULT '0',
  `pending_stop_start_time` datetime DEFAULT NULL,
  `pending_stop_latitude` decimal(10,7) DEFAULT NULL,
  `pending_stop_longitude` decimal(10,7) DEFAULT NULL,
  `noise_points` int UNSIGNED NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `device_specific_urls`
--

CREATE TABLE `device_specific_urls` (
  `id` int NOT NULL,
  `device_id` varchar(255) NOT NULL,
  `custom_url` text NOT NULL,
  `friendly_name` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `locations`
--

CREATE TABLE `locations` (
  `id` bigint UNSIGNED NOT NULL,
  `device_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `timestamp` datetime DEFAULT NULL,
  `status` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `battery` tinyint UNSIGNED DEFAULT NULL,
  `ts_date` date DEFAULT NULL,
  `ts_time` time DEFAULT NULL,
  `speed` decimal(10,2) DEFAULT NULL,
  `accuracy` decimal(10,2) DEFAULT NULL,
  `altitude` decimal(10,2) DEFAULT NULL,
  `heading` decimal(10,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `places_cache`
--

CREATE TABLE `places_cache` (
  `id` int NOT NULL,
  `lat_rounded` decimal(8,5) NOT NULL COMMENT 'خط العرض مقرب لـ 5 خانات',
  `lng_rounded` decimal(8,5) NOT NULL COMMENT 'خط الطول مقرب لـ 5 خانات',
  `place_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `place_name_short` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'اسم مختصر للمكان',
  `confidence_score` tinyint DEFAULT '100' COMMENT 'درجة الثقة في اسم المكان',
  `source` enum('nominatim','google','manual') COLLATE utf8mb4_unicode_ci DEFAULT 'nominatim' COMMENT 'مصدر البيانات',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `access_count` int DEFAULT '1' COMMENT 'عدد مرات الوصول للتحكم في الأولوية',
  `last_accessed` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='جدول محسن لتخزين أسماء الأماكن مع فهارس متقدمة';

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

--
-- Table structure for table `places_cache_backup`
--

CREATE TABLE `places_cache_backup` (
  `id` int NOT NULL,
  `lat` decimal(10,7) NOT NULL,
  `lng` decimal(10,7) NOT NULL,
  `place_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `places_cache_optimized`
--

CREATE TABLE `places_cache_optimized` (
  `id` int NOT NULL,
  `lat_rounded` decimal(8,5) NOT NULL COMMENT 'خط العرض مقرب لـ 5 خانات',
  `lng_rounded` decimal(8,5) NOT NULL COMMENT 'خط الطول مقرب لـ 5 خانات',
  `place_name` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `place_name_short` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'اسم مختصر للمكان',
  `confidence_score` tinyint DEFAULT '100' COMMENT 'درجة الثقة في اسم المكان',
  `source` enum('nominatim','google','manual') COLLATE utf8mb4_unicode_ci DEFAULT 'nominatim' COMMENT 'مصدر البيانات',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `access_count` int DEFAULT '1' COMMENT 'عدد مرات الوصول للتحكم في الأولوية',
  `last_accessed` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='جدول محسن لتخزين أسماء الأماكن مع فهارس متقدمة';

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

--
-- Table structure for table `places_cache_stats`
--

CREATE TABLE `places_cache_stats` (
  `id` int NOT NULL,
  `date` date NOT NULL,
  `total_requests` int DEFAULT '0',
  `cache_hits` int DEFAULT '0',
  `cache_misses` int DEFAULT '0',
  `api_calls` int DEFAULT '0',
  `avg_response_time` decimal(5,3) DEFAULT '0.000',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='إحصائيات أداء نظام الكاش';

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

--
-- Table structure for table `places_queue`
--

CREATE TABLE `places_queue` (
  `id` int NOT NULL,
  `lat` decimal(8,5) NOT NULL,
  `lng` decimal(8,5) NOT NULL,
  `priority` tinyint DEFAULT '5' COMMENT 'أولوية المعالجة (1-10)',
  `status` enum('pending','processing','completed','failed') DEFAULT 'pending',
  `retry_count` tinyint DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `processed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='طابور معالجة أسماء الأماكن في الخلفية';

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

--
-- Table structure for table `settings`
--

CREATE TABLE `settings` (
  `id` int NOT NULL,
  `setting_key` varchar(255) NOT NULL,
  `setting_value` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `timeline_moves`
--

CREATE TABLE `timeline_moves` (
  `id` int NOT NULL,
  `device_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `from_latitude` decimal(10,6) NOT NULL,
  `from_longitude` decimal(10,6) NOT NULL,
  `to_latitude` decimal(10,6) NOT NULL,
  `to_longitude` decimal(10,6) NOT NULL,
  `distance_km` decimal(10,2) NOT NULL,
  `average_speed_ms` decimal(10,2) DEFAULT NULL,
  `duration_minutes` int NOT NULL,
  `duration_seconds` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `timeline_stops`
--

CREATE TABLE `timeline_stops` (
  `id` int NOT NULL,
  `device_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `start_time` datetime NOT NULL,
  `end_time` datetime NOT NULL,
  `latitude` decimal(10,6) NOT NULL,
  `longitude` decimal(10,6) NOT NULL,
  `duration_minutes` int NOT NULL,
  `duration_seconds` int NOT NULL,
  `average_accuracy` decimal(10,2) DEFAULT NULL,
  `point_count` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `unique_devices`
--

CREATE TABLE `unique_devices` (
  `id` int NOT NULL,
  `device_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `device_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

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

CREATE TABLE `users` (
  `id` int NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `remember_token` varchar(255) DEFAULT NULL,
  `remember_token_expiry` datetime DEFAULT NULL,
  `role` enum('admin','user') NOT NULL DEFAULT 'user',
  `allowed_devices` varchar(255) DEFAULT NULL COMMENT 'قائمة معرفات الأجهزة المسموح بها مفصولة بفواصل'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

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

--
-- Table structure for table `user_places`
--

CREATE TABLE `user_places` (
  `id` int NOT NULL,
  `user_id` int NOT NULL,
  `place_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `place_type` enum('point','circle','polygon') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'point',
  `latitude` decimal(10,7) NOT NULL,
  `longitude` decimal(10,7) NOT NULL,
  `radius` int NOT NULL DEFAULT '100',
  `polygon_coordinates` text COLLATE utf8mb4_unicode_ci COMMENT 'JSON array of coordinates for polygon areas'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `visits`
--

CREATE TABLE `visits` (
  `id` bigint NOT NULL,
  `device_id` varchar(100) COLLATE utf8mb4_general_ci NOT NULL,
  `device_name` varchar(191) COLLATE utf8mb4_general_ci NOT NULL,
  `center_lat` decimal(10,8) NOT NULL,
  `center_lng` decimal(11,8) NOT NULL,
  `radius_m` smallint NOT NULL DEFAULT '60',
  `start_time` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  `enter_battery` tinyint DEFAULT NULL,
  `exit_battery` tinyint DEFAULT NULL,
  `enter_status` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `exit_status` varchar(50) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `points_count` int NOT NULL DEFAULT '1',
  `open` tinyint(1) NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Stand-in structure for view `v_unique_devices`
-- (See below for the actual view)
--
CREATE TABLE `v_unique_devices` (
`device_id` varchar(100)
,`device_name` varchar(255)
,`last_seen` datetime
,`location_count` bigint
);

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

--
-- Structure for view `v_unique_devices`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`amanarac_almanara`@`localhost` SQL SECURITY DEFINER VIEW `v_unique_devices`  AS SELECT `l`.`device_id` AS `device_id`, coalesce(convert((select `device_specific_urls`.`friendly_name` from `device_specific_urls` where ((`device_specific_urls`.`device_id` = `l`.`device_id`) and (`device_specific_urls`.`friendly_name` is not null)) limit 1) using utf8mb4),max(`l`.`device_name`)) AS `device_name`, max(`l`.`timestamp`) AS `last_seen`, count(0) AS `location_count` FROM `locations` AS `l` GROUP BY `l`.`device_id``device_id`  ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `auth_tokens`
--
ALTER TABLE `auth_tokens`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `selector` (`selector`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `idx_auth_tokens_selector` (`selector`),
  ADD KEY `idx_auth_tokens_expires` (`expires`);

--
-- Indexes for table `devices`
--
ALTER TABLE `devices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_device_id` (`device_id`),
  ADD KEY `idx_device_name` (`device_name`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_last_seen` (`last_seen`);

--
-- Indexes for table `device_commands`
--
ALTER TABLE `device_commands`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_device_id_status` (`device_id`,`status`);

--
-- Indexes for table `device_contacts`
--
ALTER TABLE `device_contacts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_device_id` (`device_id`),
  ADD KEY `idx_uploaded_at` (`uploaded_at`),
  ADD KEY `idx_device_uploaded` (`device_id`,`uploaded_at`);

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

--
-- Indexes for table `device_group_members`
--
ALTER TABLE `device_group_members`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_group_device` (`group_id`,`device_id`);

--
-- Indexes for table `device_motion_events`
--
ALTER TABLE `device_motion_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_device_time` (`device_id`,`start_time`),
  ADD KEY `idx_event_type` (`event_type`);

--
-- Indexes for table `device_motion_state`
--
ALTER TABLE `device_motion_state`
  ADD PRIMARY KEY (`device_id`);

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

--
-- Indexes for table `locations`
--
ALTER TABLE `locations`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_device_id_timestamp` (`device_id`,`timestamp`),
  ADD KEY `idx_device_name` (`device_name`),
  ADD KEY `idx_location` (`latitude`,`longitude`),
  ADD KEY `idx_status` (`status`),
  ADD KEY `idx_speed` (`speed`),
  ADD KEY `idx_accuracy` (`accuracy`),
  ADD KEY `idx_device_timestamp` (`device_id`,`timestamp` DESC),
  ADD KEY `idx_timestamp` (`timestamp` DESC);

--
-- Indexes for table `places_cache`
--
ALTER TABLE `places_cache`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `lat_lng_unique` (`lat_rounded`,`lng_rounded`),
  ADD KEY `idx_location_lookup` (`lat_rounded`,`lng_rounded`),
  ADD KEY `idx_access_frequency` (`access_count` DESC,`last_accessed` DESC),
  ADD KEY `idx_confidence` (`confidence_score` DESC),
  ADD KEY `idx_created_at` (`created_at` DESC);

--
-- Indexes for table `places_cache_backup`
--
ALTER TABLE `places_cache_backup`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `lat_lng_idx` (`lat`,`lng`);

--
-- Indexes for table `places_cache_optimized`
--
ALTER TABLE `places_cache_optimized`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `lat_lng_unique` (`lat_rounded`,`lng_rounded`),
  ADD KEY `idx_location_lookup` (`lat_rounded`,`lng_rounded`),
  ADD KEY `idx_access_frequency` (`access_count` DESC,`last_accessed` DESC),
  ADD KEY `idx_confidence` (`confidence_score` DESC),
  ADD KEY `idx_created_at` (`created_at` DESC);

--
-- Indexes for table `places_cache_stats`
--
ALTER TABLE `places_cache_stats`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `date_unique` (`date`),
  ADD KEY `idx_date` (`date` DESC);

--
-- Indexes for table `places_queue`
--
ALTER TABLE `places_queue`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status_priority` (`status`,`priority` DESC),
  ADD KEY `idx_location` (`lat`,`lng`),
  ADD KEY `idx_created_at` (`created_at`);

--
-- Indexes for table `timeline_moves`
--
ALTER TABLE `timeline_moves`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_device_id` (`device_id`),
  ADD KEY `idx_start_time` (`start_time`),
  ADD KEY `idx_end_time` (`end_time`);

--
-- Indexes for table `timeline_stops`
--
ALTER TABLE `timeline_stops`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_device_id` (`device_id`),
  ADD KEY `idx_start_time` (`start_time`),
  ADD KEY `idx_end_time` (`end_time`);

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

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

--
-- Indexes for table `visits`
--
ALTER TABLE `visits`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uq_device_open` (`device_id`,`open`),
  ADD KEY `idx_device_start` (`device_id`,`start_time`),
  ADD KEY `idx_time` (`start_time`,`end_time`),
  ADD KEY `idx_device_open` (`device_id`,`open`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `devices`
--
ALTER TABLE `devices`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `device_commands`
--
ALTER TABLE `device_commands`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `device_contacts`
--
ALTER TABLE `device_contacts`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `device_groups`
--
ALTER TABLE `device_groups`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `device_group_members`
--
ALTER TABLE `device_group_members`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `device_motion_events`
--
ALTER TABLE `device_motion_events`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `locations`
--
ALTER TABLE `locations`
  MODIFY `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `places_cache`
--
ALTER TABLE `places_cache`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `places_cache_backup`
--
ALTER TABLE `places_cache_backup`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `places_cache_optimized`
--
ALTER TABLE `places_cache_optimized`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `places_cache_stats`
--
ALTER TABLE `places_cache_stats`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `places_queue`
--
ALTER TABLE `places_queue`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `timeline_moves`
--
ALTER TABLE `timeline_moves`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `timeline_stops`
--
ALTER TABLE `timeline_stops`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `unique_devices`
--
ALTER TABLE `unique_devices`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `user_places`
--
ALTER TABLE `user_places`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `visits`
--
ALTER TABLE `visits`
  MODIFY `id` bigint NOT NULL AUTO_INCREMENT;
COMMIT;

