# Database Schema — Swish Auto Care GMS
## MySQL 8.x — Full Schema Reference

---

## Setup Instructions

```sql
CREATE DATABASE IF NOT EXISTS swish_gms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE swish_gms;
```

---

## Table 1 — `admin_users`

Stores the single admin login for the demo.

```sql
CREATE TABLE admin_users (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  username      VARCHAR(50) UNIQUE NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  full_name     VARCHAR(150) NOT NULL,
  created_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Seed: password is 'swish@2024' (bcrypt hashed at runtime)
INSERT INTO admin_users (username, full_name, password_hash)
VALUES ('admin', 'Swish Admin', '$2b$10$PLACEHOLDER_BCRYPT_HASH');
-- NOTE: Generate real hash in seed script using bcrypt.hash('swish@2024', 10)
```

---

## Table 2 — `staff`

All staff members of Swish Auto Care.

```sql
CREATE TABLE staff (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  staff_code       VARCHAR(20) UNIQUE NOT NULL,    -- SAC-STF-001
  full_name        VARCHAR(150) NOT NULL,
  role             VARCHAR(100),                   -- Detailer, Washer, Supervisor
  mobile_no        VARCHAR(15),
  date_of_joining  DATE,
  monthly_salary   DECIMAL(10,2) DEFAULT 0.00,
  is_active        TINYINT(1) DEFAULT 1,           -- 1=Active, 0=Inactive
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

**Auto-code generation logic (in backend):**
```js
// Get last staff code, increment
SELECT staff_code FROM staff ORDER BY id DESC LIMIT 1;
// Parse number, increment, format as SAC-STF-00N
```

---

## Table 3 — `job_cards`

Core operational table. Every vehicle service creates a row here.

```sql
CREATE TABLE job_cards (
  id                INT AUTO_INCREMENT PRIMARY KEY,
  job_no            VARCHAR(30) UNIQUE NOT NULL,    -- SAC-YYYYMMDD-001
  job_type          ENUM('full','wash') NOT NULL,   -- full=Full Service, wash=Quick Wash
  reg_no            VARCHAR(20) NOT NULL,
  car_name          VARCHAR(100) NOT NULL,
  owner_name        VARCHAR(150) NOT NULL,
  mobile_no         VARCHAR(15),
  service_type      VARCHAR(100),
  assigned_staff_id INT,
  estimated_amount  DECIMAL(10,2),
  final_amount      DECIMAL(10,2),
  payment_mode      ENUM('cash','upi','card'),
  status            ENUM('active','done') DEFAULT 'active',
  notes             TEXT,
  job_date          DATETIME DEFAULT CURRENT_TIMESTAMP,
  closed_at         DATETIME NULL,
  created_at        DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at        DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (assigned_staff_id) REFERENCES staff(id) ON DELETE SET NULL
);

CREATE INDEX idx_job_cards_date ON job_cards(job_date);
CREATE INDEX idx_job_cards_status ON job_cards(status);
CREATE INDEX idx_job_cards_reg ON job_cards(reg_no);
```

**Job number generation logic (backend):**
```js
// Get today's date as YYYYMMDD
// Count jobs created today, add 1
// Format: SAC-20260324-001
const today = new Date().toISOString().slice(0,10).replace(/-/g,'');
const count = await countTodayJobs(); // SELECT COUNT(*) WHERE DATE(job_date) = CURDATE()
const seq = String(count + 1).padStart(3, '0');
const job_no = `SAC-${today}-${seq}`;
```

---

## Table 4 — `attendance`

Daily attendance records for each staff member.

```sql
CREATE TABLE attendance (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  staff_id        INT NOT NULL,
  attendance_date DATE NOT NULL,
  status          ENUM('present','absent','half') NOT NULL,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_staff_date (staff_id, attendance_date),
  FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE CASCADE
);

CREATE INDEX idx_attendance_date ON attendance(attendance_date);
```

**Note:** The UNIQUE KEY on (staff_id, attendance_date) prevents duplicate entries. Use INSERT ... ON DUPLICATE KEY UPDATE for upsert behavior.

---

## Table 5 — `staff_payments`

All salary and payment records for staff.

```sql
CREATE TABLE staff_payments (
  id                 INT AUTO_INCREMENT PRIMARY KEY,
  staff_id           INT NOT NULL,
  payment_date       DATE NOT NULL,
  payment_month      VARCHAR(10) NOT NULL,          -- e.g. 'March 2026'
  days_present       INT DEFAULT 0,
  calculated_salary  DECIMAL(10,2) DEFAULT 0.00,
  paid_amount        DECIMAL(10,2) NOT NULL,        -- Admin can override
  payment_type       ENUM('salary','advance','bonus','deduction') NOT NULL,
  payment_mode       ENUM('cash','bank','upi') NOT NULL,
  notes              TEXT,
  created_at         DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at         DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (staff_id) REFERENCES staff(id) ON DELETE RESTRICT
);

