Recall Desain Database, Integrasi dengan Backend, ORM

praktikum-pert-3/
├─ db/                               # script SQL (sekali saja, dijalankan di SSMS)
│  ├─ 00-setup-mixed-mode.sql
│  ├─ 01-create-database-and-user.sql
│  ├─ 02-schema.sql
│  └─ 03-seed.sql
└─ hands-on-1/
   ├─ package.json
   ├─ tsconfig.json
   ├─ .env.example
   └─ src/
      ├─ index.ts
      └─ config/
         └─ database.ts

USE master;
GO

-- 1) Cek apakah instance masih Windows-only.
--    1 = SQL Authentication mati, 0 = mixed mode aktif.
SELECT
    SERVERPROPERTY('IsIntegratedSecurityOnly') AS windows_only,
    SERVERPROPERTY('InstanceName')             AS instance_name;
GO

-- 2) Aktifkan mixed mode (SQL Server + Windows Authentication).
--    LoginMode: 1 = Windows only, 2 = Mixed.
--    Path registry di bawah berlaku untuk DEFAULT instance (localhost).
--    Untuk named instance (mis. .\SQLEXPRESS), aktifkan lewat SSMS:
--    Server Properties -> Security -> "SQL Server and Windows Authentication mode".
EXEC xp_instance_regwrite
     N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer',
     N'LoginMode',
     REG_DWORD,
     2;
GO

USE master;
GO

-- 1) Database aplikasi.
IF DB_ID('review_kantin') IS NULL
BEGIN
    CREATE DATABASE review_kantin;
END
GO

-- 2) Login aplikasi (SQL Authentication).
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'praktikum_user')
BEGIN
    CREATE LOGIN praktikum_user
        WITH PASSWORD = N'Praktikum2026!',
             DEFAULT_DATABASE = review_kantin;
END
GO

-- 3) User di dalam database + hak akses baca/tulis.
USE review_kantin;
GO

IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = 'praktikum_user')
BEGIN
    CREATE USER praktikum_user FOR LOGIN praktikum_user;
END
GO

ALTER ROLE db_datareader ADD MEMBER praktikum_user;
ALTER ROLE db_datawriter ADD MEMBER praktikum_user;
GO

-- 4) Verifikasi.
SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE name = 'praktikum_user';
GO

USE review_kantin;
GO

