-- WhatsApp Business Database Schema
-- Create these tables to store webhook data and messages

-- Messages table - stores incoming and outgoing messages
CREATE TABLE IF NOT EXISTS whatsapp_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message_id VARCHAR(255) UNIQUE NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    contact_name VARCHAR(100),
    message_type ENUM('text', 'image', 'audio', 'video', 'document', 'location', 'template') NOT NULL,
    message_body LONGTEXT,
    media_id VARCHAR(255),
    media_type VARCHAR(50),
    media_filename VARCHAR(255),
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),
    is_incoming BOOLEAN DEFAULT TRUE,
    status ENUM('sent', 'delivered', 'read', 'failed') DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone_number (phone_number),
    INDEX idx_created_at (created_at),
    INDEX idx_message_id (message_id)
);

-- Contacts table - store WhatsApp user info
CREATE TABLE IF NOT EXISTS whatsapp_contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    wa_id VARCHAR(20) UNIQUE NOT NULL,
    contact_name VARCHAR(100),
    profile_picture_url VARCHAR(500),
    last_message_date TIMESTAMP,
    message_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_phone_number (phone_number),
    INDEX idx_wa_id (wa_id)
);

-- Message statuses table - track message delivery/read status
CREATE TABLE IF NOT EXISTS whatsapp_message_statuses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    message_id VARCHAR(255) NOT NULL,
    phone_number_id VARCHAR(50),
    recipient_id VARCHAR(20) NOT NULL,
    status ENUM('sent', 'delivered', 'read', 'failed') NOT NULL,
    error_code INT,
    error_message VARCHAR(255),
    status_timestamp BIGINT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (message_id) REFERENCES whatsapp_messages(message_id),
    INDEX idx_message_id (message_id),
    INDEX idx_status (status)
);

-- Templates table - manage message templates
CREATE TABLE IF NOT EXISTS whatsapp_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    template_name VARCHAR(255) UNIQUE NOT NULL,
    category ENUM('marketing', 'authentication', 'transactional', 'utility') NOT NULL,
    language VARCHAR(10) DEFAULT 'en',
    template_body LONGTEXT NOT NULL,
    header_text VARCHAR(255),
    footer_text VARCHAR(255),
    button_count INT DEFAULT 0,
    quality_score ENUM('unknown', 'low', 'medium', 'high'),
    status ENUM('approved', 'rejected', 'pending', 'disabled') DEFAULT 'pending',
    rejection_reason VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status (status),
    INDEX idx_quality (quality_score)
);

-- Account alerts table - track account notifications
CREATE TABLE IF NOT EXISTS whatsapp_account_alerts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone_number_id VARCHAR(50) NOT NULL,
    alert_type VARCHAR(100) NOT NULL,
    alert_details JSON,
    severity ENUM('info', 'warning', 'critical') DEFAULT 'info',
    is_resolved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    INDEX idx_phone_number_id (phone_number_id),
    INDEX idx_alert_type (alert_type),
    INDEX idx_created_at (created_at)
);

-- Webhook logs table - for debugging
CREATE TABLE IF NOT EXISTS whatsapp_webhook_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    webhook_type VARCHAR(100) NOT NULL,
    payload JSON,
    processing_status ENUM('success', 'error', 'pending') DEFAULT 'pending',
    error_message VARCHAR(500),
    processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_webhook_type (webhook_type),
    INDEX idx_processed_at (processed_at)
);

-- Conversation history table - group related messages
CREATE TABLE IF NOT EXISTS whatsapp_conversations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    phone_number VARCHAR(20) NOT NULL,
    last_message_id VARCHAR(255),
    last_message_body TEXT,
    last_message_at TIMESTAMP,
    message_count INT DEFAULT 0,
    conversation_status ENUM('active', 'archived', 'closed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (phone_number) REFERENCES whatsapp_contacts(phone_number),
    INDEX idx_phone_number (phone_number),
    INDEX idx_last_message_at (last_message_at)
);

-- Sample queries for common operations:

-- Get all messages from a contact
-- SELECT * FROM whatsapp_messages WHERE phone_number = '16505551234' ORDER BY created_at DESC;

-- Get unread messages
-- SELECT * FROM whatsapp_messages WHERE is_incoming = TRUE AND status != 'read' ORDER BY created_at DESC;

-- Get message delivery stats
-- SELECT status, COUNT(*) as count FROM whatsapp_message_statuses GROUP BY status;

-- Get active conversations
-- SELECT * FROM whatsapp_conversations WHERE conversation_status = 'active' ORDER BY last_message_at DESC;

-- Get pending templates
-- SELECT * FROM whatsapp_templates WHERE status = 'pending';

-- Get account alerts
-- SELECT * FROM whatsapp_account_alerts WHERE is_resolved = FALSE ORDER BY created_at DESC;