CREATE TABLE push_tags (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug VARCHAR(80) NOT NULL,
    name VARCHAR(120) NOT NULL,
    description VARCHAR(255) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_push_tags_slug (slug),
    CONSTRAINT fk_push_tags_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_push_tags_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_subscriptions (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    endpoint LONGTEXT NOT NULL,
    endpoint_hash CHAR(64) NOT NULL,
    public_key VARCHAR(255) NOT NULL,
    auth_token VARCHAR(255) NOT NULL,
    content_encoding VARCHAR(50) NOT NULL DEFAULT 'aes128gcm',
    user_id INT UNSIGNED NULL,
    lead_id INT UNSIGNED NULL,
    lead_reference VARCHAR(120) NULL,
    platform VARCHAR(80) NULL,
    locale VARCHAR(12) NULL,
    user_agent VARCHAR(255) NULL,
    device_label VARCHAR(120) NULL,
    last_seen_at DATETIME NULL,
    last_notification_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_push_subscriptions_endpoint_hash (endpoint_hash),
    INDEX idx_push_subscriptions_user (user_id, is_active),
    INDEX idx_push_subscriptions_lead (lead_id),
    INDEX idx_push_subscriptions_reference (lead_reference),
    CONSTRAINT fk_push_subscriptions_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_push_subscriptions_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_subscription_tag (
    subscription_id BIGINT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (subscription_id, tag_id),
    CONSTRAINT fk_push_subscription_tag_subscription FOREIGN KEY (subscription_id) REFERENCES push_subscriptions (id) ON DELETE CASCADE,
    CONSTRAINT fk_push_subscription_tag_tag FOREIGN KEY (tag_id) REFERENCES push_tags (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_notifications (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(150) NOT NULL,
    body VARCHAR(500) NOT NULL,
    icon_url VARCHAR(255) NULL,
    action_url VARCHAR(255) NULL,
    status ENUM('draft','scheduled','sending','sent','failed') NOT NULL DEFAULT 'draft',
    dispatch_mode ENUM('manual','immediate','scheduled') NOT NULL DEFAULT 'manual',
    tag_mode ENUM('any','all','none') NOT NULL DEFAULT 'any',
    targeting_title VARCHAR(190) NULL,
    scheduled_for DATETIME NULL,
    sent_at DATETIME NULL,
    total_targeted INT UNSIGNED NOT NULL DEFAULT 0,
    total_sent INT UNSIGNED NOT NULL DEFAULT 0,
    total_failed INT UNSIGNED NOT NULL DEFAULT 0,
    created_by INT UNSIGNED NULL,
    updated_by INT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_push_notifications_created_by FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
    CONSTRAINT fk_push_notifications_updated_by FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL,
    INDEX idx_push_notifications_status (status, dispatch_mode, scheduled_for)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_notification_tag (
    notification_id BIGINT UNSIGNED NOT NULL,
    tag_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (notification_id, tag_id),
    CONSTRAINT fk_push_notification_tag_notification FOREIGN KEY (notification_id) REFERENCES push_notifications (id) ON DELETE CASCADE,
    CONSTRAINT fk_push_notification_tag_tag FOREIGN KEY (tag_id) REFERENCES push_tags (id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE push_notification_logs (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    notification_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NOT NULL,
    status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
    response_code VARCHAR(20) NULL,
    response_message VARCHAR(255) NULL,
    dispatched_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_push_notification_logs_notification FOREIGN KEY (notification_id) REFERENCES push_notifications (id) ON DELETE CASCADE,
    CONSTRAINT fk_push_notification_logs_subscription FOREIGN KEY (subscription_id) REFERENCES push_subscriptions (id) ON DELETE CASCADE,
    INDEX idx_push_notification_logs_status (status),
    INDEX idx_push_notification_logs_notification (notification_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;