-- BlackMural Bulk SMS Platform Database Structure
-- Multi-tenant SMS platform with SuperAdmin > Admins > Clients hierarchy

CREATE DATABASE IF NOT EXISTS blackmural_bulk_sms;
USE blackmural_bulk_sms;

-- =============================================
-- CORE USER MANAGEMENT TABLES
-- =============================================

-- Super Admin table (top level)
CREATE TABLE super_admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Admins table (created by super admin)
CREATE TABLE admins (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    company_name VARCHAR(150),
    phone VARCHAR(20),
    address TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES super_admins(id) ON DELETE RESTRICT
);

-- Clients table (created by admins)
CREATE TABLE clients (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    company_name VARCHAR(150),
    phone VARCHAR(20),
    address TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_username_per_admin (admin_id, username),
    UNIQUE KEY unique_client_email_per_admin (admin_id, email)
);

-- Client Users table (users created by clients to send SMS on their behalf)
CREATE TABLE client_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    role ENUM('admin', 'user', 'viewer') DEFAULT 'user',
    permissions JSON, -- Store specific permissions as JSON
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    UNIQUE KEY unique_username_per_client (client_id, username),
    UNIQUE KEY unique_email_per_client (client_id, email)
);

-- =============================================
-- SMS ROUTING TABLES
-- =============================================

-- Sender IDs (alphanumeric, created by admins)
CREATE TABLE sender_ids (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    sender_id VARCHAR(11) NOT NULL, -- Max 11 characters for alphanumeric sender ID
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    UNIQUE KEY unique_sender_id_per_admin (admin_id, sender_id)
);

-- Short Codes (numeric, created by admins)
CREATE TABLE short_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    short_code VARCHAR(10) NOT NULL, -- Numeric short codes
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE,
    UNIQUE KEY unique_short_code_per_admin (admin_id, short_code)
);

-- Client Sender ID assignments (which sender IDs can each client use)
CREATE TABLE client_sender_ids (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    sender_id_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (sender_id_id) REFERENCES sender_ids(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_sender_assignment (client_id, sender_id_id)
);

-- Client Short Code assignments (which short codes can each client use)
CREATE TABLE client_short_codes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    short_code_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (short_code_id) REFERENCES short_codes(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_shortcode_assignment (client_id, short_code_id)
);

-- =============================================
-- CONTACT MANAGEMENT TABLES
-- =============================================

-- Contact Groups (created by clients)
CREATE TABLE contact_groups (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    group_name VARCHAR(100) NOT NULL,
    description TEXT,
    total_contacts INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL, -- client_users.id
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES client_users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_group_name_per_client (client_id, group_name)
);

-- Contacts (MSISDNs)
CREATE TABLE contacts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    msisdn VARCHAR(15) NOT NULL, -- International format without +
    name VARCHAR(100),
    email VARCHAR(100),
    custom_fields JSON, -- Store additional custom fields as JSON
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL, -- client_users.id
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES client_users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_msisdn_per_client (client_id, msisdn),
    INDEX idx_msisdn (msisdn),
    INDEX idx_client_active (client_id, is_active)
);

-- Contact Group Members (many-to-many relationship)
CREATE TABLE contact_group_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    group_id INT NOT NULL,
    contact_id INT NOT NULL,
    added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES contact_groups(id) ON DELETE CASCADE,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    UNIQUE KEY unique_contact_in_group (group_id, contact_id)
);

-- =============================================
-- SMS TEMPLATES AND CAMPAIGNS
-- =============================================

-- SMS Templates (created by clients)
CREATE TABLE sms_templates (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    template_name VARCHAR(100) NOT NULL,
    template_content TEXT NOT NULL,
    variables JSON, -- Store template variables as JSON array
    category VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_by INT NOT NULL, -- client_users.id
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES client_users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_template_name_per_client (client_id, template_name)
);

