CREATE DATABASE IF NOT EXISTS telegram_gift_bot CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE telegram_gift_bot;

CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 telegram_id BIGINT NOT NULL UNIQUE,
 username VARCHAR(255) NULL,
 first_name VARCHAR(255) NULL,
 last_name VARCHAR(255) NULL,
 points BIGINT NOT NULL DEFAULT 0,
 rules_version INT NOT NULL DEFAULT 0,
 mandatory_join_verified TINYINT(1) NOT NULL DEFAULT 0,
 is_blocked TINYINT(1) NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 last_activity DATETIME NOT NULL,
 INDEX idx_users_activity(last_activity),
 INDEX idx_users_blocked(is_blocked)
) ENGINE=InnoDB;

CREATE TABLE admins (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 telegram_id BIGINT NOT NULL UNIQUE,
 role ENUM('OWNER','ADMIN','MODERATOR') NOT NULL DEFAULT 'ADMIN',
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE admin_permissions (
 admin_id BIGINT UNSIGNED NOT NULL,
 permission VARCHAR(100) NOT NULL,
 PRIMARY KEY(admin_id, permission),
 CONSTRAINT fk_perm_admin FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE channels (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 chat_id VARCHAR(100) NOT NULL UNIQUE,
 username VARCHAR(255) NULL,
 title VARCHAR(255) NOT NULL,
 invite_link VARCHAR(1024) NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL
) ENGINE=InnoDB;

CREATE TABLE accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 email_ciphertext TEXT NOT NULL,
 email_nonce VARBINARY(32) NOT NULL,
 password_ciphertext TEXT NOT NULL,
 password_nonce VARBINARY(32) NOT NULL,
 fingerprint CHAR(64) NOT NULL UNIQUE,
 status ENUM('ACTIVE','DISABLED') NOT NULL DEFAULT 'ACTIVE',
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX idx_accounts_status(status)
) ENGINE=InnoDB;

CREATE TABLE combos (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 telegram_file_id VARCHAR(255) NULL,
 filename VARCHAR(255) NOT NULL,
 storage_path VARCHAR(1024) NOT NULL,
 size_bytes BIGINT UNSIGNED NOT NULL,
 sha256 CHAR(64) NOT NULL UNIQUE,
 status ENUM('ACTIVE','DISABLED') NOT NULL DEFAULT 'ACTIVE',
 uploaded_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NOT NULL,
 INDEX idx_combos_status(status),
 CONSTRAINT fk_combo_admin FOREIGN KEY(uploaded_by) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE point_transactions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 amount BIGINT NOT NULL,
 type VARCHAR(100) NOT NULL,
 reason VARCHAR(255) NOT NULL,
 reference_id VARCHAR(191) NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_point_ref(user_id,type,reference_id),
 INDEX idx_point_user(user_id,created_at),
 CONSTRAINT fk_point_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE account_deliveries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 account_id BIGINT UNSIGNED NOT NULL,
 points BIGINT NOT NULL,
 created_at DATETIME NOT NULL,
 INDEX idx_account_delivery_user(user_id,created_at),
 CONSTRAINT fk_account_delivery_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_account_delivery_account FOREIGN KEY(account_id) REFERENCES accounts(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE combo_deliveries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 combo_id BIGINT UNSIGNED NOT NULL,
 points BIGINT NOT NULL,
 created_at DATETIME NOT NULL,
 INDEX idx_combo_delivery_user(user_id,created_at),
 CONSTRAINT fk_combo_delivery_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
 CONSTRAINT fk_combo_delivery_combo FOREIGN KEY(combo_id) REFERENCES combos(id) ON DELETE RESTRICT
) ENGINE=InnoDB;

CREATE TABLE deliveries (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 type ENUM('ACCOUNT','COMBO') NOT NULL,
 resource_id BIGINT UNSIGNED NOT NULL,
 points BIGINT NOT NULL,
 status ENUM('CREATED','SENDING','SENT','FAILED','REFUNDED') NOT NULL DEFAULT 'CREATED',
 created_at DATETIME NOT NULL,
 sent_at DATETIME NULL,
 error VARCHAR(500) NULL,
 INDEX idx_delivery_user(user_id,created_at),
 INDEX idx_delivery_status(status),
 CONSTRAINT fk_delivery_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE broadcasts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 created_by BIGINT UNSIGNED NOT NULL,
 type ENUM('TEXT','PHOTO','VIDEO','DOCUMENT') NOT NULL,
 payload_json JSON NOT NULL,
 status ENUM('PENDING','RUNNING','COMPLETED','FAILED','CANCELLED') NOT NULL DEFAULT 'PENDING',
 total INT NOT NULL DEFAULT 0,
 success INT NOT NULL DEFAULT 0,
 failed INT NOT NULL DEFAULT 0,
 blocked INT NOT NULL DEFAULT 0,
 created_at DATETIME NOT NULL,
 completed_at DATETIME NULL,
 CONSTRAINT fk_broadcast_admin FOREIGN KEY(created_by) REFERENCES admins(id)
) ENGINE=InnoDB;

CREATE TABLE broadcast_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 broadcast_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 status ENUM('SUCCESS','FAILED','BLOCKED') NOT NULL,
 error VARCHAR(500) NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_broadcast_user(broadcast_id,user_id),
 CONSTRAINT fk_blog_b FOREIGN KEY(broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
 CONSTRAINT fk_blog_u FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE admin_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 admin_id BIGINT UNSIGNED NULL,
 action VARCHAR(100) NOT NULL,
 target_type VARCHAR(100) NULL,
 target_id VARCHAR(100) NULL,
 metadata_json JSON NULL,
 created_at DATETIME NOT NULL,
 INDEX idx_admin_log_date(created_at),
 CONSTRAINT fk_audit_admin FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE user_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(100) NOT NULL,
 metadata_json JSON NULL,
 created_at DATETIME NOT NULL,
 INDEX idx_user_log_date(created_at),
 CONSTRAINT fk_user_log_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_states (
 user_id BIGINT UNSIGNED PRIMARY KEY,
 flow VARCHAR(100) NOT NULL,
 state VARCHAR(100) NOT NULL,
 payload_json JSON NULL,
 expires_at DATETIME NULL,
 updated_at DATETIME NOT NULL,
 CONSTRAINT fk_state_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE idempotency_keys (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(100) NOT NULL,
 idem_key VARCHAR(191) NOT NULL,
 status ENUM('PROCESSING','COMPLETED','FAILED') NOT NULL,
 result_json JSON NULL,
 created_at DATETIME NOT NULL,
 UNIQUE KEY uq_idem(user_id,action,idem_key),
 CONSTRAINT fk_idem_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE bot_meta (`key` VARCHAR(100) PRIMARY KEY, `value` TEXT NOT NULL);

INSERT INTO bot_meta(`key`,`value`) VALUES ('polling_offset','0')
ON DUPLICATE KEY UPDATE `value`=VALUES(`value`);