CREATE DATABASE IF NOT EXISTS billingnet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE billingnet;
CREATE TABLE IF NOT EXISTS clients (
 id INT AUTO_INCREMENT PRIMARY KEY, nama VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, wa_admin VARCHAR(30) NULL, status ENUM('active','suspended') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS users (
 id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NULL, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','client') NOT NULL DEFAULT 'client', status ENUM('active','blocked') NOT NULL DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS whatsapp_bots (
 id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL UNIQUE, phone VARCHAR(30) NULL, status ENUM('disconnected','connecting','connected','logged_out') NOT NULL DEFAULT 'disconnected', qr_data LONGTEXT NULL, last_error TEXT NULL, connected_at DATETIME NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS mikrotik_servers (
 id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, name VARCHAR(100) NOT NULL, host VARCHAR(190) NOT NULL, port INT NOT NULL DEFAULT 8728, username VARCHAR(100) NOT NULL, password_enc TEXT NOT NULL, use_ssl TINYINT(1) NOT NULL DEFAULT 0, status ENUM('unknown','online','offline') NOT NULL DEFAULT 'unknown', last_test DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS customers (
 id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, name VARCHAR(120) NOT NULL, phone VARCHAR(30) NOT NULL, username VARCHAR(120) NULL, mac_address VARCHAR(30) NULL, ip_address VARCHAR(45) NULL, profile VARCHAR(120) NULL, status ENUM('active','blocked') NOT NULL DEFAULT 'active', notes TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(client_id,phone), FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS voucher_products (
 id INT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, name VARCHAR(120) NOT NULL, profile VARCHAR(120) NOT NULL, price BIGINT NOT NULL, duration_label VARCHAR(80) NULL, active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS vouchers (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, product_id INT NULL, order_id BIGINT NULL, code VARCHAR(80) NOT NULL, password VARCHAR(120) NOT NULL, profile VARCHAR(120) NOT NULL, status ENUM('available','sold','used','disabled') NOT NULL DEFAULT 'available', sold_to VARCHAR(30) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_voucher(client_id,code), FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY(product_id) REFERENCES voucher_products(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS orders (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, customer_id INT NULL, phone VARCHAR(30) NOT NULL, product_id INT NOT NULL, ref_id VARCHAR(120) NOT NULL UNIQUE, txn_id VARCHAR(120) NULL, amount BIGINT NOT NULL, unique_amount BIGINT NULL, payment_url TEXT NULL, qris_string TEXT NULL, status ENUM('pending','paid','expired','cancelled','refunded','fulfilled','failed') NOT NULL DEFAULT 'pending', voucher_id BIGINT NULL, error_message TEXT NULL, paid_at DATETIME NULL, fulfilled_at DATETIME NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(client_id,status), FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL, FOREIGN KEY(product_id) REFERENCES voucher_products(id) ON DELETE RESTRICT
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS complaints (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, customer_id INT NULL, phone VARCHAR(30) NOT NULL, message TEXT NOT NULL, status ENUM('open','process','resolved','closed') NOT NULL DEFAULT 'open', reply TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE, FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS device_actions (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, customer_id INT NULL, phone VARCHAR(30) NOT NULL, action ENUM('check','block','activate') NOT NULL, target VARCHAR(120) NULL, result TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS webhook_logs (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, event_key VARCHAR(190) NULL UNIQUE, payload LONGTEXT NOT NULL, signature VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE IF NOT EXISTS bot_logs (
 id BIGINT AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, phone VARCHAR(30) NULL, direction ENUM('in','out','system') NOT NULL DEFAULT 'system', message TEXT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX(client_id,created_at), FOREIGN KEY(client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;