-- SMS Campaigns
CREATE TABLE sms_campaigns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    campaign_name VARCHAR(100) NOT NULL,
    group_id INT NOT NULL,
    template_id INT,
    message_content TEXT NOT NULL,
    sender_type ENUM('sender_id', 'short_code') NOT NULL,
    sender_id_id INT NULL,
    short_code_id INT NULL,
    total_recipients INT DEFAULT 0,
    status ENUM('draft', 'scheduled', 'sending', 'completed', 'failed', 'cancelled') DEFAULT 'draft',
    scheduled_at TIMESTAMP NULL,
    started_at TIMESTAMP NULL,
    completed_at TIMESTAMP NULL,
    created_by INT NOT NULL, -- client_users.id
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES contact_groups(id) ON DELETE RESTRICT,
    FOREIGN KEY (template_id) REFERENCES sms_templates(id) ON DELETE SET NULL,
    FOREIGN KEY (sender_id_id) REFERENCES sender_ids(id) ON DELETE RESTRICT,
    FOREIGN KEY (short_code_id) REFERENCES short_codes(id) ON DELETE RESTRICT,
    FOREIGN KEY (created_by) REFERENCES client_users(id) ON DELETE RESTRICT,
    UNIQUE KEY unique_campaign_name_per_client (client_id, campaign_name),
    CHECK ((sender_type = 'sender_id' AND sender_id_id IS NOT NULL AND short_code_id IS NULL) OR 
           (sender_type = 'short_code' AND short_code_id IS NOT NULL AND sender_id_id IS NULL))
);

-- =============================================
-- API INTEGRATION TABLES
-- =============================================

-- Packages (for packageId in API)
CREATE TABLE packages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    admin_id INT NOT NULL,
    package_name VARCHAR(100) NOT NULL,
    description TEXT,
    credits_per_sms DECIMAL(10,4) DEFAULT 1.0000,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE CASCADE
);

-- Client Package assignments
CREATE TABLE client_packages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    package_id INT NOT NULL,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE CASCADE,
    UNIQUE KEY unique_client_package_assignment (client_id, package_id)
);

-- API Requests (to track all incoming API requests)
CREATE TABLE api_requests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    timestamp VARCHAR(50) NOT NULL, -- From API request
    unique_id VARCHAR(100) NOT NULL, -- uniqueId from API
    message TEXT NOT NULL,
    oa VARCHAR(11) NOT NULL, -- Originating Address/Sender Name
    msisdn VARCHAR(15) NOT NULL,
    channel VARCHAR(10) DEFAULT 'SMS',
    package_id INT NULL,
    username VARCHAR(50) NOT NULL,
    action_response_url TEXT NULL,
    cp_id VARCHAR(50) NULL, -- Partner ID
    request_data JSON, -- Store full request data
    status_code VARCHAR(10) DEFAULT 'SC0000',
    status_description VARCHAR(100) DEFAULT 'SUCCESS',
    keyword VARCHAR(20) DEFAULT 'BULK',
    processed BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (package_id) REFERENCES packages(id) ON DELETE SET NULL,
    UNIQUE KEY unique_request_id_per_client (client_id, unique_id),
    INDEX idx_timestamp (timestamp),
    INDEX idx_status_code (status_code),
    INDEX idx_processed (processed)
);

-- API Response Log (to track responses sent back)
CREATE TABLE api_responses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    api_request_id INT NOT NULL,
    keyword VARCHAR(20) NOT NULL,
    status VARCHAR(100) NOT NULL,
    status_code VARCHAR(10) NOT NULL,
    response_data JSON,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (api_request_id) REFERENCES api_requests(id) ON DELETE CASCADE
);

-- Webhook Callbacks (for actionResponseURL)
CREATE TABLE webhook_callbacks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    api_request_id INT NOT NULL,
    callback_url TEXT NOT NULL,
    payload JSON NOT NULL,
    response_code INT NULL,
    response_body TEXT NULL,
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    status VARCHAR(20) DEFAULT 'pending', -- pending, success, failed
    next_attempt_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (api_request_id) REFERENCES api_requests(id) ON DELETE CASCADE,
    INDEX idx_status_next_attempt (status, next_attempt_at)
);

-- =============================================
-- SMS DELIVERY TRACKING
-- =============================================

