SELECT TIMESTAMP(sent_date) AS timestamp, COUNT(contacts.id) AS stopped
FROM contacts
JOIN campaigns
ON campaigns.list_id = contacts.list_id
WHERE STOP = 1 AND campaigns.user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

SELECT
TIMESTAMP(sent_date) AS timestamp,
COUNT(sent_messages.id) AS messages,
SUM(IF(sent_messages.status = 1, 1, 0)) AS received,
SUM(IF(sent_messages.status > 1, 1, 0)) AS errored,
SUM(IF(stop = 1, 1, 0)) AS stopped
FROM campaigns
JOIN contacts
ON campaigns.list_id = contacts.list_id
JOIN sent_messages
ON campaigns.id = sent_messages.campaign_id
WHERE user_id = 1 AND sent_date != 0
GROUP BY YEAR(sent_date), MONTH(sent_date)
ORDER BY sent_date DESC LIMIT 12;

CREATE TABLE contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    list_id INT UNSIGNED NOT NULL,
    telephone CHAR(12) NOT NULL,
    firstname VARCHAR(50),
    lastname VARCHAR(50),
    attributes VARCHAR(10000),
    stop BOOLEAN DEFAULT 0,
    PRIMARY KEY (id),
    INDEX contact_ind_list_id (list_id),
    INDEX contact_ind_stop (stop),
    UNIQUE KEY contact_index_telephone_and_list (telephone, list_id),
    CONSTRAINT contact_fk_list
        FOREIGN KEY (list_id)
        REFERENCES lists (id)
        ON DELETE CASCADE
);

CREATE TABLE campaigns (
    id INT UNSIGNED AUTO_INCREMENT,
    user_id INT UNSIGNED NOT NULL,
    list_id INT UNSIGNED,
    name VARCHAR(50),
    text VARCHAR(500),
    send_date TIMESTAMP,
    sent_date TIMESTAMP,
    sender_name VARCHAR(11),
    status TINYINT UNSIGNED DEFAULT 0, -- 0 draft, 1 to be sent, 2 sent
    batch_number SMALLINT UNSIGNED DEFAULT 0,
    PRIMARY KEY (id),
    INDEX campaign_ind_owner (user_id),
    INDEX campaign_ind_push_id (push_id),
    UNIQUE KEY campaign_ind_list_and_name (list_id, name),
    CONSTRAINT campaign_fk_owner
        FOREIGN KEY (user_id)
        REFERENCES users (id)
        ON DELETE CASCADE,
    CONSTRAINT campaign_fk_list
        FOREIGN KEY (list_id)
        REFERENCES lists (id)
        ON DELETE CASCADE
);

CREATE TABLE sent_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT,
    campaign_id INT UNSIGNED NOT NULL,
    telephone CHAR(12) NOT NULL,
    status TINYINT NOT NULL, -- 0 = waiting / 1 = received / 2 = npai / 3 = net_error (error_credits, unknown_error)
    message TINYINT NOT NULL,
    PRIMARY KEY (id),
    INDEX sent_messages_ind_campaign (campaign_id),
    UNIQUE KEY sent_messages_ind_campaign_and_telephone (campaign_id, telephone),
    CONSTRAINT sent_messages_fk_campaign
        FOREIGN KEY (campaign_id)
        REFERENCES campaigns (id)
        ON DELETE CASCADE
);