<?php
declare(strict_types=1);


namespace OneClickFormSync\Database;

if (!defined('ABSPATH')) {
    exit;
}

/**
 * Manages database schemas for the custom tables.
 */
class SchemaManager
{
    /**
     * Get SQL schema definitions for all custom tables.
     *
     * @return array Map of table keys to SQL create statements.
     */
    public function getTablesSchema(): array
    {
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $connectionsTable = "CREATE TABLE {$wpdb->prefix}oneclickformsync_connections (
            id VARCHAR(36) NOT NULL,
            name VARCHAR(100) NOT NULL,
            email VARCHAR(100) NOT NULL,
            client_id TEXT NOT NULL,
            client_secret TEXT NOT NULL,
            refresh_token TEXT NOT NULL,
            access_token TEXT NOT NULL,
            scopes TEXT NOT NULL,
            owner_id BIGINT(20) UNSIGNED NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'active',
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            last_success_at DATETIME DEFAULT NULL,
            last_error TEXT DEFAULT NULL,
            PRIMARY KEY (id)
        ) $charset_collate;";

        $queueTable = "CREATE TABLE {$wpdb->prefix}oneclickformsync_queue (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            connection_id VARCHAR(36) NOT NULL,
            spreadsheet_id VARCHAR(100) NOT NULL,
            worksheet_name VARCHAR(100) NOT NULL,
            payload LONGTEXT NOT NULL,
            attempts INT NOT NULL DEFAULT 0,
            status VARCHAR(20) NOT NULL DEFAULT 'pending',
            error_message TEXT DEFAULT NULL,
            next_attempt_at DATETIME DEFAULT NULL,
            created_at DATETIME NOT NULL,
            updated_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY status_next_attempt (status, next_attempt_at)
        ) $charset_collate;";

        $logsTable = "CREATE TABLE {$wpdb->prefix}oneclickformsync_logs (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            submission_id VARCHAR(100) NOT NULL,
            provider_id VARCHAR(50) NOT NULL,
            form_id VARCHAR(100) NOT NULL,
            connection_id VARCHAR(36) NOT NULL,
            spreadsheet_id_masked VARCHAR(100) NOT NULL,
            worksheet_name VARCHAR(100) NOT NULL,
            status VARCHAR(20) NOT NULL,
            error_code VARCHAR(50) DEFAULT NULL,
            error_message TEXT DEFAULT NULL,
            payload LONGTEXT DEFAULT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY (id),
            KEY created_at_idx (created_at)
        ) $charset_collate;";

        return [
            'connections' => $connectionsTable,
            'queue'       => $queueTable,
            'logs'        => $logsTable,
        ];
    }
}