# SQL snippets - trigger schema: }s<command>
# s = sql

matches:
  # === DML ===
  - trigger: "}ss"
    replace: |
      SELECT
          $1$
      FROM
          $2$
      $3$;

  - trigger: "}ssa"
    replace: |
      SELECT
          $1$ -- columns
      FROM
          $2$ -- table
      $3$ -- WHERE, GROUP BY, etc.
      $4$; -- ORDER BY, LIMIT

  - trigger: "}si"
    replace: |
      INSERT INTO
          $1$ ($2$)
      VALUES
          ($3$);

  - trigger: "}su"
    replace: |
      UPDATE
          $1$
      SET
          $2$ = $3$
      WHERE
          $4$;

  - trigger: "}sd"
    replace: |
      DELETE FROM
          $1$
      WHERE
          $2$;

  # === DDL ===
  - trigger: "}sc"
    replace: |
      CREATE TABLE $1$ (
          $2$ $3$ $4$,
          $5$ $6$ $7$
      );

  - trigger: "}sci"
    replace: "CREATE INDEX $1$ ON $2$ ($3$);"

  - trigger: "}sct"
    replace: "CREATE TABLE $1$ ($2$);"

  # === CLAUSES ===
  - trigger: "}sw"
    replace: |
      WHERE
          $1$

  - trigger: "}sj"
    replace: |
      $1$ JOIN $2$
          ON $3$.$4$ = $5$.$6$

  - trigger: "}so"
    replace: |
      ORDER BY
          $1$ $2$

  - trigger: "}sg"
    replace: |
      GROUP BY
          $1$

  - trigger: "}sl"
    replace: "LIMIT $1$"

  # === ADVANCED ===
  - trigger: "}sa"
    replace: |
      SELECT
          $1$,
          COUNT(*) AS cnt,
          SUM($2$) AS total,
          AVG($3$) AS avg_val
      FROM
          $4$
      GROUP BY
          $1$;

  - trigger: "}ssq"
    replace: |
      SELECT
          $1$
      FROM
          $2$
      WHERE $3$ IN (
          SELECT $4$
          FROM $5$
          WHERE $6$
      );

  - trigger: "}scte"
    replace: |
      WITH $1$ AS (
          SELECT
              $2$
          FROM
              $3$
          WHERE
              $4$
      )
      SELECT
          $5$
      FROM
          $1$;

  - trigger: "}scase"
    replace: |
      CASE
          WHEN $1$ THEN $2$
          WHEN $3$ THEN $4$
          ELSE $5$
      END AS $6$

  - trigger: "}swin"
    replace: |
      $1$() OVER (
          PARTITION BY $2$
          ORDER BY $3$
      ) AS $4$

  # === TRANSACTIONS ===
  - trigger: "}str"
    replace: |
      BEGIN;

      $1$

      COMMIT;
      -- ROLLBACK;

  # === COMMENTS ===
  - trigger: "}scmt"
    replace: "-- $|$"

  - trigger: "}scmb"
    replace: "/* $|$ */"