import sys
import sqlite3 as sql
from os import system, name
class Database: # We create a class Database with two properties.
    def __init__(self, name):
        self.connection = sql.connect(name) # sql.connect returns a Connection object that represents the database.
        self.cursor = self.connection.cursor() # It creates a Cursor object using the cursor method of the Connection object.

    def __del__(self): # Our destructor is called when an object of the class gets destroyed.
        self.connection.commit() # It saves all the modifications made since the last commit. In other words, it commits any pending transaction to the database.
        self.connection.close() # It closes the SQLite connection. It is important to be sure that any changes have already been committed or they will be lost.

    def commit(self): # It saves or commits the changes to the database.
        self.connection.commit() # It saves all the modifications being made since the last commit. In other words, it commits any pending transaction to the database.

    def execute(self, sql, params=None): # It executes SQL commands.
        self.cursor.execute(sql, params or ()) # It executes an SQL statement.

    def query(self, sql, params=None): # It queries our database and retrieves a set of rows.
        self.cursor.execute(sql, params or ()) # It executes an SQL statement.
        return self.fetchall() # It retrieves the query's result.

    def fetchall(self): # It retrieves the query's result.
        return self.cursor.fetchall() # It fetches all rows of a query result.

class TextExpander:
    def __init__(self, database):
        self.db = Database(database) # db is an object or instance of our Database class.
        sql = "CREATE TABLE IF NOT EXISTS snippets(trigger TEXT PRIMARY KEY, text TEXT)" # We create the table snippets if and only if does not exist. It has two fields: trigger (name) and text (Lindsey Williams). Each value stored in an SQLite database belongs to one of the following storage classes: NULL, INTEGER, REAL, TEXT (it defines a text string), and BLOG.
        self.execute(sql) # It executes the SQL command and creates the table.

    def execute(self, query, params=None): # It executes SQL commands in our Database's object.
        try:
            self.db.execute(query, params or None)
            self.db.commit() # We commit after every insert or update.
        except Exception as err:
            print('Query Failed: %s\\nError: %s' % (query, str(err))) 
    
    def query(self, query, params=None): # It queries our Database's object.
        try:
            results = self.db.query(query, params or None)
            return results
        except Exception as err:
            print('Query Failed: %s\\nError: %s' % (query, str(err))) 
    
    def replaceTrigger(self, myTrigger): # It takes the trigger as an argument and returns its predefined text.
        trigger = (myTrigger, )
        sql = "SELECT text FROM snippets WHERE trigger=?"
        results = self.query(sql, trigger)
        for r in results:
            print(r[0])

    def allSnippets(self): # It prints all your text expansion shortcuts.
        sql = "SELECT trigger, text FROM snippets"
        results = self.query(sql)
        for r in results:
            print(r[0], ":", r[1])

    def insertSnippets(self, myTrigger, myText):  # It adds a snippet or updates it.
        sql = "INSERT OR REPLACE INTO snippets ( trigger, text ) VALUES (?, ?)"
        newTrigger = ( myTrigger, myText )
        results = self.execute(sql, newTrigger)

user@pc:~$ python Python 3.9.5 (default, May 11 2021, 08:20:37) [GCC 10.3.0] on linux Type "help", "copyright", "credits" or "license" for more information. 
>>> from faker import Faker 
>>> fake = Faker() 
>>> fake.name() 'Lindsey Williams' 
>>> fake.address() '3346 Jeffrey Bypass\\nNorth Michael, MT 79279' 
>>> fake.sentence() 'Show summer now stuff total ready.'

if __name__ == "__main__":
    clear()
    tx = TextExpander('/home/nmaximo7/myPython/snippets.db')
    tx.insertSnippets("address", "3346 Jeffrey Bypass\\nNorth Michael, MT 79279")
    tx.insertSnippets("name", "Lindsey Williams")
    tx.insertSnippets("website", "http://justtothepoint.com")
    tx.insertSnippets("sentence", "Show summer now stuff total ready")
    tx.replaceTrigger(sys.argv[1]) # sys.argv is a list which contains the command-line arguments passed to the script. sys.argv[0] is the name of the script. sys.argv[0] is the first argument passed to the script.
    tx.replaceTrigger("name")
    tx.replaceTrigger("city") # This is about testing unexpected inputs.
    tx.allSnippets()

{    
    "version": "0.2.0",   
     "configurations": [       
        {         
             "name": "Python: Current File",         
             "type": "python",          
            "request": "launch",         
             "program": "${file}",        
             "args": ["address"], // We are going to pass "address" as the first and only argument to the script.          
             "console": "integratedTerminal"     
          }    
    ] 
}

