-- Skema database: Domain & Hosting Manager
-- Import file ini lewat phpMyAdmin di cPanel/Hostinger

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(150) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS domains (
    id INT AUTO_INCREMENT PRIMARY KEY,
    domain_name VARCHAR(255) NOT NULL,
    owner_name VARCHAR(150) NOT NULL,
    owner_email VARCHAR(150) NOT NULL,
    owner_phone VARCHAR(30) DEFAULT NULL,
    provider VARCHAR(100) NOT NULL,          -- Hostinger, Niagahoster, dll
    item_type ENUM('domain','hosting','domain+hosting') NOT NULL DEFAULT 'domain+hosting',
    expiry_date DATE NOT NULL,
    renewal_cost DECIMAL(12,2) NOT NULL DEFAULT 0,
    status ENUM('active','suspended','expired') NOT NULL DEFAULT 'active',
    notes TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    domain_id INT NOT NULL,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    public_token VARCHAR(64) UNIQUE NOT NULL,  -- untuk link publik tanpa login
    amount DECIMAL(12,2) NOT NULL,
    due_date DATE NOT NULL,
    status ENUM('unpaid','pending_verification','paid','cancelled') NOT NULL DEFAULT 'unpaid',
    payment_proof_path VARCHAR(255) DEFAULT NULL,
    uploaded_at DATETIME DEFAULT NULL,
    verified_at DATETIME DEFAULT NULL,
    sent_at DATETIME DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS reminder_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    domain_id INT NOT NULL,
    days_before INT NOT NULL,       -- 30, 14, 7, 1
    sent_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE
);

-- Admin default: username "admin", password "ganti123"
-- SEGERA GANTI setelah login pertama kali (hash di-generate oleh setup.php)