-- SMS Messages (individual messages sent)
CREATE TABLE sms_messages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    campaign_id INT NULL,
    contact_id INT NOT NULL,
    msisdn VARCHAR(15) NOT NULL,
    message_content TEXT NOT NULL,
    sender_value VARCHAR(11) NOT NULL, -- The actual sender ID or short code used
    message_id VARCHAR(100), -- External SMS gateway message ID
    status VARCHAR(50) DEFAULT 'pending',
    error_message TEXT,
    cost DECIMAL(10,4) DEFAULT 0.0000,
    sent_at TIMESTAMP NULL,
    delivered_at TIMESTAMP NULL,
    created_by INT NOT NULL, -- client_users.id
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES sms_campaigns(id) ON DELETE SET NULL,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES client_users(id) ON DELETE RESTRICT,
    INDEX idx_campaign_status (campaign_id, status),
    INDEX idx_msisdn_status (msisdn, status),
    INDEX idx_sent_at (sent_at)
);

-- =============================================
-- BULK UPLOAD TRACKING
-- =============================================

-- Bulk Upload Jobs (for CSV imports)
CREATE TABLE bulk_upload_jobs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    group_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    total_rows INT DEFAULT 0,
    processed_rows INT DEFAULT 0,
    successful_imports INT DEFAULT 0,
    failed_imports INT DEFAULT 0,
    status VARCHAR(50) DEFAULT 'pending',
    error_log TEXT,
    created_by INT NOT NULL, -- client_users.id
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id) REFERENCES contact_groups(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES client_users(id) ON DELETE RESTRICT
);

-- =============================================
-- SYSTEM CONFIGURATION
-- =============================================