user@pc:~$ sqlite3 snippets.db # It connects to the SQLite database. SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints. # It will show all available commands. 
sqlite> .databases # It shows all the databases of the current connection. 
main: /home/nmaximo7/myPython/snippets.db r/w 
sqlite> .tables # It displays all the tables in the current database. 
snippets 
sqlite> select * from snippets; # Query all the rows from the table snippets.
address|3346 Jeffrey Bypass North Michael, MT 79279 name|Lindsey Williams website|http://justtothepoint.com sentence|Show summer now stuff total ready 
sqlite> .schema snippets # The ".schema" command shows the structure of a table. 
CREATE TABLE snippets(trigger VARCHAR PRIMARY KEY, text VARCHAR); 
sqlite> .quit # Exit sqlite3. Ctrl + D also works.

def clear(): # Clear screen in Python, [Geeks for Geeks](https://www.geeksforgeeks.org/clear-screen-python/).
    # for windows
    if name == 'nt':
        response = system('cls')
    # os.name is 'posix' for macOS and Linux
    else:
        response = system('clear')

class TextExpander:
    def __init__(self, database):
        self.db = Database(database) # db is an object or instance of our Database class.
        sql = """CREATE TABLE IF NOT EXISTS snippets(trigger TEXT PRIMARY KEY, 
        text TEXT)"""  # We create the table snippets if and only if does not exist.
        self.execute(sql) # It executes the SQL command and creates the table.
        sql2 = """CREATE TABLE IF NOT EXISTS textalt (id integer PRIMARY KEY AUTOINCREMENT, 
        optionstext TEXT UNIQUE, trigger TEXT NOT NULL,
	    FOREIGN KEY (trigger) REFERENCES snippets (trigger))"""
        self.execute(sql2)

[...]
    def replaceTrigger2(self, myTrigger):
        trigger = (myTrigger, )
        sql = """SELECT snippets.text, optionstext FROM snippets INNER JOIN 
        textalt on textalt.trigger = snippets.trigger WHERE snippets.trigger = ?;"""

        results =  self.query(sql, trigger)
        if len(results)==0: # If the query brings no result, the trigger does not have any optionstext linked to it, we will use the old replaceTrigger method.
            self.replaceTrigger(myTrigger)
        else: # Otherwise, we will print the snippets.text and one optionstext randomly chosen from the query's result, so we can add an extra, random text to personalize the trigger.
            result = random.choice(results)
            print(result[0] + " " + result[1])

    def insertSnippetsAltTexts(self, myTrigger, altTexts):
        for altText in altTexts:
            sql = "INSERT OR REPLACE INTO textalt ( optionstext, trigger ) VALUES (?, ?)"
            newTextAlt = ( altText, myTrigger )
            results = self.execute(sql, newTextAlt)
        
if __name__ == "__main__":
    clear()
    tx = TextExpander('/home/nmaximo7/myPython/snippets.db')
    tx.insertSnippets("address", "3346 Jeffrey Bypass\\nNorth Michael, MT 79279")
    tx.insertSnippets("name", "Lindsey Williams")
    tx.insertSnippets("website", "http://justtothepoint.com")
    tx.insertSnippets("gn", "Good night my honey")
    tx.insertSnippetsAltTexts("gn", ["(♥_♥)", "Love all, trust a few, do wrong to none.", "We can only learn to love by loving"]) # You may want to visit [1 Line Art](https://1lineart.kulaone.com/#/) or [ASCII Art Archive](https://www.asciiart.eu/) for ideas.
    tx.replaceTrigger(sys.argv[1])
    tx.replaceTrigger("name")
    tx.replaceTrigger("city") 
    tx.replaceTrigger2("name")
    tx.replaceTrigger2("gn")

user@pc:~$ python Python 3.9.5 (default, May 11 2021, 08:20:37) [GCC 10.3.0] on linux Type "help", "copyright", "credits" or "license" for more information. 
>>> import sqlalchemy 
>>> sqlalchemy.__version__ # Let's verify whether the installation was successful or not. '1.4.26'

from os import system, name
from sqlalchemy import create_engine, exc
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, ForeignKey
from sqlalchemy import Table
from sqlalchemy.orm import sessionmaker, relationship, backref
import random

