use crate::ast_nav;
use rowan::TextSize;
use smallvec::{SmallVec, smallvec};
use squawk_syntax::{
    SyntaxKind, SyntaxNode, SyntaxNodePtr,
    ast::{self, AstNode},
};

use crate::builtins::builtins_file;
use crate::column_name::ColumnName;
use crate::db::File;
use crate::location::{Location, LocationKind};
use crate::name::{self, Name, Schema};
use crate::symbols::SymbolKind;
use crate::{
    classify::{NameRefClass, classify_name_ref},
    db::{bind, parse},
};
use salsa::Database as Db;

/// Resolves a name reference to its definition(s).
///
/// Most of the time returns one result, but can return two definitions
/// in the case of:
///
/// ```sql
/// select * from t join u using (col);
/// ```
///
/// since `col` is defined in both `t` and `u`.
pub(crate) fn resolve_name_ref(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let binder = bind(db, file);
    let context = classify_name_ref(name_ref.syntax())?;

    match context {
        NameRefClass::Table => {
            let (schema, table_name) = name::schema_and_table_name(name_ref)?;
            let position = name_ref.syntax().text_range().start();

            if schema.is_none()
                && let Some(cte_ptr) = resolve_cte_table(name_ref, &table_name)
            {
                return Some(smallvec![Location::new(
                    file,
                    cte_ptr.text_range(),
                    LocationKind::Table
                )]);
            }

            let ptr = resolve_table_name_ptr(db, file, &table_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Table
            )])
        }
        NameRefClass::InsertTable => {
            let (schema, relation_name) = name::schema_and_table_name(name_ref)?;
            let position = name_ref.syntax().text_range().start();

            if let Some(ptr) =
                resolve_table_name_ptr(db, file, &relation_name, schema.as_ref(), position)
            {
                return Some(smallvec![Location::new(
                    file,
                    ptr.text_range(),
                    LocationKind::Table
                )]);
            }

            let ptr = resolve_view_name_ptr(db, file, &relation_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::View
            )])
        }
        NameRefClass::NamedArgParameter => {
            let (schema, function_name) = name_ref.syntax().ancestors().find_map(|a| {
                if let Some(call_expr) = ast::CallExpr::cast(a.clone()) {
                    name::schema_and_func_name(&call_expr)
                } else if let Some(call) = ast::Call::cast(a) {
                    name::schema_and_name_path(&call.path()?)
                } else {
                    None
                }
            })?;
            let param_name = Name::from_node(name_ref);
            let position = name_ref.syntax().text_range().start();

            // TODO: this should be one lookup
            let function_ptr = binder
                .lookup_with(
                    &function_name,
                    SymbolKind::Function,
                    position,
                    schema.as_ref(),
                )
                .or_else(|| {
                    binder.lookup_with(
                        &function_name,
                        SymbolKind::Procedure,
                        position,
                        schema.as_ref(),
                    )
                })
                .or_else(|| {
                    binder.lookup_with(
                        &function_name,
                        SymbolKind::Aggregate,
                        position,
                        schema.as_ref(),
                    )
                })?;

            let param_ptr = find_param_in_func_def(db, file, function_ptr, &param_name)?;
            Some(smallvec![Location::new(
                file,
                param_ptr.text_range(),
                LocationKind::NamedArgParameter
            )])
        }
        NameRefClass::Cursor => {
            let cursor_name = &Name::from_node(name_ref);
            let ptr = binder.lookup(cursor_name, SymbolKind::Cursor)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Cursor
            )])
        }
        NameRefClass::PreparedStatement => {
            let statement_name = &Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(statement_name, SymbolKind::PreparedStatement)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::PreparedStatement
            )])
        }
        NameRefClass::Channel => {
            let channel_name = &Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(channel_name, SymbolKind::Channel)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Channel
            )])
        }
        NameRefClass::FromTable => {
            let (schema, table_name) = name::schema_and_name(name_ref);
            let position = name_ref.syntax().text_range().start();
            let (ptr, kind) = resolve_table_like(
                db,
                file,
                Some(name_ref),
                &table_name,
                schema.as_ref(),
                position,
            )?;
            Some(smallvec![Location::new(file, ptr.text_range(), kind)])
        }
        NameRefClass::Index => {
            let position = name_ref.syntax().text_range().start();
            let index_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup_with(&index_name, SymbolKind::Index, position, None)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Index
            )])
        }
        NameRefClass::Type => {
            let (schema, type_name) = name::schema_and_table_name(name_ref)?;
            let type_name = resolve_float_precision(name_ref, type_name);
            let position = name_ref.syntax().text_range().start();
            let ptr = resolve_type_name_ptr(db, file, &type_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Type
            )])
        }
        NameRefClass::View => {
            let (schema, view_name) = name::schema_and_table_name(name_ref)?;
            let position = name_ref.syntax().text_range().start();
            let ptr = resolve_view_name_ptr(db, file, &view_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::View
            )])
        }
        NameRefClass::Window => {
            let window_name = Name::from_node(name_ref);
            let select = name_ref.syntax().ancestors().find_map(ast::Select::cast)?;
            for window_def in select.window_clause()?.window_defs() {
                if let Some(name) = window_def.name()
                    && Name::from_node(&name) == window_name
                {
                    return Some(smallvec![Location::new(
                        file,
                        name.syntax().text_range(),
                        LocationKind::Window
                    )]);
                }
            }
            None
        }
        NameRefClass::Sequence => {
            let (schema, sequence_name) = name::schema_and_table_name(name_ref)?;
            let position = name_ref.syntax().text_range().start();
            let binder = bind(db, file);
            let ptr = binder.lookup_with(
                &sequence_name,
                SymbolKind::Sequence,
                position,
                schema.as_ref(),
            )?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Sequence
            )])
        }
        NameRefClass::Trigger => {
            let drop_trigger = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::DropTrigger::cast)?;
            let path = drop_trigger.path()?;
            let (mut schema, trigger_name) = name::schema_and_name_path(&path)?;
            let on_table_path = drop_trigger
                .on_table()
                .and_then(|on_table| on_table.path())?;
            if schema.is_none() {
                schema = name::schema_name(&on_table_path);
            }
            let table_name = name::table_name(&on_table_path)?;
            let position = name_ref.syntax().text_range().start();
            let binder = bind(db, file);
            let ptr = binder.lookup_with_table(
                &trigger_name,
                SymbolKind::Trigger,
                position,
                schema.as_ref(),
                &Some(table_name),
            )?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Trigger
            )])
        }
        NameRefClass::Policy => {
            let (policy_name, on_table) = name_ref.syntax().ancestors().find_map(|a| {
                if let Some(policy) = ast::DropPolicy::cast(a.clone()) {
                    Some((policy.name_ref(), policy.on_table()))
                } else {
                    ast::AlterPolicy::cast(a).map(|policy| (policy.name_ref(), policy.on_table()))
                }
            })?;
            let policy_name = Name::from_node(&policy_name?);
            let on_table_path = on_table.and_then(|on_table| on_table.path())?;
            let (schema, table_name) = name::schema_and_name_path(&on_table_path)?;
            let position = name_ref.syntax().text_range().start();
            let binder = bind(db, file);
            let ptr = binder.lookup_with_table(
                &policy_name,
                SymbolKind::Policy,
                position,
                schema.as_ref(),
                &Some(table_name),
            )?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Policy
            )])
        }
        NameRefClass::EventTrigger => {
            let event_trigger_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(&event_trigger_name, SymbolKind::EventTrigger)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::EventTrigger
            )])
        }
        NameRefClass::PropertyGraph => {
            let path = name_ref.syntax().ancestors().find_map(ast::Path::cast)?;
            let (schema, property_graph_name) = name::schema_and_name_path(&path)?;
            let position = name_ref.syntax().text_range().start();
            let binder = bind(db, file);
            let ptr = binder.lookup_with(
                &property_graph_name,
                SymbolKind::PropertyGraph,
                position,
                schema.as_ref(),
            )?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::PropertyGraph
            )])
        }
        NameRefClass::Database => {
            let database_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(&database_name, SymbolKind::Database)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Database
            )])
        }
        NameRefClass::Server => {
            let server_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(&server_name, SymbolKind::Server)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Server
            )])
        }
        NameRefClass::Extension => {
            let extension_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(&extension_name, SymbolKind::Extension)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Extension
            )])
        }
        NameRefClass::Role => {
            let role_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(&role_name, SymbolKind::Role)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Role
            )])
        }
        NameRefClass::QualifiedColumn => {
            let path = name_ref.syntax().ancestors().find_map(ast::Path::cast)?;
            let column_name = Name::from_node(name_ref);
            let table_path = path.qualifier()?;
            resolve_column_for_path(db, file, &table_path, column_name)
        }
        NameRefClass::Tablespace => {
            let tablespace_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(&tablespace_name, SymbolKind::Tablespace)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Tablespace
            )])
        }
        NameRefClass::ForeignKeyTable => {
            let path = name_ref.syntax().ancestors().find_map(ast::Path::cast)?;
            let (schema, table_name) = name::schema_and_name_path(&path)?;
            let position = name_ref.syntax().text_range().start();
            let ptr = resolve_table_name_ptr(db, file, &table_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Table
            )])
        }
        NameRefClass::ForeignKeyColumn => {
            // TODO: the ast is too flat here
            let path = if let Some(foreign_key) = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::ForeignKeyConstraint::cast)
            {
                foreign_key.path()?
            } else if let Some(references_constraint) = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::ReferencesConstraint::cast)
            {
                references_constraint.table()?
            } else {
                return None;
            };
            let column_name = Name::from_node(name_ref);
            resolve_column_for_path(db, file, &path, column_name)
        }
        NameRefClass::ConstraintColumn => {
            let column_name = Name::from_node(name_ref);
            for ancestor in name_ref.syntax().ancestors() {
                if let Some(create_table) = ast::CreateTableLike::cast(ancestor.clone()) {
                    return find_column_in_create_table(db, file, &create_table, &column_name);
                }
                if let Some(alter_table) = ast::AlterTable::cast(ancestor) {
                    let table_path = alter_table.relation_name()?.path()?;
                    return resolve_column_for_path(db, file, &table_path, column_name);
                }
            }
            None
        }
        NameRefClass::PolicyColumn => {
            let on_table_path = name_ref.syntax().ancestors().find_map(|n| {
                if let Some(create_policy) = ast::CreatePolicy::cast(n.clone()) {
                    create_policy.on_table()?.path()
                } else if let Some(alter_policy) = ast::AlterPolicy::cast(n) {
                    alter_policy.on_table()?.path()
                } else {
                    None
                }
            })?;
            let column_name = Name::from_node(name_ref);
            resolve_column_for_path(db, file, &on_table_path, column_name)
        }
        NameRefClass::PolicyQualifiedColumnTable => {
            let on_table_path = name_ref.syntax().ancestors().find_map(|n| {
                if let Some(create_policy) = ast::CreatePolicy::cast(n.clone()) {
                    create_policy.on_table()?.path()
                } else if let Some(alter_policy) = ast::AlterPolicy::cast(n) {
                    alter_policy.on_table()?.path()
                } else {
                    None
                }
            })?;
            let (schema, table_name) = name::schema_and_name_path(&on_table_path)?;
            let position = name_ref.syntax().text_range().start();
            let ptr = resolve_table_name_ptr(db, file, &table_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Table
            )])
        }
        NameRefClass::LikeTable => {
            let like_clause = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::LikeClause::cast)?;
            let path = like_clause.path()?;
            let (schema, table_name) = name::schema_and_name_path(&path)?;
            let position = name_ref.syntax().text_range().start();
            if let Some(ptr) =
                resolve_table_name_ptr(db, file, &table_name, schema.as_ref(), position)
            {
                return Some(smallvec![Location::new(
                    file,
                    ptr.text_range(),
                    LocationKind::Table
                )]);
            }
            if let Some(ptr) =
                resolve_view_name_ptr(db, file, &table_name, schema.as_ref(), position)
            {
                return Some(smallvec![Location::new(
                    file,
                    ptr.text_range(),
                    LocationKind::View
                )]);
            }
            None
        }
        NameRefClass::Function => {
            let function_sig = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::FunctionSig::cast)?;
            let path = function_sig.path()?;
            let (schema, function_name) = name::schema_and_name_path(&path)?;
            let params = param_signature(&function_sig);
            let position = name_ref.syntax().text_range().start();
            resolve_function(
                db,
                file,
                &function_name,
                schema.as_ref(),
                params.as_deref(),
                position,
            )
        }
        NameRefClass::Aggregate => {
            let aggregate = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::Aggregate::cast)?;
            let path = aggregate.path()?;
            let (schema, aggregate_name) = name::schema_and_name_path(&path)?;
            let params = param_signature(&aggregate);
            let position = name_ref.syntax().text_range().start();
            resolve_aggregate(
                db,
                file,
                &aggregate_name,
                schema.as_ref(),
                params.as_deref(),
                position,
            )
        }
        NameRefClass::Procedure => {
            let function_sig = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::FunctionSig::cast)?;
            let path = function_sig.path()?;
            let (schema, procedure_name) = name::schema_and_name_path(&path)?;
            let params = param_signature(&function_sig);
            let position = name_ref.syntax().text_range().start();
            resolve_procedure(
                db,
                file,
                &procedure_name,
                schema.as_ref(),
                params.as_deref(),
                position,
            )
        }
        NameRefClass::Routine => {
            let function_sig = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::FunctionSig::cast)?;
            let path = function_sig.path()?;
            let (schema, routine_name) = name::schema_and_name_path(&path)?;
            let params = param_signature(&function_sig);
            let position = name_ref.syntax().text_range().start();

            if let Some(results) = resolve_function(
                db,
                file,
                &routine_name,
                schema.as_ref(),
                params.as_deref(),
                position,
            ) {
                return Some(results);
            }

            if let Some(results) = resolve_aggregate(
                db,
                file,
                &routine_name,
                schema.as_ref(),
                params.as_deref(),
                position,
            ) {
                return Some(results);
            }

            resolve_procedure(
                db,
                file,
                &routine_name,
                schema.as_ref(),
                params.as_deref(),
                position,
            )
        }
        NameRefClass::CallProcedure => {
            let call = name_ref.syntax().ancestors().find_map(ast::Call::cast)?;
            let path = call.path()?;
            let (schema, procedure_name) = name::schema_and_name_path(&path)?;
            let position = name_ref.syntax().text_range().start();
            resolve_procedure(db, file, &procedure_name, schema.as_ref(), None, position)
        }
        NameRefClass::Schema => {
            let schema_name = Name::from_node(name_ref);
            let binder = bind(db, file);
            let ptr = binder.lookup(&schema_name, SymbolKind::Schema)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Schema
            )])
        }
        NameRefClass::FunctionCall => {
            let (schema, function_name) = name::schema_and_name(name_ref);
            let position = name_ref.syntax().text_range().start();
            resolve_function(db, file, &function_name, schema.as_ref(), None, position)
        }
        NameRefClass::ProcedureCall => {
            let (schema, procedure_name) = name::schema_and_name(name_ref);
            let position = name_ref.syntax().text_range().start();

            resolve_procedure(db, file, &procedure_name, schema.as_ref(), None, position)
        }
        NameRefClass::FunctionName => {
            let path_type = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::PathType::cast)?;
            let path = path_type.path()?;
            let (schema, function_name) = name::schema_and_name_path(&path)?;
            let position = name_ref.syntax().text_range().start();
            resolve_function(db, file, &function_name, schema.as_ref(), None, position)
        }
        NameRefClass::SelectFunctionCall => {
            let (schema, function_name) = name::schema_and_name(name_ref);
            let position = name_ref.syntax().text_range().start();

            // functions take precedence
            if let Some(results) =
                resolve_function(db, file, &function_name, schema.as_ref(), None, position)
            {
                return Some(results);
            }

            // aggregates take precedence over function-call-style column access
            if let Some(results) =
                resolve_aggregate(db, file, &function_name, schema.as_ref(), None, position)
            {
                return Some(results);
            }

            // if no function found, check if this is function-call-style column access
            // ```sql
            // create table t(a int, b int);
            // select a(t) from t;
            // ```
            if schema.is_none()
                && let Some(ptr) = resolve_fn_call_column(db, file, name_ref)
            {
                return Some(ptr);
            }

            None
        }
        NameRefClass::CreateIndexColumn => resolve_create_index_column_ptr(db, file, name_ref),
        NameRefClass::SelectColumn => resolve_select_column_ptr(db, file, name_ref),
        NameRefClass::SelectGroupByAliasOrColumn => {
            resolve_select_group_by_alias_or_column_ptr(db, file, name_ref)
        }
        NameRefClass::SelectOrderByAliasOrColumn => {
            resolve_select_order_by_alias_or_column_ptr(db, file, name_ref)
        }
        NameRefClass::SelectQualifiedColumnTable => {
            resolve_select_qualified_column_table_name_ptr(db, file, name_ref)
        }
        NameRefClass::SelectQualifiedColumn => {
            resolve_select_qualified_column_ptr(db, file, name_ref)
        }
        NameRefClass::CompositeTypeField => resolve_composite_type_field_ptr(db, file, name_ref),
        NameRefClass::InsertColumn => {
            let column_name = Name::from_node(name_ref);
            let insert = name_ref.syntax().ancestors().find_map(ast::Insert::cast)?;
            let path = insert.path()?;
            resolve_column_for_path(db, file, &path, column_name)
        }
        NameRefClass::InsertQualifiedColumnTable => {
            let insert = name_ref.syntax().ancestors().find_map(ast::Insert::cast)?;
            let path = insert.path()?;
            resolve_table_in_returning_clause(
                db,
                file,
                name_ref,
                insert.alias(),
                &path,
                insert.returning_clause(),
            )
        }
        NameRefClass::DeleteColumn => {
            let column_name = Name::from_node(name_ref);
            let delete = name_ref.syntax().ancestors().find_map(ast::Delete::cast)?;
            let path = delete.relation_name()?.path()?;
            resolve_column_for_path(db, file, &path, column_name)
        }
        NameRefClass::DeleteQualifiedColumnTable => {
            let delete = name_ref.syntax().ancestors().find_map(ast::Delete::cast)?;
            let path = delete.relation_name()?.path()?;
            resolve_table_in_returning_clause(
                db,
                file,
                name_ref,
                delete.alias(),
                &path,
                delete.returning_clause(),
            )
        }
        NameRefClass::UpdateColumn => {
            let column_name = Name::from_node(name_ref);
            let update = name_ref.syntax().ancestors().find_map(ast::Update::cast)?;
            // `update t set a = b from u`
            if let Some(from_clause) = update.from_clause() {
                for from_item in ast_nav::iter_from_clause(&from_clause) {
                    if let Some(result) =
                        resolve_from_item_column_ptr(db, file, &from_item, name_ref)
                    {
                        return Some(result);
                    }
                }
            }
            // `update t set a = b`
            let path = update.relation_name()?.path()?;
            resolve_column_for_path(db, file, &path, column_name)
        }
        NameRefClass::UpdateQualifiedColumnTable => {
            let update = name_ref.syntax().ancestors().find_map(ast::Update::cast)?;
            let path = update.relation_name()?.path()?;
            resolve_table_in_returning_clause(
                db,
                file,
                name_ref,
                update.alias(),
                &path,
                update.returning_clause(),
            )
        }
        NameRefClass::MergeColumn => resolve_merge_column_ptr(db, file, name_ref),
        NameRefClass::MergeQualifiedColumnTable => resolve_merge_table_name_ptr(db, file, name_ref),
        NameRefClass::JoinUsingColumn => {
            let join_expr = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::JoinExpr::cast)?;

            let mut results: SmallVec<[Location; 1]> = SmallVec::new();
            for from_item in ast_nav::iter_join_expr(&join_expr) {
                if let Some(locations) =
                    resolve_from_item_column_ptr(db, file, &from_item, name_ref)
                {
                    results.extend(locations);
                }
            }
            (!results.is_empty()).then_some(results)
        }
        NameRefClass::PropertyGraphColumn => resolve_property_graph_column_ptr(db, file, name_ref),
        NameRefClass::AlterColumn => {
            let column_name = Name::from_node(name_ref);
            let alter_table = name_ref
                .syntax()
                .ancestors()
                .find_map(ast::AlterTable::cast)?;
            let table_path = alter_table.relation_name()?.path()?;
            resolve_column_for_path(db, file, &table_path, column_name)
        }
    }
    .or_else(|| resolve_special_keyword_as_function(db, file, name_ref))
}

