-- SMS Hub Database Schema
-- Create database for SMS Gateway application

CREATE DATABASE IF NOT EXISTS softshoresa_sms_hub CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE softshoresa_sms_hub;

-- SMS Logs table to track all sent messages
CREATE TABLE IF NOT EXISTS sms_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
   user_id INT NOT NULL,
    sender_id VARCHAR(20) NOT NULL,
    receiver_id VARCHAR(20) NOT NULL,
    sms_body TEXT NOT NULL,
    sms_provider_id INT,
    provider_name VARCHAR(100),
    message_type ENUM('masked', 'unmarked') NOT NULL,
    is_unicode BOOLEAN DEFAULT FALSE,
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    status ENUM('PENDING', 'SUCCESS', 'FAILED') DEFAULT 'PENDING',
    provider_response TEXT,
   cost DECIMAL(10, 4) DEFAULT 0.0000,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (sms_provider_id) REFERENCES sms_providers(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_sent_at (sent_at),
    INDEX idx_status (status),
    INDEX idx_message_type (message_type),
    INDEX idx_provider_name (provider_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Create indexes for better query performance on reports
CREATE INDEX idx_logs_user_status ON sms_logs(user_id, status);
CREATE INDEX idx_logs_user_type ON sms_logs(user_id, message_type);
CREATE INDEX idx_logs_date ON sms_logs(sent_at);
