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

CREATE TABLE admins (
 id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
 id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(180) NOT NULL,
 slug VARCHAR(200) NOT NULL UNIQUE, short_description VARCHAR(255) NOT NULL,
 description TEXT NOT NULL, features TEXT NULL, demo_url VARCHAR(500) NULL,
 normal_price DECIMAL(14,2) NOT NULL DEFAULT 0, promo_price DECIMAL(14,2) NULL,
 download_file VARCHAR(255) NULL, seo_title VARCHAR(180) NULL,
 seo_description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
CREATE TABLE product_images (
 id INT AUTO_INCREMENT PRIMARY KEY, product_id INT NOT NULL,
 image_path VARCHAR(255) NOT NULL, sort_order INT NOT NULL DEFAULT 0,
 FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);
CREATE TABLE orders (
 id INT AUTO_INCREMENT PRIMARY KEY, invoice VARCHAR(40) NOT NULL UNIQUE,
 product_id INT NOT NULL, customer_name VARCHAR(150) NOT NULL,
 email VARCHAR(190) NOT NULL, whatsapp VARCHAR(30) NOT NULL,
 amount DECIMAL(14,2) NOT NULL, status ENUM('pending','waiting_verification','paid','rejected') NOT NULL DEFAULT 'pending',
 payment_proof VARCHAR(255) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 paid_at DATETIME NULL, FOREIGN KEY (product_id) REFERENCES products(id)
);
CREATE TABLE settings (`key` VARCHAR(100) PRIMARY KEY, value TEXT NOT NULL);
INSERT INTO settings (`key`,value) VALUES
('brand_name','Mobista Digital'),('whatsapp','6285859999731'),
('site_description','Solusi aplikasi web siap pakai untuk membantu bisnis Anda berkembang.')
ON DUPLICATE KEY UPDATE value=VALUES(value);