new

#!/bin/bash

# SQLite3 wrapper script for ease of manipulation inspired by TaskWarrior

warnings() {
    warn "add usage case for each function"
    warn "Concept only.
    documentation incomplete
    functions incomplete"
}

# Define an associative array to store option descriptions
# TODO make function for this
declare -A option_descriptions=(
    [" "]="What happens if no arg given?"
    ["help"]="Display a help message"
    ["debug"]="Opens this script with \$EDITOR"
	["doc,writer"]="Creates an empty Writer document."
    ["*"]="Default function"
)

main() {
    
    if [ $# -eq 0 ]; then default_fn; fi

    case "$1" in
        -h | --help  | h?lp  ) shift ; show_help ;;
        -D | --debug | d*bug ) shift ; debug_this_script ;;
        doc* | writ* ) shift ; open_writer_document "$@" ;;
        *) default_fn "$@" ;; #TODO
    esac

    # Process should not reach this point, if it does, maybe forgot exit
    echo -e "\e[31mERROR\e[0m" >&2
    exit 1
}

# Now add functions here in alphabetical order **********************************************

open_writer_document() {
    if [ $# -gt 0 ]; then
        filename="$1.odt"
        touch $filename
    fi
    $filename="./new-doc.odt"
    soffice --writer $filename &
}

execute_sql() {
#TODO detect sql keywords and autocapitalize
    if [ $# -eq 0 ]; then
        echo "Enter SQL query (Ctrl+D when finished):"
        query=$(cat)
        warn "EXECUTING QUERY...."
        sleep 1 # for dramatic effect
        warn "RESULTS:"
        sleep 0.9
    else
#        check_help "$@"
        warn "EXECUTING [$*]"
    fi
}

insert_sql() {
    warn "Desired Usage:"
        echo -e "
        sql add
        sql add default
        sql add field1:value1 field2:value2 ..."
        
    if [ $# -eq 0 ]; then
        read -p "col1: " -t 9 col1
        warn "if empty, ABORT"
        read -p "col2: " -t 9 col2
        read -p "col3: " -t 9 col3
        read -p "col4: " -t 9 col4
        echo "$col1 $col2 $col3 $col4"
        exit 1
    fi
    
    case "$1" in
        def*) warn INSERT DEFAULT VALUES ; exit 1 ;;
        *) ;;
    esac
    
   warn "iterate and extract field:value"
   echo "$@" | cut -d':' -f1
   echo "$@" | cut -d':' -f2
}

update_sql() {
    warn "Desired Usage:"
        echo -e "
        sql mod <filter> column1:value1 column2:value2 ..."
    warn "UPDATE <table> SET ( col1 [,col2,col3] = expr )... FROM <table> WHERE con1 AND/OR con2 AND/OR"
}

delete_sql() {
    warn "Desired Usage:"
    echo -e "
    sql delete <filter>
    sql delete -c [db] -t [table] <filter>
    
    where <filter> is a set of SQL conditions to be joined by AND/OR"

    warn "if no filter provided, do filter=\$(cat) as such:"
    echo "Provide filter (SQL conditions) [Ctrl+D]":
    filter=$(cat)
}

connect_to_database() {
    if [ $# -eq 0 ]; then
        warn "launching fuzzy search, please select database..."
        sleep 1.12
        db=$(fzf --query "'.db")
    else
        db="$1"
    fi
    echo "Connecting to $db"
    #TODO
}

debug_this_script() {
    micro $0
    exit "$?"
}

default_fn() {
    warn "no argument means select all "
    warn "* means select all"
    warn "check if $1 matches the name of a table"
    warn "check if $1 matches the name of a column"
    warn "check if $1 is id or rowid"
    warn "check if $* has filter keywords"
    echo "SELECT * FROM $target_table"
    exit 1
}

edit_aliases() {
	"$EDITOR" $HOME/.aliases
	exit "$?"
}

edit_variables() {
	"$EDITOR" $HOME/.variables
	exit "$?"
}

list_tables() {
    # Test if the string is empty
    if [ -z "$CONNECTED_DB" ]; then
        warn NO CONNECTION
        warn "Do sql connect to connect to db"
    else
        echo "CONNECTED: $DB"
        echo "SELECTED: $target_table"
        err "SELECT ALL TABLES FROM $DB"
    fi
    exit "$?"
}

set_x() {
    case "$1" in
        db) echo "set database $2" ;;
        ta??e*) echo "Set table/target $2" ;;
        *) echo "set $*" ;;
    esac
