MCP Database Server

-- Create schemas for each system CREATE SCHEMA IF NOT EXISTS cs; CREATE SCHEMA IF NOT EXISTS dms; CREATE SCHEMA IF NOT EXISTS los; CREATE SCHEMA IF NOT EXISTS mls; -- CustomerScoreView (cs) system tables CREATE TABLE IF NOT EXISTS cs.customers ( customer_id SERIAL PRIMARY KEY, customer_name VARCHAR(255) NOT NULL, customer_code VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(255), phone VARCHAR(50), credit_score INTEGER, registration_date DATE, status VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS cs.credit_assessments ( assessment_id SERIAL PRIMARY KEY, customer_id INTEGER REFERENCES cs.customers(customer_id), assessment_date DATE NOT NULL, score INTEGER NOT NULL, risk_level VARCHAR(50), notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Data Management System (dms) tables CREATE TABLE IF NOT EXISTS dms.documents ( document_id SERIAL PRIMARY KEY, document_name VARCHAR(255) NOT NULL, document_type VARCHAR(100), customer_code VARCHAR(50), file_path VARCHAR(500), upload_date DATE, status VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS dms.document_versions ( version_id SERIAL PRIMARY KEY, document_id INTEGER REFERENCES dms.documents(document_id), version_number INTEGER NOT NULL, modified_by VARCHAR(100), modified_date TIMESTAMP, notes TEXT ); -- Loan Management System (los) tables CREATE TABLE IF NOT EXISTS los.loans ( loan_id SERIAL PRIMARY KEY, loan_number VARCHAR(50) UNIQUE NOT NULL, customer_code VARCHAR(50), loan_amount DECIMAL(15, 2), interest_rate DECIMAL(5, 2), loan_term INTEGER, start_date DATE, end_date DATE, status VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS los.payments ( payment_id SERIAL PRIMARY KEY, loan_id INTEGER REFERENCES los.loans(loan_id), payment_date DATE NOT NULL, payment_amount DECIMAL(15, 2), principal_amount DECIMAL(15, 2), interest_amount DECIMAL(15, 2), balance DECIMAL(15, 2), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Mahatheun Leasing System (mls) - Core system tables CREATE TABLE IF NOT EXISTS mls.contracts ( contract_id SERIAL PRIMARY KEY, contract_number VARCHAR(50) UNIQUE NOT NULL, customer_code VARCHAR(50), asset_description TEXT, contract_amount DECIMAL(15, 2), lease_term INTEGER, monthly_payment DECIMAL(15, 2), start_date DATE, end_date DATE, status VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS mls.assets ( asset_id SERIAL PRIMARY KEY, contract_id INTEGER REFERENCES mls.contracts(contract_id), asset_type VARCHAR(100), asset_name VARCHAR(255), serial_number VARCHAR(100), purchase_price DECIMAL(15, 2), current_value DECIMAL(15, 2), condition VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS mls.lease_payments ( lease_payment_id SERIAL PRIMARY KEY, contract_id INTEGER REFERENCES mls.contracts(contract_id), payment_date DATE NOT NULL, payment_amount DECIMAL(15, 2), late_fee DECIMAL(10, 2), status VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Create indexes for better query performance CREATE INDEX IF NOT EXISTS idx_customers_code ON cs.customers(customer_code); CREATE INDEX IF NOT EXISTS idx_documents_customer ON dms.documents(customer_code); CREATE INDEX IF NOT EXISTS idx_loans_customer ON los.loans(customer_code); CREATE INDEX IF NOT EXISTS idx_contracts_customer ON mls.contracts(customer_code); -- Grant permissions GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA cs TO postgres; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA dms TO postgres; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA los TO postgres; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA mls TO postgres; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA cs TO postgres; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA dms TO postgres; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA los TO postgres; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA mls TO postgres; -- Insert some sample data for testing INSERT INTO cs.customers (customer_name, customer_code, email, phone, credit_score, registration_date, status) VALUES ('John Doe', 'CUST001', 'john.doe@example.com', '+856-20-1234567', 750, '2024-01-15', 'Active'), ('Jane Smith', 'CUST002', 'jane.smith@example.com', '+856-20-2345678', 680, '2024-02-20', 'Active'), ('Bob Johnson', 'CUST003', 'bob.johnson@example.com', '+856-20-3456789', 720, '2024-03-10', 'Active') ON CONFLICT DO NOTHING; INSERT INTO dms.documents (document_name, document_type, customer_code, file_path, upload_date, status) VALUES ('ID Card Copy', 'Identification', 'CUST001', '/docs/id_001.pdf', '2024-01-15', 'Approved'), ('Bank Statement', 'Financial', 'CUST002', '/docs/bank_002.pdf', '2024-02-20', 'Pending') ON CONFLICT DO NOTHING; INSERT INTO los.loans (loan_number, customer_code, loan_amount, interest_rate, loan_term, start_date, end_date, status) VALUES ('LOAN001', 'CUST001', 100000.00, 8.5, 36, '2024-02-01', '2027-02-01', 'Active'), ('LOAN002', 'CUST002', 50000.00, 9.0, 24, '2024-03-01', '2026-03-01', 'Active') ON CONFLICT DO NOTHING; INSERT INTO mls.contracts (contract_number, customer_code, asset_description, contract_amount, lease_term, monthly_payment, start_date, end_date, status) VALUES ('LEASE001', 'CUST001', 'Toyota Camry 2023', 250000.00, 48, 6000.00, '2024-01-15', '2028-01-15', 'Active'), ('LEASE002', 'CUST003', 'Excavator CAT 320', 500000.00, 60, 10000.00, '2024-03-01', '2029-03-01', 'Active') ON CONFLICT DO NOTHING;