-- SUSCHAT Database Schema
-- Compatible with MySQL 5.7+ / 8.0+ and MariaDB 10.3+
-- Engine: InnoDB, Charset: utf8mb4, Collation: utf8mb4_unicode_ci

SET FOREIGN_KEY_CHECKS = 0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

-- 1. Users Table
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` VARCHAR(64) NOT NULL UNIQUE,
  `full_name` VARCHAR(128) NOT NULL,
  `username` VARCHAR(64) NOT NULL UNIQUE,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `phone` VARCHAR(32) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `profile_pic` VARCHAR(255) DEFAULT NULL,
  `about` VARCHAR(255) DEFAULT 'Hey there! I am using SUSCHAT.',
  `country` VARCHAR(64) DEFAULT NULL,
  `gender` ENUM('male', 'female', 'other', 'unspecified') DEFAULT 'unspecified',
  `dob` DATE DEFAULT NULL,
  `is_online` TINYINT(1) DEFAULT 0,
  `last_seen` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `status` ENUM('active', 'suspended', 'banned') DEFAULT 'active',
  `is_admin` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_users_username` (`username`),
  INDEX `idx_users_email` (`email`),
  INDEX `idx_users_phone` (`phone`),
  INDEX `idx_users_last_seen` (`last_seen`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. User Sessions & Tokens Table
CREATE TABLE IF NOT EXISTS `user_sessions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` VARCHAR(64) NOT NULL,
  `token` VARCHAR(255) NOT NULL UNIQUE,
  `refresh_token` VARCHAR(255) DEFAULT NULL,
  `device_name` VARCHAR(128) DEFAULT NULL,
  `platform` VARCHAR(32) DEFAULT 'android',
  `ip_address` VARCHAR(45) DEFAULT NULL,
  `user_agent` TEXT DEFAULT NULL,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_sessions_user_id` (`user_id`),
  INDEX `idx_sessions_token` (`token`),
  CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. User Devices Table (For Push Notifications / FCM)
CREATE TABLE IF NOT EXISTS `user_devices` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` VARCHAR(64) NOT NULL,
  `device_token` VARCHAR(255) NOT NULL UNIQUE,
  `platform` ENUM('android', 'ios', 'web') DEFAULT 'android',
  `device_name` VARCHAR(128) DEFAULT NULL,
  `last_active` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_devices_user_id` (`user_id`),
  CONSTRAINT `fk_devices_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Privacy Settings Table
CREATE TABLE IF NOT EXISTS `privacy_settings` (
  `user_id` VARCHAR(64) PRIMARY KEY,
  `last_seen` ENUM('everyone', 'contacts', 'nobody') DEFAULT 'everyone',
  `profile_photo` ENUM('everyone', 'contacts', 'nobody') DEFAULT 'everyone',
  `about` ENUM('everyone', 'contacts', 'nobody') DEFAULT 'everyone',
  `status` ENUM('everyone', 'contacts', 'nobody') DEFAULT 'everyone',
  `read_receipts` TINYINT(1) DEFAULT 1,
  `groups` ENUM('everyone', 'contacts', 'nobody') DEFAULT 'everyone',
  `calls` ENUM('everyone', 'contacts', 'nobody') DEFAULT 'everyone',
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_privacy_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Contacts Table
CREATE TABLE IF NOT EXISTS `contacts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` VARCHAR(64) NOT NULL,
  `contact_user_id` VARCHAR(64) NOT NULL,
  `custom_name` VARCHAR(128) DEFAULT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_user_contact` (`user_id`, `contact_user_id`),
  INDEX `idx_contacts_user_id` (`user_id`),
  INDEX `idx_contacts_contact_id` (`contact_user_id`),
  CONSTRAINT `fk_contacts_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_contacts_target` FOREIGN KEY (`contact_user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Blocked Users Table
CREATE TABLE IF NOT EXISTS `blocked_users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` VARCHAR(64) NOT NULL,
  `blocked_user_id` VARCHAR(64) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_user_block` (`user_id`, `blocked_user_id`),
  INDEX `idx_block_user_id` (`user_id`),
  CONSTRAINT `fk_block_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_block_target` FOREIGN KEY (`blocked_user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Conversations Table (1-to-1 & Groups)
CREATE TABLE IF NOT EXISTS `conversations` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `conversation_id` VARCHAR(64) NOT NULL UNIQUE,
  `type` ENUM('direct', 'group') DEFAULT 'direct',
  `title` VARCHAR(128) DEFAULT NULL,
  `group_description` TEXT DEFAULT NULL,
  `group_photo` VARCHAR(255) DEFAULT NULL,
  `created_by` VARCHAR(64) NOT NULL,
  `last_message_text` TEXT DEFAULT NULL,
  `last_message_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_conv_id` (`conversation_id`),
  INDEX `idx_conv_last_time` (`last_message_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Conversation Members Table
CREATE TABLE IF NOT EXISTS `conversation_members` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `conversation_id` VARCHAR(64) NOT NULL,
  `user_id` VARCHAR(64) NOT NULL,
  `role` ENUM('member', 'admin', 'creator') DEFAULT 'member',
  `last_read_message_id` BIGINT UNSIGNED DEFAULT 0,
  `is_muted` TINYINT(1) DEFAULT 0,
  `is_archived` TINYINT(1) DEFAULT 0,
  `joined_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `left_at` DATETIME DEFAULT NULL,
  UNIQUE KEY `uniq_conv_user` (`conversation_id`, `user_id`),
  INDEX `idx_cm_user_id` (`user_id`),
  INDEX `idx_cm_conv_id` (`conversation_id`),
  CONSTRAINT `fk_cm_conv` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`conversation_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_cm_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Messages Table
CREATE TABLE IF NOT EXISTS `messages` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `message_id` VARCHAR(64) NOT NULL UNIQUE,
  `client_message_id` VARCHAR(64) NOT NULL,
  `conversation_id` VARCHAR(64) NOT NULL,
  `sender_id` VARCHAR(64) NOT NULL,
  `message_type` ENUM('TEXT', 'IMAGE', 'VIDEO', 'AUDIO', 'VOICE_NOTE', 'DOCUMENT', 'LOCATION', 'CONTACT', 'SYSTEM') DEFAULT 'TEXT',
  `message_text` TEXT DEFAULT NULL,
  `media_url` VARCHAR(255) DEFAULT NULL,
  `file_name` VARCHAR(255) DEFAULT NULL,
  `file_size` BIGINT UNSIGNED DEFAULT 0,
  `duration_seconds` INT UNSIGNED DEFAULT 0,
  `reply_to_message_id` VARCHAR(64) DEFAULT NULL,
  `reply_preview_text` VARCHAR(255) DEFAULT NULL,
  `reply_preview_sender` VARCHAR(128) DEFAULT NULL,
  `status` ENUM('sending', 'sent', 'delivered', 'read') DEFAULT 'sent',
  `deleted_for_everyone` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` DATETIME DEFAULT NULL,
  INDEX `idx_msg_conv_id` (`conversation_id`),
  INDEX `idx_msg_client_id` (`client_message_id`),
  INDEX `idx_msg_sender_id` (`sender_id`),
  INDEX `idx_msg_created_at` (`created_at`),
  CONSTRAINT `fk_msg_conv` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`conversation_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_msg_sender` FOREIGN KEY (`sender_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Message Status per Recipient Table
CREATE TABLE IF NOT EXISTS `message_status` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `message_id` VARCHAR(64) NOT NULL,
  `user_id` VARCHAR(64) NOT NULL,
  `status` ENUM('sent', 'delivered', 'read') DEFAULT 'sent',
  `delivered_at` DATETIME DEFAULT NULL,
  `read_at` DATETIME DEFAULT NULL,
  `deleted_for_me` TINYINT(1) DEFAULT 0,
  UNIQUE KEY `uniq_msg_user` (`message_id`, `user_id`),
  INDEX `idx_ms_user_id` (`user_id`),
  INDEX `idx_ms_status` (`status`),
  CONSTRAINT `fk_ms_msg` FOREIGN KEY (`message_id`) REFERENCES `messages` (`message_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_ms_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10b. Message Reactions Table
CREATE TABLE IF NOT EXISTS `message_reactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `message_id` VARCHAR(64) NOT NULL,
  `user_id` VARCHAR(64) NOT NULL,
  `emoji` VARCHAR(32) NOT NULL,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_msg_user_reaction` (`message_id`, `user_id`),
  INDEX `idx_mr_message_id` (`message_id`),
  INDEX `idx_mr_user_id` (`user_id`),
  CONSTRAINT `fk_mr_msg` FOREIGN KEY (`message_id`) REFERENCES `messages` (`message_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_mr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Statuses / Stories Table (24h lifespan)
CREATE TABLE IF NOT EXISTS `statuses` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `status_id` VARCHAR(64) NOT NULL UNIQUE,
  `user_id` VARCHAR(64) NOT NULL,
  `content_type` ENUM('text', 'image', 'video') DEFAULT 'text',
  `content` TEXT DEFAULT NULL,
  `media_url` VARCHAR(255) DEFAULT NULL,
  `background_color` VARCHAR(16) DEFAULT '#0F172A',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `expires_at` DATETIME NOT NULL,
  INDEX `idx_status_user_id` (`user_id`),
  INDEX `idx_status_expires` (`expires_at`),
  CONSTRAINT `fk_status_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 12. Status Views Table
CREATE TABLE IF NOT EXISTS `status_views` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `status_id` VARCHAR(64) NOT NULL,
  `viewer_user_id` VARCHAR(64) NOT NULL,
  `viewed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_status_viewer` (`status_id`, `viewer_user_id`),
  INDEX `idx_sv_status_id` (`status_id`),
  CONSTRAINT `fk_sv_status` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`status_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_sv_viewer` FOREIGN KEY (`viewer_user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 13. Calls Table
CREATE TABLE IF NOT EXISTS `calls` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `call_id` VARCHAR(64) NOT NULL UNIQUE,
  `caller_id` VARCHAR(64) NOT NULL,
  `receiver_id` VARCHAR(64) NOT NULL,
  `call_type` ENUM('audio', 'video') DEFAULT 'audio',
  `status` ENUM('calling', 'ringing', 'accepted', 'rejected', 'missed', 'ended') DEFAULT 'calling',
  `started_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `ended_at` DATETIME DEFAULT NULL,
  `duration_seconds` INT UNSIGNED DEFAULT 0,
  INDEX `idx_calls_caller` (`caller_id`),
  INDEX `idx_calls_receiver` (`receiver_id`),
  CONSTRAINT `fk_call_caller` FOREIGN KEY (`caller_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_call_receiver` FOREIGN KEY (`receiver_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 14. Notifications Table
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` VARCHAR(64) NOT NULL,
  `sender_id` VARCHAR(64) NOT NULL,
  `title` VARCHAR(128) NOT NULL,
  `body` VARCHAR(255) NOT NULL,
  `type` VARCHAR(32) DEFAULT 'chat',
  `reference_id` VARCHAR(64) DEFAULT NULL,
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_notif_user` (`user_id`),
  INDEX `idx_notif_read` (`is_read`),
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 15. Reports Table
CREATE TABLE IF NOT EXISTS `reports` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `report_id` VARCHAR(64) NOT NULL UNIQUE,
  `reporter_id` VARCHAR(64) NOT NULL,
  `reported_user_id` VARCHAR(64) DEFAULT NULL,
  `message_id` VARCHAR(64) DEFAULT NULL,
  `group_id` VARCHAR(64) DEFAULT NULL,
  `status_id` VARCHAR(64) DEFAULT NULL,
  `reason` VARCHAR(128) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `status` ENUM('pending', 'reviewed', 'resolved', 'dismissed') DEFAULT 'pending',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_rep_reporter` (`reporter_id`),
  INDEX `idx_rep_status` (`status`),
  CONSTRAINT `fk_rep_reporter` FOREIGN KEY (`reporter_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 16. Typing Status Table (Ephemeral typing indicator tracking)
CREATE TABLE IF NOT EXISTS `typing_status` (
  `conversation_id` VARCHAR(64) NOT NULL,
  `user_id` VARCHAR(64) NOT NULL,
  `is_typing` TINYINT(1) DEFAULT 1,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`conversation_id`, `user_id`),
  INDEX `idx_typing_conv` (`conversation_id`),
  INDEX `idx_typing_updated` (`updated_at`),
  CONSTRAINT `fk_typing_conv` FOREIGN KEY (`conversation_id`) REFERENCES `conversations` (`conversation_id`) ON DELETE CASCADE,
  CONSTRAINT `fk_typing_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;