#    exit 1
}

# Help function to display usage information and option descriptions
show_help() {
    # ANSI color codes for colors without using \e[33m and \e[31m
    GREEN='\033[0;32m'
    BLUE='\033[0;34m'
    NC='\033[0m' # No Color

    echo "Usage: $(basename "$0") [OPTIONS]"
    echo "Options:"
    for option in "${!option_descriptions[@]}"; do
        printf "  ${GREEN}%-12s${NC} %s\n" "$option" "${option_descriptions[$option]}"
    done
    exit 0
}

show_usage() {
	warn 'edit aliases|variables|debug|help|...'
	exit 1
}

# Messages
warn(){ echo -e "\e[33m$@\e[0m"; }
err() { echo -e "\e[31m$@\e[0m" 1>&2; }

#warnings
main "$@" #<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

#=============================================================================
# Additional documentation
#=============================================================================

# SQL PROJECT *********************************************************************

#"sqlite3 -batch {db_filepath} \".mode {Q.mode}\" \"{Q.sql}\""

### general commands

#sql                      -> run default (if not connected, connect. If no table specified, show tables. Else, select * all)
#sql connect, -c          -> fzf search db
#sql connect <*.db>.<tablename>       -> connect, fetch tables, print tables ,export db name
#sql tables               -> print tables
#sql <table>              -> print information about table
#sql set target <table_name>  -> export table name: DB: finances.db/expenses
#sql <filter> <user defined command>
#sql run|execute <SQL commands as is>

### select [DEFAULT OPERATION, DO NOT SPECIFY]

#sql *                    -> SELECT * [FROM TABLE]
#sql <id>...              -> SELECT * [FROM TABLE] WHERE id/rowid = <val> AND ...
#sql <column>...          -> SELECT c1,c2,c3,... [FROM TABLE WHERE ...] 
#sql <condition>...       -> SELECT [...] [FROM TABLE] WHERE con1 AND/OR con2 AND/OR con3
#sql <column> <condition>... [by <column><+|->] [limit:<value>] [offset:<value>]
#sql <*filter>            -> interpret as any non-commands as filter


#-> SELECT [DISTINCT] * | <column>... | <expr> AS [alias] ...
#   FROM <table>
#   WHERE <filter1> <filter2> <filter3> ...
#   GROUP BY <col>... HAVING expr
#   ORDER BY <term>...
#   LIMIT <val>
#   OFFSET <val>
#   

### insert

#sql add|insert [table] <column>:<value>...

#-> INSERT INTO <table> [(col1,col2,c3)] VALUES (cal1,val2,val3)
#-> INSERT INTO <table>                  VALUES ...
#-> INSERT INTO <table> DEFAULT VALUES

### update

#sql mod|update [table] <column>:<value>...

#-> UPDATE <table> SET ( col1 [,col2,col3] = expr )... FROM <table> WHERE con1 AND/OR con2 AND/OR

### delete

#sql del|delete [table] <condition>...

#-> DELETE FROM <table> WHERE con1 AND/OR con2 AND/OR con3 ...

### other commands

#sql --backup to <file>

#sql --dump ...

#sql --mode <csv|column|html|insert|line|list|tabs|tcl>

#sql --read <file>

#sql --width

#sql alter <table> <string>
#-> ALTER TABLE $table RENAME TO $string
#-> ALTER TABLE $table RENAME COLUMN $col TO $new-col
#-> ALTER TABLE $table ADD COLUMN $def
#-> ALTER TABLE $table DROP COLUMN $name

#sql attach
#-> ATTACH DATABASE <expr> AS <shema-name>

#sql rollback
#-> ROLLBACK TRANSACTION TO SAVEPOINT <savename>

#sql savepoint
#-> SAVEPOINT <savename>

# **************************************************************************
# REFERENCE TO TASKWARRIOR DOCUMENTATION
#cybersamurai@Luminous:~$ task help

