import { Cardano, MultipleChoiceSearchFilter, QueryStakePoolsArgs } from '@cardano-sdk/core';
import { OrderByOptions, SubQuery } from './types';
export declare const findLastEpochWithData = "\nSELECT\n  epoch.\"no\",\n  ep.optimal_pool_count\nFROM epoch\nLEFT JOIN epoch_param ep ON\n  ep.epoch_no = epoch.\"no\"\nORDER BY no DESC\nLIMIT 1";
export declare const findPoolsMetrics = "\nWITH current_epoch AS (\n  SELECT\n    e.\"no\" AS epoch_no,\n    optimal_pool_count\n  FROM epoch e\n  JOIN epoch_param ep ON\n    ep.epoch_no = e.\"no\"\n  ORDER BY e.no DESC LIMIT 1\n),\nblocks_created AS (\n  SELECT\n    COUNT(1) AS blocks_created,\n    pool_hash.id AS pool_hash_id\n  FROM block\n    JOIN slot_leader ON block.slot_leader_id = slot_leader.id\n    JOIN pool_hash ON slot_leader.pool_hash_id = pool_hash.id\n  WHERE pool_hash.id = ANY($1)\n  GROUP BY pool_hash.id\n),\npools_delegates AS (\n  SELECT\n    ph.id AS pool_hash_id,\n    sa.id AS addr_id\n  FROM pool_hash ph\n  JOIN pool_update pu\n    ON pu.id = (\n      SELECT id\n      FROM pool_update pu2\n      WHERE pu2.hash_id = ph.id\n      ORDER BY id DESC\n      LIMIT 1\n    )\n  LEFT JOIN pool_retire pr\n    ON pr.id = (\n      SELECT id\n      FROM pool_retire pr2\n      WHERE pr2.hash_id = ph.id\n      ORDER BY id desc\n      LIMIT 1\n    )\n  JOIN stake_address sa ON\n    sa.id  = pu.reward_addr_id\n  WHERE (pr.id is null or pr.announced_tx_id < pu.registered_tx_id) and\n    ph.id = ANY($1)\n  ),\n  total_rewards_of_reward_acc AS (\n    SELECT\n      SUM(r.amount) AS amount,\n      pd.pool_hash_id\n    FROM reward r\n    JOIN pools_delegates pd ON\n      pd.addr_id = r.addr_id\n    GROUP BY pd.pool_hash_id\n  ),\n  total_withdraws_of_reward_acc AS (\n    SELECT\n      SUM(w.amount) AS amount,\n      pd.pool_hash_id\n    FROM withdrawal w\n    JOIN pools_delegates pd ON\n      pd.addr_id = w.addr_id\n    GROUP BY pd.pool_hash_id\n  ),\n  owners_total_utxos AS (\n    SELECT\n      sum(tx_out.value) AS amount,\n      pu.hash_id\n    FROM tx_out\n    JOIN pool_owner o ON\n      o.addr_id = tx_out.stake_address_id\n    JOIN pool_update pu ON\n      o.pool_update_id = pu.id\n      AND pu.hash_id = ANY($1)\n    LEFT JOIN tx_in ON\n      tx_out.tx_id = tx_in.tx_out_id AND\n      tx_out.index = tx_in.tx_out_index\n    WHERE\n      tx_in.tx_out_id IS null\n    GROUP BY pu.hash_id\n  ),\nactive_stake AS (\nSELECT\n  sum(es.amount) AS active_stake,\n  es.pool_id  AS pool_hash_id\nFROM epoch_stake es\nWHERE es.pool_id = ANY($1)\n  AND es.epoch_no = (SELECT epoch_no FROM current_epoch)\nGROUP BY es.pool_id\n),\nactive_delegations AS (\n  SELECT\n    d1.addr_id,\n    ph.id AS pool_hash_id\n  FROM pool_hash ph\n  JOIN delegation d1 ON\n    ph.id = d1.pool_hash_id\n  WHERE ph.id = ANY($1)\n   AND NOT EXISTS\n     (SELECT TRUE\n      FROM delegation d2\n      WHERE d2.addr_id=d1.addr_id\n        AND d2.tx_id>d1.tx_id)\n   AND NOT EXISTS\n     (SELECT TRUE\n      FROM stake_deregistration\n      WHERE stake_deregistration.addr_id=d1.addr_id\n        AND stake_deregistration.tx_id>d1.tx_id)\n),\ndelegators AS (\n  SELECT\n    COUNT(1) AS delegators,\n    d.pool_hash_id\n  FROM active_delegations d\n  GROUP BY d.pool_hash_id\n),\ntotal_utxos AS (\n  SELECT\n    COALESCE(SUM(tx_out.value),0) AS total_amount,\n    ad.pool_hash_id\n  FROM active_delegations ad\n  JOIN tx_out ON\n    tx_out.stake_address_id = ad.addr_id\n  LEFT JOIN tx_in ON\n    tx_out.tx_id = tx_in.tx_out_id AND\n    tx_out.index = tx_in.tx_out_index\n  WHERE\n    tx_in.tx_out_id IS NULL\n  GROUP BY ad.pool_hash_id\n),\ntotal_rewards AS (\n  SELECT\n    COALESCE(SUM(r.amount),0) AS total_amount,\n    ad.pool_hash_id\n    FROM active_delegations ad\n    JOIN reward r ON\n      ad.addr_id = r.addr_id\n    WHERE r.spendable_epoch <= (SELECT epoch_no FROM current_epoch)\n    GROUP BY ad.pool_hash_id\n),\ntotal_withdraws AS (\n  SELECT\n    COALESCE(SUM(w.amount),0) AS total_amount,\n    ad.pool_hash_id\n    FROM withdrawal w\n    JOIN active_delegations ad ON ad.addr_id = w.addr_id\n    GROUP BY ad.pool_hash_id\n),\nlive_stake AS (\n  SELECT\n    (total_utxos.total_amount +\n    COALESCE(tr.total_amount,0) -\n    COALESCE(tw.total_amount,0)) AS live_stake,\n    total_utxos.pool_hash_id\n  FROM total_utxos\n  LEFT JOIN total_rewards tr ON\n    total_utxos.pool_hash_id = tr.pool_hash_id\n  LEFT JOIN total_withdraws tw ON\n    total_utxos.pool_hash_id = tw.pool_hash_id\n)\nSELECT\n COALESCE(bc.blocks_created,0)::integer AS blocks_created,\n COALESCE(d.delegators,0)::integer AS delegators,\n COALESCE(a_stake.active_stake,0) AS active_stake,\n COALESCE(l_stake.live_stake,0) AS live_stake,\n (COALESCE(tr.amount,0) - COALESCE(tw.amount,0) + COALESCE (otu.amount,0))\n AS live_pledge,\n CASE\n    WHEN $2::numeric = 0::numeric\n    THEN 0::numeric\n    ELSE\n      (\n        COALESCE(l_stake.live_stake,0::numeric) *\n        ((SELECT optimal_pool_count FROM current_epoch)::NUMERIC) /\n        ($2::numeric)\n      )::numeric\n  END AS saturation,\n  CASE\n    WHEN (COALESCE(l_stake.live_stake,0))::numeric = 0::numeric\n    THEN 0::numeric\n    ELSE\n    (COALESCE(a_stake.active_stake,0)/COALESCE(l_stake.live_stake,0))\n  END AS active_stake_percentage,\n  ph.id AS pool_hash_id\nFROM pool_hash ph\nLEFT JOIN blocks_created bc ON\n  bc.pool_hash_id = ph.id\nLEFT JOIN delegators d ON\n  d.pool_hash_id = ph.id\nLEFT JOIN active_stake a_stake ON\n  a_stake.pool_hash_id = ph.id\nLEFT JOIN live_stake l_stake ON\n  l_stake.pool_hash_id = ph.id\nLEFT JOIN total_rewards_of_reward_acc AS tr ON\n  tr.pool_hash_id = ph.id\nLEFT JOIN total_withdraws_of_reward_acc AS tw ON\n  tw.pool_hash_id = ph.id\nLEFT JOIN owners_total_utxos otu ON\n  otu.hash_id = ph.id\nWHERE id = ANY($1)\n";
export declare const findBlockfrostPoolsMetrics = "\nSELECT\n  COALESCE(blocks_created, 0) AS blocks_created,\n  COALESCE(delegators, 0) AS delegators,\n  COALESCE(active_stake, 0) AS active_stake,\n  COALESCE(live_stake, 0) AS live_stake,\n  COALESCE(live_pledge, 0) AS live_pledge,\n  COALESCE(saturation, 0) AS saturation,\n  COALESCE(active_stake / NULLIF(live_stake, 0), 0) AS active_stake_percentage,\n  COALESCE(reward_address, '') AS reward_address,\n  COALESCE(extra, '[[],[],[]]') AS extra,\n  COALESCE(status, 'retired') AS status,\n  pool_hash_id\nFROM pool_hash\nLEFT JOIN blockfrost.pool_metric ON\n  pool_hash_id = id\nWHERE\n  id = ANY($1)\n";
export declare const findPoolAPY: (epochLength: number, limit?: number) => string;
export declare const findPools = "\nSELECT\n  ph.id,\n  pu.id AS update_id\nFROM pool_hash ph\nJOIN pool_update pu\n  ON pu.id = (\n    SELECT id\n    FROM pool_update pu2\n    WHERE pu2.hash_id = ph.id\n    ORDER BY id DESC\n    LIMIT 1\n  )\n";
export declare const findPoolsRelays = "\nSELECT\n  hash_id,\n  update_id,\n  ipv4,\n  ipv6,\n  port,\n  dns_name,\n  dns_srv_name AS hostname --fixme: check this is correct\nFROM pool_relay\nJOIN pool_update\n  ON pool_relay.update_id = pool_update.id\nWHERE update_id = ANY($1)\n";
export declare const findPoolsOwners = "\nSELECT\n   address.\"view\" AS address,\n  pool_update.hash_id AS hash_id\nFROM pool_owner AS \"owner\"\nJOIN pool_update\n   ON \"owner\".pool_update_id = pool_update.id\nJOIN stake_address AS address\n   ON \"owner\".addr_id = address.id\nWHERE pool_update.id = ANY($1)\n";
export declare const findPoolsRegistrations = "\nSELECT\n  tx.hash AS tx_hash,\n  pu.hash_id AS hash_id,\n  active_epoch_no\nFROM pool_update pu\nJOIN tx\n  ON tx.id = pu.registered_tx_id\nWHERE pu.hash_id = ANY($1)\nORDER BY pu.id DESC\n";
export declare const findPoolsRetirements = "\nSELECT\n  tx.hash AS tx_hash,\n  pr.hash_id AS hash_id,\n  retiring_epoch\nFROM pool_retire pr\nJOIN tx\n  ON tx.id = pr.announced_tx_id\nWHERE pr.hash_id = ANY($1)\n";
export declare const poolsByPledgeMetSubqueries: readonly SubQuery[];
export declare const POOLS_WITH_PLEDGE_MET: {
    JOIN_CLAUSE: string;
    SELECT_CLAUSE: string;
    WHERE_CLAUSE: (metPledge: boolean) => string;
    WITH_CLAUSE: string;
};
export declare const STATUS_QUERY: {
    SELECT_CLAUSE: string;
    WITH_CLAUSE: string;
};
export declare const IDENTIFIER_QUERY: {
    JOIN_CLAUSE: {
        OFFLINE_METADATA: string;
        POOL_UPDATE: string;
    };
    SELECT_CLAUSE: string;
};
export declare const getIdentifierFullJoinClause: () => string;
export declare const findPoolsData = "\nSELECT\n  pu.hash_id,\n  ph.hash_raw AS pool_hash,\n  pu.id AS update_id,\n  ph.view AS pool_id,\n  sa.view AS reward_address,\n  pu.reward_addr_id,\n  pu.pledge,\n  pu.fixed_cost,\n  pu.margin,\n  pu.vrf_key_hash,\n  metadata.url AS metadata_url,\n  metadata.hash AS metadata_hash,\n  pod.json AS offline_data\nFROM pool_update pu\nJOIN pool_hash ph ON\n  ph.id = pu.hash_id\nJOIN stake_address sa ON\n  sa.id = pu.reward_addr_id\nLEFT JOIN pool_metadata_ref metadata\n  ON metadata.id = pu.meta_id\nLEFT JOIN off_chain_pool_data pod\n  ON metadata.id = pod.pmr_id\nWHERE pu.id = ANY($1)\n";
export declare const findBlockfrostPoolsData = "\nSELECT\n  pu.hash_id,\n  ph.hash_raw AS pool_hash,\n  pu.id AS update_id,\n  ph.view AS pool_id,\n  pu.reward_addr_id,\n  pu.pledge,\n  pu.fixed_cost,\n  pu.margin,\n  pu.vrf_key_hash,\n  metadata.url AS metadata_url,\n  metadata.hash AS metadata_hash,\n  pod.json AS offline_data\nFROM pool_update pu\nJOIN pool_hash ph ON\n  ph.id = pu.hash_id\nLEFT JOIN pool_metadata_ref metadata\n  ON metadata.id = pu.meta_id\nLEFT JOIN off_chain_pool_data pod\n  ON metadata.id = pod.pmr_id\nWHERE pu.id = ANY($1)\n";
export declare const getIdentifierWhereClause: (identifier: MultipleChoiceSearchFilter<Partial<Pick<Cardano.PoolParameters, 'id'> & Pick<Cardano.StakePoolMetadata, 'name' | 'ticker'>>>) => {
    params: string[];
    where: string;
};
export declare const getStatusWhereClause: (status: Cardano.StakePoolStatus[], columns?: {
    activeEpoch?: string;
}) => string;
export declare const withPagination: (query: string, args: unknown[], pagination?: QueryStakePoolsArgs['pagination']) => {
    args: unknown[];
    query: string;
};
export declare const addSentenceToQuery: (query: string, sentence: string) => string;
export declare const buildOrQueryFromClauses: (clauses: SubQuery[]) => string;
export declare const findPoolStats = "\nWITH current_epoch AS (\n  SELECT MAX(epoch_no) AS epoch_no\n  FROM block\n),\nlast_pool_update AS (\n  SELECT\n    pool_update.hash_id,\n    pool_update.registered_tx_id,\n    pool_update.active_epoch_no\n  FROM pool_update\n  JOIN (\n    SELECT hash_id, MAX(registered_tx_id) AS tx_id\n    FROM pool_update\n    WHERE active_epoch_no <= (SELECT epoch_no FROM current_epoch)\n    GROUP BY hash_id\n  ) AS last_update ON pool_update.hash_id = last_update.hash_id\n  AND pool_update.registered_tx_id = last_update.tx_id\n),\nlast_pool_retire AS (\n  SELECT\n    pool_retire.hash_id,\n    MAX(pool_retire.announced_tx_id) AS announced_tx_id,\n    pool_retire.retiring_epoch FROM pool_retire\n  JOIN (\n    SELECT hash_id, MAX(retiring_epoch) AS epoch\n    FROM pool_retire\n    GROUP BY hash_id\n  ) AS last_retired ON pool_retire.hash_id = last_retired.hash_id\n  AND pool_retire.retiring_epoch = last_retired.epoch\n  GROUP BY pool_retire.hash_id, pool_retire.retiring_epoch\n)\nSELECT\n  COUNT(\n    CASE WHEN pool_retire.hash_id IS NULL\n      OR (\n        pool_update.active_epoch_no > pool_retire.retiring_epoch\n        AND pool_retire.retiring_epoch <= (SELECT epoch_no FROM current_epoch)\n      ) THEN 1 ELSE NULL END) AS active,\n  COUNT(\n    CASE WHEN pool_retire.hash_id IS NOT NULL\n      AND (\n        pool_update.active_epoch_no <= pool_retire.retiring_epoch\n        AND pool_retire.retiring_epoch <= (SELECT epoch_no FROM current_epoch)\n      ) THEN 1 ELSE NULL END) AS retired,\n  COUNT(\n    CASE WHEN pool_retire.hash_id IS NOT NULL\n      AND (\n        pool_update.active_epoch_no <= pool_retire.retiring_epoch\n        AND pool_retire.retiring_epoch > (SELECT epoch_no FROM current_epoch)\n      ) THEN 1 ELSE NULL END) AS retiring\nFROM last_pool_update AS pool_update\nLEFT JOIN last_pool_retire AS pool_retire\n  ON pool_update.hash_id = pool_retire.hash_id";
export declare const withSort: (query: string, sort?: QueryStakePoolsArgs['sort'], defaultSort?: OrderByOptions[]) => string;
export declare const blockfrostQuery: {
    readonly SELECT: "\nWITH pool_updates AS (\n  SELECT\n    hash_id,\n    MAX(id) AS update_id\n  FROM pool_update\n  GROUP BY hash_id\n)\nSELECT\n  ph.id,\n  pu.update_id\nFROM pool_hash ph\nJOIN pool_updates pu ON\n  pu.hash_id = ph.id";
    readonly identifier: {
        readonly JOIN: "\nLEFT JOIN off_chain_pool_data pod ON\n  pmr_id = meta_id";
    };
    readonly identifierOrPledge: {
        readonly JOIN: "\nJOIN pool_update pl ON\n  pl.id = pu.update_id";
    };
    readonly pledge: {
        readonly WHERE: (pledgeMet: boolean) => string;
    };
    readonly pledgeOrStatus: {
        readonly JOIN: "\nLEFT JOIN blockfrost.pool_metric pm ON\n  pm.pool_hash_id = ph.id";
    };
    readonly status: {
        readonly WHERE: (status: Cardano.StakePoolStatus[]) => string;
    };
};
declare const Queries: {
    IDENTIFIER_QUERY: {
        JOIN_CLAUSE: {
            OFFLINE_METADATA: string;
            POOL_UPDATE: string;
        };
        SELECT_CLAUSE: string;
    };
    POOLS_WITH_PLEDGE_MET: {
        JOIN_CLAUSE: string;
        SELECT_CLAUSE: string;
        WHERE_CLAUSE: (metPledge: boolean) => string;
        WITH_CLAUSE: string;
    };
    STATUS_QUERY: {
        SELECT_CLAUSE: string;
        WITH_CLAUSE: string;
    };
    findBlockfrostPoolsData: string;
    findBlockfrostPoolsMetrics: string;
    findLastEpoch: string;
    findLastEpochWithData: string;
    findPoolAPY: (epochLength: number, limit?: number) => string;
    findPoolStats: string;
    findPools: string;
    findPoolsData: string;
    findPoolsMetrics: string;
    findPoolsOwners: string;
    findPoolsRegistrations: string;
    findPoolsRelays: string;
    findPoolsRetirements: string;
};
export default Queries;
//# sourceMappingURL=queries.d.ts.map