fn resolve_table_name_ptr(
    db: &dyn Db,
    file: File,
    table_name: &Name,
    schema: Option<&Schema>,
    position: TextSize,
) -> Option<SyntaxNodePtr> {
    let binder = bind(db, file);
    binder.lookup_with(table_name, SymbolKind::Table, position, schema)
}

fn resolve_type_name_ptr(
    db: &dyn Db,
    file: File,
    type_name: &Name,
    schema: Option<&Schema>,
    position: TextSize,
) -> Option<SyntaxNodePtr> {
    let binder = bind(db, file);
    if let Some(ptr) = binder.lookup_with(type_name, SymbolKind::Type, position, schema) {
        return Some(ptr);
    }

    if schema.is_none()
        && let Some(fallback_name) = fallback_type_alias(type_name)
    {
        return binder.lookup_with(&fallback_name, SymbolKind::Type, position, None);
    }

    None
}

pub(crate) fn resolve_type_ptr_from_type(
    db: &dyn Db,
    file: File,
    ty: &ast::Type,
    position: TextSize,
) -> Option<SyntaxNodePtr> {
    let (schema, type_name) = name::schema_and_type_name(ty)?;
    resolve_type_name_ptr(db, file, &type_name, schema.as_ref(), position)
}

fn fallback_type_alias(type_name: &Name) -> Option<Name> {
    match type_name.0.as_str() {
        "bigint" | "bigserial" | "serial8" => Some(Name::from_string("int8")),
        "boolean" => Some(Name::from_string("bool")),
        "dec" | "decimal" => Some(Name::from_string("numeric")),
        "float" => Some(Name::from_string("float8")),
        "int" | "integer" | "serial" | "serial4" => Some(Name::from_string("int4")),
        "real" => Some(Name::from_string("float4")),
        "smallint" | "smallserial" | "serial2" => Some(Name::from_string("int2")),
        _ => None,
    }
}

