-- 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;

-- Users table for authentication
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
   username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_username (username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- SMS Providers table to store provider configurations
CREATE TABLE IF NOT EXISTS sms_providers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    api_url TEXT NOT NULL,
    api_key VARCHAR(255),
    sender_id VARCHAR(20),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name),
    INDEX idx_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 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;

-- Insert default admin user(password: admin123)
-- The password hash is for 'admin123' using bcrypt with salt rounds = 10
INSERT INTO users (username, password, role) VALUES 
('admin', '$2a$12$4nIm/fR7sOlO95knlJNwfu9/T4uSJ0RJlHKw.JsdfOQPajZyZ0mOm', 'admin');

-- Insert sample SMS provider(B NET BD as per requirements)
INSERT INTO sms_providers (name, api_url, api_key, sender_id, is_active) VALUES 
('B NET BD', 
 'https://sms.maestro.com.bd/api/smsapi?api_key=051145568f2344409656ba7330ab52ed&type=text&contacts=#contact#&senderid=8809648904110&msg=#smstext#', 
 '051145568f2344409656ba7330ab52ed', 
 '8809648904110', 
 TRUE);

-- Optional: Create a view for easy reporting
CREATE OR REPLACE VIEW v_user_sms_summary AS
SELECT 
   u.id AS user_id,
   u.username,
    COUNT(sl.id) AS total_sms_sent,
    SUM(CASE WHEN sl.message_type = 'masked' THEN 1 ELSE 0 END) AS masked_sms_count,
    SUM(CASE WHEN sl.message_type = 'unmarked' THEN 1 ELSE 0 END) AS unmarked_sms_count,
    SUM(CASE WHEN sl.status = 'SUCCESS' THEN 1 ELSE 0 END) AS successful_sms,
    SUM(CASE WHEN sl.status = 'FAILED' THEN 1 ELSE 0 END) AS failed_sms,
    COALESCE(SUM(sl.cost), 0) AS total_cost
FROM users u
LEFT JOIN sms_logs sl ON u.id = sl.user_id
GROUP BY u.id, u.username;

-- 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);