pub const INIT_SCHEMA_QUERY: &str = r#"
            DEFINE TABLE IF NOT EXISTS temporal_node SCHEMAFULL;
            DEFINE FIELD IF NOT EXISTS tenant_id         ON temporal_node TYPE string;
            DEFINE FIELD IF NOT EXISTS session_id        ON temporal_node TYPE string;
            DEFINE FIELD IF NOT EXISTS raw               ON temporal_node TYPE string;
            DEFINE FIELD IF NOT EXISTS tier              ON temporal_node TYPE string;
            DEFINE FIELD IF NOT EXISTS timestamp         ON temporal_node TYPE datetime;
            DEFINE FIELD IF NOT EXISTS compression_depth ON temporal_node TYPE int;
            DEFINE FIELD IF NOT EXISTS parent_node_id    ON temporal_node TYPE option<string>;
            DEFINE FIELD IF NOT EXISTS psi               ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS rho               ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS kappa             ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS user_stability    ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS user_friction     ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS user_logic        ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS user_autonomy     ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS user_psi          ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS model_stability   ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS model_friction    ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS model_logic       ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS model_autonomy    ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS model_psi         ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS comp_stability    ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS comp_friction     ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS comp_logic        ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS comp_autonomy     ON temporal_node TYPE float;
            DEFINE FIELD IF NOT EXISTS comp_psi          ON temporal_node TYPE float;

            DEFINE TABLE IF NOT EXISTS calibration SCHEMAFULL;
            DEFINE FIELD IF NOT EXISTS tenant_id   ON calibration TYPE string;
            DEFINE FIELD IF NOT EXISTS session_id  ON calibration TYPE string;
            DEFINE FIELD IF NOT EXISTS stability   ON calibration TYPE float;
            DEFINE FIELD IF NOT EXISTS friction    ON calibration TYPE float;
            DEFINE FIELD IF NOT EXISTS logic       ON calibration TYPE float;
            DEFINE FIELD IF NOT EXISTS autonomy    ON calibration TYPE float;
            DEFINE FIELD IF NOT EXISTS psi         ON calibration TYPE float;
            DEFINE FIELD IF NOT EXISTS trigger     ON calibration TYPE string;
            DEFINE FIELD IF NOT EXISTS created_at  ON calibration TYPE datetime;

            DEFINE INDEX IF NOT EXISTS idx_node_session ON temporal_node FIELDS session_id;
            DEFINE INDEX IF NOT EXISTS idx_node_tenant_session ON temporal_node FIELDS tenant_id, session_id;
            DEFINE INDEX IF NOT EXISTS idx_cal_session ON calibration FIELDS session_id;
            DEFINE INDEX IF NOT EXISTS idx_cal_tenant_session ON calibration FIELDS tenant_id, session_id;
            SELECT * FROM calibration LIMIT 0;
            "#;

pub fn query_nodes_query(where_clause: &str, capped_limit: usize) -> String {
    format!(
        r#"
            SELECT
                tenant_id AS TenantId,
                session_id AS SessionId,
                raw AS Raw,
                tier AS Tier,
                timestamp AS Timestamp,
                compression_depth AS CompressionDepth,
                parent_node_id AS ParentNodeId,
                psi AS Psi,
                rho AS Rho,
                kappa AS Kappa,
                user_stability AS UserStability,
                user_friction AS UserFriction,
                user_logic AS UserLogic,
                user_autonomy AS UserAutonomy,
                user_psi AS UserPsi,
                model_stability AS ModelStability,
                model_friction AS ModelFriction,
                model_logic AS ModelLogic,
                model_autonomy AS ModelAutonomy,
                model_psi AS ModelPsi,
                comp_stability AS CompStability,
                comp_friction AS CompFriction,
                comp_logic AS CompLogic,
                comp_autonomy AS CompAutonomy,
                comp_psi AS CompPsi,
                0 AS ResonanceDelta
            FROM temporal_node
            {where_clause}
            ORDER BY Timestamp DESC
            LIMIT {capped_limit};
            "#
    )
}

pub fn create_temporal_node_query(record_id: &str, include_parent_assignment: bool) -> String {
    let parent_assignment = if include_parent_assignment {
        "\n                parent_node_id = $parent_node_id,"
    } else {
        ""
    };

    format!(
        r#"
            CREATE temporal_node:`{record_id}` SET
                tenant_id = $tenant_id,
                session_id = $session_id,
                raw = $raw,
                tier = $tier,
                timestamp = <datetime>$timestamp,
                compression_depth = $compression_depth,{parent_assignment}
                psi = $psi,
                rho = $rho,
                kappa = $kappa,
                user_stability = $user_stability,
                user_friction = $user_friction,
                user_logic = $user_logic,
                user_autonomy = $user_autonomy,
                user_psi = $user_psi,
                model_stability = $model_stability,
                model_friction = $model_friction,
                model_logic = $model_logic,
                model_autonomy = $model_autonomy,
                model_psi = $model_psi,
                comp_stability = $comp_stability,
                comp_friction = $comp_friction,
                comp_logic = $comp_logic,
                comp_autonomy = $comp_autonomy,
                comp_psi = $comp_psi;
            "#
    )
}