CREATE INDEX idx_payments_staff ON staff_payments(staff_id);
CREATE INDEX idx_payments_date ON staff_payments(payment_date);
```

---

## Table 6 — `parts`

Parts and product inventory master.

```sql
CREATE TABLE parts (
  id               INT AUTO_INCREMENT PRIMARY KEY,
  part_code        VARCHAR(20) UNIQUE NOT NULL,     -- SAC-PRT-001
  name             VARCHAR(200) NOT NULL,
  category         VARCHAR(100),                    -- Chemicals, Films, Tools, Consumables
  unit             VARCHAR(30),                     -- ml, sheet, piece, bottle
  purchase_price   DECIMAL(10,2) DEFAULT 0.00,
  selling_price    DECIMAL(10,2) DEFAULT 0.00,
  stock_qty        INT DEFAULT 0,
  low_stock_alert  INT DEFAULT 5,
  created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at       DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## Table 7 — `parts_sales`

Each parts sale transaction. Stock auto-decrements on insert.

```sql
CREATE TABLE parts_sales (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  part_id        INT NOT NULL,
  job_card_id    INT NULL,                          -- Optional link to job card
  quantity       INT NOT NULL,
  unit_price     DECIMAL(10,2) NOT NULL,            -- Selling price at time of sale (snapshot)
  total_amount   DECIMAL(10,2) NOT NULL,            -- quantity × unit_price
  customer_name  VARCHAR(150),
  payment_mode   ENUM('cash','upi','card') NOT NULL,
  sale_date      DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (part_id) REFERENCES parts(id) ON DELETE RESTRICT,
  FOREIGN KEY (job_card_id) REFERENCES job_cards(id) ON DELETE SET NULL
);

CREATE INDEX idx_parts_sales_date ON parts_sales(sale_date);
```

**Stock deduction trigger (handle in backend, not DB trigger):**
```js
// On parts_sales INSERT:
await db.query(
  'UPDATE parts SET stock_qty = stock_qty - ? WHERE id = ? AND stock_qty >= ?',
  [quantity, part_id, quantity]
);
// Check affected rows — if 0, insufficient stock, rollback
```

---

## Table 8 — `cash_ledger`

Manual cash in/out entries by admin (miscellaneous transactions).

```sql
CREATE TABLE cash_ledger (
  id           INT AUTO_INCREMENT PRIMARY KEY,
  entry_type   ENUM('in','out') NOT NULL,
  amount       DECIMAL(10,2) NOT NULL,
  category     VARCHAR(100),                        -- Utilities, Supplies, Advance, Refund, Other
  description  VARCHAR(500) NOT NULL,               -- Mandatory reason
  entry_date   DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at   DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at   DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE INDEX idx_cash_ledger_date ON cash_ledger(entry_date);
```

---

## Complete Schema File (run this to initialize)

Save as `backend/src/config/schema.sql` and run once:

```sql
-- Full initialization script
CREATE DATABASE IF NOT EXISTS swish_gms CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE swish_gms;

-- [all CREATE TABLE statements above in order]
-- admin_users → staff → job_cards → attendance → staff_payments → parts → parts_sales → cash_ledger
```

---

## Key Relationships

```
admin_users (standalone — auth only)

staff
  ├── job_cards.assigned_staff_id → staff.id
  ├── attendance.staff_id → staff.id
  └── staff_payments.staff_id → staff.id

parts
  └── parts_sales.part_id → parts.id

job_cards
  └── parts_sales.job_card_id → job_cards.id (optional)
```

---

## Seed Data (for demo)

```sql
-- Staff seed
INSERT INTO staff (staff_code, full_name, role, monthly_salary) VALUES
('SAC-STF-001', 'Rajan Mehta', 'Senior Detailer', 18000),
('SAC-STF-002', 'Suresh Kumar', 'Washer', 12000),
('SAC-STF-003', 'Vikram Patel', 'PPF Technician', 22000),
('SAC-STF-004', 'Arjun Shah', 'Supervisor', 25000);

-- Parts seed
INSERT INTO parts (part_code, name, category, unit, purchase_price, selling_price, stock_qty) VALUES
('SAC-PRT-001', 'Ceramic Coat 9H', 'Chemicals', 'bottle', 1200, 2500, 15),
('SAC-PRT-002', 'PPF Film Roll (1m)', 'Films', 'sheet', 800, 1800, 10),
('SAC-PRT-003', 'Microfiber Cloth Set', 'Consumables', 'piece', 150, 350, 50),
('SAC-PRT-004', 'Car Shampoo (1L)', 'Chemicals', 'bottle', 120, 280, 30),
('SAC-PRT-005', 'Dashboard Polish', 'Chemicals', 'bottle', 90, 220, 25);
```