fn resolve_float_precision(name_ref: &ast::NameRef, type_name: Name) -> Name {
    if type_name.0.as_str() == "float"
        && let Some(ast::Expr::Literal(lit)) = name_ref
            .syntax()
            .ancestors()
            .find_map(ast::PathType::cast)
            .and_then(|x| x.arg_list()?.args_().next()?.expr())
    {
        let precision: u32 = lit.syntax().text().to_string().parse().unwrap_or(0);
        return Name::from_string(if precision <= 24 { "float4" } else { "float8" });
    }
    type_name
}

fn resolve_view_name_ptr(
    db: &dyn Db,
    file: File,
    view_name: &Name,
    schema: Option<&Schema>,
    position: TextSize,
) -> Option<SyntaxNodePtr> {
    let binder = bind(db, file);
    binder.lookup_with(view_name, SymbolKind::View, position, schema)
}

pub(crate) fn resolve_table_like(
    db: &dyn Db,
    file: File,
    name_ref: Option<&ast::NameRef>,
    table_name: &Name,
    schema: Option<&Schema>,
    position: TextSize,
) -> Option<(SyntaxNodePtr, LocationKind)> {
    if let Some(view_name_ptr) = resolve_view_name_ptr(db, file, table_name, schema, position) {
        return Some((view_name_ptr, LocationKind::View));
    }

    if let Some(table_name_ptr) = resolve_table_name_ptr(db, file, table_name, schema, position) {
        return Some((table_name_ptr, LocationKind::Table));
    }

    if schema.is_none()
        && let Some(name_ref) = name_ref
        && let Some(cte_ptr) = resolve_cte_table(name_ref, table_name)
    {
        return Some((cte_ptr, LocationKind::Table));
    }

    None
}

fn resolve_for_kind_with_params(
    db: &dyn Db,
    file: File,
    name: &Name,
    schema: Option<&Schema>,
    params: Option<&[Name]>,
    position: TextSize,
    kind: SymbolKind,
) -> Option<SyntaxNodePtr> {
    let binder = bind(db, file);
    binder.lookup_with_params(name, kind, position, schema, params)
}

// some keywords behave as functions
fn resolve_special_keyword_as_function(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let function_name = name_ref
        .syntax()
        .first_child_or_token()
        .and_then(|t| match t.kind() {
            SyntaxKind::CURRENT_SCHEMA_KW => Some("current_schema"),
            SyntaxKind::CURRENT_TIMESTAMP_KW => Some("now"),
            SyntaxKind::CURRENT_USER_KW | SyntaxKind::USER_KW => Some("current_user"),
            SyntaxKind::SESSION_USER_KW => Some("session_user"),
            _ => None,
        })?;
    let function_name = Name::from_string(function_name);
    let position = name_ref.syntax().text_range().start();
    resolve_function(db, file, &function_name, None, None, position)
}

fn resolve_function(
    db: &dyn Db,
    file: File,
    function_name: &Name,
    schema: Option<&Schema>,
    params: Option<&[Name]>,
    position: TextSize,
) -> Option<SmallVec<[Location; 1]>> {
    let ptr = resolve_for_kind_with_params(
        db,
        file,
        function_name,
        schema,
        params,
        position,
        SymbolKind::Function,
    )?;
    Some(smallvec![Location::new(
        file,
        ptr.text_range(),
        LocationKind::Function
    )])
}

fn resolve_aggregate(
    db: &dyn Db,
    file: File,
    aggregate_name: &Name,
    schema: Option<&Schema>,
    params: Option<&[Name]>,
    position: TextSize,
) -> Option<SmallVec<[Location; 1]>> {
    let ptr = resolve_for_kind_with_params(
        db,
        file,
        aggregate_name,
        schema,
        params,
        position,
        SymbolKind::Aggregate,
    )?;
    Some(smallvec![Location::new(
        file,
        ptr.text_range(),
        LocationKind::Aggregate
    )])
}

fn resolve_procedure(
    db: &dyn Db,
    file: File,
    procedure_name: &Name,
    schema: Option<&Schema>,
    params: Option<&[Name]>,
    position: TextSize,
) -> Option<SmallVec<[Location; 1]>> {
    let ptr = resolve_for_kind_with_params(
        db,
        file,
        procedure_name,
        schema,
        params,
        position,
        SymbolKind::Procedure,
    )?;
    Some(smallvec![Location::new(
        file,
        ptr.text_range(),
        LocationKind::Procedure
    )])
}

fn resolve_create_index_column_ptr(
    db: &dyn Db,
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let column_name = Name::from_node(column_name_ref);

    let create_index = column_name_ref
        .syntax()
        .ancestors()
        .find_map(ast::CreateIndex::cast)?;
    let path = create_index.relation_name()?.path()?;

    resolve_column_for_path(db, file, &path, column_name)
}

fn resolve_property_graph_column_ptr(
    db: &dyn Db,
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let column_name = Name::from_node(column_name_ref);
    let parent = column_name_ref.syntax().parent()?;

    if let Some(column) = ast::Column::cast(parent.clone())
        && let Some(column_list) = ast::ColumnList::cast(column.syntax().parent()?)
    {
        if let Some(references_table) = ast::ReferencesTable::cast(column_list.syntax().parent()?) {
            let table_name = Name::from_node(&references_table.name_ref()?);
            let position = column_name_ref.syntax().text_range().start();
            return resolve_column_for_table(db, file, &table_name, None, &column_name, position);
        } else if let Some(edge_table_def) = column_list
            .syntax()
            .ancestors()
            .find_map(ast::EdgeTableDef::cast)
        {
            return resolve_column_for_path(db, file, &edge_table_def.path()?, column_name);
        } else if let Some(vertex_table_def) =
            ast::VertexTableDef::cast(column_list.syntax().parent()?)
        {
            return resolve_column_for_path(db, file, &vertex_table_def.path()?, column_name);
        }
    } else if let Some(expr_as_name) = ast::ExprAsName::cast(parent)
        && let Some(expr_as_name_list) = ast::ExprAsNameList::cast(expr_as_name.syntax().parent()?)
        && let Some(properties) = ast::Properties::cast(expr_as_name_list.syntax().parent()?)
    {
        let parent = properties.syntax().parent()?;
        if let Some(edge) = ast::EdgeTableDef::cast(parent.clone()) {
            return resolve_column_for_path(db, file, &edge.path()?, column_name);
        } else if let Some(vertex) = ast::VertexTableDef::cast(parent) {
            return resolve_column_for_path(db, file, &vertex.path()?, column_name);
        }
    }

    None
}

fn resolve_column_for_path(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
    column_name: Name,
) -> Option<SmallVec<[Location; 1]>> {
    let (schema, table_name) = name::schema_and_name_path(path)?;
    let position = path.syntax().text_range().start();

    resolve_column_for_table(
        db,
        file,
        &table_name,
        schema.as_ref(),
        &column_name,
        position,
    )
}

fn resolve_select_qualified_column_table_name_ptr(
    db: &dyn Db,
    file: File,
    table_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let table_name = Name::from_node(table_name_ref);

    let field_expr = table_name_ref
        .syntax()
        .parent()
        .and_then(ast::FieldExpr::cast)?;

    let explicit_schema = if field_expr
        .field()
        .is_some_and(|f| f.syntax() == table_name_ref.syntax())
        && field_expr.star_token().is_none()
        && let Some(ast::Expr::NameRef(schema_name_ref)) = field_expr.base()
    {
        // `foo.bar` where table_name_ref is `bar`
        Some(Schema(Name::from_node(&schema_name_ref)))
    } else if let Some(ast::Expr::FieldExpr(inner_field_expr)) = field_expr.base()
        && let Some(ast::Expr::NameRef(schema_name_ref)) = inner_field_expr.base()
    {
        // `foo.buzz.bar` where table_name_ref is `buzz`
        Some(Schema(Name::from_node(&schema_name_ref)))
    } else {
        None
    };

    let from_item = find_from_item_for_select_qualified_name_ref(table_name_ref, &table_name)?;

    if let Some(alias_name) = from_item.alias().and_then(|a| a.name())
        && Name::from_node(&alias_name) == table_name
    {
        return Some(smallvec![Location::new(
            file,
            alias_name.syntax().text_range(),
            LocationKind::Table
        )]);
    }

    if let Some(call_expr) = from_item.call_expr()
        && let Some((function_schema, function_name)) = name::schema_and_func_name(&call_expr)
        && function_name == table_name
        && function_schema == explicit_schema
    {
        let position = table_name_ref.syntax().text_range().start();
        return resolve_function(
            db,
            file,
            &function_name,
            function_schema.as_ref(),
            None,
            position,
        );
    }

    let (schema, table_name) = if let Some(schema) = explicit_schema {
        (Some(schema), table_name)
    } else {
        name::schema_and_table_from_from_item(&from_item)?
    };
    let position = table_name_ref.syntax().text_range().start();
    let (ptr, kind) = resolve_table_like(
        db,
        file,
        Some(table_name_ref),
        &table_name,
        schema.as_ref(),
        position,
    )?;
    Some(smallvec![Location::new(file, ptr.text_range(), kind)])
}

enum ReturningClauseMatch {
    ReturningAlias(ast::Name),
    TableAlias(ast::Name),
    PseudoTable,
    Table,
}

fn match_table_in_returning_clause(
    table_name: &Name,
    stmt_table_name: &Name,
    alias: Option<ast::Alias>,
    returning_clause: Option<ast::ReturningClause>,
) -> Option<ReturningClauseMatch> {
    // Check `returning with (old as alias, new as alias)`
    if let Some(option_list) = returning_clause.and_then(|x| x.returning_option_list()) {
        for option in option_list.returning_options() {
            if let Some(name) = option.name()
                && Name::from_node(&name) == *table_name
            {
                return Some(ReturningClauseMatch::ReturningAlias(name));
            }
        }
    }

    if let Some(alias_name) = alias.and_then(|x| x.name())
        && Name::from_node(&alias_name) == *table_name
    {
        return Some(ReturningClauseMatch::TableAlias(alias_name));
    }

    let old_name = Name::from_string("old");
    let new_name = Name::from_string("new");
    if *table_name == old_name || *table_name == new_name {
        return Some(ReturningClauseMatch::PseudoTable);
    }

    if *stmt_table_name == *table_name {
        return Some(ReturningClauseMatch::Table);
    }

    None
}

