view release on metacpan or  search on metacpan

lib/Concierge/Users/Database.pm  view on Meta::CPAN

package Concierge::Users::Database v0.8.0;
use v5.36;
use Carp qw/ croak /;
use DBI;
use parent qw/ Concierge::Users::Meta /;

# ABSTRACT: Database backend for Concierge::Users

# ==============================================================================
# Configure Class Method - One-time setup (called by Users->setup)
# ==============================================================================

sub configure {
    my ($class, $setup_config) = @_;

    # Extract storage_dir
    my $storage_dir = $setup_config->{storage_dir};

    # Build SQLite DSN and file path
    my $db_file = "$storage_dir/users.db";
    my $dsn = "dbi:SQLite:$db_file";

    # Connect to database
    my $dbh = DBI->connect($dsn, '', '', {
        RaiseError => 0,
        AutoCommit => 1,
        PrintError => 0,
        sqlite_unicode => 1,
    });

    unless ($dbh) {
        return {
            success => 0,
            message => sprintf(
                "Database backend connection failed:\n" .
                "  - Database file: %s\n" .
                "  - Error: %s",
                $db_file,
                $DBI::errstr || 'Unknown error'
            ),
        };
    }

    # Create temporary object for ensure_storage
    my $temp_backend = bless {
        dbh         => $dbh,
        table_name  => 'users',
        storage_dir => $storage_dir,
        db_file     => $db_file,
        field_definitions => $setup_config->{field_definitions},
        fields      => $setup_config->{fields} || [],
    }, $class;

    # Check for existing data and archive if present
    my $check_sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
    my $sth = $dbh->prepare($check_sql);
    if ($sth) {
        $sth->execute('users');
        my ($table_exists) = $sth->fetchrow_array();
        $sth->finish();

        if ($table_exists) {
            # Check if table has data
            my $count_sql = "SELECT COUNT(*) FROM users";
            my $count_sth = $dbh->prepare($count_sql);
            if ($count_sth) {
                $count_sth->execute();
                my ($user_count) = $count_sth->fetchrow_array();
                $count_sth->finish();

                # Archive if table has data
                if ($user_count > 0) {
                    my $archive_result = $temp_backend->_archive_user_data();
                    unless ($archive_result->{success}) {
                        $temp_backend->disconnect();
                        return {
                            success => 0,
                            message => $archive_result->{message},
                        };
                    }
                } else {
                    # Drop empty table
                    $dbh->do("DROP TABLE users");
                }
            }
        }
    }

    # Ensure storage (table) exists
    my $storage_ok = $temp_backend->ensure_storage();
    unless ($storage_ok) {
        return {
            success => 0,
            message => "Failed to initialize storage for database backend",
        };
    }

    # Disconnect temp object
    $temp_backend->disconnect();

    # Return success with config
    return {
        success => 1,
        message => "Database backend configured successfully",
        config => {
            storage_dir       => $storage_dir,
            db_file           => 'users.db',
            db_full_path      => $db_file,
            table_name        => 'users',
            fields            => $setup_config->{fields} || [],
            field_definitions => $setup_config->{field_definitions},
        },
    };
}

# ==============================================================================
# Constructor - Runtime instantiation (called by Users->new)
# ==============================================================================
sub new {
    my ($class, $runtime_config) = @_;

    # Extract parameters from saved config (no validation needed)
    my $storage_dir = $runtime_config->{storage_dir};
    my $db_file     = $runtime_config->{db_full_path};
    my $table_name  = $runtime_config->{table_name} || 'users';

    # Build SQLite DSN
    my $dsn = "dbi:SQLite:$db_file";

    # Connect to database
    my $dbh = DBI->connect($dsn, '', '', {
        RaiseError => 0,
        AutoCommit => 1,
        PrintError => 0,
        sqlite_unicode => 1,
    });

    unless ($dbh) {
        croak sprintf(
            "Database backend connection failed:\n" .
            "  - Database file: %s\n" .
            "  - Error: %s",
            $db_file,
            $DBI::errstr || 'Unknown error'
        );
    }

    return bless {
        dbh              => $dbh,
        table_name       => $table_name,
        storage_dir      => $storage_dir,
        db_file          => $db_file,
        fields           => $runtime_config->{fields} || [],
        field_definitions => $runtime_config->{field_definitions} || {},
    }, $class;
}

# Report backend configuration (for debugging/info)
sub config {
    my ($self) = @_;

    return {
        storage_dir       => $self->{storage_dir},
        db_file           => $self->{db_file},
        db_full_path      => $self->{db_file},
        table_name        => $self->{table_name},
        fields	       	  => $self->{fields},
        field_definitions => $self->{field_definitions},
    };
}

# Ensure storage (table) exists
sub ensure_storage {
    my ($self) = @_;

    # Check if table exists
    my $check_sql = "SELECT name FROM sqlite_master WHERE type='table' AND name=?";
    my $sth = $self->{dbh}->prepare($check_sql);
    return 0 unless $sth;
    $sth->execute($self->{table_name});
    my ($exists) = $sth->fetchrow_array();

    return 1 if $exists;  # Table already exists

    # Build CREATE TABLE SQL
    my @field_defs;
    my @indexes;

    foreach my $field (@{$self->{fields}}) {
        # All fields are TEXT in our schema
        my $field_def = "$field TEXT";

        # Check if field is required
        my $field_def_info = $self->{field_definitions}{$field};
        if ($field_def_info && $field_def_info->{required}) {