import psycopg2
import psycopg2.extras

<%# https://fastapi.tiangolo.com/reference/status/ -%>
from fastapi import APIRouter, Depends, HTTPException, status

<%# LATER: add only when POST/PUT endpoints are present -%>
from pydantic import BaseModel

<%# LATER: add only when POST/PUT endpoints are present -%>
from typing import Optional

from db import db_connection

router = APIRouter()
<%
// { "get", "/v1/categories/:categoryId" } => "get_v1_categories_category_id"
function generate_method_name(method, path) {
    const name = camel2snakeCase(path).replace(/\//g, '_').replace(/[^_a-z0-9]/g, '')
    return `${method}${name}`
}

// "INSERT INTO ... VALUES(:categoryId)" => "INSERT INTO ... VALUES(%(categoryId)s)"
// See: https://www.psycopg.org/docs/usage.html#passing-parameters-to-sql-queries
function convertToPsycopgNamedArguments(sql) {
    return sql.replace(/(?<!:):([_a-zA-Z]+)/g, '%($1)s')
}

// "/categories/:categoryId" => "/categories/{categoryId}"
function convertToFastApiPath(path) {
    return path.replace(/:([_a-zA-Z]+)/g, '{$1}')
}

// LATER: reduce duplication with routes.go.ejs
// {'values':
//   [
//     {
//       type: 'expr_list',
//       value: [ { type: 'param', value: 'user_id' } ]
//     }
//   ]
// } => [ 'user_id' ]
function extractInsertValues(queryAst) {
	const values = queryAst.values.flatMap(elem => elem.value)
		.map(elem => elem.type === 'param' ? elem.value : null)
		.filter(elem => elem) // filter out nulls
	return Array.from(new Set(values))
}

// LATER: reduce duplication with routes.go.ejs
// {'set':
//   [
//     {
//       column: 'updated_by',
//       value: { type: 'param', value: 'user_id' },
//       table: null
//     }
//   ]
// } => [ 'user_id' ]
function extractUpdateValues(queryAst) {
	// LATER: distinguish between b.param and q.param and extract only first
	return queryAst.set.map(elem => elem.value.type === 'param' ? elem.value.value : null)
		.filter(value => value) // filter out nulls
}

// LATER: reduce duplication with routes.go.ejs
function extractProperties(queryAst) {
	if (queryAst.type === 'insert') {
		return extractInsertValues(queryAst)
	}
    if (queryAst.type === 'update') {
		return extractUpdateValues(queryAst)
	}
	return []
}

function findOutType(fieldsInfo, fieldName) {
	const fieldType = retrieveType(fieldsInfo, fieldName)
	if (fieldType === 'integer') {
		return 'int'
	}
	if (fieldType === 'boolean') {
		return 'bool'
	}
	return 'str'
}

// "q.title"  => "q.title: str"
// "q.active" => "q.active: bool"
// "q.age"    => "q.age: int"
// "p.id"     => "p.id"
// "b.name"   => "b.name"
function appendVariableTypeToQueryParam(paramsInfo, varName) {
    if (varName.startsWith('q.')) {
        return `${varName}: ${findOutType(paramsInfo, stipOurPrefixes(varName))}`
    }
    return varName
}

// LATER: reduce duplication with routes.go.ejs
function addTypes(props, fieldsInfo) {
	return props.map(prop => {
		return {
			"name": prop,
			"type": findOutType(fieldsInfo, prop),
		}
	})
}

// LATER: reduce duplication with routes.go.ejs
function query2dto(parser, method) {
	const query = removePlaceholders(method.query)
	const queryAst = parser.astify(query)
	const props = extractProperties(queryAst)
	if (props.length === 0) {
		console.warn('Could not create DTO for query:', formatQueryAsSingleLine(query))
		console.debug('Query AST:')
		console.debug(queryAst)
		return null
	}
	const fieldsInfo = method.dto && method.dto.fields ? method.dto.fields : {}
	const propsWithTypes = addTypes(props, fieldsInfo)
	const hasName = method.dto && method.dto.name && method.dto.name.length > 0
	const name = hasName ? method.dto.name : "Dto" + ++globalDtoCounter
	return {
		"name": name,
		"hasUserProvidedName": hasName,
		"props": propsWithTypes,
		// required for de-duplication
		// [ {name:foo, type:int}, {name:bar, type:string} ] => "foo=int bar=string"
		// LATER: sort before join
		"signature": propsWithTypes.map(field => `${field.name}=${field.type}`).join(' ')
	}
}

// https://fastapi.tiangolo.com/tutorial/body/
function dto2model(dto) {
	let result = `class ${dto.name}(BaseModel):\n`
	dto.props.forEach(prop => {
		result += `    ${prop.name}: Optional[${prop.type}] = None\n`
	})
	return result
}

let globalDtoCounter = 0
const dtoCache = {}
const namedDtoCache = {}

// LATER: reduce duplication with routes.go.ejs
function cacheDto(dto) {
	if (dto.hasUserProvidedName) {
		namedDtoCache[dto.signature] = dto.name
	} else {
		dtoCache[dto.signature] = dto.name
	}
	return dto
}

// LATER: reduce duplication with routes.go.ejs
function dtoInCache(dto) {
	const existsNamed = namedDtoCache.hasOwnProperty(dto.signature)
	// always prefer user specified name even when we have a similar DTO in cache for generated names
	if (dto.hasUserProvidedName) {
		return existsNamed
	}
	// prefer to re-use named DTO
	return existsNamed || dtoCache.hasOwnProperty(dto.signature)
}

function obtainDtoName(dto) {
    const cacheKey = dto.signature
	return namedDtoCache.hasOwnProperty(cacheKey) ? namedDtoCache[cacheKey] : dto.name
}

// Generate models
const verbs_with_dto = [ 'post', 'put' ]
endpoints.forEach(function(endpoint) {
	const dtos = endpoint.methods
		.filter(method => verbs_with_dto.includes(method.verb))
		.map(method => query2dto(sqlParser, method))
		.filter(elem => elem) // filter out nulls
		.filter(dto => !dtoInCache(dto))
		.map(dto => dto2model(cacheDto(dto)))
		.forEach(model => {
%>
<%- model -%>
<%
	})
})

// Generate endpoints
endpoints.forEach(function(endpoint) {
    const path = convertToFastApiPath(endpoint.path)
    const argsFromPath = extractParamsFromPath(endpoint.path)

    endpoint.methods.forEach(function(method) {
        const hasGetOne = method.name === 'get'
        const hasGetMany = method.name === 'get_list'
        const pythonMethodName = generate_method_name(method.name, path)

        // LATER: add support for aggregated_queries (#17)
        const queryParamsInfo = method.params && method.params.query ? method.params.query : {}
        const argsFromQuery = method.query ? extractParamsFromQuery(method.query).map(param => appendVariableTypeToQueryParam(queryParamsInfo, param)).map(stipOurPrefixes) : []

        // define before "if", to make them available later
        let methodArgs
        let sql
        let formattedParams
        let model
        if (method.name === 'post' || method.name === 'put' || method.name === 'delete') {
            sql = convertToPsycopgNamedArguments(formatQueryForPython(method.query, 20))
            const params = extractParamsFromQuery(method.query)
            formattedParams = formatParamsAsPythonDict(params)

            if (method.name === 'post' || method.name === 'put') {
                const dto = query2dto(sqlParser, method)
                // LATER: do we really need signature and cache?
                model = obtainDtoName(dto)
                methodArgs = [ `body: ${model}`, ...argsFromPath, 'conn=Depends(db_connection)' ]

            } else if (method.name === 'delete') {
                methodArgs = [ ...argsFromPath, 'conn=Depends(db_connection)' ]
            }
        }

        if (hasGetOne || hasGetMany) {
            methodArgs = Array.from(new Set([...argsFromPath, ...argsFromQuery, 'conn=Depends(db_connection)']))

            const queriesWithNames = []
            if (method.query) {
                queriesWithNames.push({ "result" : method.query })
            } else if (method.aggregated_queries) {
                for (const [key, value] of Object.entries(method.aggregated_queries)) {
                    queriesWithNames.push({ [key]: value })
                }
            }

            const queries = []
            queriesWithNames.forEach(queryWithName => {
                for (const [name, query] of Object.entries(queryWithName)) {
                    const sql = convertToPsycopgNamedArguments(formatQueryForPython(query, 20))
                    const params = extractParamsFromQuery(query)
                    const formattedParams = formatParamsAsPythonDict(params)
                    queries.push({ [name]: { sql : sql, formattedParams: formattedParams }})
                }
            })
%>

@router.get('<%- path %>')
def <%- pythonMethodName %>(<%- methodArgs.join(', ') %>):
    try:
<%#
    https://www.psycopg.org/docs/usage.html#with-statement
    https://www.psycopg.org/docs/extras.html#dictionary-like-cursor
    https://stackoverflow.com/questions/45399347/dictcursor-vs-realdictcursor
-%>
        with conn:
<%          if (hasGetOne && queries.length > 1) { /* we can omit cursor_factory but in this case we might get an unused import  */-%>
            with conn.cursor(cursor_factory=psycopg2.extras.DictCursor) as cur:
                result = {}
<%              queries.forEach(queryInfo => {
                    for (const [name, query] of Object.entries(queryInfo)) {
-%>
                cur.execute(<%- query.sql %><%- query.formattedParams %>)
                result['<%- name %>'] = cur.fetchone()[0]
<%                      }
                })
-%>
                return result
<%
            } else {
                const query = queries[0].result
-%>
            with conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor) as cur:
                cur.execute(<%- query.sql %><%- query.formattedParams %>)
<%              if (hasGetMany) { -%>
                return cur.fetchall()
<%              } else { /* GET with a single result */ -%>
                result = cur.fetchone()
                if result is None:
                    raise HTTPException(status_code=status.HTTP_404_NOT_FOUND)
                return result
<%
                }
            }
-%>
    finally:
        conn.close()
<%
        }
        if (method.name === 'post') {
%>

@router.post('<%- path %>', status_code=status.HTTP_204_NO_CONTENT)
def <%- pythonMethodName %>(<%- methodArgs.join(', ') %>):
    try:
        with conn:
            with conn.cursor() as cur:
                cur.execute(<%- sql %><%- formattedParams %>)
    finally:
        conn.close()
<%

        }
        if (method.name === 'put') {
%>

@router.put('<%- path %>', status_code=status.HTTP_204_NO_CONTENT)
def <%- pythonMethodName %>(<%- methodArgs.join(', ') %>):
    try:
        with conn:
            with conn.cursor() as cur:
                cur.execute(<%- sql %><%- formattedParams %>)
    finally:
        conn.close()
<%

        }
        if (method.name === 'delete') {
%>

@router.delete('<%- path %>', status_code=status.HTTP_204_NO_CONTENT)
def <%- pythonMethodName %>(<%- methodArgs.join(', ') %>):
    try:
        with conn:
            with conn.cursor() as cur:
                cur.execute(<%- sql %><%- formattedParams %>)
    finally:
        conn.close()
<%

        }
    })
})
%>