#Query SQL yang potensial muncul

1. Select all data
SELECT * FROM users;

2. Select data by id (params)
SELECT * FROM users WHERE id_user = ?;

3. Update data
UPDATE users
SET username = ?, email = ?, status = ?
WHERE id_user = ?;

4. Insert data
INSERT INTO users (username, email, status)
VALUES (?, ?, ?);

5. Delete data
DELETE FROM users WHERE id_user = ?;

6. Order by
SELECT * FROM produk ORDER BY created_at DESC;

7. Filter data (where)
SELECT * FROM produk WHERE stok > 10;

8. Filter + Order by 
SELECT * FROM produk
WHERE harga > 100000
ORDER BY harga ASC;

9. Limit data
SELECT * FROM produk
ORDER BY created_at DESC
LIMIT 5;

10. Search (like)
SELECT * FROM produk
WHERE nama_produk LIKE '%rak%';

11. Count (hitung aggregat)
SELECT COUNT(*) AS total_user FROM users;

12. Join (tampilkan order beserta nama user)
SELECT users.username, orders.total_harga
FROM orders
JOIN users ON users.id_user = orders.user_id;

PATTERN BACA SOAL

| Kata di Soal   | SQL                        |
| -------------- | -------------------------- |
| berdasarkan ID | `WHERE id = ?`             |
| terbaru        | `ORDER BY created_at DESC` |
| terlama        | `ORDER BY created_at ASC`  |
| lebih dari     | `>`                        |
| kurang dari    | `<`                        |
| mengandung     | `LIKE '%keyword%'`         |
| jumlah         | `COUNT()`                  |
| hapus          | `DELETE`                   |

=================
# Kode Regex
=================

const idRegex = /^[0-9]+$/;

const usernameRegex = /^[a-zA-Z0-9_]{3,20}$/;

const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

const passwordRegex = /^(?=.*[A-Za-z])(?=.*\d)[A-Za-z\d]{8,}$/;

const statusRegex = /^(active|inactive)$/;

const numberRegex = /^[0-9]+$/;

const productNameRegex = /^[a-zA-Z0-9\s]{3,50}$/;

const searchRegex = /^[a-zA-Z0-9\s]+$/;

const phoneRegex = /^08[0-9]{8,11}$/;

const dateRegex = /^\d{4}-\d{2}-\d{2}$/;

================
SEQUELIZE CLI
================

0. Templete project backend
 -Install semua dependency

npm init -y 
npm i express nodemon mysql2 sequelize sequelize-cli multer dotenv express-validator jsonwebtoken bcrypt express-rate-limit

 -Customize Sequelizerc 

const path = require("path");

module.exports = {
  config: path.resolve("src", "module", "config", "database.js"),
  "models-path": path.resolve("src", "db", "models"),
  "seeders-path": path.resolve("src", "db", "seeders"),
  "migrations-path": path.resolve("src", "db", "migrations"),
};

 -Customize .env

NODE_ENV=development
DB_HOST=localhost
DB_PORT=3306
DB_NAME=petik-care
DB_USER=
DB_PASS=
DB_DIALECT=mysql
TZ=Asia/Jakarta
JWT_SECRET=
JWT_REFRESH_SECRET=

1. Project bootstraping
Ini buat ngeinisialisasi dependensinya
   ---npx sequelize-cli init---
   kode ini akan secara otomatis menggenerate 4 folder utama kita (models, migrations, config, seeders)
   - models
   folder yang berhubungan dengan semua program (Representasi JavaScript)
   - migrations
   adalah folder yang menyimpan perubahan dalam database

   "MODELS dan MIGRATIONS adalah 2 file yang selalu berhubungan"
   maka models tidak akan bisa dibuat tanpa migrations, tapi migations bias tanpa models.

Ini buat ngecreate table
    ---npx sequelize-cli model:generate --name User --attributes firstName:string,lastName:string---

Ini buat ngeeksekusi migrasi
  ---npx sequelize-cli db:migrate---

Ini buat nge-undo
   ---npx sequelize-cli db:migrate:undo---

Ini untuk membuat seeders   
   ---npx sequelize-cli seed:generate --name demo-user---

2. Jika ingin menambahkan collumn baru maka harus membuat folder baru dengan sintaks seperti di bawah
    -- npx sequelize-cli migration-generate --name nama-kolom --
  Jangan lupa ganti createTable di fungsi up dengan addColumn,
  dan ganti dropTable dengan removeColumn.



=================
SEQUELIZERC
=================

adalah tools untuk sequelize-cli menentukan folder menjadi lebih fleksible, tidak melulu harus 4 folder bawaan (models, migrations, config, seeders) , atau sederhananya SQLZRC ini adalah konfigurasi sqlz-cli
Sintaks dasarnya:

const path = require("path");

module.exports = {
  config: path.resolve("src/config/database.json"),
  "models-path": path.resolve("src/db/models"),
  "migrations-path": path.resolve("src/db/migrations"),
  "seeders-path": path.resolve("src/db/seeders"),
};

===================================
RELASI (hasOne, hasMany, belongsTo)
===================================
noted: atur erlasi di models sebelum terjun ke coding

-Belongs To 
  Peserta.belongsTo(Jurusan, {
  foreignKey: "jurusanId",
  as: "jurusan",
  });

-Has Many 
  Jurusan.hasMany(Peserta, {
  foreignKey: "jurusanId",
  as: "peserta",
  });

-Include (JOIN)
  include: [
  {
    model: Jurusan,
    as: "jurusan",
    attributes: ["nama_jurusan"],
  },
  ]


==========================  
MOST USED SEQUELIZE SYNTAX
==========================

1. CRUD dasar
---------------
  .create() 
  .findAll()
  .findOne({where: {idPeserta : 1}})
  .update({ status: "active" }, { where: { id_peserta: 1 } });
  .destroy({where: { id_peserta: 1 },});

2 Where Operator
----------------
*noted: sebelum menggunakan operator, lakukan dulu import { Op } dari sequelize /

  -and 
  where: {status: "active",jurusanId: 1,}

  -or
  where: {
  [Op.or]: [
    { status: "active" },
    { status: "inactive" },
  ],
  }

  -like
  where: {
  nama_peserta: {
    [Op.like]: "%bud%",
  },
  }

  -in 
  where: {
  jurusanId: {
    [Op.in]: [1, 2, 3],
  },
  }

3. Select Column (agar response lebih bersih)
---------------------------------------------
  - EXCLUDE createdAt & updatedAt
  attributes: {
  exclude: ["createdAt", "updatedAt"],
  }

  - PILIH KOLOM
  attributes: ["id_peserta", "nama_peserta"]

4. Aggregation (Statistik)
--------------------------
- Count
  attributes: [
  "jurusanId",
  [Sequelize.fn("COUNT", Sequelize.col("id_peserta")), "total"],
  ],
  group: ["jurusanId"]

- Count + Join
  Jurusan.findAll({
  attributes: [
    "nama_jurusan",
    [Sequelize.fn("COUNT", Sequelize.col("peserta.id_peserta")), "total_peserta"],
  ],
  include: [{ model: Peserta, as: "peserta", attributes: [] }],
  group: ["Jurusan.id_jurusan"],
  });


5. Pagination
--------------
  const limit = 10;
  const offset = (page - 1) * limit;

  await Peserta.findAll({
  limit,
  offset,
  });

===============
MULTER
===============

Noted:
-Batasi extensi file
-Batasi ukuran file

=============
DOTENV
=============

Untuk mengamankan case sensitive yang berkaitan dengan database 



