CREATE TABLE IF NOT EXISTS ledger_mutex (
  id TINYINT UNSIGNED NOT NULL PRIMARY KEY
) ENGINE=InnoDB;
INSERT IGNORE INTO ledger_mutex (id) VALUES (1);

CREATE TABLE IF NOT EXISTS users (
  telegram_user_id VARCHAR(32) NOT NULL PRIMARY KEY,
  full_name VARCHAR(190) NOT NULL,
  username VARCHAR(190) NOT NULL DEFAULT '-',
  chat_id VARCHAR(32) NOT NULL,
  chat_type VARCHAR(32) NOT NULL DEFAULT 'private',
  chat_title VARCHAR(190) NOT NULL DEFAULT '-',
  first_seen_at DATETIME NOT NULL,
  last_seen_at DATETIME NOT NULL,
  last_command VARCHAR(100) NOT NULL DEFAULT '',
  INDEX idx_users_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
  id VARCHAR(64) NOT NULL PRIMARY KEY,
  created_at DATETIME(6) NOT NULL,
  updated_at DATETIME(6) NULL,
  type ENUM('PENJUALAN','HUTANG','BAYAR') NOT NULL,
  customer_name VARCHAR(190) NOT NULL,
  customer_key VARCHAR(190) NOT NULL,
  amount BIGINT UNSIGNED NOT NULL,
  note TEXT NOT NULL,
  balance_after BIGINT NOT NULL DEFAULT 0,
  status ENUM('TUNAI','BELUM LUNAS','LUNAS') NOT NULL,
  telegram_username VARCHAR(190) NOT NULL DEFAULT '-',
  chat_id VARCHAR(32) NOT NULL DEFAULT '',
  telegram_update_id VARCHAR(64) NULL,
  telegram_user_id VARCHAR(32) NOT NULL DEFAULT '',
  input_name VARCHAR(190) NOT NULL DEFAULT 'Pengguna Telegram',
  chat_type VARCHAR(32) NOT NULL DEFAULT 'web',
  chat_title VARCHAR(190) NOT NULL DEFAULT '-',
  UNIQUE KEY uq_transactions_update (telegram_update_id),
  INDEX idx_transactions_created (created_at, id),
  INDEX idx_transactions_customer (customer_key, created_at, id),
  INDEX idx_transactions_type (type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transaction_audit (
  audit_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  action VARCHAR(32) NOT NULL,
  transaction_id VARCHAR(64) NOT NULL,
  data_before JSON NULL,
  data_after JSON NULL,
  telegram_user_id VARCHAR(32) NOT NULL DEFAULT '',
  actor_name VARCHAR(190) NOT NULL DEFAULT 'Pengguna Web',
  username VARCHAR(190) NOT NULL DEFAULT '-',
  chat_id VARCHAR(32) NOT NULL DEFAULT '',
  INDEX idx_audit_transaction (transaction_id),
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_sessions (
  token_hash CHAR(64) NOT NULL PRIMARY KEY,
  telegram_user_id VARCHAR(32) NOT NULL,
  first_name VARCHAR(190) NOT NULL,
  last_name VARCHAR(190) NOT NULL DEFAULT '',
  username VARCHAR(190) NOT NULL DEFAULT '',
  photo_url TEXT NULL,
  auth_mode ENUM('TELEGRAM_MINI_APP','TELEGRAM_BROWSER') NOT NULL,
  created_at DATETIME NOT NULL,
  last_seen_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  INDEX idx_auth_sessions_user (telegram_user_id),
  INDEX idx_auth_sessions_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_requests (
  auth_id CHAR(32) NOT NULL PRIMARY KEY,
  status ENUM('PENDING','AUTHORIZED') NOT NULL DEFAULT 'PENDING',
  telegram_user_id VARCHAR(32) NULL,
  session_token_hash CHAR(64) NULL,
  session_token_encrypted TEXT NULL,
  created_at DATETIME NOT NULL,
  authorized_at DATETIME NULL,
  expires_at DATETIME NOT NULL,
  INDEX idx_auth_requests_expiry (expires_at),
  INDEX idx_auth_requests_session (session_token_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS processed_updates (
  update_id VARCHAR(64) NOT NULL PRIMARY KEY,
  processed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_processed_updates_time (processed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;