CREATE TABLE boards (
    id INT AUTO_INCREMENT PRIMARY KEY,
    board_id VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_board_id (board_id)
);

CREATE TABLE threads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    thread_id VARCHAR(6) NOT NULL UNIQUE,
    board_id VARCHAR(10) NOT NULL,
    title VARCHAR(255),
    description TEXT,
    file_name VARCHAR(255),
    file_size INT,
    file_type VARCHAR(10),
    ip_address VARCHAR(45),
    anonymous_id VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_thread_id (thread_id),
    INDEX idx_board_id (board_id),
    INDEX idx_updated_at (updated_at),
    INDEX idx_anonymous_id (anonymous_id),
    FOREIGN KEY (board_id) REFERENCES boards(board_id)
);

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    post_id VARCHAR(6) NOT NULL UNIQUE,
    thread_id VARCHAR(6) NOT NULL,
    board_id VARCHAR(10) NOT NULL,
    message TEXT,
    file_name VARCHAR(255),
    file_size INT,
    file_type VARCHAR(10),
    ip_address VARCHAR(45),
    anonymous_id VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_post_id (post_id),
    INDEX idx_thread_id (thread_id),
    INDEX idx_board_id (board_id),
    INDEX idx_created_at (created_at),
    INDEX idx_anonymous_id (anonymous_id),
    FOREIGN KEY (thread_id) REFERENCES threads(thread_id),
    FOREIGN KEY (board_id) REFERENCES boards(board_id)
);

CREATE TABLE rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip_address VARCHAR(45) NOT NULL,
    action_type VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_action (ip_address, action_type),
    INDEX idx_created_at (created_at)
);

INSERT INTO boards (board_id, name, description) VALUES 
('b', 'Random', 'Случайные темы'),
('mk', 'MK', 'MK темы');