-- ---------------------------------------------------------------- USERS
IF OBJECT_ID('dbo.USERS', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.USERS (
        id            INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_USERS PRIMARY KEY,
        name          NVARCHAR(100) NOT NULL,
        email         NVARCHAR(150) NOT NULL CONSTRAINT UQ_USERS_email UNIQUE,
        password_hash NVARCHAR(255) NOT NULL,
        role          NVARCHAR(20)  NOT NULL
            CONSTRAINT CK_USERS_role CHECK (role IN ('admin', 'owner', 'customer')),
        created_at    DATETIME2(0)  NOT NULL CONSTRAINT DF_USERS_created_at DEFAULT SYSDATETIME()
    );
END
GO

-- --------------------------------------------------------------- STALLS
IF OBJECT_ID('dbo.STALLS', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.STALLS (
        id           INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_STALLS PRIMARY KEY,
        owner_id     INT NOT NULL,
        name         NVARCHAR(100) NOT NULL,
        category     NVARCHAR(50)  NULL,
        location     NVARCHAR(100) NULL,
        description  NVARCHAR(MAX) NULL,
        avg_rating   DECIMAL(3,2) NOT NULL CONSTRAINT DF_STALLS_avg_rating DEFAULT (0),
        review_count INT          NOT NULL CONSTRAINT DF_STALLS_review_count DEFAULT (0),
        created_at   DATETIME2(0) NOT NULL CONSTRAINT DF_STALLS_created_at DEFAULT SYSDATETIME(),
        CONSTRAINT FK_STALLS_USERS FOREIGN KEY (owner_id) REFERENCES dbo.USERS (id)
    );
END
GO

-- ----------------------------------------------------------- MENU_ITEMS
IF OBJECT_ID('dbo.MENU_ITEMS', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.MENU_ITEMS (
        id           INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_MENU_ITEMS PRIMARY KEY,
        stall_id     INT NOT NULL,
        name         NVARCHAR(100) NOT NULL,
        price        INT NOT NULL CONSTRAINT CK_MENU_ITEMS_price CHECK (price >= 0),
        is_available BIT NOT NULL CONSTRAINT DF_MENU_ITEMS_is_available DEFAULT (1),
        CONSTRAINT FK_MENU_ITEMS_STALLS FOREIGN KEY (stall_id)
            REFERENCES dbo.STALLS (id) ON DELETE CASCADE
    );
END
GO

-- -------------------------------------------------------------- REVIEWS
IF OBJECT_ID('dbo.REVIEWS', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.REVIEWS (
        id         INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_REVIEWS PRIMARY KEY,
        stall_id   INT NOT NULL,
        user_id    INT NOT NULL,
        rating     INT NOT NULL CONSTRAINT CK_REVIEWS_rating CHECK (rating BETWEEN 1 AND 5),
        comment    NVARCHAR(MAX) NULL,
        like_count INT NOT NULL CONSTRAINT DF_REVIEWS_like_count DEFAULT (0),
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_REVIEWS_created_at DEFAULT SYSDATETIME(),
        updated_at DATETIME2(0) NULL,
        CONSTRAINT UQ_REVIEWS_user_stall UNIQUE (user_id, stall_id),
        CONSTRAINT FK_REVIEWS_STALLS FOREIGN KEY (stall_id)
            REFERENCES dbo.STALLS (id) ON DELETE CASCADE,
        CONSTRAINT FK_REVIEWS_USERS FOREIGN KEY (user_id)
            REFERENCES dbo.USERS (id)
    );
END
GO

-- ---------------------------------------------------------------- LIKES
IF OBJECT_ID('dbo.LIKES', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.LIKES (
        id         INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_LIKES PRIMARY KEY,
        review_id  INT NOT NULL,
        user_id    INT NOT NULL,
        created_at DATETIME2(0) NOT NULL CONSTRAINT DF_LIKES_created_at DEFAULT SYSDATETIME(),
        CONSTRAINT UQ_LIKES_review_user UNIQUE (review_id, user_id),
        CONSTRAINT FK_LIKES_REVIEWS FOREIGN KEY (review_id)
            REFERENCES dbo.REVIEWS (id) ON DELETE CASCADE,
        CONSTRAINT FK_LIKES_USERS FOREIGN KEY (user_id)
            REFERENCES dbo.USERS (id)
    );
END
GO

-- ---------------------------------------------------------------- FLAGS
IF OBJECT_ID('dbo.FLAGS', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.FLAGS (
        id          INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_FLAGS PRIMARY KEY,
        review_id   INT NOT NULL,
        reported_by INT NOT NULL,
        reason      NVARCHAR(255) NULL,
        status      NVARCHAR(20) NOT NULL
            CONSTRAINT DF_FLAGS_status DEFAULT ('pending')
            CONSTRAINT CK_FLAGS_status CHECK (status IN ('pending', 'resolved', 'dismissed')),
        created_at  DATETIME2(0) NOT NULL CONSTRAINT DF_FLAGS_created_at DEFAULT SYSDATETIME(),
        CONSTRAINT FK_FLAGS_REVIEWS FOREIGN KEY (review_id)
            REFERENCES dbo.REVIEWS (id) ON DELETE CASCADE,
        CONSTRAINT FK_FLAGS_USERS FOREIGN KEY (reported_by)
            REFERENCES dbo.USERS (id)
    );
END
GO

-- ----------------------------------------------------------- AUDIT_LOGS
IF OBJECT_ID('dbo.AUDIT_LOGS', 'U') IS NULL
BEGIN
    CREATE TABLE dbo.AUDIT_LOGS (
        id           INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_AUDIT_LOGS PRIMARY KEY,
        user_id      INT NOT NULL,
        action       NVARCHAR(50) NOT NULL,
        target_table NVARCHAR(50) NOT NULL,
        target_id    INT NOT NULL,
        metadata     NVARCHAR(MAX) NULL,
        created_at   DATETIME2(0) NOT NULL CONSTRAINT DF_AUDIT_LOGS_created_at DEFAULT SYSDATETIME(),
        CONSTRAINT FK_AUDIT_LOGS_USERS FOREIGN KEY (user_id)
            REFERENCES dbo.USERS (id)
    );
END
GO

PRINT 'Skema selesai.';
GO

SELECT t.name AS tabel
FROM sys.tables t
WHERE t.schema_id = SCHEMA_ID('dbo')
ORDER BY t.name;
GO

USE review_kantin;
GO

-- ================================================================ USERS
IF NOT EXISTS (SELECT 1 FROM dbo.USERS)
BEGIN
    SET IDENTITY_INSERT dbo.USERS ON;
    INSERT INTO dbo.USERS (id, name, email, password_hash, role) VALUES
        -- admin
        (1,  N'Admin Kantin', N'admin@kantin.test',   N'hash_admin',  N'admin'),
        -- owner (1 owner per warung)
        (2,  N'Bu Tini',      N'tini@kantin.test',    N'hash_owner1', N'owner'),
        (3,  N'Pak Slamet',   N'slamet@kantin.test',  N'hash_owner2', N'owner'),
        (4,  N'Pak Makmur',   N'makmur@kantin.test',  N'hash_owner3', N'owner'),
        (5,  N'Cak Nur',      N'caknur@kantin.test',  N'hash_owner4', N'owner'),
        (6,  N'Bu Wati',      N'wati@kantin.test',    N'hash_owner5', N'owner'),
        (7,  N'Mas Bayu',     N'bayu@kantin.test',    N'hash_owner6', N'owner'),
        (8,  N'Mas Andre',    N'andre@kantin.test',   N'hash_owner7', N'owner'),
        (9,  N'Bu Inah',      N'inah@kantin.test',    N'hash_owner8', N'owner'),
        (10, N'Bu Sari',      N'sari@kantin.test',    N'hash_owner9', N'owner'),
        (11, N'Pak Hasan',    N'hasan@kantin.test',   N'hash_owner10',N'owner'),
        -- customer
        (12, N'Bagas Pratama',N'bagas@student.test',  N'hash_cust1',  N'customer'),
        (13, N'Sinta Maharani',N'sinta@student.test', N'hash_cust2',  N'customer'),
        (14, N'Yoga Saputra', N'yoga@student.test',   N'hash_cust3',  N'customer'),
        (15, N'Dewi Lestari', N'dewi@student.test',   N'hash_cust4',  N'customer');
    SET IDENTITY_INSERT dbo.USERS OFF;
    DBCC CHECKIDENT ('dbo.USERS', RESEED, 15) WITH NO_INFOMSGS;
    PRINT 'Seed USERS selesai.';
END
GO

-- =============================================================== STALLS
IF NOT EXISTS (SELECT 1 FROM dbo.STALLS)
BEGIN
    SET IDENTITY_INSERT dbo.STALLS ON;
    INSERT INTO dbo.STALLS (id, owner_id, name, category, location, description, avg_rating, review_count) VALUES
        (1,  2,  N'Warung Bu Tini',    N'Kwetiau', N'Kantin FKIP',  N'Kedai kwetiau goreng & kuah',          4.50, 2),
        (2,  9,  N'Dapur Bu Inah',     N'Nasi',    N'Kantin FKIP',  N'Nasi goreng dadakan',                  4.00, 1),
        (3,  3,  N'Kedai Pak Slamet',  N'Bakso',   N'Kantin FK',    N'Bakso urat jumbo & beranak',           4.67, 3),
        (4,  5,  N'Kedai Cak Nur',     N'Mie',     N'Kantin FK',    N'Mie ayam original & pangsit',          4.00, 1),
        (5,  4,  N'Warung Makmur',     N'Minuman', N'Kantin FEB',   N'Aneka es teh',                         4.50, 2),
        (6,  10, N'Kedai Segar',       N'Minuman', N'Kantin FEB',   N'Jus buah tanpa gula tambahan',         5.00, 1),
        (7,  7,  N'Dapur Mas Bayu',    N'Western', N'Kantin FEB',   N'Chicken steak & katsu hot plate',      4.50, 2),
        (8,  8,  N'Warung Mas Andre',  N'Sate',    N'Kantin FISIP', N'Sate ayam & kambing bumbu kacang',     4.00, 1),
        (9,  6,  N'Warung Bu Wati',    N'Soto',    N'Kantin FISIP', N'Soto ayam kuah bening',                4.50, 2),
        (10, 11, N'Dapur Pak Hasan',   N'Nasi',    N'Kantin FKIP',  N'Nasi uduk dengan aneka lauk',          4.00, 1);
    SET IDENTITY_INSERT dbo.STALLS OFF;
    DBCC CHECKIDENT ('dbo.STALLS', RESEED, 10) WITH NO_INFOMSGS;
    PRINT 'Seed STALLS selesai.';
END
GO

-- =========================================================== MENU_ITEMS
IF NOT EXISTS (SELECT 1 FROM dbo.MENU_ITEMS)
BEGIN
    SET IDENTITY_INSERT dbo.MENU_ITEMS ON;
    INSERT INTO dbo.MENU_ITEMS (id, stall_id, name, price, is_available) VALUES
        (1,  1, N'Kwetiau Goreng Spesial', 15000, 1),
        (2,  1, N'Kwetiau Kuah',           13000, 1),
        (3,  2, N'Nasi Goreng Spesial',    14000, 1),
        (4,  2, N'Nasi Goreng Telur',      12000, 1),
        (5,  3, N'Bakso Urat Jumbo',       18000, 1),
        (6,  3, N'Bakso Beranak',          25000, 1),
        (7,  4, N'Mie Ayam Original',      12000, 1),
        (8,  4, N'Mie Ayam Pangsit',       15000, 1),
        (9,  5, N'Es Teh Manis',            4000, 1),
        (10, 5, N'Es Teh Jumbo',            6000, 1),
        (11, 6, N'Jus Alpukat',            12000, 1),
        (12, 6, N'Jus Mangga',             10000, 1),
        (13, 7, N'Chicken Steak Hot Plate',25000, 1),
        (14, 7, N'Chicken Katsu',          22000, 1),
        (15, 8, N'Sate Ayam 10 Tusuk',     20000, 1),
        (16, 8, N'Sate Kambing 10 Tusuk',  30000, 1),
        (17, 9, N'Soto Ayam',              13000, 1),
        (18, 9, N'Soto Ayam + Nasi',       16000, 1),
        (19, 10, N'Nasi Uduk Komplit',     15000, 1),
        (20, 10, N'Nasi Uduk Ayam',        17000, 1);
    SET IDENTITY_INSERT dbo.MENU_ITEMS OFF;
    DBCC CHECKIDENT ('dbo.MENU_ITEMS', RESEED, 20) WITH NO_INFOMSGS;
    PRINT 'Seed MENU_ITEMS selesai.';
END
GO

-- ============================================================== REVIEWS
IF NOT EXISTS (SELECT 1 FROM dbo.REVIEWS)
BEGIN
    SET IDENTITY_INSERT dbo.REVIEWS ON;
    INSERT INTO dbo.REVIEWS (id, stall_id, user_id, rating, comment) VALUES
        (1,  1,  12, 5, N'Enak!'),
        (2,  1,  13, 4, N'Oke'),
        (3,  2,  12, 4, N'Lumayan'),
        (4,  3,  12, 5, N'Baksonya mantap'),
        (5,  3,  13, 5, N'Jumbo!'),
        (6,  3,  14, 4, N'Enak'),
        (7,  4,  13, 4, N'Standar'),
        (8,  5,  14, 5, N'Segar'),
        (9,  5,  15, 4, N'Murah'),
        (10, 6,  15, 5, N'Mantap'),
        (11, 7,  12, 5, N'Porsi besar'),
        (12, 7,  14, 4, N'Oke'),
        (13, 8,  13, 4, N'Enak'),
        (14, 9,  12, 5, N'Kuahnya enak'),
        (15, 9,  15, 4, N'Lumayan'),
        (16, 10, 14, 4, N'Oke');
    SET IDENTITY_INSERT dbo.REVIEWS OFF;
    DBCC CHECKIDENT ('dbo.REVIEWS', RESEED, 16) WITH NO_INFOMSGS;
    PRINT 'Seed REVIEWS selesai.';
END
GO

PRINT 'Seed selesai.';
GO

SELECT 'USERS' AS tabel, COUNT(*) AS jumlah FROM dbo.USERS
UNION ALL SELECT 'STALLS', COUNT(*) FROM dbo.STALLS
UNION ALL SELECT 'MENU_ITEMS', COUNT(*) FROM dbo.MENU_ITEMS
UNION ALL SELECT 'REVIEWS', COUNT(*) FROM dbo.REVIEWS;
GO

{
  "name": "hands-on-1",
  "version": "1.0.0",
  "private": true,
  "description": "Hands-on 1 Pertemuan 03 - setup database dan koneksi SQL Server dari backend",
  "main": "src/index.ts",
  "scripts": {
    "start": "tsx src/index.ts",
    "dev": "tsx watch src/index.ts"
  },
  "type": "commonjs",
  "dependencies": {
    "dotenv": "^17.4.2",
    "express": "^5.2.1",
    "mssql": "^12.7.2"
  },
  "devDependencies": {
    "@types/express": "^5.0.6",
    "@types/mssql": "^12.3.0",
    "@types/node": "^26.5.1",
    "tsx": "^4.23.13",
    "typescript": "^7.0.2"
  }
}

{
  "compilerOptions": {
    "target": "ES2022",
    "lib": ["ES2022"],
    "module": "ESNext",
    "moduleResolution": "Bundler",
    "types": ["node"],
    "strict": true,
    "noEmit": true,
    "allowImportingTsExtensions": true,
    "resolveJsonModule": true,
    "esModuleInterop": true,
    "forceConsistentCasingInFileNames": true,
    "skipLibCheck": true
  },
  "include": ["src"]
}

# Salin file ini menjadi .env lalu sesuaikan bila perlu.
# Jangan commit file .env yang berisi kredensial asli.

DB_SERVER=localhost
DB_PORT=1433
DB_USER=praktikum_user
DB_PASSWORD=Praktikum2026!
DB_NAME=review_kantin

# Untuk SQL Server lokal biasanya: encrypt=false, trustServerCertificate=true
DB_ENCRYPT=false
DB_TRUST_SERVER_CERTIFICATE=true

import 'dotenv/config';
import sql from 'mssql';

function required(name: string): string {
  const value = process.env[name];
  if (!value) {
    throw new Error(`Environment variable ${name} belum diisi. Salin .env.example ke .env.`);
  }
  return value;
}

const config: sql.config = {
  server: process.env.DB_SERVER ?? 'localhost',
  port: Number(process.env.DB_PORT ?? 1433),
  user: required('DB_USER'),
  password: required('DB_PASSWORD'),
  database: required('DB_NAME'),
  options: {
    encrypt: process.env.DB_ENCRYPT === 'true',
    trustServerCertificate: process.env.DB_TRUST_SERVER_CERTIFICATE === 'true',
  },
  pool: {
    max: 10,
    min: 0,
    idleTimeoutMillis: 30000,
  },
};

let poolPromise: Promise<sql.ConnectionPool> | null = null;

// Membuat (sekali) lalu memakai kembali connection pool yang sama.
export function getPool(): Promise<sql.ConnectionPool> {
  if (!poolPromise) {
    poolPromise = new sql.ConnectionPool(config)
      .connect()
      .catch((error) => {
        poolPromise = null;
        throw error;
      });
  }
  return poolPromise;
}

export async function closePool(): Promise<void> {
  if (!poolPromise) return;
  const pool = await poolPromise;
  await pool.close();
  poolPromise = null;
}

import express, { type Request, type Response } from 'express';
import { getPool } from './config/database.ts';

const app = express();
const PORT: number = 3000;

app.use(express.json());

// Health check: memastikan server DAN database dapat dijangkau.
app.get('/health', async (req: Request, res: Response) => {
  try {
    const pool = await getPool();
    const result = await pool.request().query('SELECT 1 AS ok');

    res.status(200).json({
      status: 'success',
      message: 'Server dan database terhubung',
      database: result.recordset[0],
      timestamp: new Date(),
    });
  } catch (error) {
    res.status(500).json({
      status: 'error',
      message: 'Gagal terhubung ke database',
      error: error instanceof Error ? error.message : String(error),
    });
  }
});

app.listen(PORT, () => {
  console.log(`Server berjalan di http://localhost:${PORT}`);
});

{
  "status": "success",
  "message": "Server dan database terhubung",
  "database": { "ok": 1 },
  "timestamp": "2026-09-14T00:00:00.000Z"
}

praktikum-pert-3/
├─ db/                               # sudah dijalankan pada hands-on 1
└─ hands-on-2/
   ├─ package.json
   ├─ tsconfig.json
   ├─ .env.example
   ├─ drizzle.config.ts
   └─ src/
      ├─ index.ts
      ├─ db/
      │  ├─ schema.ts
      │  └─ index.ts
      ├─ docs/
      │  ├─ swagger.ts
      │  └─ swagger-output.json       # hasil generate
      ├─ dtos/
      │  └─ stallDto.ts
      ├─ repositories/
      │  ├─ stallRepository.ts
      │  └─ menuItemRepository.ts
      ├─ services/
      │  └─ stallService.ts
      ├─ controllers/
      │  └─ stallController.ts
      └─ routes/
         └─ stallRouter.ts

{
  "name": "hands-on-2",
  "version": "1.0.0",
  "private": true,
  "description": "Hands-on 2 Pertemuan 03 - akses database dengan Drizzle ORM",
  "main": "src/index.ts",
  "scripts": {
    "start": "tsx src/index.ts",
    "prestart": "npm run docs:gen",
    "dev": "tsx watch src/index.ts",
    "predev": "npm run docs:gen",
    "docs:gen": "tsx src/docs/swagger.ts"
  },
  "type": "commonjs",
  "dependencies": {
    "dotenv": "^17.4.2",
    "drizzle-orm": "^1.0.0-rc.5-5935859",
    "express": "^5.2.1",
    "mssql": "^11.0.2",
    "swagger-autogen": "^2.23.7",
    "swagger-ui-express": "^5.0.1"
  },
  "devDependencies": {
    "@types/express": "^5.0.6",
    "@types/mssql": "^9.1.11",
    "@types/node": "^26.5.1",
    "@types/swagger-ui-express": "^4.1.8",
    "drizzle-kit": "^1.0.0-rc.5-5935859",
    "tsx": "^4.23.13",
    "typescript": "^7.0.2"
  }
}

{
  "compilerOptions": {
    "target": "ES2022",
    "lib": ["ES2022"],
    "module": "ESNext",
    "moduleResolution": "Bundler",
    "types": ["node"],
    "strict": true,
    "noEmit": true,
    "allowImportingTsExtensions": true,
    "resolveJsonModule": true,
    "esModuleInterop": true,
    "forceConsistentCasingInFileNames": true,
    "skipLibCheck": true
  },
  "include": ["src", "drizzle.config.ts"]
}

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'mssql',
  schema: './src/db/schema.ts',
  out: './drizzle',
  dbCredentials: {
    server: process.env.DB_SERVER ?? 'localhost',
    port: Number(process.env.DB_PORT ?? 1433),
    user: process.env.DB_USER ?? '',
    password: process.env.DB_PASSWORD ?? '',
    database: process.env.DB_NAME ?? '',
    options: {
      encrypt: process.env.DB_ENCRYPT === 'true',
      trustServerCertificate: process.env.DB_TRUST_SERVER_CERTIFICATE === 'true',
    },
  },
});

import {
  mssqlTable,
  int,
  bit,
  nvarchar,
  decimal,
  datetime2,
} from 'drizzle-orm/mssql-core';

export const users = mssqlTable('USERS', {
  id: int('id').primaryKey().identity(),
  name: nvarchar('name', { length: 100 }).notNull(),
  email: nvarchar('email', { length: 150 }).notNull().unique(),
  passwordHash: nvarchar('password_hash', { length: 255 }).notNull(),
  role: nvarchar('role', { length: 20, enum: ['admin', 'owner', 'customer'] }).notNull(),
  createdAt: datetime2('created_at'),
});

export const stalls = mssqlTable('STALLS', {
  id: int('id').primaryKey().identity(),
  ownerId: int('owner_id').notNull().references(() => users.id),
  name: nvarchar('name', { length: 100 }).notNull(),
  category: nvarchar('category', { length: 50 }),
  location: nvarchar('location', { length: 100 }),
  description: nvarchar('description', { length: 'max' }),
  avgRating: decimal('avg_rating', { precision: 3, scale: 2 }).notNull(),
  reviewCount: int('review_count').notNull(),
  createdAt: datetime2('created_at'),
});

export const menuItems = mssqlTable('MENU_ITEMS', {
  id: int('id').primaryKey().identity(),
  stallId: int('stall_id').notNull().references(() => stalls.id),
  name: nvarchar('name', { length: 100 }).notNull(),
  price: int('price').notNull(),
  isAvailable: bit('is_available').notNull(),
});

export const reviews = mssqlTable('REVIEWS', {
  id: int('id').primaryKey().identity(),
  stallId: int('stall_id').notNull().references(() => stalls.id),
  userId: int('user_id').notNull().references(() => users.id),
  rating: int('rating').notNull(),
  comment: nvarchar('comment', { length: 'max' }),
  likeCount: int('like_count').notNull(),
  createdAt: datetime2('created_at'),
  updatedAt: datetime2('updated_at'),
});

export const likes = mssqlTable('LIKES', {
  id: int('id').primaryKey().identity(),
  reviewId: int('review_id').notNull().references(() => reviews.id),
  userId: int('user_id').notNull().references(() => users.id),
  createdAt: datetime2('created_at'),
});

export const flags = mssqlTable('FLAGS', {
  id: int('id').primaryKey().identity(),
  reviewId: int('review_id').notNull().references(() => reviews.id),
  reportedBy: int('reported_by').notNull().references(() => users.id),
  reason: nvarchar('reason', { length: 255 }),
  status: nvarchar('status', { length: 20 }).notNull(),
  createdAt: datetime2('created_at'),
});

export const auditLogs = mssqlTable('AUDIT_LOGS', {
  id: int('id').primaryKey().identity(),
  userId: int('user_id').notNull().references(() => users.id),
  action: nvarchar('action', { length: 50 }).notNull(),
  targetTable: nvarchar('target_table', { length: 50 }).notNull(),
  targetId: int('target_id').notNull(),
  metadata: nvarchar('metadata', { length: 'max' }),
  createdAt: datetime2('created_at'),
});

import 'dotenv/config';
import mssql from 'mssql';
import { drizzle } from 'drizzle-orm/node-mssql';

const config: mssql.config = {
  server: process.env.DB_SERVER ?? 'localhost',
  port: Number(process.env.DB_PORT ?? 1433),
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  options: {
    encrypt: process.env.DB_ENCRYPT === 'true',
    trustServerCertificate: process.env.DB_TRUST_SERVER_CERTIFICATE === 'true',
  },
  pool: { max: 10, min: 0, idleTimeoutMillis: 30000 },
};

let poolPromise: Promise<mssql.ConnectionPool> | null = null;
let dbPromise: ReturnType<typeof buildDb> | null = null;

async function buildDb() {
  poolPromise ??= mssql.connect(config);
  const pool = await poolPromise;
  return drizzle({ client: pool });
}

// Satu instance Drizzle dipakai ulang untuk seluruh aplikasi.
export function getDb() {
  if (!dbPromise) {
    dbPromise = buildDb().catch((error) => {
      dbPromise = null;
      poolPromise = null;
      throw error;
    });
  }
  return dbPromise;
}

import { and, count, eq, like, type SQL } from 'drizzle-orm';
import { getDb } from '../db/index.ts';
import { stalls } from '../db/schema.ts';

export interface FindAllParams {
  search?: string;
  category?: string;
  page: number;
  limit: number;
}

export interface CreateStallInput {
  ownerId: number;
  name: string;
  category?: string | null;
  location?: string | null;
  description?: string | null;
}

export class StallRepository {
  async findAll(params: FindAllParams) {
    const db = await getDb();

    const conditions: SQL[] = [];
    if (params.search) conditions.push(like(stalls.name, `%${params.search}%`));
    if (params.category) conditions.push(eq(stalls.category, params.category));
    const where = conditions.length > 0 ? and(...conditions) : undefined;

    const offset = (params.page - 1) * params.limit;

    // MSSQL: pagination memakai ORDER BY + OFFSET ... FETCH NEXT.
    const rows = await db
      .select()
      .from(stalls)
      .where(where)
      .orderBy(stalls.id)
      .offset(offset)
      .fetch(params.limit);

    const totals = await db.select({ total: count() }).from(stalls).where(where);

    return { rows, total: Number(totals[0]?.total ?? 0) };
  }

  async findById(id: number) {
    const db = await getDb();
    const rows = await db.select().from(stalls).where(eq(stalls.id, id));
    return rows[0];
  }

  async create(input: CreateStallInput) {
    const db = await getDb();
    const rows = await db
      .insert(stalls)
      .output()
      .values({
        ownerId: input.ownerId,
        name: input.name,
        category: input.category ?? null,
        location: input.location ?? null,
        description: input.description ?? null,
        avgRating: '0.00',
        reviewCount: 0,
      });
    return rows[0];
  }

  async update(id: number, input: Partial<CreateStallInput>) {
    const db = await getDb();
    const rows = await db
      .update(stalls)
      .set({
        ...(input.ownerId !== undefined ? { ownerId: input.ownerId } : {}),
        ...(input.name !== undefined ? { name: input.name } : {}),
        ...(input.category !== undefined ? { category: input.category } : {}),
        ...(input.location !== undefined ? { location: input.location } : {}),
        ...(input.description !== undefined ? { description: input.description } : {}),
      })
      .where(eq(stalls.id, id))
      .output();
    return rows[0];
  }

  async remove(id: number) {
    const db = await getDb();
    const rows = await db.delete(stalls).where(eq(stalls.id, id)).output();
    return rows[0];
  }
}

import { eq } from 'drizzle-orm';
import { getDb } from '../db/index.ts';
import { menuItems } from '../db/schema.ts';

export class MenuItemRepository {
  async findByStallId(stallId: number) {
    const db = await getDb();
    return db.select().from(menuItems).where(eq(menuItems.stallId, stallId));
  }
}

export interface StallResponseDto {
  id: number;
  ownerId: number;
  name: string;
  category: string | null;
  location: string | null;
  description: string | null;
  avgRating: number;
  reviewCount: number;
  isPopular: boolean;
}

export interface MenuItemDto {
  id: number;
  stallId: number;
  name: string;
  price: number;
  isAvailable: boolean;
}

import {
  StallRepository,
  type CreateStallInput,
  type FindAllParams,
} from '../repositories/stallRepository.ts';
import { MenuItemRepository } from '../repositories/menuItemRepository.ts';
import type { MenuItemDto, StallResponseDto } from '../dtos/stallDto.ts';

type StallRow = NonNullable<Awaited<ReturnType<StallRepository['findById']>>>;

export class StallService {
  private stallRepository: StallRepository;
  private menuItemRepository: MenuItemRepository;

  constructor(
    stallRepository: StallRepository = new StallRepository(),
    menuItemRepository: MenuItemRepository = new MenuItemRepository(),
  ) {
    this.stallRepository = stallRepository;
    this.menuItemRepository = menuItemRepository;
  }

  // Mapping row DB -> DTO API (sekaligus logika bisnis isPopular).
  private toDto(row: StallRow): StallResponseDto {
    const avgRating = Number(row.avgRating);
    return {
      id: row.id,
      ownerId: row.ownerId,
      name: row.name,
      category: row.category,
      location: row.location,
      description: row.description,
      avgRating,
      reviewCount: row.reviewCount,
      isPopular: avgRating >= 4.7,
    };
  }

  async getAllStalls(params: FindAllParams) {
    const { rows, total } = await this.stallRepository.findAll(params);
    return { data: rows.map((row) => this.toDto(row)), total };
  }

  async getStallById(id: number): Promise<StallResponseDto> {
    const row = await this.stallRepository.findById(id);
    if (!row) throw new Error('STALL_NOT_FOUND');
    return this.toDto(row);
  }

  async getStallMenus(id: number): Promise<MenuItemDto[]> {
    const row = await this.stallRepository.findById(id);
    if (!row) throw new Error('STALL_NOT_FOUND');
    return this.menuItemRepository.findByStallId(id);
  }

  async createStall(input: CreateStallInput): Promise<StallResponseDto> {
    const row = await this.stallRepository.create(input);
    if (!row) throw new Error('STALL_NOT_FOUND');
    return this.toDto(row);
  }

  async updateStall(id: number, input: Partial<CreateStallInput>): Promise<StallResponseDto> {
    const row = await this.stallRepository.update(id, input);
    if (!row) throw new Error('STALL_NOT_FOUND');
    return this.toDto(row);
  }

  async deleteStall(id: number): Promise<StallResponseDto> {
    const row = await this.stallRepository.remove(id);
    if (!row) throw new Error('STALL_NOT_FOUND');
    return this.toDto(row);
  }
}

import type { Request, Response } from 'express';
import { StallService } from '../services/stallService.ts';

export class StallController {
  private stallService: StallService;

  constructor(stallService: StallService = new StallService()) {
    this.stallService = stallService;
  }

  private handleError(res: Response, error: unknown): Response {
    if (error instanceof Error && error.message === 'STALL_NOT_FOUND') {
      return res.status(404).json({ status: 'fail', message: 'Data warung tidak ditemukan' });
    }
    return res.status(500).json({
      status: 'error',
      message: 'Terjadi kesalahan pada server',
      error: error instanceof Error ? error.message : String(error),
    });
  }

  getStalls = async (req: Request, res: Response): Promise<Response> => {
    try {
      const page = Number(req.query.page) || 1;
      const limit = Number(req.query.limit) || 10;
      const search = typeof req.query.search === 'string' ? req.query.search : undefined;
      const category = typeof req.query.category === 'string' ? req.query.category : undefined;

      const { data, total } = await this.stallService.getAllStalls({
        search,
        category,
        page,
        limit,
      });

      return res.status(200).json({ status: 'success', meta: { page, limit, total }, data });
    } catch (error) {
      return this.handleError(res, error);
    }
  };

  getStallById = async (req: Request, res: Response): Promise<Response> => {
    try {
      const stall = await this.stallService.getStallById(Number(req.params.id));
      return res.status(200).json({ status: 'success', data: stall });
    } catch (error) {
      return this.handleError(res, error);
    }
  };

  getStallMenus = async (req: Request, res: Response): Promise<Response> => {
    try {
      const menus = await this.stallService.getStallMenus(Number(req.params.id));
      return res.status(200).json({ status: 'success', data: menus });
    } catch (error) {
      return this.handleError(res, error);
    }
  };

  createStall = async (req: Request, res: Response): Promise<Response> => {
    try {
      const stall = await this.stallService.createStall(req.body);
      return res.status(201).json({ status: 'success', data: stall });
    } catch (error) {
      return this.handleError(res, error);
    }
  };

  updateStall = async (req: Request, res: Response): Promise<Response> => {
    try {
      const stall = await this.stallService.updateStall(Number(req.params.id), req.body);
      return res.status(200).json({ status: 'success', data: stall });
    } catch (error) {
      return this.handleError(res, error);
    }
  };

  deleteStall = async (req: Request, res: Response): Promise<Response> => {
    try {
      const stall = await this.stallService.deleteStall(Number(req.params.id));
      return res.status(200).json({ status: 'success', data: stall });
    } catch (error) {
      return this.handleError(res, error);
    }
  };
}

import { Router } from 'express';
import { StallController } from '../controllers/stallController.ts';

const stallRouter = Router();
const stallController = new StallController();

stallRouter.get('/', (req, res) => {
  // #swagger.parameters['search']   = { in: 'query', type: 'string' }
  // #swagger.parameters['category'] = { in: 'query', type: 'string' }
  // #swagger.parameters['page']     = { in: 'query', type: 'integer' }
  // #swagger.parameters['limit']    = { in: 'query', type: 'integer' }
  // #swagger.responses[200] = { description: 'Daftar warung' }
  return stallController.getStalls(req, res);
});

stallRouter.post('/', (req, res) => {
  // #swagger.parameters['body'] = { in: 'body', required: true, schema: { $ref: '#/definitions/StallInput' } }
  // #swagger.responses[201] = { description: 'Warung dibuat' }
  return stallController.createStall(req, res);
});

stallRouter.get('/:id', (req, res) => {
  // #swagger.parameters['id'] = { in: 'path', required: true, type: 'integer' }
  // #swagger.responses[200] = { description: 'Detail warung' }
  // #swagger.responses[404] = { description: 'Tidak ditemukan' }
  return stallController.getStallById(req, res);
});

stallRouter.put('/:id', (req, res) => {
  // #swagger.parameters['id'] = { in: 'path', required: true, type: 'integer' }
  // #swagger.parameters['body'] = { in: 'body', required: true, schema: { $ref: '#/definitions/StallInput' } }
  // #swagger.responses[200] = { description: 'Warung ter-update' }
  // #swagger.responses[404] = { description: 'Tidak ditemukan' }
  return stallController.updateStall(req, res);
});

stallRouter.delete('/:id', (req, res) => {
  // #swagger.parameters['id'] = { in: 'path', required: true, type: 'integer' }
  // #swagger.responses[200] = { description: 'Warung terhapus' }
  // #swagger.responses[404] = { description: 'Tidak ditemukan' }
  return stallController.deleteStall(req, res);
});

stallRouter.get('/:id/menus', (req, res) => {
  // #swagger.parameters['id'] = { in: 'path', required: true, type: 'integer' }
  // #swagger.responses[200] = { description: 'Daftar menu' }
  // #swagger.responses[404] = { description: 'Tidak ditemukan' }
  return stallController.getStallMenus(req, res);
});

export { stallRouter };

import express, { type Request, type Response, type Application } from 'express';
import { sql } from 'drizzle-orm';
import swaggerUi from 'swagger-ui-express';
import swaggerDocument from './docs/swagger-output.json' with { type: 'json' };
import { getDb } from './db/index.ts';
import { stallRouter } from './routes/stallRouter.ts';

const app: Application = express();
const PORT: number = 3000;

app.use(express.json());

// Dokumentasi API (Swagger UI) dari spec hasil generate swagger-autogen.
app.use('/docs', swaggerUi.serve, swaggerUi.setup(swaggerDocument));

app.get('/health', async (req: Request, res: Response) => {
  try {
    const db = await getDb();
    await db.execute(sql`SELECT 1 AS ok`);
    res.status(200).json({ status: 'success', message: 'Server dan database terhubung' });
  } catch (error) {
    res.status(500).json({
      status: 'error',
      message: 'Gagal terhubung ke database',
      error: error instanceof Error ? error.message : String(error),
    });
  }
});

app.use('/api/v1/stalls', stallRouter);

app.listen(PORT, () => {
  console.log(`Server berjalan di http://localhost:${PORT}`);
});

import swaggerAutogen from 'swagger-autogen';

const doc = {
  info: { title: 'Review Kantin API', version: '1.0.0' },
  servers: [{ url: 'http://localhost:3000' }],
  definitions: {
    StallInput: {
      $ownerId: 2,
      $name: 'Warung Baru',
      category: 'Nasi',
      location: 'Kantin FK',
      description: '',
    },
  },
};

const outputFile = './swagger-output.json';
const endpointsFiles = ['./src/index.ts'];

swaggerAutogen()(outputFile, endpointsFiles, doc);