-- Users table holds Telegram users interacting with the bot CREATE TABLE IF NOT EXISTS users ( telegram_id BIGINT PRIMARY KEY, telegram_login VARCHAR(32), balance DECIMAL(10,2) NOT NULL DEFAULT 150.00, total_topped_up DECIMAL(10,2) NOT NULL DEFAULT 0.00, registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); -- History of transcription requests made by users CREATE TABLE IF NOT EXISTS transcription_history ( id INTEGER PRIMARY KEY AUTO_INCREMENT, telegram_id BIGINT NOT NULL REFERENCES users(telegram_id), status VARCHAR(32) NOT NULL, audio_s3_path TEXT NOT NULL, result_json TEXT, llm_tokens_by_encoding JSON, duration_seconds INTEGER, price_for_user DECIMAL(10,2), actual_price DECIMAL(10,2), result_s3_path TEXT, provider VARCHAR(16), model VARCHAR(64), operation_id VARCHAR(64), message_id INTEGER, rating INTEGER, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, started_at TIMESTAMP, finished_at TIMESTAMP ); -- Index to speed up lookups by user CREATE INDEX idx_transcription_history_telegram_id ON transcription_history(telegram_id); -- Payments processed via Tinkoff acquiring CREATE TABLE IF NOT EXISTS payments ( id INTEGER PRIMARY KEY AUTO_INCREMENT, telegram_id BIGINT NOT NULL REFERENCES users(telegram_id), message_id INTEGER, order_id VARCHAR(64) NOT NULL UNIQUE, payment_id BIGINT NOT NULL UNIQUE, amount DECIMAL(10,2) NOT NULL, status VARCHAR(32) NOT NULL, payment_url TEXT NOT NULL, description VARCHAR(255) NOT NULL, tinkoff_response TEXT NOT NULL, next_check_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX idx_payments_telegram_id ON payments(telegram_id); -- Trigger to maintain users.total_topped_up automatically. -- Fires after each payment row update; adds amount only when status -- transitions to CONFIRMED to avoid double-counting. CREATE TRIGGER trg_payment_confirmed AFTER UPDATE ON payments FOR EACH ROW BEGIN IF NEW.status = 'CONFIRMED' AND OLD.status != 'CONFIRMED' THEN UPDATE users SET total_topped_up = total_topped_up + NEW.amount WHERE telegram_id = NEW.telegram_id; END IF; END;