fn resolve_select_qualified_column_ptr(
    db: &dyn Db,
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let column_name = Name::from_node(column_name_ref);

    let field_expr = column_name_ref
        .syntax()
        .parent()
        .and_then(ast::FieldExpr::cast)?;

    let (explicit_schema, column_table_name) = name::schema_and_table_from_field_expr(&field_expr)?;

    let position = column_name_ref.syntax().text_range().start();

    let (schema, mut table_name) = if let Some(schema) = explicit_schema {
        (Some(schema), column_table_name)
    } else {
        match ast_nav::node_parent_query(column_name_ref.syntax())? {
            ast_nav::ParentQuery::Select(_select) => {
                let from_item = find_from_item_for_select_qualified_name_ref(
                    column_name_ref,
                    &column_table_name,
                )?;

                if let Some(call_expr) = from_item.call_expr()
                    && let Some(ptr) = resolve_column_from_call_expr_return_table(
                        db,
                        file,
                        &call_expr,
                        column_name_ref,
                        &column_name,
                        0,
                    )
                {
                    return Some(ptr);
                }

                // `from t as u`
                // `from t as u(a, b, c)`
                if let Some(alias) = from_item.alias()
                    && let Some(alias_name) = alias.name()
                    && Name::from_node(&alias_name) == column_table_name
                {
                    if let Some(paren_select) = from_item.paren_select() {
                        return resolve_subquery_column_ptr(
                            db,
                            file,
                            &paren_select,
                            column_name_ref,
                            &column_name,
                            Some(&alias),
                        );
                    }

                    if let Some(paren_expr) = from_item.paren_expr() {
                        return resolve_column_from_paren_expr(
                            db,
                            file,
                            &paren_expr,
                            column_name_ref,
                            &column_name,
                        );
                    }

                    // `from t as u(a, b, c)`
                    if let Some(column_list) = alias.column_list() {
                        for column in column_list.columns() {
                            if let Some(col_name) = column.name()
                                && Name::from_node(&col_name) == column_name
                            {
                                return Some(smallvec![Location::new(
                                    file,
                                    col_name.syntax().text_range(),
                                    LocationKind::Column
                                )]);
                            }
                        }

                        // ```sql
                        // create table t(a int, b int);
                        // select b from t as u(x);
                        //        ^
                        // ```
                        if let Some(name_ref_node) = from_item.name_ref() {
                            let cte_name = Name::from_node(&name_ref_node);
                            return resolve_cte_column(
                                db,
                                file,
                                column_name_ref,
                                &cte_name,
                                &column_name,
                            );
                        }
                    }
                }
                name::schema_and_table_from_from_item(&from_item)?
            }
            ast_nav::ParentQuery::Update(update) => {
                let path = update.relation_name()?.path()?;
                name::schema_and_name_path(&path)?
            }
            ast_nav::ParentQuery::Delete(delete) => {
                let path = delete.relation_name()?.path()?;
                name::schema_and_name_path(&path)?
            }
            ast_nav::ParentQuery::Insert(insert) => {
                let path = insert.path()?;
                name::schema_and_name_path(&path)?
            }
            ast_nav::ParentQuery::Merge(merge) => {
                let found_in_using = if let Some(using_on) = merge.using_on_clause()
                    && let Some(from_item) = using_on.from_item()
                {
                    if let Some((schema, item_name)) =
                        name::schema_and_table_from_from_item(&from_item)
                        && item_name == column_table_name
                    {
                        Some((schema, item_name))
                    } else if let Some(alias_name) = from_item.alias().and_then(|x| x.name())
                        && let alias_name = Name::from_node(&alias_name)
                        && alias_name == column_table_name
                    {
                        Some((None, alias_name))
                    } else {
                        None
                    }
                } else {
                    None
                };

                if let Some(result) = found_in_using {
                    result
                } else {
                    let path = merge.relation_name()?.path()?;
                    name::schema_and_name_path(&path)?
                }
            }
        }
    };

    if schema.is_none() {
        if resolve_cte_table(column_name_ref, &table_name).is_some() {
            if let Some(cte_column_ptr) =
                resolve_cte_column(db, file, column_name_ref, &table_name, &column_name)
            {
                return Some(cte_column_ptr);
            }
            return None;
        }
        if let Some(alias_table_name) = resolve_merge_alias(column_name_ref, &table_name) {
            table_name = alias_table_name;
        }
    }

    resolve_column_for_table(
        db,
        file,
        &table_name,
        schema.as_ref(),
        &column_name,
        position,
    )
}

fn resolve_column_for_table(
    db: &dyn Db,
    file: File,
    table_name: &Name,
    schema: Option<&Schema>,
    column_name: &Name,
    position: TextSize,
) -> Option<SmallVec<[Location; 1]>> {
    let (file, resolved) = resolve_table_name(db, file, table_name, schema, position)?;
    match resolved {
        ResolvedTableName::View(create_view) => {
            if let Some(ptr) = find_column_in_create_view_like(file, &create_view, column_name) {
                return Some(ptr);
            }
            return resolve_function(db, file, column_name, schema, None, position);
        }
        ResolvedTableName::Table(create_table_like) => {
            // 1. Try to find a matching column (columns take precedence)
            if let Some(ptr) =
                find_column_in_create_table(db, file, &create_table_like, column_name)
            {
                return Some(ptr);
            }
            // 2. No column found, check for field-style function call
            // e.g., select t.b from t where b is a function that takes t as an argument
            return resolve_function(db, file, column_name, schema, None, position);
        }
        ResolvedTableName::TableAs(create_table_as) => {
            if let Some(ptr) =
                find_column_in_create_table_as(db, file, &create_table_as, column_name)
            {
                return Some(ptr);
            }
            return resolve_function(db, file, column_name, schema, None, position);
        }
        ResolvedTableName::SelectInto(select_into) => {
            if let Some(ptr) = find_column_in_select_into(file, &select_into, column_name) {
                return Some(ptr);
            }
            return resolve_function(db, file, column_name, schema, None, position);
        }
    }
}

pub(crate) enum ResolvedTableName {
    Table(ast::CreateTableLike),
    TableAs(ast::CreateTableAs),
    SelectInto(ast::SelectInto),
    View(ast::CreateViewLike),
}
// TODO: basically goto def for a table name, we should refactor into an ast_nav
// + goto def setup
pub(crate) fn resolve_table_name(
    db: &dyn Db,
    file: File,
    table_name: &Name,
    schema: Option<&Schema>,
    position: TextSize,
) -> Option<(File, ResolvedTableName)> {
    use ResolvedTableName::*;
    for file in [file, builtins_file(db)] {
        let Some((ptr, kind)) = resolve_table_like(db, file, None, table_name, schema, position)
        else {
            continue;
        };
        let tree = parse(db, file).tree();
        let node = ptr.to_node(tree.syntax());
        match kind {
            LocationKind::Table => {
                if let Some(create_table) = node.ancestors().find_map(ast::CreateTableLike::cast) {
                    return Some((file, Table(create_table)));
                }
                if let Some(create_table_as) = node.ancestors().find_map(ast::CreateTableAs::cast) {
                    return Some((file, TableAs(create_table_as)));
                }
                if let Some(select_into) = node.ancestors().find_map(ast::SelectInto::cast) {
                    return Some((file, SelectInto(select_into)));
                }
            }
            LocationKind::View => {
                if let Some(view) = node.ancestors().find_map(ast::CreateViewLike::cast) {
                    return Some((file, View(view)));
                }
            }
            _ => (),
        }
    }
    None
}

fn resolve_merge_alias(name_ref: &ast::NameRef, table_name: &Name) -> Option<Name> {
    let from_item = name_ref.syntax().ancestors().find_map(|x| {
        ast::Merge::cast(x)?
            .using_on_clause()
            .and_then(|c| c.from_item())
    })?;
    if let Some(alias_name) = from_item.alias().and_then(|x| x.name())
        && Name::from_node(&alias_name) == *table_name
    {
        let table_name = Name::from_node(&from_item.name_ref()?);
        return Some(table_name);
    }
    None
}

fn resolve_from_item_column_ptr(
    db: &dyn Db,
    file: File,
    from_item: &ast::FromItem,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let column_name = Name::from_node(column_name_ref);
    if let Some(paren_select) = from_item.paren_select() {
        let alias = from_item.alias();
        if let Some(ptr) = resolve_subquery_column_ptr(
            db,
            file,
            &paren_select,
            column_name_ref,
            &column_name,
            alias.as_ref(),
        ) {
            return Some(ptr);
        }
        if let Some(alias_name) = alias.and_then(|x| x.name())
            && Name::from_node(&alias_name) == column_name
        {
            return Some(smallvec![Location::new(
                file,
                alias_name.syntax().text_range(),
                LocationKind::Table
            )]);
        }
        return None;
    }

    if let Some(paren_expr) = from_item.paren_expr() {
        if let Some(column_list) = from_item.alias().and_then(|x| x.column_list()) {
            for col in column_list.columns() {
                if let Some(col_name) = col.name()
                    && Name::from_node(&col_name) == column_name
                {
                    return Some(smallvec![Location::new(
                        file,
                        col_name.syntax().text_range(),
                        LocationKind::Column
                    )]);
                }
            }
        }

        if let Some(ptr) =
            resolve_column_from_paren_expr(db, file, &paren_expr, column_name_ref, &column_name)
        {
            return Some(ptr);
        }
        if let Some(alias_name) = from_item.alias().and_then(|x| x.name())
            && Name::from_node(&alias_name) == column_name
        {
            return Some(smallvec![Location::new(
                file,
                alias_name.syntax().text_range(),
                LocationKind::Table
            )]);
        }
        return None;
    }

    let alias_len = if let Some(column_list) = from_item.alias().and_then(|x| x.column_list()) {
        for col in column_list.columns() {
            if let Some(col_name) = col.name()
                && Name::from_node(&col_name) == column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    col_name.syntax().text_range(),
                    LocationKind::Column
                )]);
            }
        }
        column_list.columns().count()
    } else {
        0
    };

    if let Some(call_expr) = from_item.call_expr()
        && let Some(ptr) = resolve_column_from_call_expr_return_table(
            db,
            file,
            &call_expr,
            column_name_ref,
            &column_name,
            alias_len,
        )
    {
        return Some(ptr);
    }

    let (schema, table_name) = name::schema_and_table_from_from_item(from_item)?;

    if let Some(ptr) = resolve_column_from_table_or_view_or_cte(
        db,
        file,
        column_name_ref,
        &table_name,
        schema.as_ref(),
        &column_name,
    ) {
        return Some(ptr);
    }

    if let Some(alias_name) = from_item.alias().and_then(|x| x.name())
        && Name::from_node(&alias_name) == column_name
    {
        return Some(smallvec![Location::new(
            file,
            alias_name.syntax().text_range(),
            LocationKind::Table
        )]);
    }

    None
}

