CREATE TABLE IF NOT EXISTS administrators (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(190) NOT NULL UNIQUE, name VARCHAR(150) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('owner','manager','viewer') NOT NULL DEFAULT 'viewer', active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS login_attempts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, fingerprint CHAR(64) NOT NULL, created_at DATETIME NOT NULL, INDEX(fingerprint,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bots (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bale_id BIGINT NOT NULL UNIQUE, title VARCHAR(190) NOT NULL, username VARCHAR(190) NULL,
 token_cipher TEXT NOT NULL, webhook_secret CHAR(64) NOT NULL UNIQUE, welcome_text TEXT NOT NULL, fallback_text TEXT NOT NULL,
 active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL, INDEX(active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS bot_users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bot_id BIGINT UNSIGNED NOT NULL, bale_user_id BIGINT NOT NULL, chat_id BIGINT NOT NULL,
 first_name VARCHAR(190) NOT NULL DEFAULT '', last_name VARCHAR(190) NOT NULL DEFAULT '', username VARCHAR(190) NOT NULL DEFAULT '',
 fields_json JSON NULL, created_at DATETIME NOT NULL, last_seen_at DATETIME NOT NULL,
 UNIQUE KEY uq_bot_bale_user(bot_id,bale_user_id), KEY idx_seen(bot_id,last_seen_at),
 CONSTRAINT fk_users_bot FOREIGN KEY (bot_id) REFERENCES bots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS workflows (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bot_id BIGINT UNSIGNED NOT NULL, title VARCHAR(190) NOT NULL,
 command VARCHAR(50) NOT NULL DEFAULT '/start', draft_command VARCHAR(50) NOT NULL DEFAULT '/start', draft_json JSON NOT NULL, published_version_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL, updated_at DATETIME NOT NULL, INDEX(bot_id,command),
 CONSTRAINT fk_flows_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS workflow_versions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, workflow_id BIGINT UNSIGNED NOT NULL, version_no INT NOT NULL, graph_json JSON NOT NULL, published_at DATETIME NOT NULL,
 UNIQUE KEY uq_flow_version(workflow_id,version_no),
 CONSTRAINT fk_versions_flow FOREIGN KEY(workflow_id) REFERENCES workflows(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS workflow_sessions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bot_id BIGINT UNSIGNED NOT NULL, chat_id BIGINT NOT NULL, user_id BIGINT UNSIGNED NOT NULL,
 workflow_version_id BIGINT UNSIGNED NOT NULL, current_node VARCHAR(64) NULL, state_json JSON NULL, status ENUM('active','done') NOT NULL DEFAULT 'active',
 updated_at DATETIME NOT NULL, UNIQUE KEY uq_bot_chat(bot_id,chat_id),
 CONSTRAINT fk_session_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE,
 CONSTRAINT fk_session_user FOREIGN KEY(user_id) REFERENCES bot_users(id) ON DELETE CASCADE,
 CONSTRAINT fk_session_version FOREIGN KEY(workflow_version_id) REFERENCES workflow_versions(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS inbound_updates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bot_id BIGINT UNSIGNED NOT NULL, update_id BIGINT NOT NULL,
 payload_json JSON NOT NULL, status ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
 attempts TINYINT UNSIGNED NOT NULL DEFAULT 0, last_error VARCHAR(400) NULL, created_at DATETIME NOT NULL,
 UNIQUE KEY uq_bot_update(bot_id,update_id), KEY idx_inbound(status,id),
 CONSTRAINT fk_inbound_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS outbound_jobs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bot_id BIGINT UNSIGNED NOT NULL, chat_id BIGINT NOT NULL,
 method VARCHAR(40) NOT NULL DEFAULT 'sendMessage', payload_json JSON NOT NULL, dedupe_key VARCHAR(190) NOT NULL,
 status ENUM('pending','sending','sent','failed','uncertain') NOT NULL DEFAULT 'pending', attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
 available_at DATETIME NOT NULL, last_error VARCHAR(400) NULL, bale_message_id BIGINT NULL, created_at DATETIME NOT NULL, sent_at DATETIME NULL,
 UNIQUE KEY uq_bot_dedupe(bot_id,dedupe_key), KEY idx_send(status,available_at,id),
 CONSTRAINT fk_outbound_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bot_id BIGINT UNSIGNED NOT NULL, chat_id BIGINT NOT NULL, direction ENUM('in','out') NOT NULL,
 text_body TEXT NOT NULL, created_at DATETIME NOT NULL, KEY idx_messages_chat(bot_id,chat_id,id),
 CONSTRAINT fk_messages_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS form_submissions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, bot_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, workflow_version_id BIGINT UNSIGNED NOT NULL,
 score DECIMAL(12,2) NOT NULL DEFAULT 0, answers_json JSON NOT NULL, submitted_at DATETIME NOT NULL,
 KEY idx_form_bot(bot_id,submitted_at), CONSTRAINT fk_form_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE,
 CONSTRAINT fk_form_user FOREIGN KEY(user_id) REFERENCES bot_users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS audit_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, admin_id BIGINT UNSIGNED NULL, bot_id BIGINT UNSIGNED NULL,
 action VARCHAR(120) NOT NULL, details_json JSON NULL, created_at DATETIME NOT NULL, KEY idx_audit(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS admin_bot_access (
 admin_id BIGINT UNSIGNED NOT NULL, bot_id BIGINT UNSIGNED NOT NULL, role ENUM('viewer','editor','manager') NOT NULL DEFAULT 'viewer',
 PRIMARY KEY(admin_id,bot_id), CONSTRAINT fk_access_admin FOREIGN KEY(admin_id) REFERENCES administrators(id) ON DELETE CASCADE,
 CONSTRAINT fk_access_bot FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;