CREATE TABLE forward_definitions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    creator_user_id INTEGER NOT NULL,
    source_chat_id INTEGER NOT NULL,
    destination_chat_id INTEGER NOT NULL,
    review_group_id INTEGER NOT NULL,
    forward_message TEXT NOT NULL DEFAULT '',
    code TEXT NOT NULL UNIQUE,
    share_mode TEXT NOT NULL DEFAULT 'b',
    revoked_at TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX idx_forward_code ON forward_definitions(code);
CREATE INDEX idx_forward_source ON forward_definitions(source_chat_id);

CREATE TABLE forward_submissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    forward_id INTEGER NOT NULL REFERENCES forward_definitions(id),
    user_id INTEGER NOT NULL,
    content_id TEXT NOT NULL REFERENCES contents(id),
    status TEXT NOT NULL DEFAULT 'pending',
    review_message_id INTEGER,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    resolved_at TEXT,
    resolver_id INTEGER
);
CREATE INDEX idx_fwd_sub_forward ON forward_submissions(forward_id);
CREATE INDEX idx_fwd_sub_user ON forward_submissions(user_id);
CREATE INDEX idx_fwd_sub_status ON forward_submissions(status);

CREATE TABLE forward_lists (
    forward_id INTEGER NOT NULL REFERENCES forward_definitions(id),
    user_id INTEGER NOT NULL,
    list_type TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (forward_id, user_id, list_type)
);