505 lines
22 KiB
Python
505 lines
22 KiB
Python
import psycopg2
|
|
from psycopg2 import sql
|
|
|
|
# Database connection parameters (hardcoded as requested)
|
|
DB_PARAMS = {
|
|
"host": "31.97.228.132",
|
|
"port": 5433,
|
|
"database": "logistics",
|
|
"user": "admin",
|
|
"password": "Package@321#"
|
|
}
|
|
|
|
# SQL Statements for creating all 20 tables in topological order (with constraints)
|
|
TABLES = {
|
|
# 1. Partner info
|
|
"partnerinfo": """
|
|
CREATE TABLE IF NOT EXISTS partnerinfo (
|
|
partnerid SERIAL PRIMARY KEY,
|
|
partnername VARCHAR(255) NOT NULL,
|
|
partnertypeid INT,
|
|
contactno VARCHAR(50),
|
|
status VARCHAR(50) DEFAULT 'Active',
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 2. Hubs
|
|
"hubs": """
|
|
CREATE TABLE IF NOT EXISTS hubs (
|
|
hubid SERIAL PRIMARY KEY,
|
|
hubname VARCHAR(255) NOT NULL,
|
|
hubtype VARCHAR(50) NOT NULL CHECK (hubtype IN ('sorting_center', 'delivery_hub')),
|
|
applocationid INT,
|
|
address VARCHAR(500),
|
|
latitude DECIMAL(10, 7),
|
|
longitude DECIMAL(10, 7),
|
|
pincode VARCHAR(10),
|
|
status VARCHAR(50) DEFAULT 'Active' CHECK (status IN ('Active', 'InActive')),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT,
|
|
updatedby INT,
|
|
deletedat TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 3. Vehicles
|
|
"vehicles": """
|
|
CREATE TABLE IF NOT EXISTS vehicles (
|
|
vehicleid SERIAL PRIMARY KEY,
|
|
vehicleno VARCHAR(50) UNIQUE NOT NULL,
|
|
vehicletype VARCHAR(50) NOT NULL,
|
|
maxweight NUMERIC(10, 2) NOT NULL,
|
|
maxvolume NUMERIC(10, 2) NOT NULL,
|
|
partnerid INT REFERENCES partnerinfo(partnerid) ON DELETE SET NULL,
|
|
batterypercentage INT,
|
|
status VARCHAR(50) DEFAULT 'Available' CHECK (status IN ('Available', 'In_Transit', 'Maintenance', 'InActive')),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT,
|
|
updatedby INT,
|
|
deletedat TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 4. App Users
|
|
"appusers": """
|
|
CREATE TABLE IF NOT EXISTS appusers (
|
|
userid SERIAL PRIMARY KEY,
|
|
authname VARCHAR(150) NOT NULL,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
contactno VARCHAR(50) NOT NULL,
|
|
password VARCHAR(255) NOT NULL,
|
|
roleid INT NOT NULL,
|
|
hubid INT REFERENCES hubs(hubid) ON DELETE SET NULL,
|
|
applocationid INT,
|
|
partnerid INT REFERENCES partnerinfo(partnerid) ON DELETE SET NULL,
|
|
tenantid INT,
|
|
onduty INT DEFAULT 0,
|
|
status VARCHAR(50) DEFAULT 'Active',
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 5. Miler Profiles
|
|
"milerprofiles": """
|
|
CREATE TABLE IF NOT EXISTS milerprofiles (
|
|
milerprofileid SERIAL PRIMARY KEY,
|
|
userid INT UNIQUE NOT NULL REFERENCES appusers(userid) ON DELETE CASCADE,
|
|
displayname VARCHAR(150) NOT NULL,
|
|
phone VARCHAR(20) NOT NULL,
|
|
profilephotourl VARCHAR(500),
|
|
vehicleid INT REFERENCES vehicles(vehicleid) ON DELETE SET NULL,
|
|
defaultvehicletype VARCHAR(50),
|
|
currentlatitude DECIMAL(10, 7),
|
|
currentlongitude DECIMAL(10, 7),
|
|
currentpincode VARCHAR(10),
|
|
availabilitystatus VARCHAR(50) DEFAULT 'Offline' CHECK (availabilitystatus IN ('Offline', 'Available', 'Assigned', 'On_Pickup', 'At_Customer', 'Picked_Up', 'On_Delivery', 'Break', 'Blocked')),
|
|
rating NUMERIC(3, 2) DEFAULT 5.00,
|
|
totalcompletedpickups INT DEFAULT 0,
|
|
totalcancelledpickups INT DEFAULT 0,
|
|
lastlocationupdatedat TIMESTAMP,
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 6. App Customers
|
|
"appcustomers": """
|
|
CREATE TABLE IF NOT EXISTS appcustomers (
|
|
appcustomerid SERIAL PRIMARY KEY,
|
|
firstname VARCHAR(100) NOT NULL,
|
|
lastname VARCHAR(100),
|
|
phone VARCHAR(20) UNIQUE NOT NULL,
|
|
email VARCHAR(150),
|
|
loginpinhash VARCHAR(255),
|
|
defaultlatitude DECIMAL(10, 7),
|
|
defaultlongitude DECIMAL(10, 7),
|
|
defaultpincode VARCHAR(10),
|
|
status VARCHAR(50) DEFAULT 'Active' CHECK (status IN ('Active', 'Blocked', 'Deleted')),
|
|
lastloginat TIMESTAMP,
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 7. App Customer Locations
|
|
"appcustomerlocations": """
|
|
CREATE TABLE IF NOT EXISTS appcustomerlocations (
|
|
appcustomerlocationid SERIAL PRIMARY KEY,
|
|
appcustomerid INT REFERENCES appcustomers(appcustomerid) ON DELETE CASCADE,
|
|
label VARCHAR(50) DEFAULT 'Home',
|
|
receivername VARCHAR(150),
|
|
receiverphone VARCHAR(20),
|
|
address VARCHAR(500) NOT NULL,
|
|
landmark VARCHAR(255),
|
|
city VARCHAR(100),
|
|
state VARCHAR(100),
|
|
pincode VARCHAR(10) NOT NULL,
|
|
latitude DECIMAL(10, 7) NOT NULL,
|
|
longitude DECIMAL(10, 7) NOT NULL,
|
|
isdefault BOOLEAN DEFAULT FALSE,
|
|
status VARCHAR(50) DEFAULT 'Active',
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 8. Consignments (Base for dispatch, created in order ahead of trip sheet links)
|
|
"consignments": """
|
|
CREATE TABLE IF NOT EXISTS consignments (
|
|
consignmentid SERIAL PRIMARY KEY,
|
|
trackingno VARCHAR(100) UNIQUE NOT NULL,
|
|
orderheaderid INT,
|
|
tenantid INT,
|
|
senderid INT,
|
|
receiverid INT,
|
|
pickuplocationid INT,
|
|
deliverylocationid INT,
|
|
originhubid INT REFERENCES hubs(hubid),
|
|
currenthubid INT REFERENCES hubs(hubid),
|
|
destinationhubid INT REFERENCES hubs(hubid),
|
|
pickuppincode VARCHAR(10),
|
|
deliverypincode VARCHAR(10),
|
|
pickuplatitude DECIMAL(10, 7),
|
|
pickuplongitude DECIMAL(10, 7),
|
|
deliverylatitude DECIMAL(10, 7),
|
|
deliverylongitude DECIMAL(10, 7),
|
|
length NUMERIC(8, 2),
|
|
width NUMERIC(8, 2),
|
|
height NUMERIC(8, 2),
|
|
deadweight NUMERIC(10, 2) NOT NULL,
|
|
volumetricweight NUMERIC(10, 2),
|
|
chargeableweight NUMERIC(10, 2) NOT NULL,
|
|
codamount NUMERIC(10, 2) DEFAULT 0.00,
|
|
codcollected NUMERIC(10, 2) DEFAULT 0.00,
|
|
paymentmode VARCHAR(50) CHECK (paymentmode IN ('Prepaid', 'COD', 'To_Pay')),
|
|
billingstatus VARCHAR(50) DEFAULT 'Unbilled' CHECK (billingstatus IN ('Unbilled', 'Billed', 'Paid', 'Settled')),
|
|
status VARCHAR(50) DEFAULT 'Created' CHECK (status IN ('Created', 'Inwarded_at_Hub', 'Tripsheet_Loaded', 'In_Transit', 'Out_for_Delivery', 'Delivered', 'RTO_Initiated', 'Returned_to_Sender', 'Missing', 'Damaged')),
|
|
attemptcount INT DEFAULT 0,
|
|
estimateddeliveryat TIMESTAMP,
|
|
sladueat TIMESTAMP,
|
|
returnreason VARCHAR(255),
|
|
returninitiatedat TIMESTAMP,
|
|
returndeliveredat TIMESTAMP,
|
|
parentconsignmentid INT REFERENCES consignments(consignmentid) ON DELETE SET NULL,
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT,
|
|
updatedby INT,
|
|
deletedat TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 9. Pickup Bookings (Links to appcustomers, milers, and consignments)
|
|
"pickupbookings": """
|
|
CREATE TABLE IF NOT EXISTS pickupbookings (
|
|
bookingid SERIAL PRIMARY KEY,
|
|
bookingno VARCHAR(100) UNIQUE NOT NULL,
|
|
appcustomerid INT REFERENCES appcustomers(appcustomerid) ON DELETE CASCADE,
|
|
pickuplocationid INT REFERENCES appcustomerlocations(appcustomerlocationid),
|
|
pickupaddress VARCHAR(500) NOT NULL,
|
|
pickuppincode VARCHAR(10) NOT NULL,
|
|
pickuplatitude DECIMAL(10, 7) NOT NULL,
|
|
pickuplongitude DECIMAL(10, 7) NOT NULL,
|
|
deliveryaddress VARCHAR(500) NOT NULL,
|
|
deliverypincode VARCHAR(10) NOT NULL,
|
|
deliverylatitude DECIMAL(10, 7) NOT NULL,
|
|
deliverylongitude DECIMAL(10, 7) NOT NULL,
|
|
nearesthubid INT REFERENCES hubs(hubid) ON DELETE SET NULL,
|
|
bookingsource VARCHAR(50) DEFAULT 'Customer_App',
|
|
status VARCHAR(50) DEFAULT 'Created' CHECK (status IN ('Created', 'Miler_Assigned', 'Pickup_Scheduled', 'Picked_Up', 'Converted_To_Consignment', 'Cancelled')),
|
|
preferredpickupfrom TIMESTAMP,
|
|
preferredpickupto TIMESTAMP,
|
|
assignedmileruserid INT REFERENCES appusers(userid) ON DELETE SET NULL,
|
|
consignmentid INT REFERENCES consignments(consignmentid) ON DELETE SET NULL,
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 10. Booking Parcels
|
|
"bookingparcels": """
|
|
CREATE TABLE IF NOT EXISTS bookingparcels (
|
|
bookingparcelid SERIAL PRIMARY KEY,
|
|
bookingid INT REFERENCES pickupbookings(bookingid) ON DELETE CASCADE,
|
|
itemcategory VARCHAR(100),
|
|
itemdescription VARCHAR(255),
|
|
declaredvalue NUMERIC(10, 2),
|
|
weight NUMERIC(10, 2),
|
|
length NUMERIC(8, 2),
|
|
width NUMERIC(8, 2),
|
|
height NUMERIC(8, 2),
|
|
isfragile BOOLEAN DEFAULT FALSE,
|
|
needsinsurance BOOLEAN DEFAULT FALSE,
|
|
insuranceamount NUMERIC(10, 2) DEFAULT 0.00,
|
|
requireslargevehicle BOOLEAN DEFAULT FALSE,
|
|
suggestedvehicletype VARCHAR(50),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 11. Booking Service Options
|
|
"bookingserviceoptions": """
|
|
CREATE TABLE IF NOT EXISTS bookingserviceoptions (
|
|
bookingserviceid SERIAL PRIMARY KEY,
|
|
bookingid INT REFERENCES pickupbookings(bookingid) ON DELETE CASCADE,
|
|
servicetype VARCHAR(50) CHECK (servicetype IN ('Normal', 'Fast', 'Superfast')),
|
|
estimatedprice NUMERIC(10, 2),
|
|
estimateddeliveryat TIMESTAMP,
|
|
sladueat TIMESTAMP,
|
|
pricingid INT,
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 12. Booking Payments
|
|
"bookingpayments": """
|
|
CREATE TABLE IF NOT EXISTS bookingpayments (
|
|
bookingpaymentid SERIAL PRIMARY KEY,
|
|
bookingid INT REFERENCES pickupbookings(bookingid) ON DELETE CASCADE,
|
|
amount NUMERIC(10, 2) NOT NULL,
|
|
paymentmode VARCHAR(50) CHECK (paymentmode IN ('Cash', 'UPI', 'Card', 'Wallet')),
|
|
paymentstatus VARCHAR(50) DEFAULT 'Pending' CHECK (paymentstatus IN ('Pending', 'Paid', 'Failed', 'Refunded')),
|
|
collectedbyuserid INT REFERENCES appusers(userid) ON DELETE SET NULL,
|
|
transactionref VARCHAR(150),
|
|
paidat TIMESTAMP,
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 13. Booking Assignments
|
|
"bookingassignments": """
|
|
CREATE TABLE IF NOT EXISTS bookingassignments (
|
|
bookingassignmentid SERIAL PRIMARY KEY,
|
|
bookingid INT REFERENCES pickupbookings(bookingid) ON DELETE CASCADE,
|
|
mileruserid INT REFERENCES appusers(userid) ON DELETE CASCADE,
|
|
assignedbyuserid INT REFERENCES appusers(userid) ON DELETE SET NULL,
|
|
assignmentstatus VARCHAR(50) DEFAULT 'Assigned' CHECK (assignmentstatus IN ('Assigned', 'Accepted', 'Rejected', 'Reassigned', 'Completed', 'Cancelled')),
|
|
assignedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
acceptedat TIMESTAMP,
|
|
completedat TIMESTAMP,
|
|
remarks VARCHAR(500)
|
|
);
|
|
""",
|
|
|
|
# 14. Booking Vehicle Requirements
|
|
"bookingvehiclerequirements": """
|
|
CREATE TABLE IF NOT EXISTS bookingvehiclerequirements (
|
|
requirementid SERIAL PRIMARY KEY,
|
|
bookingid INT REFERENCES pickupbookings(bookingid) ON DELETE CASCADE,
|
|
requiredvehicletype VARCHAR(50) NOT NULL,
|
|
reason VARCHAR(255),
|
|
nearesthubid INT REFERENCES hubs(hubid) ON DELETE SET NULL,
|
|
scheduledpickupfrom TIMESTAMP,
|
|
scheduledpickupto TIMESTAMP,
|
|
assignedvehicleid INT REFERENCES vehicles(vehicleid) ON DELETE SET NULL,
|
|
assigneddriveruserid INT REFERENCES appusers(userid) ON DELETE SET NULL,
|
|
status VARCHAR(50) DEFAULT 'Required' CHECK (status IN ('Required', 'Scheduled', 'Assigned', 'Arrived', 'Picked_Up', 'Cancelled')),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 15. Trip Sheets
|
|
"tripsheets": """
|
|
CREATE TABLE IF NOT EXISTS tripsheets (
|
|
tripsheetid SERIAL PRIMARY KEY,
|
|
tripsheetno VARCHAR(100) UNIQUE NOT NULL,
|
|
sourcehubid INT REFERENCES hubs(hubid) ON DELETE RESTRICT,
|
|
destinationhubid INT REFERENCES hubs(hubid) ON DELETE RESTRICT,
|
|
vehicleid INT REFERENCES vehicles(vehicleid) ON DELETE SET NULL,
|
|
driveruserid INT REFERENCES appusers(userid) ON DELETE SET NULL,
|
|
dispatchtime TIMESTAMP,
|
|
arrivaltime TIMESTAMP,
|
|
status VARCHAR(50) DEFAULT 'Draft' CHECK (status IN ('Draft', 'Dispatched', 'Arrived', 'Cancelled')),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT,
|
|
updatedby INT,
|
|
deletedat TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 16. Trip Sheet Items
|
|
"tripsheetitems": """
|
|
CREATE TABLE IF NOT EXISTS tripsheetitems (
|
|
tripsheetitemid SERIAL PRIMARY KEY,
|
|
tripsheetid INT REFERENCES tripsheets(tripsheetid) ON DELETE CASCADE,
|
|
consignmentid INT REFERENCES consignments(consignmentid) ON DELETE CASCADE,
|
|
scanstatus VARCHAR(50) DEFAULT 'Pending' CHECK (scanstatus IN ('Pending', 'Loaded', 'Unloaded', 'Discrepancy')),
|
|
scannedat TIMESTAMP,
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT,
|
|
updatedby INT,
|
|
deletedat TIMESTAMP,
|
|
UNIQUE(tripsheetid, consignmentid)
|
|
);
|
|
""",
|
|
|
|
# 17. Delivery Proofs (POD)
|
|
"deliveryproofs": """
|
|
CREATE TABLE IF NOT EXISTS deliveryproofs (
|
|
proofid SERIAL PRIMARY KEY,
|
|
consignmentid INT UNIQUE NOT NULL REFERENCES consignments(consignmentid) ON DELETE CASCADE,
|
|
tripsheetid INT REFERENCES tripsheets(tripsheetid) ON DELETE SET NULL,
|
|
deliveredat TIMESTAMP NOT NULL,
|
|
deliveredtoname VARCHAR(255) NOT NULL,
|
|
receiversignatureurl VARCHAR(500),
|
|
photourl VARCHAR(500),
|
|
otpverified BOOLEAN DEFAULT FALSE,
|
|
geolatitude DECIMAL(10, 7),
|
|
geolongitude DECIMAL(10, 7),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT
|
|
);
|
|
""",
|
|
|
|
# 18. Pricing
|
|
"pricing": """
|
|
CREATE TABLE IF NOT EXISTS pricing (
|
|
pricingid SERIAL PRIMARY KEY,
|
|
tenantid INT,
|
|
applocationid INT,
|
|
vehicletype VARCHAR(50),
|
|
baseprice NUMERIC(10, 2) NOT NULL,
|
|
baseweight NUMERIC(10, 2) NOT NULL,
|
|
priceperkg NUMERIC(10, 2) NOT NULL,
|
|
basedistance NUMERIC(10, 2) NOT NULL,
|
|
priceperkm NUMERIC(10, 2) NOT NULL,
|
|
handlingcharges NUMERIC(10, 2) DEFAULT 0.00,
|
|
effectivefrom TIMESTAMP NOT NULL,
|
|
effectiveto TIMESTAMP NOT NULL,
|
|
currency VARCHAR(10) DEFAULT 'INR',
|
|
priority INT DEFAULT 0,
|
|
status VARCHAR(50) DEFAULT 'Active' CHECK (status IN ('Active', 'InActive')),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT,
|
|
updatedby INT,
|
|
deletedat TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 19. Consignment History
|
|
"consignmenthistory": """
|
|
CREATE TABLE IF NOT EXISTS consignmenthistory (
|
|
historyid SERIAL PRIMARY KEY,
|
|
consignmentid INT REFERENCES consignments(consignmentid) ON DELETE CASCADE,
|
|
tripsheetid INT REFERENCES tripsheets(tripsheetid) ON DELETE SET NULL,
|
|
hubid INT REFERENCES hubs(hubid) ON DELETE SET NULL,
|
|
userid INT REFERENCES appusers(userid) ON DELETE SET NULL,
|
|
eventstatus VARCHAR(50) NOT NULL,
|
|
remarks VARCHAR(500),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 20. Consignment Exceptions
|
|
"consignmentexceptions": """
|
|
CREATE TABLE IF NOT EXISTS consignmentexceptions (
|
|
exceptionid SERIAL PRIMARY KEY,
|
|
consignmentid INT REFERENCES consignments(consignmentid) ON DELETE CASCADE,
|
|
tripsheetid INT REFERENCES tripsheets(tripsheetid) ON DELETE SET NULL,
|
|
hubid INT REFERENCES hubs(hubid) ON DELETE SET NULL,
|
|
reportedbyuserid INT REFERENCES appusers(userid) ON DELETE SET NULL,
|
|
exceptiontype VARCHAR(50) NOT NULL CHECK (exceptiontype IN ('Lost', 'Damaged', 'Misrouted', 'Receiver_Refused', 'Missing_Contents', 'Undeliverable')),
|
|
severity VARCHAR(50) DEFAULT 'Medium' CHECK (severity IN ('Low', 'Medium', 'High', 'Critical')),
|
|
description TEXT,
|
|
resolution VARCHAR(255),
|
|
status VARCHAR(50) DEFAULT 'Open' CHECK (status IN ('Open', 'Under_Investigation', 'Resolved', 'Closed')),
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
createdby INT,
|
|
updatedby INT,
|
|
deletedat TIMESTAMP
|
|
);
|
|
""",
|
|
|
|
# 21. Tenant Locations (Physical storefronts)
|
|
"tenantlocations": """
|
|
CREATE TABLE IF NOT EXISTS tenantlocations (
|
|
tenantlocationid SERIAL PRIMARY KEY,
|
|
tenantid INT NOT NULL,
|
|
address VARCHAR(500) NOT NULL,
|
|
city VARCHAR(100) NOT NULL,
|
|
state VARCHAR(100) NOT NULL,
|
|
pincode VARCHAR(10) NOT NULL,
|
|
latitude DECIMAL(10, 7) NOT NULL,
|
|
longitude DECIMAL(10, 7) NOT NULL,
|
|
isprimary BOOLEAN DEFAULT FALSE,
|
|
status VARCHAR(50) DEFAULT 'Active',
|
|
createdat TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updatedat TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
"""
|
|
}
|
|
|
|
# Index creation statements
|
|
INDEXES = [
|
|
# Consignment Indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_consignments_trackingno ON consignments (trackingno);",
|
|
"CREATE INDEX IF NOT EXISTS idx_consignments_status ON consignments (status);",
|
|
"CREATE INDEX IF NOT EXISTS idx_consignments_tenantid ON consignments (tenantid);",
|
|
"CREATE INDEX IF NOT EXISTS idx_consignments_currenthubid ON consignments (currenthubid);",
|
|
|
|
# Tripsheet Indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_tripsheets_vehicleid ON tripsheets (vehicleid);",
|
|
"CREATE INDEX IF NOT EXISTS idx_tripsheets_driveruserid ON tripsheets (driveruserid);",
|
|
"CREATE INDEX IF NOT EXISTS idx_tripsheets_lookup ON tripsheets (status, dispatchtime);",
|
|
|
|
# Tripsheet Items Indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_tripsheetitems_tripsheetid ON tripsheetitems (tripsheetid);",
|
|
"CREATE INDEX IF NOT EXISTS idx_tripsheetitems_consignmentid ON tripsheetitems (consignmentid);",
|
|
|
|
# Delivery Proofs Indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_deliveryproofs_consignmentid ON deliveryproofs (consignmentid);",
|
|
|
|
# Miler Profile Indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_milerprofiles_userid ON milerprofiles (userid);",
|
|
"CREATE INDEX IF NOT EXISTS idx_milerprofiles_availabilitystatus ON milerprofiles (availabilitystatus);",
|
|
"CREATE INDEX IF NOT EXISTS idx_milerprofiles_currentpincode ON milerprofiles (currentpincode);",
|
|
"CREATE INDEX IF NOT EXISTS idx_milerprofiles_geo ON milerprofiles (currentlatitude, currentlongitude);",
|
|
|
|
# History & Exceptions Audit Lookup Indexes
|
|
"CREATE INDEX IF NOT EXISTS idx_consignmenthistory_lookup ON consignmenthistory (consignmentid, createdat);",
|
|
"CREATE INDEX IF NOT EXISTS idx_consignmentexceptions_lookup ON consignmentexceptions (status, createdat);"
|
|
]
|
|
|
|
def create_schema():
|
|
conn = None
|
|
try:
|
|
print("Connecting to the PostgreSQL database...")
|
|
conn = psycopg2.connect(**DB_PARAMS)
|
|
cur = conn.cursor()
|
|
|
|
# 1. Create Tables
|
|
for table_name, create_sql in TABLES.items():
|
|
print(f"Creating table '{table_name}'...")
|
|
cur.execute(create_sql)
|
|
|
|
# 2. Create Indexes
|
|
for idx_sql in INDEXES:
|
|
print("Executing index script...")
|
|
cur.execute(idx_sql)
|
|
|
|
conn.commit()
|
|
print("✅ Database schema created successfully under database: 'logistics'!")
|
|
cur.close()
|
|
except Exception as error:
|
|
print(f"❌ Error during database schema initialization: {error}")
|
|
if conn:
|
|
conn.rollback()
|
|
finally:
|
|
if conn:
|
|
conn.close()
|
|
print("Database connection closed.")
|
|
|
|
if __name__ == "__main__":
|
|
create_schema()
|