-- CloudHestia Database Schema
-- Note: Tables are created automatically by the PHP backend
-- This file is for reference only

-- Database: sammarle_smhcp

-- Users table (admin only)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    role ENUM('admin') DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Clients table (Cloudflare accounts managed by admin)
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    cf_api_token VARCHAR(255),
    cf_zone_id VARCHAR(100),
    cf_zone_name VARCHAR(255),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Admin HestiaCP configuration
CREATE TABLE IF NOT EXISTS admin_config (
    id INT AUTO_INCREMENT PRIMARY KEY,
    hestia_hostname VARCHAR(255),
    hestia_port VARCHAR(10) DEFAULT '8083',
    hestia_auth_type ENUM('credentials', 'accesskey') DEFAULT 'credentials',
    hestia_username VARCHAR(100),
    hestia_password VARCHAR(255),
    hestia_access_key VARCHAR(255),
    hestia_secret_key VARCHAR(255),
    hestia_user VARCHAR(100) DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Activity logs
CREATE TABLE IF NOT EXISTS activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    client_id INT,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    status ENUM('success', 'error', 'info') DEFAULT 'info',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_logs (created_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default admin user is created automatically by PHP
-- Username: admin
-- Password: admin123