-- Таблица пользователей
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    phone VARCHAR(50),
    email VARCHAR(255),
    ip VARCHAR(45),
    user_agent TEXT,
    domain VARCHAR(255),
    affiliate_id VARCHAR(50),
    offer_id VARCHAR(50),
    aff_sub VARCHAR(255),
    aff_sub2 VARCHAR(255),
    aff_sub3 VARCHAR(255),
    aff_sub5 VARCHAR(255),
    aff_sub20 VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP,
    push_subscribed BOOLEAN DEFAULT FALSE,
    push_token TEXT,
    geo VARCHAR(100),
    utm_source VARCHAR(255),
    utm_medium VARCHAR(255),
    utm_campaign VARCHAR(255)
);

-- Таблица групп для сегментации
CREATE TABLE push_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица для связи пользователей с группами
CREATE TABLE user_groups (
    user_id INT,
    group_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (group_id) REFERENCES push_groups(id),
    PRIMARY KEY (user_id, group_id)
);

-- Таблица шаблонов пушей
CREATE TABLE push_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    message TEXT,
    image_url VARCHAR(255),
    link_url VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
);

-- Таблица отправленных пушей
CREATE TABLE push_campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    template_id INT,
    group_id INT,
    name VARCHAR(255),
    status ENUM('draft', 'scheduled', 'sending', 'completed', 'failed'),
    scheduled_time TIMESTAMP,
    sent_at TIMESTAMP,
    total_recipients INT,
    successful_sends INT,
    failed_sends INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (template_id) REFERENCES push_templates(id),
    FOREIGN KEY (group_id) REFERENCES push_groups(id)
);

-- Таблица для хранения статистики пушей
CREATE TABLE push_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    campaign_id INT,
    user_id INT,
    status ENUM('sent', 'delivered', 'clicked', 'failed'),
    clicked_at TIMESTAMP,
    FOREIGN KEY (campaign_id) REFERENCES push_campaigns(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Таблица для хранения фильтров сегментации
CREATE TABLE segmentation_filters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    group_id INT,
    filter_type ENUM('geo', 'affiliate', 'offer', 'sub', 'date', 'activity'),
    filter_value TEXT,
    operator ENUM('equals', 'contains', 'starts_with', 'ends_with', 'greater_than', 'less_than'),
    FOREIGN KEY (group_id) REFERENCES push_groups(id)
); 