package WWWDBI;
use strict;
use warnings;
use DBI;
use LWP::UserAgent;
use Crypt::Eksblowfish::Bcrypt qw(bcrypt en_base64);
use Mojo::JSON qw(decode_json encode_json);
use Exporter qw(import);

our @EXPORT = qw(dob paste push_over get_pasted delete_message push_gotify);


sub new {
    my ($class, %args) = @_;
    
    my ($authDbUser, $authDbPass, $db_host);
    
    # Method 1: Environment Variables (Docker + traditional)
    if ($ENV{DB_USER} && $ENV{DB_PASS}) {
        $authDbUser = $ENV{DB_USER};
        $authDbPass = $ENV{DB_PASS};
        $db_host = $ENV{DB_HOST} || 'localhost';
        print "Using environment variables for DB connection\n" if $ENV{DEBUG};
    } else {
        die "Database credentials not found. Set DB_USER/DB_PASS environment variables.";
    }
    
    my $db_name = $ENV{DB_NAME} || 'www';
    my $db_port = $ENV{DB_PORT} || '3306';
    
    # Fix DSN format - handle localhost specially
    my $dsn;
    if ($db_host eq 'localhost' && $db_port eq '3306') {
        # For localhost with default port, don't specify port
        $dsn = "DBI:MariaDB:$db_name:$db_host";
    } elsif ($db_host eq 'localhost') {
        # For localhost with non-default port, use socket or different format
        $dsn = "DBI:MariaDB:database=$db_name;host=$db_host;port=$db_port";
    } else {
        # For remote hosts, use full specification
        $dsn = "DBI:MariaDB:database=$db_name;host=$db_host;port=$db_port";
    }
    
    print "DSN: $dsn\n" if $ENV{DEBUG};
    
    my $self = bless {
        dsn => $dsn,
        dbUser => $authDbUser,
        dbPass => $authDbPass,
        %args
    }, $class;
    
    $self->connect();
    return $self;
}


sub connect {
    my ($self) = @_;
    $self->{dbh} = DBI->connect($self->{dsn}, $self->{dbUser}, $self->{dbPass}, 
        { 
            PrintError => 0, 
            RaiseError => 1
        }) or die $DBI::errstr;
}


sub ensure_connection {
    my ($self) = @_;
    
    eval {
        # Try a simple query to check the connection
        $self->{dbh}->do('SELECT 1');
    };
    
    if ($@) {
        # Connection is dead, reconnect
        $self->connect();
    }
}


sub dob {
    my ($self) = @_;
    $self->ensure_connection;
    return $self->{dbh}->selectall_hashref("SELECT * FROM dob", 'name');
}


sub paste {
    my ($self, $paste) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("INSERT INTO copy (text) VALUES(?)");
    $sth->execute($paste);
}


sub push_over {
    my ($self, $message) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT * FROM pushover");
    $sth->execute();
    my $pushOver = $sth->fetchrow_hashref();
    my $ua = LWP::UserAgent->new();
    my $res = $ua->post(
        'https://api.pushover.net/1/messages.json',
        [
            token => $pushOver->{token},
            user => $pushOver->{user},
            message => $message
        ]
    );
}


sub push_gotify {
    my ($self, $message, $title, $priority) = @_;
    $self->ensure_connection;
    
    my $sth = $self->{dbh}->prepare("SELECT * FROM gotify");
    $sth->execute();
    my $gotify = $sth->fetchrow_hashref();
    
    my $ua = LWP::UserAgent->new();
    my $url = 'https://go.rendler.org/message?token=' . $gotify->{token};
    
    my @params = (message => $message);
    push @params, (title => $title) if defined $title;
    push @params, (priority => $priority) if defined $priority;
    
    my $res = $ua->post($url, \@params);
    
    return $res;
}