pub fn get_by_resonance_query(current_psi: f32, limit: usize) -> String {
    let psi = format!("{current_psi:.4}");
    format!(
        r#"
            SELECT
                tenant_id AS TenantId,
                session_id AS SessionId,
                raw AS Raw,
                tier AS Tier,
                timestamp AS Timestamp,
                compression_depth AS CompressionDepth,
                parent_node_id AS ParentNodeId,
                psi AS Psi,
                rho AS Rho,
                kappa AS Kappa,
                user_stability AS UserStability,
                user_friction AS UserFriction,
                user_logic AS UserLogic,
                user_autonomy AS UserAutonomy,
                user_psi AS UserPsi,
                model_stability AS ModelStability,
                model_friction AS ModelFriction,
                model_logic AS ModelLogic,
                model_autonomy AS ModelAutonomy,
                model_psi AS ModelPsi,
                comp_stability AS CompStability,
                comp_friction AS CompFriction,
                comp_logic AS CompLogic,
                comp_autonomy AS CompAutonomy,
                comp_psi AS CompPsi,
                math::abs(psi - {psi}) AS ResonanceDelta
            FROM temporal_node
                        WHERE session_id = $session_id
                            AND (tenant_id = $tenant_id OR tenant_id = NONE OR tenant_id = '')
            ORDER BY ResonanceDelta ASC
            LIMIT {limit};
            "#
    )
}

pub const GET_LAST_AVEC_QUERY: &str = r#"
            SELECT stability, friction, logic, autonomy, psi, created_at
            FROM calibration
                        WHERE session_id = $session_id
                            AND (tenant_id = $tenant_id OR tenant_id = NONE OR tenant_id = '')
            ORDER BY created_at DESC
            LIMIT 1;
            "#;

pub const GET_TRIGGER_HISTORY_QUERY: &str = r#"
            SELECT trigger, created_at FROM calibration
                        WHERE session_id = $session_id
                            AND (tenant_id = $tenant_id OR tenant_id = NONE OR tenant_id = '')
            ORDER BY created_at ASC;
            "#;

pub const STORE_CALIBRATION_QUERY: &str = r#"
            CREATE calibration SET
                tenant_id = $tenant_id,
                session_id = $session_id,
                stability = $stability,
                friction = $friction,
                logic = $logic,
                autonomy = $autonomy,
                psi = $psi,
                trigger = $trigger,
                created_at = <datetime>$created_at;
            "#;

pub const SELECT_TEMPORAL_NODE_MISSING_TENANT_QUERY: &str = r#"
            SELECT id, session_id
            FROM temporal_node
            WHERE tenant_id = NONE OR tenant_id = '';
            "#;

pub const SELECT_CALIBRATION_MISSING_TENANT_QUERY: &str = r#"
            SELECT id, session_id
            FROM calibration
            WHERE tenant_id = NONE OR tenant_id = '';
            "#;

pub const SELECT_SCOPE_BY_NODE_ID_QUERY: &str = r#"
                        SELECT
                                tenant_id AS TenantId,
                                session_id AS SessionId
                        FROM temporal_node
            WHERE id = type::record('temporal_node', $node_id)
                        LIMIT 1;
                        "#;

pub const COUNT_TEMPORAL_SCOPE_QUERY: &str = r#"
                        SELECT count() AS Count
                        FROM temporal_node
                        WHERE session_id = $session_id
                            AND (tenant_id = $tenant_id OR ($include_legacy AND (tenant_id = NONE OR tenant_id = '')))
                        LIMIT 1;
                        "#;

pub const COUNT_CALIBRATION_SCOPE_QUERY: &str = r#"
                        SELECT count() AS Count
                        FROM calibration
                        WHERE session_id = $session_id
                            AND (tenant_id = $tenant_id OR ($include_legacy AND (tenant_id = NONE OR tenant_id = '')))
                        LIMIT 1;
                        "#;

pub const APPLY_SCOPE_REKEY_QUERY: &str = r#"
                        BEGIN TRANSACTION;

                        UPDATE temporal_node
                        SET
                                tenant_id = $target_tenant_id,
                                session_id = $target_session_id
                        WHERE session_id = $source_session_id
                            AND (tenant_id = $source_tenant_id OR ($source_include_legacy AND (tenant_id = NONE OR tenant_id = '')));

                        UPDATE calibration
                        SET
                                tenant_id = $target_tenant_id,
                                session_id = $target_session_id
                        WHERE session_id = $source_session_id
                            AND (tenant_id = $source_tenant_id OR ($source_include_legacy AND (tenant_id = NONE OR tenant_id = '')));

                        COMMIT TRANSACTION;
                        "#;

pub fn update_record_tenant_query(record_id: &str) -> String {
    format!(
        r#"
            UPDATE {record_id}
            SET tenant_id = $tenant_id;
            "#
    )
}
