CREATE DATABASE IF NOT EXISTS clienthub CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE clienthub;
CREATE TABLE users(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(120) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,password_hash VARCHAR(255) NOT NULL,role ENUM('admin','staff') DEFAULT 'staff',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE customers(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_code VARCHAR(30) NOT NULL UNIQUE,name VARCHAR(150) NOT NULL,company VARCHAR(150),email VARCHAR(190),whatsapp VARCHAR(40),address TEXT,city VARCHAR(100),status ENUM('Aktif','Nonaktif','Prospek') DEFAULT 'Aktif',notes TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,INDEX(name),INDEX(status)) ENGINE=InnoDB;
CREATE TABLE packages(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR(150) NOT NULL,type ENUM('Website','Hosting','Domain','Maintenance','Lainnya') DEFAULT 'Hosting',price DECIMAL(15,2) DEFAULT 0,duration VARCHAR(50),description TEXT,status ENUM('Aktif','Nonaktif') DEFAULT 'Aktif',created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;
CREATE TABLE services(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_id BIGINT UNSIGNED NOT NULL,type ENUM('Website','Hosting','Domain','SSL','Maintenance','Email','SEO','Lainnya') DEFAULT 'Website',name VARCHAR(150) NOT NULL,domain VARCHAR(190),package_id BIGINT UNSIGNED,server VARCHAR(150),start_date DATE,due_date DATE,price DECIMAL(15,2) DEFAULT 0,status ENUM('Aktif','Nonaktif','Expired','Suspended') DEFAULT 'Aktif',notes TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,FOREIGN KEY(package_id) REFERENCES packages(id) ON DELETE SET NULL,INDEX(due_date)) ENGINE=InnoDB;
CREATE TABLE invoices(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,customer_id BIGINT UNSIGNED NOT NULL,service_id BIGINT UNSIGNED,invoice_no VARCHAR(60) NOT NULL UNIQUE,invoice_date DATE NOT NULL,due_date DATE,subtotal DECIMAL(15,2) DEFAULT 0,discount DECIMAL(15,2) DEFAULT 0,total DECIMAL(15,2) DEFAULT 0,status ENUM('Draft','Pending','Sebagian','Lunas','Batal') DEFAULT 'Pending',notes TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,FOREIGN KEY(customer_id) REFERENCES customers(id) ON DELETE CASCADE,FOREIGN KEY(service_id) REFERENCES services(id) ON DELETE SET NULL,INDEX(due_date),INDEX(status)) ENGINE=InnoDB;
CREATE TABLE payments(id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,invoice_id BIGINT UNSIGNED NOT NULL,payment_date DATE NOT NULL,amount DECIMAL(15,2) NOT NULL,method ENUM('Transfer','Cash','QRIS','E-Wallet','Lainnya') DEFAULT 'Transfer',reference_no VARCHAR(100),notes TEXT,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,FOREIGN KEY(invoice_id) REFERENCES invoices(id) ON DELETE CASCADE) ENGINE=InnoDB;
CREATE TABLE settings(id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,setting_key VARCHAR(100) UNIQUE NOT NULL,setting_value TEXT);
INSERT INTO settings(setting_key,setting_value) VALUES ('business_name','Nama Bisnis Anda'),('business_email','admin@contoh.com'),('business_phone','08xxxxxxxxxx'),('invoice_prefix','INV') ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);