sub get_pasted {
    my ($self) = @_;
    $self->ensure_connection;
    my @messages;
    for my $m (@{ $self->{dbh}->selectall_arrayref("SELECT id, text FROM copy ORDER BY id DESC") }) {
        my ($id, $text) = @$m;
        if ($text =~ /^http/) {
            $text =~ s/(http\S+)/<a href="$1">$1<\/a>/g;
            $text =~ s/\n/<br>/g;
        }
        push @messages, { id => $id, text => $text };
    }
    return @messages;
}


sub delete_message {
    my ($self, $id) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("DELETE FROM copy WHERE id = ?");
    $sth->execute($id);
}


sub get_app_secret {
    my ($self) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT secret_value FROM app_secrets WHERE key_name = 'mojo_app_secret'");
    $sth->execute();
    my ($secret) = $sth->fetchrow_array();
    return $secret;
}


sub user_exists {
    my ($self, $username) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT COUNT(*) FROM users WHERE username = ?");
    $sth->execute($username);
    my ($count) = $sth->fetchrow_array();
    return $count > 0;
}


sub create_user {
    my ($self, $username, $password, $email) = @_;
    $self->ensure_connection;
   
    my $hashed_password;
    eval {
        my $salt = en_base64(join('', map chr(int(rand(256))), 1..16));
        $hashed_password = bcrypt($password, '$2a$10$'.$salt);
    };
    if ($@) {
        die "Failed to hash password: $@";
    }
   
    eval {
        my $sth = $self->{dbh}->prepare("INSERT INTO users (username, password, email) VALUES (?, ?, ?)");
        $sth->execute($username, $hashed_password, $email);
    };
    if ($@) {
        die "Failed to insert user into database: $@";
    }
    return 1;
}


sub authenticate_user {
    my ($self, $username, $password) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT password, status FROM users WHERE username = ?");
    $sth->execute($username);
    my $user = $sth->fetchrow_hashref();
    
    return 0 unless $user; # User not found
    return 2 if $user->{status} ne 'approved'; # User not approved
    
    return (bcrypt($password, $user->{password}) eq $user->{password}) ? 1 : 0;
}


sub approve_user {
    my ($self, $id) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("UPDATE users SET status = 'approved' WHERE id = ?");
    $sth->execute($id);
}


sub is_admin {
    my ($self, $username) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT is_admin FROM users WHERE username = ?");
    $sth->execute($username);
    my ($is_admin) = $sth->fetchrow_array();
    return $is_admin ? 1 : 0;
}


sub get_all_users {
    my ($self) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT id, username, email, created_at, is_admin, status FROM users");
    $sth->execute();
    return $sth->fetchall_arrayref({});
}


sub delete_user {
    my ($self, $id) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("DELETE FROM users WHERE id = ?");
    $sth->execute($id);
}


sub get_user_by_id {
    my ($self, $id) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT id, username, email, is_admin FROM users WHERE id = ?");
    $sth->execute($id);
    return $sth->fetchrow_hashref();
}


sub update_user {
    my ($self, $id, $username, $email, $is_admin) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("UPDATE users SET username = ?, email = ?, is_admin = ? WHERE id = ?");
    $sth->execute($username, $email, $is_admin, $id);
}


sub update_user_password {
    my ($self, $id, $password) = @_;
    $self->ensure_connection;

    my $hashed_password;
    eval {
        my $salt = en_base64(join('', map chr(int(rand(256))), 1..16));
        $hashed_password = bcrypt($password, '$2a$10$'.$salt);
    };
    if ($@) {
        die "Failed to hash password: $@";
    }

    my $sth = $self->{dbh}->prepare("UPDATE users SET password = ? WHERE id = ?");
    $sth->execute($hashed_password, $id);
}


sub get_user_id {
    my ($self, $username) = @_;
    $self->ensure_connection;
    my $sth = $self->{dbh}->prepare("SELECT id FROM users WHERE username = ?");
    $sth->execute($username);
    my ($id) = $sth->fetchrow_array();
    return $id;
}


sub _get_combined_key {
    my ($self, $page_key, $resolution_key) = @_;
    my $res_key = $resolution_key || 'default';
    return "$page_key\@$res_key";
}

1;