fn resolve_column_from_table_or_view_or_cte(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    table_name: &Name,
    schema: Option<&Schema>,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    resolve_column_from_table_or_view_or_cte_impl(
        db,
        file,
        name_ref,
        table_name,
        schema,
        column_name,
        0,
    )
}

fn resolve_column_from_table_or_view_or_cte_impl(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    table_name: &Name,
    schema: Option<&Schema>,
    column_name: &Name,
    depth: u32,
) -> Option<SmallVec<[Location; 1]>> {
    if depth > 40 {
        log::info!("max resolve depth reached, probably in a cycle");
        return None;
    }

    let position = name_ref.syntax().text_range().start();
    let (table_like_ptr, kind) =
        resolve_table_like(db, file, Some(name_ref), table_name, schema, position)?;

    match kind {
        LocationKind::Table => {
            if schema.is_none() && resolve_cte_table(name_ref, table_name).is_some() {
                return resolve_cte_column(db, file, name_ref, table_name, column_name);
            }

            let tree = parse(db, file).tree();
            let root = tree.syntax();
            let node = table_like_ptr.to_node(root);

            if let Some(create_table) = node.ancestors().find_map(ast::CreateTableLike::cast) {
                if let Some(cols) =
                    find_column_in_create_table(db, file, &create_table, column_name)
                {
                    return Some(cols);
                }

                // check if this is a partitioned table
                if let Some(parent_path) = ast::CreateTable::cast(create_table.syntax().clone())
                    .and_then(|x| x.partition_of())
                    .and_then(|x| x.path())
                {
                    let (parent_schema, parent_table_name) =
                        name::schema_and_name_path(&parent_path)?;
                    return resolve_column_from_table_or_view_or_cte_impl(
                        db,
                        file,
                        name_ref,
                        &parent_table_name,
                        parent_schema.as_ref(),
                        column_name,
                        depth + 1,
                    );
                }

                // For example, in:
                // ```sql
                // create table t(a int);
                // select t from t;
                // ```
                if column_name == table_name {
                    return Some(smallvec![Location::new(
                        file,
                        table_like_ptr.text_range(),
                        LocationKind::Table
                    )]);
                }
            }

            if let Some(create_table_as) = node.ancestors().find_map(ast::CreateTableAs::cast) {
                if let Some(cols) =
                    find_column_in_create_table_as(db, file, &create_table_as, column_name)
                {
                    return Some(cols);
                }

                if column_name == table_name {
                    return Some(smallvec![Location::new(
                        file,
                        table_like_ptr.text_range(),
                        LocationKind::Table
                    )]);
                }
            }

            if let Some(select_into) = node.ancestors().find_map(ast::SelectInto::cast) {
                if let Some(cols) = find_column_in_select_into(file, &select_into, column_name) {
                    return Some(cols);
                }

                if column_name == table_name {
                    return Some(smallvec![Location::new(
                        file,
                        table_like_ptr.text_range(),
                        LocationKind::Table
                    )]);
                }
            }

            None
        }
        LocationKind::View => {
            let tree = parse(db, file).tree();
            let root = tree.syntax();
            let node = table_like_ptr.to_node(root);

            if let Some(create_view) = node.ancestors().find_map(ast::CreateViewLike::cast) {
                if let Some(cols) = find_column_in_create_view_like(file, &create_view, column_name)
                {
                    return Some(cols);
                }

                if column_name == table_name {
                    return Some(smallvec![Location::new(
                        file,
                        table_like_ptr.text_range(),
                        LocationKind::View
                    )]);
                }
            }

            None
        }
        _ => None,
    }
}

fn resolve_from_item_for_cte_star(
    db: &dyn Db,
    file: File,
    from_item: &ast::FromItem,
    name_ref: &ast::NameRef,
    cte_name: &Name,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    if let Some((schema, table_name)) = name::schema_and_table_from_from_item(from_item)
        && table_name == *cte_name
    {
        let position = name_ref.syntax().text_range().start();
        return resolve_column_for_table(
            db,
            file,
            &table_name,
            schema.as_ref(),
            column_name,
            position,
        );
    }

    resolve_from_item_column_ptr(db, file, from_item, name_ref)
}

fn resolve_select_column_ptr(
    db: &dyn Db,
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let select = column_name_ref
        .syntax()
        .ancestors()
        .find_map(ast::Select::cast)?;
    let from_clause = select.from_clause()?;

    for from_item in ast_nav::iter_from_clause(&from_clause) {
        if let Some(column_ptr) =
            resolve_from_item_column_ptr(db, file, &from_item, column_name_ref)
        {
            return Some(column_ptr);
        }
    }

    None
}

fn resolve_select_group_by_alias_or_column_ptr(
    db: &dyn Db,
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    if let Some(ptr) = resolve_select_column_ptr(db, file, column_name_ref) {
        return Some(ptr);
    }
    resolve_select_target_alias_ptr(file, column_name_ref)
}

fn resolve_select_order_by_alias_or_column_ptr(
    db: &dyn Db,
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    if let Some(ptr) = resolve_select_target_alias_ptr(file, column_name_ref) {
        return Some(ptr);
    }
    resolve_select_column_ptr(db, file, column_name_ref)
}

fn resolve_select_target_alias_ptr(
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let select = column_name_ref
        .syntax()
        .ancestors()
        .find_map(ast::Select::cast)?;
    let column_name = Name::from_node(column_name_ref);
    let target_list = select.select_clause()?.target_list()?;
    for target in target_list.targets() {
        if let Some((target_name, node)) = ColumnName::from_target(target)
            && let Some(target_name) = target_name.to_string()
            && Name::from_string(target_name) == column_name
        {
            return Some(smallvec![Location::new(
                file,
                node.text_range(),
                LocationKind::Column,
            )]);
        }
    }
    None
}

fn resolve_fn_call_column(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let column_name = Name::from_node(name_ref);

    // function call syntax for columns is only valid if there is one argument
    let call_expr = name_ref
        .syntax()
        .ancestors()
        .find_map(ast::CallExpr::cast)?;
    let arg_count = call_expr.arg_list()?.args().count();
    if arg_count != 1 {
        return None;
    }

    let select = name_ref.syntax().ancestors().find_map(ast::Select::cast)?;
    let from_clause = select.from_clause()?;

    for from_item in ast_nav::iter_from_clause(&from_clause) {
        if let Some((schema, table_name)) = name::schema_and_table_from_from_item(&from_item)
            && let Some(result) = resolve_column_from_table_or_view_or_cte(
                db,
                file,
                name_ref,
                &table_name,
                schema.as_ref(),
                &column_name,
            )
        {
            return Some(result);
        }
    }

    None
}

fn is_from_item_match(from_item: &ast::FromItem, qualifier: &Name) -> bool {
    if let Some(alias_name) = from_item.alias().and_then(|a| a.name())
        && Name::from_node(&alias_name) == *qualifier
    {
        return true;
    }

    if let Some((_schema, function_name)) = from_item
        .call_expr()
        .and_then(|x| name::schema_and_func_name(&x))
        && function_name == *qualifier
    {
        return true;
    }

    if let Some(name_ref) = from_item.name_ref()
        && Name::from_node(&name_ref) == *qualifier
    {
        return true;
    }

    if let Some(field) = from_item.field_expr().and_then(|x| x.field())
        && Name::from_node(&field) == *qualifier
    {
        return true;
    }

    false
}

pub(crate) fn find_from_item_in_from_clause(
    from_clause: &ast::FromClause,
    qualifier: &Name,
) -> Option<ast::FromItem> {
    ast_nav::iter_from_clause(from_clause)
        .find(|from_item| is_from_item_match(from_item, qualifier))
}

fn find_from_item_for_select_qualified_name_ref(
    name_ref: &ast::NameRef,
    table_name: &Name,
) -> Option<ast::FromItem> {
    let select = name_ref.syntax().ancestors().find_map(ast::Select::cast)?;

    if let Some(from_clause) = select.from_clause()
        && let Some(from_item) = find_from_item_in_from_clause(&from_clause, table_name)
    {
        return Some(from_item);
    }

    let lateral_from_item = name_ref.syntax().ancestors().find_map(|ancestor| {
        ast::FromItem::cast(ancestor).filter(|from_item| from_item.lateral_token().is_some())
    })?;

    for ancestor in lateral_from_item.syntax().ancestors() {
        if let Some(from_clause) = ast::Select::cast(ancestor).and_then(|x| x.from_clause())
            && let Some(outer_from_item) = find_from_item_in_from_clause(&from_clause, table_name)
        {
            return Some(outer_from_item);
        }
    }

    None
}

pub(crate) fn find_column_in_create_table(
    db: &dyn Db,
    file: File,
    create_table: &impl ast::HasCreateTable,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    find_column_in_create_table_impl(db, file, create_table, column_name, 0)
}

fn find_column_in_create_table_impl(
    db: &dyn Db,
    file: File,
    create_table: &impl ast::HasCreateTable,
    column_name: &Name,
    depth: usize,
) -> Option<SmallVec<[Location; 1]>> {
    if depth > 40 {
        log::info!("max depth reached, probably in a cycle");
        return None;
    }

    for arg in ast_nav::create_table_args(create_table) {
        match arg {
            ast_nav::CreateTableArg::Inherits(path) => {
                let (schema, table_name) = name::schema_and_name_path(&path)?;
                let position = path.syntax().text_range().start();
                if let Some((lookup_file, resolved)) =
                    resolve_table_name(db, file, &table_name, schema.as_ref(), position)
                    && let Some(ptr) =
                        find_column_in_resolved(db, lookup_file, resolved, column_name, depth + 1)
                {
                    return Some(ptr);
                }
            }
            ast_nav::CreateTableArg::Column(column) => {
                if let Some(name) = column.name()
                    && Name::from_node(&name) == *column_name
                {
                    return Some(smallvec![Location::new(
                        file,
                        name.syntax().text_range(),
                        LocationKind::Column
                    )]);
                }
            }
            ast_nav::CreateTableArg::LikeClause(like_clause) => {
                let path = like_clause.path()?;
                let (schema, table_name) = name::schema_and_name_path(&path)?;
                let position = path.syntax().text_range().start();
                if let Some((lookup_file, resolved)) =
                    resolve_table_name(db, file, &table_name, schema.as_ref(), position)
                    && let Some(ptr) =
                        find_column_in_resolved(db, lookup_file, resolved, column_name, depth + 1)
                {
                    return Some(ptr);
                }
            }
            ast_nav::CreateTableArg::TableConstraint(_) => (),
        }
    }

    None
}