#Usage: task                                       Runs rc.default.command, if specified.
#       task <filter> active                       Active tasks
#       task          add <mods>                   Adds a new task
#       task <filter> all                          All tasks
#       task <filter> annotate <mods>              Adds an annotation to an existing task
#       task <filter> append <mods>                Appends text to an existing task description
#       task <filter> blocked                      Blocked tasks
#       task <filter> blocking                     Blocking tasks
#       task <filter> burndown.daily               Shows a graphical burndown chart, by day
#       task <filter> burndown.monthly             Shows a graphical burndown chart, by month
#       task <filter> burndown.weekly              Shows a graphical burndown chart, by week
#       task          calc <expression>            Calculator
#       task          calendar [due|<month>        Shows a calendar, with due tasks marked
#       <year>|<year>] [y]
#       task          colors [sample | legend]     All colors, a sample, or a legend
#       task          columns [substring]          All supported columns and formatting styles
#       task          commands                     Generates a list of all commands, with
#                                                  behavior details
#       task <filter> completed                    Completed tasks
#       task          config [name [value | '']]   Change settings in the task configuration
#       task          context [<name> |            Set and define contexts (default filters /
#       <subcommand>]                              modifications)
#       task <filter> count                        Counts matching tasks
#       task <filter> delete <mods>                Deletes the specified task
#       task <filter> denotate <pattern>           Deletes an annotation
#       task          diagnostics                  Platform, build and environment details
#       task <filter> done <mods>                  Marks the specified task as completed
#       task <filter> duplicate <mods>             Duplicates the specified tasks
#       task <filter> edit                         Launches an editor to modify a task directly
#       task          execute <external command>   Executes external commands and scripts
#       task <filter> expenses                     Reports all expenses
#       task <filter> export [<report>]            Exports tasks in JSON format
#       task <filter> ghistory.annual              Shows a graphical report of task history, by
#                                                  year
#       task <filter> ghistory.daily               Shows a graphical report of task history, by
#                                                  day
#       task <filter> ghistory.monthly             Shows a graphical report of task history, by
#                                                  month
#       task <filter> ghistory.weekly              Shows a graphical report of task history, by
#                                                  week
#       task          help ['usage']               Displays this usage help text
#       task <filter> history.annual               Shows a report of task history, by year
#       task <filter> history.daily                Shows a report of task history, by day
#       task <filter> history.monthly              Shows a report of task history, by month
#       task <filter> history.weekly               Shows a report of task history, by week
#       task <filter> ids                          Shows the IDs of matching tasks, as a range
#       task          import [<file> ...]          Imports JSON files
#       task <filter> information                  Shows all data and metadata
#       task <filter> last_insert                  Reports all info on last inserted task
#       task <filter> list                         Most details of tasks
#       task          log <mods>                   Adds a new task that is already completed
#       task          logo                         Displays the Taskwarrior logo
#       task <filter> long                         All details of tasks
#       task <filter> ls                           Few details of tasks
#       task <filter> minimal                      Minimal details of tasks
#       task <filter> modify <mods>                Modifies the existing task with provided
#                                                  arguments.
#       task <filter> newest                       Newest tasks
#       task          news                         Displays news about the recent releases
#       task <filter> next                         Most urgent tasks
#       task <filter> oldest                       Oldest tasks
#       task <filter> overdue                      Overdue tasks
#       task <filter> prepend <mods>               Prepends text to an existing task
#                                                  description
#       task <filter> projects                     Shows all project names used
#       task <filter> purge                        Removes the specified tasks from the data
#                                                  files. Causes permanent loss of data.
#       task <filter> ready                        Most urgent actionable tasks
#       task <filter> recurring                    Recurring Tasks
#       task          reports                      Lists all supported reports
#       task          show [all | substring]       Shows all configuration variables or subset
#       task <filter> simple                       Simple list of open tasks by project
#       task <filter> start <mods>                 Marks specified task as started
#       task <filter> stats                        Shows task database statistics
#       task <filter> stop <mods>                  Removes the 'start' time from a task
#       task <filter> summary                      Shows a report of task status by project
#       task          synchronize [initialize]     Synchronizes data with the Taskserver
#       task <filter> tags                         Shows a list of all tags used
#       task [filter] timesheet                    Summary of completed and started tasks
#       task <filter> trash                        List all deleted tasks
#       task          udas                         Shows all the defined UDA details
#       task <filter> unblocked                    Unblocked tasks
#       task          undo                         Reverts the most recent change to a task