schema.sql

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE TYPE UserRole AS ENUM (
  'administrator',
  'customer',
  'vendor'
);
CREATE TYPE ApartmentContractRole AS ENUM (
  'host',
  'board'
);
CREATE TYPE SubscriptionType AS ENUM (
  'monthly',
  'yearly'
);
CREATE TYPE InvoiceStatus AS ENUM (
  'paid',
  'overdue',
  'pending'
);
CREATE TYPE PaymentMethodType AS ENUM (
  'Cash',
  'Card',
  'Bank',
  'eWallet'
);
CREATE TABLE "User" (
  "UserID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "HashedPassword" TEXT NOT NULL,
  "PasswordSalt" TEXT NOT NULL, 
  "Username" VARCHAR(255) UNIQUE NOT NULL,
  "PhoneNumber" VARCHAR(20),
  "Email" TEXT,
  "Role" UserRole NOT NULL,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "RequirePasswordChange" BOOLEAN DEFAULT false
);
CREATE TABLE "Administrator" (
  "AdminID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "UserID" UUID UNIQUE NOT NULL REFERENCES "User"("UserID") ON DELETE CASCADE,
  "FullName" TEXT NOT NULL,
  "Position" TEXT,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "Customer" (
  "CustomerID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "UserID" UUID UNIQUE NOT NULL REFERENCES "User"("UserID") ON DELETE CASCADE,
  "FullName" TEXT NOT NULL,
  "DateOfBirth" DATE,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "Vendor" (
  "VendorID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "UserID" UUID UNIQUE NOT NULL REFERENCES "User"("UserID") ON DELETE CASCADE,
  "CompanyName" VARCHAR(255) NOT NULL,
  "ServiceType" VARCHAR(255),
  "ContactEmail" TEXT,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "Apartment" (
  "ApartmentID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "Building" INTEGER,
  "Floor" INTEGER,
  "Occupied" BOOLEAN DEFAULT false,
  "ApartmentNumber" TEXT UNIQUE NOT NULL,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "ApartmentContract" (
  "ApartmentContractID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "ApartmentID" UUID NOT NULL REFERENCES "Apartment"("ApartmentID"),
  "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID"), 
  "StartDate" TIMESTAMP,
  "EndDate" TIMESTAMP,
  "IsActive" BOOLEAN DEFAULT true,
  "Role" ApartmentContractRole,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "Service" (
  "ServiceID" UUID PRIMARY KEY DEFAULT gen_random_uuid(), 
  "ServiceName" VARCHAR(255) NOT NULL,
  "ServicePrice" DECIMAL NOT NULL,
  "ServiceType" VARCHAR(255), 
  "Availability" BOOLEAN DEFAULT true,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "ServiceContract" (
  "ServiceContractID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "ServiceID" UUID NOT NULL REFERENCES "Service"("ServiceID"),
  "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID")
  "Subscription" SubscriptionType,
  "ContractPrice" DECIMAL NOT NULL,
  "StartDate" TIMESTAMP,
  "EndDate" TIMESTAMP,
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "Invoice" (
  "InvoiceID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID"), 
  "VendorID" UUID REFERENCES "Vendor"("VendorID"),
  "DateIssued" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "BillNumber" VARCHAR(255) UNIQUE NOT NULL,
  "DueDate" TIMESTAMP,
  "Status" InvoiceStatus DEFAULT 'pending',
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "TotalPrice" DECIMAL NOT NULL,
  "InvoiceType" VARCHAR(50) DEFAULT 'service',
  "MeterReadingID" UUID REFERENCES "MeterReading"("ReadingID") 
);
CREATE TABLE "InvoiceDetail" (
  "InvoiceDetailID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "ServiceContractID" UUID NOT NULL REFERENCES "ServiceContract"("ServiceContractID"),
  "InvoiceID" UUID NOT NULL REFERENCES "Invoice"("InvoiceID") ON DELETE CASCADE,
  "ContractPrice" DECIMAL NOT NULL 
);
CREATE TABLE "Payment" (
  "PaymentID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "InvoiceID" UUID NOT NULL REFERENCES "Invoice"("InvoiceID"),
  "AmountPaid" DECIMAL NOT NULL,
  "PaymentDate" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "PaymentMethod" PaymentMethodType,
  "TransactionRef" VARCHAR(255),
  "Note" VARCHAR(1000),
  "BatchPaymentID" UUID
);
CREATE TABLE "BatchPayment" (
  "BatchPaymentID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID"),
  "TotalAmount" DECIMAL NOT NULL,
  "PaymentMethod" VARCHAR(50) NOT NULL,
  "TransactionRef" VARCHAR(255),
  "PaymentDate" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "Status" VARCHAR(50) DEFAULT 'pending',
  "Note" VARCHAR(1000)
);
ALTER TABLE "Payment" 
ADD CONSTRAINT "fk_payment_batch" 
FOREIGN KEY ("BatchPaymentID") REFERENCES "BatchPayment"("BatchPaymentID");
CREATE TABLE "MeterReading" (
  "ReadingID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "ApartmentID" UUID NOT NULL REFERENCES "Apartment"("ApartmentID"),
  "UtilityType" VARCHAR(50) NOT NULL,
  "ReadingDate" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "PreviousReading" DECIMAL NOT NULL DEFAULT 0,
  "CurrentReading" DECIMAL NOT NULL,
  "UnitsConsumed" DECIMAL NOT NULL,
  "PricePerUnit" DECIMAL NOT NULL,
  "TotalCost" DECIMAL NOT NULL,
  "BillingMonth" VARCHAR(7) NOT NULL,
  "RecordedBy" UUID REFERENCES "Administrator"("AdminID"),
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE TABLE "CustomerRequest" (
  "RequestID" UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  "CustomerID" UUID NOT NULL REFERENCES "Customer"("CustomerID"),
  "ServiceID" UUID REFERENCES "Service"("ServiceID"),
  "RequestType" VARCHAR(100) NOT NULL, 
  "RequestedSubscription" SubscriptionType,
  "RequestDate" TIMESTAMP WITH TIME ZONE DEFAULT now(),
  "Status" VARCHAR(50) DEFAULT 'pending',
  "Notes" TEXT,
  "ProcessedBy" UUID REFERENCES "Administrator"("AdminID"),
  "ProcessedDate" TIMESTAMP WITH TIME ZONE,
  "AdminNotes" TEXT
);
CREATE TABLE IF NOT EXISTS "VNPayTransaction" (
  "TxnRef" TEXT PRIMARY KEY,
  "InvoiceID" UUID REFERENCES "Invoice"("InvoiceID"),
  "BatchPaymentID" UUID REFERENCES "BatchPayment"("BatchPaymentID"),
  "CreatedAt" TIMESTAMP WITH TIME ZONE DEFAULT now()
);
CREATE INDEX ON "Administrator" ("UserID");
CREATE INDEX ON "Customer" ("UserID");
CREATE INDEX ON "Vendor" ("UserID");
CREATE INDEX ON "ApartmentContract" ("ApartmentID");
CREATE INDEX ON "ApartmentContract" ("CustomerID");
CREATE INDEX ON "ServiceContract" ("ServiceID");
CREATE INDEX ON "ServiceContract" ("CustomerID");
CREATE INDEX ON "Invoice" ("CustomerID");
CREATE INDEX ON "InvoiceDetail" ("ServiceContractID");
CREATE INDEX ON "InvoiceDetail" ("InvoiceID");
CREATE INDEX ON "Payment" ("InvoiceID");
CREATE INDEX ON "Payment" ("BatchPaymentID");
CREATE INDEX ON "BatchPayment" ("CustomerID");
CREATE INDEX ON "BatchPayment" ("Status");
CREATE INDEX ON "MeterReading" ("ApartmentID");
CREATE INDEX ON "MeterReading" ("UtilityType");
CREATE INDEX ON "MeterReading" ("BillingMonth");
CREATE INDEX ON "Invoice" ("InvoiceType");
CREATE INDEX ON "Invoice" ("MeterReadingID");
CREATE INDEX ON "CustomerRequest" ("CustomerID");
CREATE INDEX ON "CustomerRequest" ("ServiceID");
CREATE INDEX ON "CustomerRequest" ("Status");
CREATE INDEX ON "VNPayTransaction" ("InvoiceID");
CREATE INDEX ON "VNPayTransaction" ("BatchPaymentID");

--Create indexes
CREATE INDEX idx_invoice_vendor_id ON "Invoice" ("VendorID");
CREATE INDEX idx_invoice_status ON "Invoice" ("Status");
CREATE INDEX idx_invoice_date_issued ON "Invoice" ("DateIssued" DESC);
CREATE INDEX idx_invoice_due_date ON "Invoice" ("DueDate");
CREATE INDEX idx_apartmentcontract_active ON "ApartmentContract" ("IsActive") WHERE "IsActive" = true;
CREATE INDEX idx_apartment_occupied ON "Apartment" ("Occupied") WHERE "Occupied" = true;