<?php

function settings_extension_schema_queries(): array
{
    return [
        "CREATE TABLE IF NOT EXISTS direct_deposit_setups (
            id INT AUTO_INCREMENT PRIMARY KEY,
            organization_id INT NOT NULL,
            provider_code VARCHAR(50) NOT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'draft',
            customer_id VARCHAR(120) DEFAULT NULL,
            password_part_one VARCHAR(120) DEFAULT NULL,
            password_part_two VARCHAR(120) DEFAULT NULL,
            bank_account_label VARCHAR(180) DEFAULT NULL,
            bank_account_number VARCHAR(50) DEFAULT NULL,
            live_balance DECIMAL(12,2) DEFAULT NULL,
            registered_mobile VARCHAR(20) DEFAULT NULL,
            secret_pin VARCHAR(20) DEFAULT NULL,
            notes TEXT DEFAULT NULL,
            extra_json LONGTEXT DEFAULT NULL,
            last_synced_at TIMESTAMP NULL DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_org_provider_setup (organization_id, provider_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS direct_deposit_onboarding_profiles (
            id INT AUTO_INCREMENT PRIMARY KEY,
            organization_id INT NOT NULL,
            provider_code VARCHAR(50) NOT NULL,
            owner_confirmed TINYINT(1) NOT NULL DEFAULT 0,
            onboarding_step INT NOT NULL DEFAULT 1,
            business_pan VARCHAR(30) DEFAULT NULL,
            business_type VARCHAR(120) DEFAULT NULL,
            business_name VARCHAR(190) DEFAULT NULL,
            trade_name VARCHAR(190) DEFAULT NULL,
            business_address_1 VARCHAR(255) DEFAULT NULL,
            business_address_2 VARCHAR(255) DEFAULT NULL,
            business_address_3 VARCHAR(255) DEFAULT NULL,
            country VARCHAR(120) DEFAULT NULL,
            state VARCHAR(120) DEFAULT NULL,
            city VARCHAR(120) DEFAULT NULL,
            pin_code VARCHAR(20) DEFAULT NULL,
            business_phone VARCHAR(30) DEFAULT NULL,
            industry VARCHAR(120) DEFAULT NULL,
            business_website VARCHAR(255) DEFAULT NULL,
            business_description TEXT DEFAULT NULL,
            annual_turnover DECIMAL(14,2) DEFAULT NULL,
            transactions_per_day VARCHAR(60) DEFAULT NULL,
            avg_transaction_value DECIMAL(14,2) DEFAULT NULL,
            contact_name VARCHAR(190) DEFAULT NULL,
            contact_email VARCHAR(190) DEFAULT NULL,
            contact_mobile VARCHAR(30) DEFAULT NULL,
            contact_role VARCHAR(120) DEFAULT NULL,
            review_notes TEXT DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_org_provider_onboarding (organization_id, provider_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS zoho_app_integrations (
            id INT AUTO_INCREMENT PRIMARY KEY,
            organization_id INT NOT NULL,
            app_code VARCHAR(50) NOT NULL,
            app_name VARCHAR(120) NOT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'inactive',
            connection_name VARCHAR(180) DEFAULT NULL,
            connected_at TIMESTAMP NULL DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_org_app_integration (organization_id, app_code)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS developer_connections (
            id INT AUTO_INCREMENT PRIMARY KEY,
            organization_id INT NOT NULL,
            service_type VARCHAR(30) NOT NULL DEFAULT 'default',
            service_code VARCHAR(80) NOT NULL,
            service_name VARCHAR(150) NOT NULL,
            connection_name VARCHAR(150) NOT NULL,
            scope_summary TEXT DEFAULT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            UNIQUE KEY uniq_org_connection_name (organization_id, connection_name)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS incoming_webhooks (
            id INT AUTO_INCREMENT PRIMARY KEY,
            organization_id INT NOT NULL,
            name VARCHAR(150) NOT NULL,
            description TEXT DEFAULT NULL,
            script_body LONGTEXT DEFAULT NULL,
            status VARCHAR(30) NOT NULL DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS data_backups (
            id INT AUTO_INCREMENT PRIMARY KEY,
            organization_id INT NOT NULL,
            backup_type VARCHAR(30) NOT NULL,
            requested_by VARCHAR(190) DEFAULT NULL,
            recipient_email VARCHAR(190) DEFAULT NULL,
            file_type VARCHAR(20) NOT NULL DEFAULT 'ZIP',
            export_status VARCHAR(30) NOT NULL DEFAULT 'queued',
            download_link VARCHAR(255) DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        "CREATE TABLE IF NOT EXISTS support_requests (
            id INT AUTO_INCREMENT PRIMARY KEY,
            organization_id INT NOT NULL,
            subject VARCHAR(255) NOT NULL,
            message TEXT NOT NULL,
            attachment_path VARCHAR(255) DEFAULT NULL,
            contact_number VARCHAR(30) DEFAULT NULL,
            criticality VARCHAR(30) NOT NULL DEFAULT 'None',
            created_by VARCHAR(190) DEFAULT NULL,
            created_email VARCHAR(190) DEFAULT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
    ];
}

function settings_default_direct_deposit_providers(): array
{
    return [
        'zoho_payments' => [
            'name' => 'Zoho Payments - Payouts',
            'description' => 'Pay employees directly from Zoho Payroll.',
            'cta' => 'Set Up Now',
            'status' => 'available',
            'badge' => 'Platform Fee: Rs 3.00 per employee per pay run',
        ],
        'icici_bank' => [
            'name' => 'ICICI Bank',
            'description' => "Transfer salaries directly to your employees' bank accounts.",
            'cta' => 'Upgrade Plan',
            'status' => 'trial_locked',
            'badge' => 'Trial plan restriction',
        ],
        'yes_bank' => [
            'name' => 'YES Bank',
            'description' => "Transfer salaries directly to your employees' bank accounts.",
            'cta' => 'Set Up Now',
            'status' => 'available',
            'badge' => 'Bank integration',
        ],
        'hsbc_bank' => [
            'name' => 'HSBC Bank',
            'description' => "Transfer salaries directly to your employees' bank accounts.",
            'cta' => 'Set Up Now',
            'status' => 'redirect',
            'badge' => 'Redirect-based setup',
        ],
    ];
}

function settings_get_direct_deposit_setup(mysqli $conn, int $organizationId, string $providerCode): array
{
    $defaults = [
        'provider_code' => $providerCode,
        'status' => 'draft',
        'customer_id' => '',
        'password_part_one' => '',
        'password_part_two' => '',
        'bank_account_label' => 'Zoho Payroll - Bank Account',
        'bank_account_number' => '',
        'live_balance' => '',
        'registered_mobile' => '',
        'secret_pin' => '',
        'notes' => '',
        'extra_json' => '',
    ];

    $stmt = $conn->prepare(
        "SELECT * FROM direct_deposit_setups
         WHERE organization_id = ? AND provider_code = ?
         LIMIT 1"
    );
    if (!$stmt) {
        return $defaults;
    }

    $stmt->bind_param('is', $organizationId, $providerCode);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    return $row ? array_merge($defaults, $row) : $defaults;
}

function settings_save_direct_deposit_setup(mysqli $conn, int $organizationId, string $providerCode, array $data): bool
{
    $stmt = $conn->prepare(
        "INSERT INTO direct_deposit_setups
        (organization_id, provider_code, status, customer_id, password_part_one, password_part_two, bank_account_label, bank_account_number, live_balance, registered_mobile, secret_pin, notes, extra_json, last_synced_at)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())
        ON DUPLICATE KEY UPDATE
        status = VALUES(status),
        customer_id = VALUES(customer_id),
        password_part_one = VALUES(password_part_one),
        password_part_two = VALUES(password_part_two),
        bank_account_label = VALUES(bank_account_label),
        bank_account_number = VALUES(bank_account_number),
        live_balance = VALUES(live_balance),
        registered_mobile = VALUES(registered_mobile),
        secret_pin = VALUES(secret_pin),
        notes = VALUES(notes),
        extra_json = VALUES(extra_json),
        last_synced_at = VALUES(last_synced_at)"
    );

    if (!$stmt) {
        return false;
    }

    $liveBalance = $data['live_balance'] === '' ? null : (float) $data['live_balance'];
    $stmt->bind_param(
        'issssssssdsss',
        $organizationId,
        $providerCode,
        $data['status'],
        $data['customer_id'],
        $data['password_part_one'],
        $data['password_part_two'],
        $data['bank_account_label'],
        $data['bank_account_number'],
        $liveBalance,
        $data['registered_mobile'],
        $data['secret_pin'],
        $data['notes'],
        $data['extra_json']
    );
    $ok = $stmt->execute();
    $stmt->close();

    return $ok;
}

function settings_get_direct_deposit_onboarding_profile(mysqli $conn, int $organizationId, string $providerCode): array
{
    $defaults = [
        'provider_code' => $providerCode,
        'owner_confirmed' => 0,
        'onboarding_step' => 1,
        'business_pan' => '',
        'business_type' => '',
        'business_name' => '',
        'trade_name' => '',
        'business_address_1' => '',
        'business_address_2' => '',
        'business_address_3' => '',
        'country' => 'India',
        'state' => '',
        'city' => '',
        'pin_code' => '',
        'business_phone' => '',
        'industry' => '',
        'business_website' => '',
        'business_description' => '',
        'annual_turnover' => '',
        'transactions_per_day' => '',
        'avg_transaction_value' => '',
        'contact_name' => '',
        'contact_email' => '',
        'contact_mobile' => '',
        'contact_role' => '',
        'review_notes' => '',
    ];

    $stmt = $conn->prepare(
        "SELECT * FROM direct_deposit_onboarding_profiles
         WHERE organization_id = ? AND provider_code = ?
         LIMIT 1"
    );
    if (!$stmt) {
        return $defaults;
    }

    $stmt->bind_param('is', $organizationId, $providerCode);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    return $row ? array_merge($defaults, $row) : $defaults;
}

function settings_save_direct_deposit_onboarding_profile(mysqli $conn, int $organizationId, string $providerCode, array $data): bool
{
    $stmt = $conn->prepare(
        "INSERT INTO direct_deposit_onboarding_profiles
        (organization_id, provider_code, owner_confirmed, onboarding_step, business_pan, business_type, business_name, trade_name, business_address_1, business_address_2, business_address_3, country, state, city, pin_code, business_phone, industry, business_website, business_description, annual_turnover, transactions_per_day, avg_transaction_value, contact_name, contact_email, contact_mobile, contact_role, review_notes)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
        owner_confirmed = VALUES(owner_confirmed),
        onboarding_step = VALUES(onboarding_step),
        business_pan = VALUES(business_pan),
        business_type = VALUES(business_type),
        business_name = VALUES(business_name),
        trade_name = VALUES(trade_name),
        business_address_1 = VALUES(business_address_1),
        business_address_2 = VALUES(business_address_2),
        business_address_3 = VALUES(business_address_3),
        country = VALUES(country),
        state = VALUES(state),
        city = VALUES(city),
        pin_code = VALUES(pin_code),
        business_phone = VALUES(business_phone),
        industry = VALUES(industry),
        business_website = VALUES(business_website),
        business_description = VALUES(business_description),
        annual_turnover = VALUES(annual_turnover),
        transactions_per_day = VALUES(transactions_per_day),
        avg_transaction_value = VALUES(avg_transaction_value),
        contact_name = VALUES(contact_name),
        contact_email = VALUES(contact_email),
        contact_mobile = VALUES(contact_mobile),
        contact_role = VALUES(contact_role),
        review_notes = VALUES(review_notes)"
    );

    if (!$stmt) {
        return false;
    }

    $annualTurnover = $data['annual_turnover'] === '' ? null : (float) $data['annual_turnover'];
    $avgTransactionValue = $data['avg_transaction_value'] === '' ? null : (float) $data['avg_transaction_value'];
    $ownerConfirmed = !empty($data['owner_confirmed']) ? 1 : 0;
    $onboardingStep = max(1, (int) ($data['onboarding_step'] ?? 1));

    $stmt->bind_param(
        'isiisssssssssssssssdsdssssss',
        $organizationId,
        $providerCode,
        $ownerConfirmed,
        $onboardingStep,
        $data['business_pan'],
        $data['business_type'],
        $data['business_name'],
        $data['trade_name'],
        $data['business_address_1'],
        $data['business_address_2'],
        $data['business_address_3'],
        $data['country'],
        $data['state'],
        $data['city'],
        $data['pin_code'],
        $data['business_phone'],
        $data['industry'],
        $data['business_website'],
        $data['business_description'],
        $annualTurnover,
        $data['transactions_per_day'],
        $avgTransactionValue,
        $data['contact_name'],
        $data['contact_email'],
        $data['contact_mobile'],
        $data['contact_role'],
        $data['review_notes']
    );
    $ok = $stmt->execute();
    $stmt->close();

    return $ok;
}

function settings_default_zoho_apps(): array
{
    return [
        ['code' => 'zoho_people', 'name' => 'Zoho People', 'description' => 'Fetch employee and LOP details directly from Zoho People.'],
        ['code' => 'zoho_books', 'name' => 'Zoho Books', 'description' => 'Sync payroll transactions with your Zoho Books account automatically.'],
        ['code' => 'zoho_expense', 'name' => 'Zoho Expense', 'description' => 'Help employees submit expenses for reimbursements easily.'],
        ['code' => 'zoho_analytics', 'name' => 'Zoho Analytics', 'description' => 'Create custom reports and make better business decisions.'],
    ];
}

function settings_get_zoho_app_integrations(mysqli $conn, int $organizationId): array
{
    $apps = [];
    foreach (settings_default_zoho_apps() as $app) {
        $apps[$app['code']] = array_merge($app, [
            'status' => 'inactive',
            'connection_name' => null,
            'connected_at' => null,
        ]);
    }

    $stmt = $conn->prepare(
        "SELECT * FROM zoho_app_integrations
         WHERE organization_id = ?"
    );
    if (!$stmt) {
        return array_values($apps);
    }

    $stmt->bind_param('i', $organizationId);
    $stmt->execute();
    $result = $stmt->get_result();
    while ($result && ($row = $result->fetch_assoc())) {
        if (isset($apps[$row['app_code']])) {
            $apps[$row['app_code']] = array_merge($apps[$row['app_code']], $row);
        }
    }
    $stmt->close();

    return array_values($apps);
}

function settings_set_zoho_app_status(mysqli $conn, int $organizationId, string $appCode, string $appName, string $status): bool
{
    $connectedAt = $status === 'active' ? date('Y-m-d H:i:s') : null;
    $connectionName = $status === 'active' ? $appName . ' Connection' : null;
    $stmt = $conn->prepare(
        "INSERT INTO zoho_app_integrations
        (organization_id, app_code, app_name, status, connection_name, connected_at)
        VALUES (?, ?, ?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
        app_name = VALUES(app_name),
        status = VALUES(status),
        connection_name = VALUES(connection_name),
        connected_at = VALUES(connected_at)"
    );
    if (!$stmt) {
        return false;
    }
    $stmt->bind_param('isssss', $organizationId, $appCode, $appName, $status, $connectionName, $connectedAt);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function settings_default_connection_services(): array
{
    return [
        ['code' => 'zoho_payroll', 'name' => 'Zoho Payroll', 'type' => 'default'],
        ['code' => 'zoho_books', 'name' => 'Zoho Books', 'type' => 'default'],
        ['code' => 'zoho_people', 'name' => 'Zoho People', 'type' => 'default'],
        ['code' => 'zoho_analytics', 'name' => 'Zoho Analytics', 'type' => 'default'],
        ['code' => 'github', 'name' => 'GitHub', 'type' => 'default'],
        ['code' => 'slack', 'name' => 'Slack', 'type' => 'default'],
        ['code' => 'shopify', 'name' => 'Shopify', 'type' => 'default'],
        ['code' => 'twilio', 'name' => 'Twilio', 'type' => 'default'],
    ];
}

function settings_get_connections(mysqli $conn, int $organizationId, ?string $serviceType = null): array
{
    $sql = "SELECT * FROM developer_connections WHERE organization_id = ?";
    $types = 'i';
    $params = [$organizationId];

    if ($serviceType !== null) {
        $sql .= " AND service_type = ?";
        $types .= 's';
        $params[] = $serviceType;
    }

    $sql .= " ORDER BY created_at DESC, id DESC";
    $stmt = $conn->prepare($sql);
    if (!$stmt) {
        return [];
    }

    $bind = [$types];
    foreach ($params as $index => $value) {
        $params[$index] = $value;
        $bind[] = &$params[$index];
    }
    call_user_func_array([$stmt, 'bind_param'], $bind);
    $stmt->execute();
    $result = $stmt->get_result();

    $rows = [];
    while ($result && ($row = $result->fetch_assoc())) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function settings_add_connection(mysqli $conn, int $organizationId, array $data): bool
{
    $stmt = $conn->prepare(
        "INSERT INTO developer_connections
        (organization_id, service_type, service_code, service_name, connection_name, scope_summary, status)
        VALUES (?, ?, ?, ?, ?, ?, 'active')"
    );
    if (!$stmt) {
        return false;
    }
    $stmt->bind_param(
        'isssss',
        $organizationId,
        $data['service_type'],
        $data['service_code'],
        $data['service_name'],
        $data['connection_name'],
        $data['scope_summary']
    );
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function settings_get_webhooks(mysqli $conn, int $organizationId): array
{
    $stmt = $conn->prepare(
        "SELECT * FROM incoming_webhooks
         WHERE organization_id = ?
         ORDER BY created_at DESC, id DESC"
    );
    if (!$stmt) {
        return [];
    }
    $stmt->bind_param('i', $organizationId);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = [];
    while ($result && ($row = $result->fetch_assoc())) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function settings_add_webhook(mysqli $conn, int $organizationId, string $name, string $description, string $scriptBody): bool
{
    $stmt = $conn->prepare(
        "INSERT INTO incoming_webhooks
        (organization_id, name, description, script_body, status)
        VALUES (?, ?, ?, ?, 'active')"
    );
    if (!$stmt) {
        return false;
    }
    $stmt->bind_param('isss', $organizationId, $name, $description, $scriptBody);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function settings_get_backups(mysqli $conn, int $organizationId, string $backupType): array
{
    $stmt = $conn->prepare(
        "SELECT * FROM data_backups
         WHERE organization_id = ? AND backup_type = ?
         ORDER BY created_at DESC, id DESC"
    );
    if (!$stmt) {
        return [];
    }
    $stmt->bind_param('is', $organizationId, $backupType);
    $stmt->execute();
    $result = $stmt->get_result();
    $rows = [];
    while ($result && ($row = $result->fetch_assoc())) {
        $rows[] = $row;
    }
    $stmt->close();
    return $rows;
}

function settings_can_request_backup(mysqli $conn, int $organizationId, string $backupType): bool
{
    $stmt = $conn->prepare(
        "SELECT created_at FROM data_backups
         WHERE organization_id = ? AND backup_type = ?
         ORDER BY created_at DESC
         LIMIT 1"
    );
    if (!$stmt) {
        return true;
    }
    $stmt->bind_param('is', $organizationId, $backupType);
    $stmt->execute();
    $result = $stmt->get_result();
    $row = $result ? $result->fetch_assoc() : null;
    $stmt->close();

    if (!$row || empty($row['created_at'])) {
        return true;
    }

    return strtotime($row['created_at']) <= strtotime('-15 days');
}

function settings_request_backup(mysqli $conn, int $organizationId, string $backupType, string $requestedBy, string $recipientEmail): bool
{
    $stmt = $conn->prepare(
        "INSERT INTO data_backups
        (organization_id, backup_type, requested_by, recipient_email, file_type, export_status, download_link)
        VALUES (?, ?, ?, ?, 'ZIP', 'queued', NULL)"
    );
    if (!$stmt) {
        return false;
    }
    $stmt->bind_param('isss', $organizationId, $backupType, $requestedBy, $recipientEmail);
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}

function settings_add_support_request(
    mysqli $conn,
    int $organizationId,
    string $subject,
    string $message,
    ?string $attachmentPath,
    string $contactNumber,
    string $criticality,
    string $createdBy,
    string $createdEmail
): bool {
    $stmt = $conn->prepare(
        "INSERT INTO support_requests
        (organization_id, subject, message, attachment_path, contact_number, criticality, created_by, created_email)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    );
    if (!$stmt) {
        return false;
    }
    $stmt->bind_param(
        'isssssss',
        $organizationId,
        $subject,
        $message,
        $attachmentPath,
        $contactNumber,
        $criticality,
        $createdBy,
        $createdEmail
    );
    $ok = $stmt->execute();
    $stmt->close();
    return $ok;
}