-- System Settings
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Audit Log
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_type ENUM('super_admin', 'admin', 'client', 'client_user') NOT NULL,
    user_id INT NOT NULL,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    old_values JSON,
    new_values JSON,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_type_id (user_type, user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- =============================================
-- TRIGGERS FOR MAINTAINING COUNTS
-- =============================================

-- Trigger to update contact group total_contacts when contacts are added/removed
DELIMITER //

CREATE TRIGGER update_group_count_after_insert
AFTER INSERT ON contact_group_members
FOR EACH ROW
BEGIN
    UPDATE contact_groups 
    SET total_contacts = (
        SELECT COUNT(*) 
        FROM contact_group_members cgm 
        JOIN contacts c ON cgm.contact_id = c.id 
        WHERE cgm.group_id = NEW.group_id AND c.is_active = TRUE
    )
    WHERE id = NEW.group_id;
END//

CREATE TRIGGER update_group_count_after_delete
AFTER DELETE ON contact_group_members
FOR EACH ROW
BEGIN
    UPDATE contact_groups 
    SET total_contacts = (
        SELECT COUNT(*) 
        FROM contact_group_members cgm 
        JOIN contacts c ON cgm.contact_id = c.id 
        WHERE cgm.group_id = OLD.group_id AND c.is_active = TRUE
    )
    WHERE id = OLD.group_id;
END//

-- Trigger to update campaign total_recipients
CREATE TRIGGER update_campaign_recipients_after_insert
AFTER INSERT ON sms_campaigns
FOR EACH ROW
BEGIN
    UPDATE sms_campaigns 
    SET total_recipients = (
        SELECT COUNT(*) 
        FROM contact_group_members cgm 
        JOIN contacts c ON cgm.contact_id = c.id 
        WHERE cgm.group_id = NEW.group_id AND c.is_active = TRUE
    )
    WHERE id = NEW.id;
END//

DELIMITER ;

-- =============================================
-- USEFUL VIEWS
-- =============================================

-- View for client dashboard statistics
CREATE VIEW client_dashboard_stats AS
SELECT 
    c.id as client_id,
    c.name as client_name,
    COUNT(DISTINCT cu.id) as total_users,
    COUNT(DISTINCT cg.id) as total_groups,
    COUNT(DISTINCT cont.id) as total_contacts,
    COUNT(DISTINCT st.id) as total_templates,
    COUNT(DISTINCT sc.id) as total_campaigns,
    COUNT(DISTINCT CASE WHEN sm.status = 'sent' THEN sm.id END) as total_sent_messages,
    COUNT(DISTINCT CASE WHEN sm.status = 'delivered' THEN sm.id END) as total_delivered_messages
FROM clients c
LEFT JOIN client_users cu ON c.id = cu.client_id AND cu.is_active = TRUE
LEFT JOIN contact_groups cg ON c.id = cg.client_id AND cg.is_active = TRUE
LEFT JOIN contacts cont ON c.id = cont.client_id AND cont.is_active = TRUE
LEFT JOIN sms_templates st ON c.id = st.client_id AND st.is_active = TRUE
LEFT JOIN sms_campaigns sc ON c.id = sc.client_id
LEFT JOIN sms_messages sm ON sc.id = sm.campaign_id
WHERE c.is_active = TRUE
GROUP BY c.id, c.name;

-- View for campaign performance
CREATE VIEW campaign_performance AS
SELECT 
    sc.id as campaign_id,
    sc.campaign_name,
    sc.status as campaign_status,
    sc.total_recipients,
    COUNT(sm.id) as total_messages,
    COUNT(CASE WHEN sm.status = 'sent' THEN 1 END) as sent_count,
    COUNT(CASE WHEN sm.status = 'delivered' THEN 1 END) as delivered_count,
    COUNT(CASE WHEN sm.status = 'failed' THEN 1 END) as failed_count,
    ROUND((COUNT(CASE WHEN sm.status = 'delivered' THEN 1 END) / COUNT(sm.id)) * 100, 2) as delivery_rate,
    SUM(sm.cost) as total_cost,
    sc.created_at,
    sc.started_at,
    sc.completed_at
FROM sms_campaigns sc
LEFT JOIN sms_messages sm ON sc.id = sm.campaign_id
GROUP BY sc.id, sc.campaign_name, sc.status, sc.total_recipients, sc.created_at, sc.started_at, sc.completed_at;

-- =============================================
-- INDEXES FOR PERFORMANCE
-- =============================================

-- Additional indexes for better performance
CREATE INDEX idx_contacts_client_active ON contacts(client_id, is_active);
CREATE INDEX idx_campaigns_client_status ON sms_campaigns(client_id, status);
CREATE INDEX idx_messages_campaign_status ON sms_messages(campaign_id, status);
CREATE INDEX idx_audit_logs_user ON audit_logs(user_type, user_id, created_at);
CREATE INDEX idx_bulk_jobs_client_status ON bulk_upload_jobs(client_id, status);

-- API-related indexes
CREATE INDEX idx_api_requests_client_processed ON api_requests(client_id, processed);
CREATE INDEX idx_api_requests_username ON api_requests(username);
CREATE INDEX idx_api_responses_request_id ON api_responses(api_request_id);
CREATE INDEX idx_webhook_callbacks_status ON webhook_callbacks(status);
CREATE INDEX idx_packages_admin_active ON packages(admin_id, is_active);
CREATE INDEX idx_client_packages_client ON client_packages(client_id);

-- =============================================
-- COMMENTS AND DOCUMENTATION
-- =============================================

/*
DATABASE STRUCTURE OVERVIEW:

1. USER HIERARCHY:
   - super_admins: Top-level system administrators (username, email, password, full_name)
   - admins: Created by super admins, manage sender IDs/short codes (name, email, password)
   - clients: Created by admins, send bulk SMS (name, username, email, password)
   - client_users: Created by clients, can send SMS on client's behalf (username, email, password, full_name)

2. SMS ROUTING:
   - sender_ids: Alphanumeric sender IDs (managed by admins)
   - short_codes: Numeric short codes (managed by admins)
   - client_sender_ids/client_short_codes: Assignment tables

3. CONTACT MANAGEMENT:
   - contact_groups: Groups of contacts for campaigns
   - contacts: Individual MSISDNs with optional metadata
   - contact_group_members: Many-to-many relationship

4. MESSAGING:
   - sms_templates: Reusable message templates
   - sms_campaigns: Bulk SMS campaigns
   - sms_messages: Individual SMS tracking

5. SYSTEM:
   - bulk_upload_jobs: CSV import tracking
   - system_settings: Configuration
   - audit_logs: Activity tracking

KEY FEATURES:
- Multi-tenant architecture with proper isolation
- Simplified user fields (no duplicate admin/username fields)
- Username only required for clients and client_users
- Flexible sender ID/short code assignment
- Contact group management with bulk import
- Template system for reusable messages
- Campaign scheduling and tracking
- Comprehensive audit logging
- Performance optimized with proper indexes
- Data integrity with foreign key constraints

FIELD STRUCTURE:
- super_admins: username (unique), email, password_hash, full_name
- admins: name, email (unique), password_hash
- clients: name, username (unique per admin), email, password_hash
- client_users: username (unique per client), email, password_hash, full_name
*/