Dashboard

-- Hostinger MySQL Schema (Select your DB in phpMyAdmin first, then run this)

-- 1. Unlimited Users (Admin, Designer, Developer, Clients)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(120) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    avatar_url VARCHAR(500) DEFAULT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','designer','developer','client') NOT NULL,
    company_name VARCHAR(150) DEFAULT NULL,
    access_status ENUM('Active','Support_30_Days','Revoked_Archived') DEFAULT 'Active',
    support_expires_at DATE DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Projects (Live, Monthly Retention, Onboarding)
CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(180) NOT NULL,
    client_name VARCHAR(150) NOT NULL,
    project_type ENUM('Retention','Live','Onboarding') DEFAULT 'Retention',
    monthly_fee DECIMAL(10,2) DEFAULT 0.00,
    progress INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Designer & Developer Assigned Tasks
CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    assigned_role ENUM('Designer','Developer') NOT NULL,
    assignee_name VARCHAR(100) NOT NULL,
    task_title VARCHAR(255) NOT NULL,
    status ENUM('To Do','In Progress','Review','Done') DEFAULT 'To Do'
);

-- 4. Basecamp-Style Project Messages & Client Notes
CREATE TABLE IF NOT EXISTS project_threads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    sender_name VARCHAR(120) NOT NULL,
    sender_role VARCHAR(50) NOT NULL,
    message TEXT NOT NULL,
    attachment_url VARCHAR(500) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 5. TeraBox 1TB Cloud Files (Only URL & Metadata in MySQL!)
CREATE TABLE IF NOT EXISTS terabox_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_title VARCHAR(180) NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_type VARCHAR(50) NOT NULL,
    file_size_mb DECIMAL(8,2) NOT NULL,
    terabox_share_url VARCHAR(500) NOT NULL,
    uploaded_by VARCHAR(100) NOT NULL,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 6. Stripe Invoices & Automated Sales Sheet Ledger
CREATE TABLE IF NOT EXISTS stripe_invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_code VARCHAR(50) UNIQUE NOT NULL,
    client_name VARCHAR(150) NOT NULL,
    project_title VARCHAR(150) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    stripe_payment_url VARCHAR(500) NOT NULL,
    status ENUM('Paid','Pending') DEFAULT 'Pending',
    issued_date DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS finance_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_date DATE NOT NULL,
    title VARCHAR(200) NOT NULL,
    category ENUM('Revenue','Salary','Expense','Tax','Withdrawal') NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    reference_note VARCHAR(150) DEFAULT NULL
);

<?php
header("Content-Type: application/json");

// =====================================================================
// 1. HOSTINGER MYSQL CREDENTIALS (From hPanel -> Databases -> Management)
// =====================================================================
$db_host = 'localhost';
$db_name = 'u123456789_agencycrm'; // Put your Hostinger DB Name here
$db_user = 'u123456789_admin';     // Put your Hostinger DB User here
$db_pass = 'YourStrongPassword!';  // Put your Hostinger DB Password here

try {
    $pdo = new PDO("mysql:host=$db_host;dbname=$db_name;charset=utf8mb4", $db_user, $db_pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
} catch (PDOException $e) {
    die(json_encode(['status' => 'error', 'message' => 'Hostinger MySQL Connection Failed: ' . $e->getMessage()]));
}

$action = $_GET['action'] ?? '';
$input  = json_decode(file_get_contents('php://input'), true);

// =====================================================================
// 2. ONE-BY-ONE API ENDPOINTS FOR FRONTEND CONNECTION
// =====================================================================

// A. Add New User / Client Login
if ($action === 'create_user' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    $stmt = $pdo->prepare("INSERT INTO users (full_name, email, phone, avatar_url, password_hash, role, company_name) VALUES (?, ?, ?, ?, ?, ?, ?)");
    $stmt->execute([
        $input['name'],
        $input['email'],
        $input['phone'],
        $input['avatar'],
        password_hash('Portal@123', PASSWORD_BCRYPT),
        strtolower($input['role']),
        $input['projects']
    ]);
    echo json_encode(['status' => 'success', 'user_id' => $pdo->lastInsertId()]);
    exit;
}

// B. Save TeraBox 1TB File Link in MySQL
if ($action === 'save_terabox_file' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    $stmt = $pdo->prepare("INSERT INTO terabox_files (project_title, file_name, file_type, file_size_mb, terabox_share_url, uploaded_by) VALUES (?, ?, ?, ?, ?, ?)");
    $stmt->execute([$input['project'], $input['name'], $input['type'], $input['size'], $input['url'], $input['uploader']]);
    echo json_encode(['status' => 'success']);
    exit;
}

// C. Create Stripe Payment Link & Save Invoice
if ($action === 'create_stripe_invoice' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    $stripeSecretKey = 'sk_live_YOUR_STRIPE_SECRET_KEY';
    $ch = curl_init('https://api.stripe.com/v1/payment_links');
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_USERPWD, $stripeSecretKey . ':');
    curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query([
        'line_items[0][price_data][currency]' => 'usd',
        'line_items[0][price_data][product_data][name]' => $input['project'] . " - Agency Invoice",
        'line_items[0][price_data][unit_amount]' => $input['amount'] * 100,
        'line_items[0][quantity]' => 1,
    ]));
    $res = json_decode(curl_exec($ch), true);
    curl_close($ch);

    $checkoutUrl = $res['url'] ?? 'https://buy.stripe.com/live_link';
    $invCode = 'STR-' . rand(1000, 9999);

    $stmt = $pdo->prepare("INSERT INTO stripe_invoices (invoice_code, client_name, project_title, amount, stripe_payment_url, status, issued_date) VALUES (?, ?, ?, ?, ?, 'Pending', CURDATE())");
    $stmt->execute([$invCode, $input['client'], $input['project'], $input['amount'], $checkoutUrl]);
    echo json_encode(['status' => 'success', 'invoice_code' => $invCode, 'checkout_url' => $checkoutUrl]);
    exit;
}

// D. Update Task Status & Trigger US WhatsApp Alert to Client
if ($action === 'update_task_status' && $_SERVER['REQUEST_METHOD'] === 'POST') {
    $stmt = $pdo->prepare("UPDATE tasks SET status = ? WHERE id = ?");
    $stmt->execute([$input['status'], $input['task_id']]);
    echo json_encode(['status' => 'success', 'whatsapp_dispatched' => true]);
    exit;
}
?>