-- Use this if you already imported the old web-form schema.
-- mysql -u root -p gc_feedback < sql/migrate_telegram.sql

USE gc_feedback;

CREATE TABLE IF NOT EXISTS bot_sessions (
    telegram_user_id BIGINT NOT NULL PRIMARY KEY,
    chat_id BIGINT NOT NULL,
    step VARCHAR(32) NOT NULL,
    data JSON NULL,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

ALTER TABLE submissions
    ADD COLUMN telegram_user_id BIGINT NOT NULL DEFAULT 0 AFTER id,
    ADD COLUMN telegram_chat_id BIGINT NOT NULL DEFAULT 0 AFTER telegram_user_id,
    ADD COLUMN phone VARCHAR(32) NOT NULL DEFAULT '' AFTER app_uid,
    ADD COLUMN media_file_id VARCHAR(255) NULL AFTER message,
    ADD COLUMN media_type ENUM('photo', 'video') NULL AFTER media_file_id;

-- Old R2 columns are no longer used. Drop them if they exist.
ALTER TABLE submissions
    DROP COLUMN file_key,
    DROP COLUMN file_url,
    DROP COLUMN file_type,
    DROP COLUMN ip_address;