-- SMS Logları Tablosu
-- SMS gönderme işlemlerini kayıt etmek için

USE milk_collection_db;

CREATE TABLE IF NOT EXISTS sms_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    collection_id INT NOT NULL,
    customer_id INT NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    status ENUM('success', 'failed', 'pending') DEFAULT 'pending',
    api_code VARCHAR(10),
    api_jobid VARCHAR(100),
    api_description VARCHAR(255),
    api_response JSON,
    error_message TEXT,
    http_code INT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (collection_id) REFERENCES collections(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    INDEX idx_collection_id (collection_id),
    INDEX idx_customer_id (customer_id),
    INDEX idx_status (status),
    INDEX idx_sent_at (sent_at)
);

-- Örnek veri (isteğe bağlı)
-- INSERT INTO sms_logs (collection_id, customer_id, phone_number, message, status, api_code, api_jobid, api_description)
-- VALUES (1, 1, '905321234567', '5000 ml sütünüz Sökel Süt tarafından 08.11.2025 tarihinde alınmıştır.', 'success', '00', '17377215342605050417149344', 'queued');