fn find_column_in_resolved(
    db: &dyn Db,
    file: File,
    resolved: ResolvedTableName,
    column_name: &Name,
    depth: usize,
) -> Option<SmallVec<[Location; 1]>> {
    match resolved {
        ResolvedTableName::Table(parent_table) => {
            find_column_in_create_table_impl(db, file, &parent_table, column_name, depth)
        }
        ResolvedTableName::TableAs(create_table_as) => {
            find_column_in_create_table_as(db, file, &create_table_as, column_name)
        }
        ResolvedTableName::SelectInto(select_into) => {
            find_column_in_select_into(file, &select_into, column_name)
        }
        ResolvedTableName::View(create_view) => {
            find_column_in_create_view_like(file, &create_view, column_name)
        }
    }
}

// TODO: this is similar to the CTE funcs, maybe we can simplify
fn find_column_in_create_view_like(
    file: File,
    create_view: &ast::CreateViewLike,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    let column_list_len = if let Some(column_list) = create_view.column_list() {
        for column in column_list.columns() {
            if let Some(col_name) = column.name()
                && Name::from_node(&col_name) == *column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    col_name.syntax().text_range(),
                    LocationKind::Column
                )]);
            }
        }
        column_list.columns().count()
    } else {
        0
    };

    let select = ast_nav::select_from_variant(create_view.query()?)?;

    for (idx, target) in select.select_clause()?.target_list()?.targets().enumerate() {
        if idx < column_list_len {
            continue;
        }

        if let Some((col_name, node)) = ColumnName::from_target(target.clone()) {
            if let Some(col_name_str) = col_name.to_string()
                && Name::from_string(col_name_str) == *column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    node.text_range(),
                    LocationKind::Column
                )]);
            }
        }
    }

    None
}

fn find_column_in_create_table_as(
    db: &dyn Db,
    file: File,
    create_table_as: &ast::CreateTableAs,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    let query = create_table_as.query()?;

    if let ast::SelectVariant::Values(values) = &query {
        for (col_name, expr) in ast_nav::iter_values_columns(values) {
            if col_name == *column_name {
                return Some(smallvec![Location::new(
                    file,
                    expr.syntax().text_range(),
                    LocationKind::Column
                )]);
            }
        }
        return None;
    }

    if let ast::SelectVariant::Table(table) = &query {
        let path = table.relation_name()?.path()?;
        let (schema, table_name) = name::schema_and_name_path(&path)?;
        let position = table.syntax().text_range().start();
        let (lookup_file, resolved) =
            resolve_table_name(db, file, &table_name, schema.as_ref(), position)?;
        return find_column_in_resolved(db, lookup_file, resolved, column_name, 0);
    }

    let select = ast_nav::select_from_variant(query)?;
    let target_list = select.select_clause()?.target_list()?;
    let from_clause = select.from_clause();

    for target in target_list.targets() {
        if target.star_token().is_some() {
            if let Some(from_clause) = &from_clause
                && let Some(result) = find_column_in_from_clause(db, file, from_clause, column_name)
            {
                return Some(result);
            }
            continue;
        }
        if let Some((col_name, node)) = ColumnName::from_target(target.clone())
            && let Some(col_name_str) = col_name.to_string()
            && Name::from_string(col_name_str) == *column_name
        {
            return Some(smallvec![Location::new(
                file,
                node.text_range(),
                LocationKind::Column
            )]);
        }
    }

    None
}

fn find_column_in_from_clause(
    db: &dyn Db,
    file: File,
    from_clause: &ast::FromClause,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    for from_item in ast_nav::iter_from_clause(from_clause) {
        let Some((schema, table_name)) = name::schema_and_table_from_from_item(&from_item) else {
            continue;
        };
        let position = from_item.syntax().text_range().start();
        let Some((lookup_file, resolved)) =
            resolve_table_name(db, file, &table_name, schema.as_ref(), position)
        else {
            continue;
        };
        if let Some(result) = find_column_in_resolved(db, lookup_file, resolved, column_name, 0) {
            return Some(result);
        }
    }
    None
}

fn find_column_in_select_into(
    file: File,
    select_into: &ast::SelectInto,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    for target in select_into.select_clause()?.target_list()?.targets() {
        if let Some((col_name, node)) = ColumnName::from_target(target.clone())
            && let Some(col_name_str) = col_name.to_string()
            && Name::from_string(col_name_str) == *column_name
        {
            return Some(smallvec![Location::new(
                file,
                node.text_range(),
                LocationKind::Column
            )]);
        }
    }

    None
}

fn resolve_cte_table(name_ref: &ast::NameRef, cte_name: &Name) -> Option<SyntaxNodePtr> {
    let with_table = ast_nav::find_cte_with_table(name_ref, cte_name)?;
    Some(SyntaxNodePtr::new(with_table.name()?.syntax()))
}

fn count_columns_for_path(db: &dyn Db, file: File, path: &ast::Path) -> Option<usize> {
    let (schema, table_name) = name::schema_and_name_path(path)?;
    let position = path.syntax().text_range().start();
    count_columns_for_table_name(db, file, &table_name, schema.as_ref(), position, None)
}

fn count_columns_for_table_name(
    db: &dyn Db,
    file: File,
    table_name: &Name,
    schema: Option<&Schema>,
    position: TextSize,
    name_ref: Option<&ast::NameRef>,
) -> Option<usize> {
    let tree = parse(db, file).tree();
    let root = tree.syntax();
    let (table_like_ptr, kind) =
        resolve_table_like(db, file, name_ref, table_name, schema, position)?;

    match kind {
        LocationKind::Table => {
            if schema.is_none()
                && let Some(name_ref) = name_ref
                && let Some(with_table) = ast_nav::find_cte_with_table(name_ref, table_name)
            {
                return count_columns_for_with_table(with_table);
            }

            let table_like_node = table_like_ptr.to_node(root);
            if let Some(create_table) = table_like_node
                .ancestors()
                .find_map(ast::CreateTableLike::cast)
            {
                let mut count: usize = 0;
                if let Some(args) = create_table.table_arg_list() {
                    for arg in args.args() {
                        if matches!(arg, ast::TableArg::Column(_)) {
                            count = count.saturating_add(1);
                        }
                    }
                }
                return Some(count);
            }

            if let Some(create_table_as) = table_like_node
                .ancestors()
                .find_map(ast::CreateTableAs::cast)
            {
                let select = ast_nav::select_from_variant(create_table_as.query()?)?;
                if let Some(target_list) = select.select_clause().and_then(|c| c.target_list()) {
                    return Some(target_list.targets().count());
                }
            }

            if let Some(select_into) = table_like_node.ancestors().find_map(ast::SelectInto::cast) {
                if let Some(target_list) = select_into.select_clause().and_then(|c| c.target_list())
                {
                    return Some(target_list.targets().count());
                }
            }

            None
        }
        LocationKind::View => {
            let table_like_node = table_like_ptr.to_node(root);
            let create_view = table_like_node
                .ancestors()
                .find_map(ast::CreateViewLike::cast)?;
            if let Some(column_list) = create_view.column_list() {
                return Some(column_list.columns().count());
            }

            let select = ast_nav::select_from_variant(create_view.query()?)?;
            if let Some(target_list) = select.select_clause().and_then(|c| c.target_list()) {
                // This is not quite right if there's a `*` in the view definition.
                // It becomes recursive.
                // For now, let's assume simple views.
                return Some(target_list.targets().count());
            }

            None
        }
        _ => None,
    }
}

fn count_columns_for_with_table(with_table: ast::WithTable) -> Option<usize> {
    if let Some(column_list) = with_table.column_list() {
        return Some(column_list.columns().count());
    }

    let query = with_table.query()?;
    if let ast::WithQuery::Values(values) = query {
        return values
            .row_list()
            .and_then(|x| x.rows().next())
            .map(|row| row.exprs().count());
    }

    let select = ast_nav::select_from_with_query(query)?;
    select
        .select_clause()
        .and_then(|c| c.target_list())
        .map(|t| t.targets().count())
}

fn resolve_cte_column(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    cte_name: &Name,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    let with_table = ast_nav::find_cte_with_table(name_ref, cte_name)?;

    let column_list_len = if let Some(column_list) = with_table.column_list() {
        for column in column_list.columns() {
            if let Some(col_name) = column.name()
                && Name::from_node(&col_name) == *column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    col_name.syntax().text_range(),
                    LocationKind::Column
                )]);
            }
        }
        column_list.columns().count()
    } else {
        0
    };

    let query = with_table.query()?;

    if let ast::WithQuery::Values(values) = query {
        if column_list_len > 0 {
            return None;
        }
        if let Some(num_str) = column_name.0.strip_prefix("column")
            && let Ok(col_num) = num_str.parse::<usize>()
            && col_num > 0
            && let Some(expr) = values
                .row_list()
                .and_then(|x| x.rows().next()?.exprs().nth(col_num - 1))
        {
            return Some(smallvec![Location::new(
                file,
                expr.syntax().text_range(),
                LocationKind::Column
            )]);
        }
        return None;
    }

    if let ast::WithQuery::Table(table) = query {
        let path = table.relation_name()?.path()?;
        let (schema, table_name) = name::schema_and_name_path(&path)?;

        return resolve_column_from_table_or_view_or_cte(
            db,
            file,
            name_ref,
            &table_name,
            schema.as_ref(),
            column_name,
        );
    }

    if let Some(column) = column_in_with_query(&query, db, file, column_name, column_list_len) {
        return Some(column);
    }

    let cte_select = ast_nav::select_from_with_query(query)?;

    let target_list = cte_select.select_clause()?.target_list()?;
    let from_clause = cte_select.from_clause();
    let mut column_index: usize = 0;

    for target in target_list.targets() {
        let target_column_count = from_clause
            .as_ref()
            .and_then(|from_clause| {
                count_columns_for_target(db, file, name_ref, &target, from_clause)
            })
            .unwrap_or(1);
        let column_list_end = column_index.saturating_add(target_column_count);
        if column_list_end <= column_list_len {
            column_index = column_list_end;
            continue;
        }

        if let Some((col_name, node)) = ColumnName::from_target(target.clone()) {
            if let Some(col_name_str) = col_name.to_string()
                && Name::from_string(col_name_str) == *column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    node.text_range(),
                    LocationKind::Column
                )]);
            }

            if matches!(col_name, ColumnName::Star)
                && let Some(from_clause) = &from_clause
                && let Some(result) = resolve_from_clause_for_cte_star(
                    db,
                    file,
                    name_ref,
                    cte_name,
                    column_name,
                    from_clause,
                )
            {
                return Some(result);
            }
        }
        if let Some(ast::Expr::FieldExpr(field_expr)) = target.expr()
            && let Some(table_name) = qualified_star_table_name(&field_expr)
            && let Some(from_clause) = &from_clause
            && let Some(result) = resolve_qualified_star_in_from_clause(
                db,
                file,
                name_ref,
                cte_name,
                column_name,
                from_clause,
                &table_name,
            )
        {
            return Some(result);
        }

        column_index = column_list_end;
    }

    if column_name == cte_name
        && let Some(name_node) = with_table.name()
    {
        return Some(smallvec![Location::new(
            file,
            name_node.syntax().text_range(),
            LocationKind::Table
        )]);
    }

    None
}

