
-- Device table for WhatsApp integration
CREATE TABLE IF NOT EXISTS device (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  number VARCHAR(20) NOT NULL UNIQUE,
  status VARCHAR(20) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  name VARCHAR(255),
  number VARCHAR(50) NOT NULL,
  message TEXT,
  description VARCHAR(255),
  target VARCHAR(20) DEFAULT 'current-user',
  status VARCHAR(20) DEFAULT 'ACTIVE',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_id (user_id),
  INDEX idx_created_at (created_at)
);

-- database.sql
-- Schema MySQL untuk proyek 'setorwa'

CREATE DATABASE IF NOT EXISTS setorwa DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE setorwa;
CREATE TABLE IF NOT EXISTS website (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  nohp VARCHAR(20) NOT NULL,
  gmail VARCHAR(255) NOT NULL,
  tokengmail VARCHAR(255) NOT NULL,
  telegram VARCHAR(255) NOT NULL,
  teleuser VARCHAR(255) NOT NULL,
  favicon VARCHAR(255) NOT NULL,
  logo VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS withdrawals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount BIGINT NOT NULL,
  status VARCHAR(50) DEFAULT 'PENDING',
  requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE IF NOT EXISTS messages_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  to_number VARCHAR(50),
  content TEXT,
  cost INT DEFAULT 0,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Add new columns to website table
ALTER TABLE website ADD COLUMN rate_per_message INT DEFAULT 0;
ALTER TABLE website ADD COLUMN minimal_withdraw BIGINT DEFAULT 0;
ALTER TABLE website ADD COLUMN referral_bonus BIGINT DEFAULT 0;
ALTER TABLE website ADD COLUMN minimal_chat INT DEFAULT 0;
ALTER TABLE website ADD COLUMN IF NOT EXISTS gmailadmin VARCHAR(255) DEFAULT NULL;

-- Create table to track referral commissions
CREATE TABLE IF NOT EXISTS referral_commissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  referrer_id INT NOT NULL,
  referred_user_id INT NOT NULL,
  message_log_id INT NOT NULL,
  commission BIGINT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (referrer_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (referred_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (message_log_id) REFERENCES messages_log(id) ON DELETE CASCADE,
  INDEX idx_referrer (referrer_id),
  INDEX idx_referred (referred_user_id),
  INDEX idx_created_at (created_at)
);

-- ✅ Add blast tracking columns to contacts table (for per-user locking)
-- Allows tracking which user is currently blasting these contacts
ALTER TABLE contacts ADD COLUMN IF NOT EXISTS blast_locked_by INT DEFAULT NULL;
ALTER TABLE contacts ADD COLUMN IF NOT EXISTS blast_locked_at TIMESTAMP NULL;


-- Create table for blast queue/history
CREATE TABLE IF NOT EXISTS blast_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  status VARCHAR(20) DEFAULT 'RUNNING', -- RUNNING, COMPLETED, FAILED
  total_contacts INT DEFAULT 0,
  sent_count INT DEFAULT 0,
  start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  end_time TIMESTAMP NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_id (user_id),
  INDEX idx_status (status)
);