Base = declarative_base()
# Let's define our class Snippet. Within the class, we need to define details about the table to which we'll be mapping...      
class Snippet(Base):
    __tablename__ = 'snippets' # ... mainly, the table name, 
    id = Column(Integer, primary_key=True) # and all the names and datatypes of columns. We use Column to define a column. The first column is "id". It is an integer and it is also a primary_key.
    trigger = Column(String, unique=True) # Observe that the types of the Column are the first argument to Column. The most common types are: Integer, String, Text, DateTime, Float, Boolean, and PickleType. unique=True allows us to define unique constraints. 
    text = Column(String)
    textalts = relationship("TextAlt", backref=backref("snippets"))

    def __init__(self, trigger, text):
        self.trigger = trigger
        self.text = text
    
class TextAlt(Base):
    __tablename__ = 'textalt'
    id = Column(Integer, primary_key=True)
    optionstext = Column(String, unique=True)
    snippet_id = Column(Integer, ForeignKey("snippets.id"))

    def __init__(self, optionstext, snippet_id):
        self.optionstext = optionstext
        self.snippet_id = snippet_id

class TextExpander():
    def __init__(self):
        db_uri = "sqlite:///snippets2.db"
        engine = create_engine(db_uri) # We need to connect to our database. create_engine() produces an Engine object based on a URL. The Engine is the starting point for any SQLAlchemy application.
        Base.metadata.create_all(engine) # It creates all tables that they did not exist until now.
        Session = sessionmaker(bind=engine) # It creates a Session class, a kind of factory for new Session objects. All modifications tracked by Sessions will be applied to the underlying database together or none of them will. In other words, Sessions guarantee database consistency.
        self.session = Session() # It creates a session. It is our database handler.
        
    def addSnippet(self, trigger, text): # It adds new snippets.
        snippet = Snippet(trigger, text) # We create an instance of our class Snippet.
        self.session.add(snippet) # To persist our Snippet object, we use the method add from the Session class. The modification is pending and no SQL has yet been issued, so the object is still not a row in the database.
        try:
            self.session.commit() # We commit our change to the database.
        except exc.SQLAlchemyError as e:
            self.session.rollback() # If an exception is raised, we roll back this change to our database. 

    def getIdSnippet(self, trigger): # It gets the id (primary_key) from the table "snippet" 
        query = self.session.query(Snippet).filter(Snippet.trigger==trigger) # We create a new Query object which loads instances of Snippet and filter by the "trigger" attribute. Observe that trigger was defined as unique.
        instance = query.first() # We are only interested in the first result. 
        return instance.id

    def addTextAlt(self, trigger, optionstext): # It adds new TextAlt rows.
        textalt = TextAlt(optionstext, self.getIdSnippet(trigger)) # We create an instance of our class TextAlt. We use getIdSnippet() to get the id (primary key) of trigger.
        self.session.add(textalt)
        try:
            self.session.commit()
        except exc.SQLAlchemyError as e:
            self.session.rollback()

    def getSnippet(self, trigger): # It takes the trigger as an argument and returns its predefined text, and if it was added by the user, additional text.
        query = self.session.query(Snippet).filter(Snippet.trigger==trigger) # We create a new Query object which loads instances of Snippet and filter by the "trigger" attribute.
        instance = query.first() # We are only interested in the first result. 
        if len(instance.textalts)==0: # It is important to understand that the column textalts is filled automatically by SQLAlchemy and it may contain zero or more instances of rows of the "textalt" table  
            print(instance.text) # We print the snippet's predefined text.
        else: 
            textalt = random.choice(instance.textalts)  
            print(instance.text + " " + textalt.optionstext) # We print the snippet's predefined text and one random instance (row) of its textalts. We really only display its optiontext property (field). 

if __name__ == "__main__": 
    myTextExpander = TextExpander()
    myTextExpander.addSnippet(":h", "Hello World!")
    myTextExpander.addSnippet(":gn", "Good night.")
    myTextExpander.addTextAlt(":gn", "Sweet dreams my angel!")
    myTextExpander.addTextAlt(":gn", "Love all, trust a few, do wrong to none.")
    myTextExpander.addTextAlt(":gn", "We can only learn to love by loving")
    myTextExpander.addTextAlt(":gn", "(?_?)")
    myTextExpander.addSnippet(":gm", "Good morning.")
    myTextExpander.addTextAlt(":gm", "Rise and Shine!")
    myTextExpander.addTextAlt(":gm", "We rise by lifting others.")
    myTextExpander.addTextAlt(":gm", "Rise and shine pretty girl.")
    myTextExpander.getSnippet(":h")
    myTextExpander.getSnippet(":gn")
    myTextExpander.getSnippet(":gm")