95 lines
3.3 KiB
SQL
95 lines
3.3 KiB
SQL
-- CreateTable
|
|
CREATE TABLE "users" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"name" TEXT NOT NULL,
|
|
"username" TEXT NOT NULL,
|
|
"email" TEXT NOT NULL,
|
|
"password" TEXT NOT NULL,
|
|
"status" TEXT NOT NULL DEFAULT 'active',
|
|
"authLevel" INTEGER NOT NULL,
|
|
"createdDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"editDate" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "customers" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"name" TEXT NOT NULL,
|
|
"phone" TEXT,
|
|
"email" TEXT,
|
|
"address" TEXT,
|
|
"createdDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updateDate" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "vehicles" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"plateNumber" TEXT NOT NULL,
|
|
"bodyType" TEXT NOT NULL,
|
|
"manufacturer" TEXT NOT NULL,
|
|
"model" TEXT NOT NULL,
|
|
"trim" TEXT,
|
|
"year" INTEGER NOT NULL,
|
|
"transmission" TEXT NOT NULL,
|
|
"fuel" TEXT NOT NULL,
|
|
"cylinders" INTEGER,
|
|
"engineDisplacement" REAL,
|
|
"useType" TEXT NOT NULL,
|
|
"ownerId" INTEGER NOT NULL,
|
|
"lastVisitDate" DATETIME,
|
|
"suggestedNextVisitDate" DATETIME,
|
|
"createdDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updateDate" DATETIME NOT NULL,
|
|
CONSTRAINT "vehicles_ownerId_fkey" FOREIGN KEY ("ownerId") REFERENCES "customers" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "maintenance_visits" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"vehicleId" INTEGER NOT NULL,
|
|
"customerId" INTEGER NOT NULL,
|
|
"maintenanceType" TEXT NOT NULL,
|
|
"description" TEXT NOT NULL,
|
|
"cost" REAL NOT NULL,
|
|
"paymentStatus" TEXT NOT NULL DEFAULT 'pending',
|
|
"kilometers" INTEGER NOT NULL,
|
|
"visitDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"nextVisitDelay" INTEGER NOT NULL,
|
|
"createdDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updateDate" DATETIME NOT NULL,
|
|
CONSTRAINT "maintenance_visits_vehicleId_fkey" FOREIGN KEY ("vehicleId") REFERENCES "vehicles" ("id") ON DELETE CASCADE ON UPDATE CASCADE,
|
|
CONSTRAINT "maintenance_visits_customerId_fkey" FOREIGN KEY ("customerId") REFERENCES "customers" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "expenses" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"description" TEXT NOT NULL,
|
|
"category" TEXT NOT NULL,
|
|
"amount" REAL NOT NULL,
|
|
"expenseDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updateDate" DATETIME NOT NULL
|
|
);
|
|
|
|
-- CreateTable
|
|
CREATE TABLE "income" (
|
|
"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
|
|
"maintenanceVisitId" INTEGER NOT NULL,
|
|
"amount" REAL NOT NULL,
|
|
"incomeDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"createdDate" DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
"updateDate" DATETIME NOT NULL,
|
|
CONSTRAINT "income_maintenanceVisitId_fkey" FOREIGN KEY ("maintenanceVisitId") REFERENCES "maintenance_visits" ("id") ON DELETE CASCADE ON UPDATE CASCADE
|
|
);
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_username_key" ON "users"("username");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "users_email_key" ON "users"("email");
|
|
|
|
-- CreateIndex
|
|
CREATE UNIQUE INDEX "vehicles_plateNumber_key" ON "vehicles"("plateNumber");
|