fn column_in_with_query(
    query: &ast::WithQuery,
    db: &dyn Db,
    file: File,
    column_name: &Name,
    column_list_len: usize,
) -> Option<SmallVec<[Location; 1]>> {
    let (returning_clause, path) = match query {
        ast::WithQuery::Delete(delete) => {
            (delete.returning_clause(), delete.relation_name()?.path()?)
        }
        ast::WithQuery::Insert(insert) => (insert.returning_clause(), insert.path()?),
        ast::WithQuery::Merge(merge) => (merge.returning_clause(), merge.relation_name()?.path()?),
        ast::WithQuery::Update(update) => {
            (update.returning_clause(), update.relation_name()?.path()?)
        }
        ast::WithQuery::Select(_)
        | ast::WithQuery::CompoundSelect(_)
        | ast::WithQuery::Table(_)
        | ast::WithQuery::Values(_)
        | ast::WithQuery::ParenSelect(_) => return None,
    };

    let mut column_index: usize = 0;
    for target in returning_clause?.target_list()?.targets() {
        let target_column_count = if target.star_token().is_some() {
            count_columns_for_path(db, file, &path).unwrap_or(1)
        } else {
            1
        };
        let column_list_end = column_index.saturating_add(target_column_count);

        if column_list_end <= column_list_len {
            column_index = column_list_end;
            continue;
        }

        if let Some((col_name, node)) = ColumnName::from_target(target) {
            if let Some(col_name_str) = col_name.to_string()
                && Name::from_string(col_name_str) == *column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    node.text_range(),
                    LocationKind::Column
                )]);
            }
            if matches!(col_name, ColumnName::Star)
                && let Some(ptr) = resolve_column_for_path(db, file, &path, column_name.clone())
            {
                return Some(ptr);
            }
        }
        column_index = column_list_end;
    }

    None
}

fn resolve_subquery_column_ptr(
    db: &dyn Db,
    file: File,
    paren_select: &ast::ParenSelect,
    name_ref: &ast::NameRef,
    column_name: &Name,
    alias: Option<&ast::Alias>,
) -> Option<SmallVec<[Location; 1]>> {
    let select_variant = paren_select.select()?;

    let column_list_len = if let Some(column_list) = alias.and_then(|x| x.column_list()) {
        for col in column_list.columns() {
            if let Some(col_name) = col.name()
                && Name::from_node(&col_name) == *column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    col_name.syntax().text_range(),
                    LocationKind::Column
                )]);
            }
        }
        if matches!(select_variant, ast::SelectVariant::Values(_)) {
            return None;
        }
        column_list.columns().count()
    } else {
        0
    };

    // TODO: this should just be a match stmt
    if let ast::SelectVariant::Table(table) = select_variant {
        let path = table.relation_name()?.path()?;
        let (schema, table_name) = name::schema_and_name_path(&path)?;

        return resolve_column_from_table_or_view_or_cte(
            db,
            file,
            name_ref,
            &table_name,
            schema.as_ref(),
            column_name,
        );
    }

    if let ast::SelectVariant::Values(values) = select_variant {
        if let Some(num_str) = column_name.0.strip_prefix("column")
            && let Ok(col_num) = num_str.parse::<usize>()
            && col_num > 0
            && let Some(expr) = values
                .row_list()
                .and_then(|x| x.rows().next()?.exprs().nth(col_num - 1))
        {
            return Some(smallvec![Location::new(
                file,
                expr.syntax().text_range(),
                LocationKind::Column
            )]);
        }
        return None;
    }

    let subquery_select = ast_nav::select_from_variant(select_variant)?;
    resolve_column_from_select_targets(
        db,
        file,
        &subquery_select,
        name_ref,
        column_name,
        column_list_len,
    )
}

fn resolve_column_from_select_targets(
    db: &dyn Db,
    file: File,
    select: &ast::Select,
    name_ref: &ast::NameRef,
    column_name: &Name,
    skip_target_count: usize,
) -> Option<SmallVec<[Location; 1]>> {
    let from_clause = select.from_clause();

    for (i, target) in select.select_clause()?.target_list()?.targets().enumerate() {
        if let Some((col_name, node)) = ColumnName::from_target(target.clone()) {
            if i < skip_target_count && !matches!(col_name, ColumnName::Star) {
                continue;
            }
            if let Some(col_name_str) = col_name.to_string()
                && Name::from_string(col_name_str) == *column_name
            {
                return Some(smallvec![Location::new(
                    file,
                    node.text_range(),
                    LocationKind::Column
                )]);
            }
            if matches!(col_name, ColumnName::Star)
                && let Some(from_clause) = &from_clause
            {
                for from_item in ast_nav::iter_from_clause(from_clause) {
                    if let Some(result) =
                        resolve_from_item_column_ptr(db, file, &from_item, name_ref)
                    {
                        return Some(result);
                    }
                }
            }
        }

        if let Some(ast::Expr::FieldExpr(field_expr)) = target.expr()
            && let Some(table_name) = qualified_star_table_name(&field_expr)
            && let Some(from_clause) = &from_clause
            && let Some(from_item) = find_from_item_in_from_clause(from_clause, &table_name)
            && let Some(result) = resolve_from_item_column_ptr(db, file, &from_item, name_ref)
        {
            return Some(result);
        }
    }

    None
}

pub(crate) fn qualified_star_table_name(field_expr: &ast::FieldExpr) -> Option<Name> {
    field_expr.star_token()?;

    match field_expr.base()? {
        ast::Expr::NameRef(name_ref) => Some(Name::from_node(&name_ref)),
        ast::Expr::FieldExpr(inner_field_expr) => {
            let field = inner_field_expr.field()?;
            Some(Name::from_node(&field))
        }
        _ => None,
    }
}

// TODO: I don't think we should be passing in file here and using it, we want
// to use the content from the updated file with the completion marker
pub(crate) fn table_ptrs_from_clause(
    db: &dyn Db,
    file: File,
    from_clause: &ast::FromClause,
) -> Vec<SyntaxNodePtr> {
    let mut results = vec![];

    for from_item in ast_nav::iter_from_clause(from_clause) {
        if let Some(alias) = from_item.alias()
            && alias.column_list().is_some()
        {
            results.push(SyntaxNodePtr::new(alias.syntax()));
            continue;
        }

        if let Some(paren_select) = from_item.paren_select() {
            results.push(SyntaxNodePtr::new(paren_select.syntax()));
            continue;
        }

        let Some((schema, table_name)) = name::schema_and_table_from_from_item(&from_item) else {
            continue;
        };

        let name_ref = from_item.name_ref();
        let position = from_item.syntax().text_range().start();
        if let Some((table_like_ptr, _kind)) = resolve_table_like(
            db,
            file,
            name_ref.as_ref(),
            &table_name,
            schema.as_ref(),
            position,
        ) {
            results.push(table_like_ptr);
        }
    }

    results
}

pub(crate) fn table_ptr_from_from_item(
    db: &dyn Db,
    file: File,
    from_item: &ast::FromItem,
) -> Option<SyntaxNodePtr> {
    if let Some(paren_select) = from_item.paren_select() {
        return Some(SyntaxNodePtr::new(paren_select.syntax()));
    }

    if let Some(paren_expr) = from_item.paren_expr() {
        return table_ptr_from_paren_expr(db, file, &paren_expr);
    }

    let (schema, table_name) = name::schema_and_table_from_from_item(from_item)?;
    let name_ref = from_item.name_ref();
    let position = from_item.syntax().text_range().start();

    resolve_table_like(
        db,
        file,
        name_ref.as_ref(),
        &table_name,
        schema.as_ref(),
        position,
    )
    .map(|(table_like_ptr, _kind)| table_like_ptr)
}

fn table_ptr_from_paren_expr(
    db: &dyn Db,
    file: File,
    paren_expr: &ast::ParenExpr,
) -> Option<SyntaxNodePtr> {
    if let Some(from_item) = paren_expr.from_item() {
        return table_ptr_from_from_item(db, file, &from_item);
    }
    if let Some(ast::Expr::ParenExpr(inner)) = paren_expr.expr() {
        return table_ptr_from_paren_expr(db, file, &inner);
    }
    None
}

fn count_columns_for_target(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    target: &ast::Target,
    from_clause: &ast::FromClause,
) -> Option<usize> {
    if target.star_token().is_some() {
        return count_columns_for_from_clause(db, file, name_ref, from_clause);
    }

    if let Some(ast::Expr::FieldExpr(field_expr)) = target.expr()
        && let Some(table_name) = qualified_star_table_name(&field_expr)
        && let Some(from_item) = find_from_item_in_from_clause(from_clause, &table_name)
    {
        return count_columns_for_from_item(db, file, name_ref, &from_item);
    }

    Some(1)
}

fn count_columns_for_from_clause(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    from_clause: &ast::FromClause,
) -> Option<usize> {
    let mut total: usize = 0;
    let mut found = false;

    for from_item in ast_nav::iter_from_clause(from_clause) {
        if let Some(count) = count_columns_for_from_item(db, file, name_ref, &from_item) {
            total = total.saturating_add(count);
            found = true;
        }
    }

    found.then_some(total)
}

fn count_columns_for_from_item(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    from_item: &ast::FromItem,
) -> Option<usize> {
    let (schema, table_name) = name::schema_and_table_from_from_item(from_item)?;
    let position = name_ref.syntax().text_range().start();
    count_columns_for_table_name(
        db,
        file,
        &table_name,
        schema.as_ref(),
        position,
        Some(name_ref),
    )
}

fn resolve_from_clause_for_cte_star(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    cte_name: &Name,
    column_name: &Name,
    from_clause: &ast::FromClause,
) -> Option<SmallVec<[Location; 1]>> {
    for from_item in ast_nav::iter_from_clause(from_clause) {
        if let Some(result) =
            resolve_from_item_for_cte_star(db, file, &from_item, name_ref, cte_name, column_name)
        {
            return Some(result);
        }
    }

    None
}

fn resolve_qualified_star_in_from_clause(
    db: &dyn Db,
    file: File,
    name_ref: &ast::NameRef,
    cte_name: &Name,
    column_name: &Name,
    from_clause: &ast::FromClause,
    table_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    let from_item = find_from_item_in_from_clause(from_clause, table_name)?;
    resolve_from_item_for_cte_star(db, file, &from_item, name_ref, cte_name, column_name)
}

