CREATE TABLE IF NOT EXISTS advertisements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    image_path VARCHAR(255) NULL,
    text TEXT NOT NULL,
    start_datetime DATETIME NOT NULL,
    end_datetime DATETIME NOT NULL,
    interval_minutes INT NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    last_sent_at DATETIME NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    admin_id BIGINT NOT NULL,
    action_type VARCHAR(50) NOT NULL,
    advertisement_id INT NULL,
    description TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS admin_states (
    admin_id BIGINT PRIMARY KEY,
    state VARCHAR(50) NOT NULL,
    data JSON NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS admins (
    telegram_id BIGINT PRIMARY KEY,
    username VARCHAR(255) NULL,
    first_name VARCHAR(255) NULL,
    last_name VARCHAR(255) NULL,
    added_by BIGINT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS target_chats (
    chat_id BIGINT PRIMARY KEY,
    title VARCHAR(255) NULL,
    type VARCHAR(50) NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    added_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ad_stats (
    ad_id INT,
    chat_id BIGINT,
    post_count INT DEFAULT 0,
    last_posted_at DATETIME,
    PRIMARY KEY (ad_id, chat_id),
    FOREIGN KEY (ad_id) REFERENCES advertisements(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS ad_target_mapping (
    ad_id INT,
    chat_id BIGINT,
    PRIMARY KEY (ad_id, chat_id),
    FOREIGN KEY (ad_id) REFERENCES advertisements(id) ON DELETE CASCADE,
    FOREIGN KEY (chat_id) REFERENCES target_chats(chat_id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS users (
    telegram_id BIGINT PRIMARY KEY,
    first_name VARCHAR(255) NULL,
    last_name VARCHAR(255) NULL,
    username VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT,
    admin_id BIGINT NULL,
    direction ENUM('user_to_admin', 'admin_to_user') NOT NULL,
    message_type ENUM('text', 'photo') NOT NULL,
    content TEXT,
    file_path VARCHAR(255) NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(telegram_id) ON DELETE CASCADE
);
