-- WC360 User Submission System Tables
-- Kullanıcı onay sistemi için gerekli tablolar

-- User Submissions Table (Kullanıcı onay bekleyen işlemleri)
CREATE TABLE `user_submissions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type` enum('add_toilet', 'edit_toilet', 'delete_toilet') NOT NULL,
  `toilet_id` int(11) NULL COMMENT 'Edit/Delete işlemleri için mevcut tuvalet ID',
  `original_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'Orijinal tuvalet verileri (JSON format)' CHECK (json_valid(`original_data`)),
  `submitted_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Kullanıcının önerdiği veriler (JSON format)' CHECK (json_valid(`submitted_data`)),
  `status` enum('pending', 'approved', 'rejected') NOT NULL DEFAULT 'pending',
  `admin_notes` text NULL COMMENT 'Admin notları',
  `reviewed_by` int(11) NULL COMMENT 'Onaylayan admin ID',
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `submission_reason` text NULL COMMENT 'Kullanıcının gönderim sebebi',
  `rejection_reason` text NULL COMMENT 'Red etme sebebi',
  `priority` enum('low', 'normal', 'high', 'urgent') NOT NULL DEFAULT 'normal',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_toilet_id` (`toilet_id`),
  KEY `idx_status` (`status`),
  KEY `idx_type` (`type`),
  KEY `idx_reviewed_by` (`reviewed_by`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_user_submissions_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_submissions_toilet_id` FOREIGN KEY (`toilet_id`) REFERENCES `toilets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_user_submissions_reviewed_by` FOREIGN KEY (`reviewed_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kullanıcı onay bekleyen işlemler';

-- Notifications Table (Bildirimler)
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type` enum('submission_approved', 'submission_rejected', 'submission_pending', 'nearby_toilet', 'announcement', 'system') NOT NULL,
  `title` varchar(200) NOT NULL,
  `message` text NOT NULL,
  `data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'Ek bilgi (JSON format)' CHECK (json_valid(`data`)),
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `sent_via_email` tinyint(1) NOT NULL DEFAULT 0,
  `sent_via_push` tinyint(1) NOT NULL DEFAULT 0,
  `email_sent_at` timestamp NULL DEFAULT NULL,
  `push_sent_at` timestamp NULL DEFAULT NULL,
  `related_submission_id` int(11) NULL COMMENT 'İlgili submission ID',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_is_read` (`is_read`),
  KEY `idx_related_submission_id` (`related_submission_id`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_notifications_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_notifications_submission_id` FOREIGN KEY (`related_submission_id`) REFERENCES `user_submissions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kullanıcı bildirimleri';

-- User Notification Preferences Table (Kullanıcı bildirim tercihleri)
CREATE TABLE `user_notification_preferences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `email_submissions` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Submission güncellemeleri e-posta ile',
  `email_nearby_toilets` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Yakın tuvalet bildirimleri e-posta ile',
  `email_announcements` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Duyuru bildirimleri e-posta ile',
  `push_submissions` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Submission güncellemeleri push ile',
  `push_nearby_toilets` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Yakın tuvalet bildirimleri push ile',
  `push_announcements` tinyint(1) NOT NULL DEFAULT 1 COMMENT 'Duyuru bildirimleri push ile',
  `nearby_radius_km` int(11) NOT NULL DEFAULT 5 COMMENT 'Yakın tuvalet bildirimi için mesafe (km)',
  `quiet_hours_start` time NULL DEFAULT NULL COMMENT 'Sessiz saat başlangıcı',
  `quiet_hours_end` time NULL DEFAULT NULL COMMENT 'Sessiz saat bitişi',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_preferences` (`user_id`),
  CONSTRAINT `fk_user_notification_preferences_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kullanıcı bildirim tercihleri';

-- User Locations Table (Kullanıcı konum geçmişi)
CREATE TABLE `user_locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `latitude` decimal(10,8) NOT NULL,
  `longitude` decimal(11,8) NOT NULL,
  `address` text NULL,
  `accuracy` int(11) NULL COMMENT 'Konum doğruluğu (metre)',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_location` (`latitude`, `longitude`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_user_locations_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Kullanıcı konum geçmişi';

-- Submission Files Table (Submission dosyaları)
CREATE TABLE `submission_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submission_id` int(11) NOT NULL,
  `file_type` enum('image', 'document', 'other') NOT NULL DEFAULT 'image',
  `file_name` varchar(255) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `file_size` int(11) NOT NULL DEFAULT 0,
  `mime_type` varchar(100) NULL,
  `uploaded_by` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_submission_id` (`submission_id`),
  KEY `idx_uploaded_by` (`uploaded_by`),
  CONSTRAINT `fk_submission_files_submission_id` FOREIGN KEY (`submission_id`) REFERENCES `user_submissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_submission_files_uploaded_by` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Submission dosyaları';

-- Submission Activities Table (Submission aktiviteleri)
CREATE TABLE `submission_activities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `submission_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `activity_type` enum('created', 'reviewed', 'approved', 'rejected', 'comment_added', 'file_uploaded') NOT NULL,
  `message` text NOT NULL,
  `old_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'Eski veri (JSON format)' CHECK (json_valid(`old_data`)),
  `new_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL COMMENT 'Yeni veri (JSON format)' CHECK (json_valid(`new_data`)),
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_submission_id` (`submission_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_activity_type` (`activity_type`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `fk_submission_activities_submission_id` FOREIGN KEY (`submission_id`) REFERENCES `user_submissions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_submission_activities_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Submission aktivite logları';

-- Existing toilets tablosuna onay sistemi kolonları ekleme
ALTER TABLE `toilets` 
ADD COLUMN `approval_status` enum('approved', 'pending', 'rejected') NOT NULL DEFAULT 'approved' COMMENT 'Onay durumu',
ADD COLUMN `submitted_by` int(11) NULL COMMENT 'Gönderen kullanıcı ID',
ADD COLUMN `approved_by` int(11) NULL COMMENT 'Onaylayan admin ID',
ADD COLUMN `approved_at` timestamp NULL DEFAULT NULL COMMENT 'Onay zamanı',
ADD COLUMN `rejection_reason` text NULL COMMENT 'Red etme sebebi',
ADD COLUMN `submission_notes` text NULL COMMENT 'Gönderim notları',
ADD KEY `idx_approval_status` (`approval_status`),
ADD KEY `idx_submitted_by` (`submitted_by`),
ADD KEY `idx_approved_by` (`approved_by`),
ADD CONSTRAINT `fk_toilets_submitted_by` FOREIGN KEY (`submitted_by`) REFERENCES `users` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `fk_toilets_approved_by` FOREIGN KEY (`approved_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;