fn resolve_column_from_paren_expr(
    db: &dyn Db,
    file: File,
    paren_expr: &ast::ParenExpr,
    name_ref: &ast::NameRef,
    column_name: &Name,
) -> Option<SmallVec<[Location; 1]>> {
    if let Some(select) = paren_expr.select() {
        return resolve_column_from_select_targets(db, file, &select, name_ref, column_name, 0);
    }

    if let Some(ast::Expr::CallExpr(call_expr)) = paren_expr.expr()
        && let Some(result) = resolve_column_from_call_expr_return_table(
            db,
            file,
            &call_expr,
            name_ref,
            column_name,
            0,
        )
    {
        return Some(result);
    }

    if let Some(ast::Expr::ParenExpr(paren_expr)) = paren_expr.expr() {
        return resolve_column_from_paren_expr(db, file, &paren_expr, name_ref, column_name);
    }

    if let Some(from_item) = paren_expr.from_item() {
        if let Some(paren_select) = from_item.paren_select() {
            let alias = from_item.alias();
            return resolve_subquery_column_ptr(
                db,
                file,
                &paren_select,
                name_ref,
                column_name,
                alias.as_ref(),
            );
        }

        if let Some(select_variant) = from_item
            .syntax()
            .children()
            .find_map(ast::SelectVariant::cast)
        {
            let select = ast_nav::select_from_variant(select_variant)?;
            return resolve_column_from_select_targets(db, file, &select, name_ref, column_name, 0);
        }

        if let Some(nested_paren_expr) = from_item.paren_expr() {
            return resolve_column_from_paren_expr(
                db,
                file,
                &nested_paren_expr,
                name_ref,
                column_name,
            );
        }
    }

    None
}

fn resolve_column_from_call_expr_return_table(
    db: &dyn Db,
    file: File,
    call_expr: &ast::CallExpr,
    name_ref: &ast::NameRef,
    column_name: &Name,
    min_index: usize,
) -> Option<SmallVec<[Location; 1]>> {
    let position = name_ref.syntax().text_range().start();
    let (schema, function_name) = name::schema_and_func_name(call_expr)?;
    let function_locs =
        resolve_function(db, file, &function_name, schema.as_ref(), None, position)?;
    let function_node = function_locs.first()?.to_node(db)?;
    let create_function = function_node
        .ancestors()
        .find_map(ast::CreateFunction::cast)?;
    let mut index = 0usize;
    for arg in create_function.ret_type()?.table_arg_list()?.args() {
        if let ast::TableArg::Column(column) = arg {
            if let Some(name) = column.name()
                && Name::from_node(&name) == *column_name
                && index >= min_index
            {
                return Some(smallvec![Location::new(
                    file,
                    name.syntax().text_range(),
                    LocationKind::Column
                )]);
            }
            index += 1;
        }
    }
    None
}

pub(crate) fn resolve_table_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::Table)
}

pub(crate) fn resolve_function_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::Function)
}

pub(crate) fn resolve_aggregate_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::Aggregate)
}

pub(crate) fn resolve_procedure_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::Procedure)
}

pub(crate) fn resolve_type_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::Type)
}

pub(crate) fn resolve_property_graph_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::PropertyGraph)
}

pub(crate) fn resolve_view_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::View)
}

pub(crate) fn resolve_sequence_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
) -> Option<(Schema, String)> {
    resolve_symbol_info(db, file, path, SymbolKind::Sequence)
}

fn resolve_symbol_info(
    db: &dyn Db,
    file: File,
    path: &ast::Path,
    kind: SymbolKind,
) -> Option<(Schema, String)> {
    let binder = bind(db, file);
    let name = name::table_name(path)?;
    let schema = name::schema_name(path);
    let position = path.syntax().text_range().start();
    binder.lookup_info(&name, schema.as_ref(), kind, position)
}

fn param_signature(node: &impl ast::HasParamList) -> Option<Vec<Name>> {
    let mut params = vec![];
    for param in node.param_list()?.params() {
        if let Some(ast::Type::PathType(path_type)) = param.ty()
            && let Some(name_ref) = path_type
                .path()
                .and_then(|x| x.segment())
                .and_then(|x| x.name_ref())
        {
            params.push(Name::from_node(&name_ref));
        }
    }
    (!params.is_empty()).then_some(params)
}

fn resolve_composite_type_field_ptr(
    db: &dyn Db,
    file: File,
    field_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let tree = parse(db, file).tree();
    let root = tree.syntax();
    let field_name = Name::from_node(field_name_ref);
    let field_expr = field_name_ref
        .syntax()
        .parent()
        .and_then(ast::FieldExpr::cast)?;
    let base = field_expr.base()?;

    if let ast::Expr::ParenExpr(ref paren_expr) = base
        && let Some(result) =
            resolve_column_from_paren_expr(db, file, paren_expr, field_name_ref, &field_name)
    {
        return Some(result);
    }

    let base_name_ref = ast_nav::unwrap_paren_expr(base.clone()).find_map(|e| match e {
        ast::Expr::NameRef(nr) => Some(nr),
        _ => None,
    })?;

    let column_locs = resolve_select_column_ptr(db, file, &base_name_ref)?;
    let column_node = column_locs.first()?.to_node(db)?;

    let (schema, type_name) = resolve_composite_type_from_column_node(&column_node)
        .or_else(|| resolve_composite_type_from_cast_node(&column_node))?;

    let position = field_name_ref.syntax().text_range().start();
    let type_name_ptr = resolve_type_name_ptr(db, file, &type_name, schema.as_ref(), position)?;
    let type_node = type_name_ptr.to_node(root);

    let create_type = type_node.ancestors().find_map(ast::CreateType::cast)?;
    for column in create_type.column_list()?.columns() {
        if let Some(col_name) = column.name()
            && Name::from_node(&col_name) == field_name
        {
            return Some(smallvec![Location::new(
                file,
                col_name.syntax().text_range(),
                LocationKind::Column
            )]);
        }
    }

    None
}

fn resolve_composite_type_from_column_node(
    column_node: &SyntaxNode,
) -> Option<(Option<Schema>, Name)> {
    let column = column_node.ancestors().find_map(ast::Column::cast)?;
    let ty = column.ty()?;
    name::schema_and_type_name(&ty)
}

fn resolve_composite_type_from_cast_node(
    column_node: &SyntaxNode,
) -> Option<(Option<Schema>, Name)> {
    let target = column_node.ancestors().find_map(ast::Target::cast)?;
    let ast::Expr::CastExpr(cast_expr) = target.expr()? else {
        return None;
    };
    let ty = cast_expr.ty()?;
    name::schema_and_type_name(&ty)
}

fn resolve_merge_column_ptr(
    db: &dyn Db,
    file: File,
    column_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let column_name = Name::from_node(column_name_ref);
    let merge = column_name_ref
        .syntax()
        .ancestors()
        .find_map(ast::Merge::cast)?;

    // Try resolving in source table first
    if let Some(from_item) = merge.using_on_clause().and_then(|x| x.from_item())
        && let Some(ptr) = resolve_from_item_column_ptr(db, file, &from_item, column_name_ref)
    {
        return Some(ptr);
    }

    let path = merge.relation_name()?.path()?;
    resolve_column_for_path(db, file, &path, column_name)
}

// TODO: I think we could use trait(s) here to simplify this and have the
// callers pass in the stmt instead of the fields.
fn resolve_table_in_returning_clause(
    db: &dyn Db,
    file: File,
    table_name_ref: &ast::NameRef,
    alias: Option<ast::Alias>,
    path: &ast::Path,
    returning_clause: Option<ast::ReturningClause>,
) -> Option<SmallVec<[Location; 1]>> {
    let table_name = Name::from_node(table_name_ref);
    let (schema, stmt_table_name) = name::schema_and_name_path(path)?;

    let matched =
        match_table_in_returning_clause(&table_name, &stmt_table_name, alias, returning_clause)?;

    let position = table_name_ref.syntax().text_range().start();

    match matched {
        ReturningClauseMatch::ReturningAlias(name) => Some(smallvec![Location::new(
            file,
            name.syntax().text_range(),
            LocationKind::Table
        )]),
        ReturningClauseMatch::TableAlias(alias_name) => Some(smallvec![Location::new(
            file,
            alias_name.syntax().text_range(),
            LocationKind::Table
        )]),
        ReturningClauseMatch::PseudoTable => {
            let ptr =
                resolve_table_name_ptr(db, file, &stmt_table_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Table
            )])
        }
        ReturningClauseMatch::Table => {
            let ptr = resolve_table_name_ptr(db, file, &table_name, schema.as_ref(), position)?;
            Some(smallvec![Location::new(
                file,
                ptr.text_range(),
                LocationKind::Table
            )])
        }
    }
}

fn resolve_merge_table_name_ptr(
    db: &dyn Db,
    file: File,
    table_name_ref: &ast::NameRef,
) -> Option<SmallVec<[Location; 1]>> {
    let table_name = Name::from_node(table_name_ref);
    let merge = table_name_ref
        .syntax()
        .ancestors()
        .find_map(ast::Merge::cast)?;

    let path = merge.relation_name()?.path()?;

    // Check USING clause for the source table - MERGE-specific
    if let Some(from_item) = merge.using_on_clause().and_then(|x| x.from_item()) {
        if let Some(item_name_ref) = from_item.name_ref() {
            let item_name = Name::from_node(&item_name_ref);
            if item_name == table_name {
                let position = table_name_ref.syntax().text_range().start();
                let (ptr, kind) =
                    resolve_table_like(db, file, Some(table_name_ref), &item_name, None, position)?;
                return Some(smallvec![Location::new(file, ptr.text_range(), kind)]);
            }
        }
        // Check for aliased source tables
        if let Some(alias_name) = from_item.alias().and_then(|x| x.name())
            && Name::from_node(&alias_name) == table_name
        {
            return Some(smallvec![Location::new(
                file,
                alias_name.syntax().text_range(),
                LocationKind::Table
            )]);
        }
    }

    resolve_table_in_returning_clause(
        db,
        file,
        table_name_ref,
        merge.alias(),
        &path,
        merge.returning_clause(),
    )
}

fn find_param_in_func_def(
    db: &dyn Db,
    file: File,
    function_ptr: SyntaxNodePtr,
    param_name: &Name,
) -> Option<SyntaxNodePtr> {
    let tree = parse(db, file).tree();
    let root = tree.syntax();
    let function_node = function_ptr.to_node(root);

    let param_list = function_node.ancestors().find_map(|a| {
        if let Some(create_func) = ast::CreateFunction::cast(a.clone()) {
            create_func.param_list()
        } else if let Some(create_proc) = ast::CreateProcedure::cast(a.clone()) {
            create_proc.param_list()
        } else if let Some(create_aggregate) = ast::CreateAggregate::cast(a) {
            create_aggregate.param_list()
        } else {
            None
        }
    })?;

    for param in param_list.params() {
        if let Some(name) = param.name()
            && Name::from_node(&name) == *param_name
        {
            return Some(SyntaxNodePtr::new(name.syntax()));
        }
    }

    None
}
