import sqlparse
import os
from dotenv import load_dotenv


load_dotenv()
infamousDir = os.getenv('INFAMOUSDIR')

docSrcPath = os.path.join(infamousDir, 'projects-cea', 'infamous-docs-cea', 'content')

def init_file():
    with open(os.path.join(docSrcPath, 'sql.md'), 'w') as file:
        file.write('# The SQL snippits \n\n')
        #file.write('!!! info \n')
        file.write("***This page is auto-generated with the content of the [sql-snipit](https://github.com/SME-BUS/cea-infamous/tree/main/sql-snipit) directory***   \n")
        file.write("*Everyone is encouraged to contribute and share queries: see the [Contribute]() section* \n\n")


def fill_file():
    for filename in sorted(os.listdir(os.path.join(infamousDir, 'sql-snipit'))):
        if filename.endswith('.sql'):
            print(filename)
            with open(os.path.join(infamousDir, 'sql-snipit', filename), 'r') as file:
                sql_file = file.read()
            #parsed_sql = sqlparse.format(sql_file, reindent=True, keyword_case='upper')
            header, query = sql_file.split('*/', 1)
            header = header[header.index('/*')+2:]
    
            lines = header.split('\n')
            params_index = next((i for i, line in enumerate(lines) if line.startswith('Params:')), None)
            if params_index:
                header = '\n'.join(lines[:params_index])
                parameters = '\n'.join(lines[params_index+1:])
    
            with open(os.path.join(docSrcPath, 'sql.md'), 'a') as file:
                file.write(f'##***{filename}***  \n')
                for line in header.split('\n'):
                    if len(line) > 0:
                        if line[0].isupper() or not line[0].isalpha():
                            file.write('    \n' + line)
                        else:
                            file.write(' '+line)
                    else:
                        file.write('    \n')
                file.write('\n')
                if params_index:
                    file.write('??? tip "parameters"\n')
                    for line in parameters.split('\n'):
                        file.write('\t' + line + '  \n')
                file.write('\n')
                file.write('??? code "sql"\n')
                file.write('\t```sql\n')
                for line in query.split('\n'):
                    file.write('\t' + line + '\n')
                file.write('\t```\n\n')


if __name__ == '__main__':
    init_file()
    fill_file()