prazy1208/text2sql

1-- ============================================================================2-- COMPLETE TEXT2SQL DATABASE SETUP3-- Run this in Supabase SQL Editor: https://supabase.com/dashboard → SQL Editor4-- ============================================================================5 6-- ============================================================================7-- HEALTHCARE SCHEMA8-- ============================================================================9CREATE SCHEMA IF NOT EXISTS healthcare_schema;10 11CREATE TABLE IF NOT EXISTS healthcare_schema.patients (12    patient_id SERIAL PRIMARY KEY,13    first_name VARCHAR(100),14    last_name VARCHAR(100),15    date_of_birth DATE,16    gender VARCHAR(20),17    city VARCHAR(100),18    state VARCHAR(100),19    insurance_type VARCHAR(50),20    registration_date DATE21);22 23CREATE TABLE IF NOT EXISTS healthcare_schema.visits (24    visit_id SERIAL PRIMARY KEY,25    patient_id INT REFERENCES healthcare_schema.patients(patient_id),26    admission_date DATE,27    discharge_date DATE,28    department VARCHAR(100),29    department_id INT,30    visit_type VARCHAR(50),31    total_cost NUMERIC(12,2)32);33ALTER TABLE healthcare_schema.visits34    ADD COLUMN IF NOT EXISTS department_id INT;35 36 37CREATE TABLE IF NOT EXISTS healthcare_schema.diagnoses (38    diagnosis_id SERIAL PRIMARY KEY,39    visit_id INT REFERENCES healthcare_schema.visits(visit_id),40    diagnosis_code VARCHAR(20),41    diagnosis_description VARCHAR(255),42    severity_level VARCHAR(20)43);44 45CREATE TABLE IF NOT EXISTS healthcare_schema.departments (46    department_id SERIAL PRIMARY KEY,47    department_name VARCHAR(100),48    location VARCHAR(100)49);50 51CREATE TABLE IF NOT EXISTS healthcare_schema.providers (52    provider_id SERIAL PRIMARY KEY,53    first_name VARCHAR(50),54    last_name VARCHAR(50),55    specialty VARCHAR(100),56    department_id INT REFERENCES healthcare_schema.departments(department_id),57    years_of_experience INT58);59 60CREATE TABLE IF NOT EXISTS healthcare_schema.procedures (61    procedure_id SERIAL PRIMARY KEY,62    visit_id INT REFERENCES healthcare_schema.visits(visit_id),63    provider_id INT REFERENCES healthcare_schema.providers(provider_id),64    procedure_name VARCHAR(100),65    procedure_date DATE,66    cost DECIMAL(10,2)67);68 69CREATE TABLE IF NOT EXISTS healthcare_schema.medications (70    medication_id SERIAL PRIMARY KEY,71    medication_name VARCHAR(100),72    manufacturer VARCHAR(100),73    unit_cost DECIMAL(10,2)74);75 76CREATE TABLE IF NOT EXISTS healthcare_schema.prescriptions (77    prescription_id SERIAL PRIMARY KEY,78    patient_id INT REFERENCES healthcare_schema.patients(patient_id),79    medication_id INT REFERENCES healthcare_schema.medications(medication_id),80    provider_id INT REFERENCES healthcare_schema.providers(provider_id),81    dosage VARCHAR(50),82    start_date DATE,83    end_date DATE84);85 86CREATE TABLE IF NOT EXISTS healthcare_schema.billing (87    billing_id SERIAL PRIMARY KEY,88    visit_id INT REFERENCES healthcare_schema.visits(visit_id),89    total_amount DECIMAL(12,2),90    insurance_covered_amount DECIMAL(12,2),91    out_of_pocket_amount DECIMAL(12,2),92    billing_date DATE93);94 95CREATE TABLE IF NOT EXISTS healthcare_schema.insurance_claims (96    claim_id SERIAL PRIMARY KEY,97    visit_id INT REFERENCES healthcare_schema.visits(visit_id),98    patient_id INT REFERENCES healthcare_schema.patients(patient_id),99    claim_status VARCHAR(50),100    claim_amount DECIMAL(12,2),101    approved_amount DECIMAL(12,2),102    claim_date DATE103);104 105CREATE TABLE IF NOT EXISTS healthcare_schema.healthcare_business_rules (106    rule_id SERIAL PRIMARY KEY,107    concept_name VARCHAR(150) NOT NULL,108    description TEXT NOT NULL,109    insight TEXT,110    keywords TEXT[],111    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP112);113 114-- Healthcare comments115COMMENT ON SCHEMA healthcare_schema IS 'Healthcare domain: patients, visits, diagnoses';116COMMENT ON TABLE healthcare_schema.patients IS 'Stores patient demographic information.';117COMMENT ON COLUMN healthcare_schema.patients.patient_id IS 'Unique identifier for each patient.';118COMMENT ON COLUMN healthcare_schema.patients.first_name IS 'Patient first name.';119COMMENT ON COLUMN healthcare_schema.patients.last_name IS 'Patient last name.';120COMMENT ON COLUMN healthcare_schema.patients.date_of_birth IS 'Date of birth.';121COMMENT ON COLUMN healthcare_schema.patients.gender IS 'Gender (Male, Female, Other).';122COMMENT ON COLUMN healthcare_schema.patients.city IS 'City of residence.';123COMMENT ON COLUMN healthcare_schema.patients.state IS 'State of residence.';124COMMENT ON COLUMN healthcare_schema.patients.insurance_type IS 'Insurance type (Medicare, Medicaid, Private, etc.).';125COMMENT ON COLUMN healthcare_schema.patients.registration_date IS 'Registration date.';126 127COMMENT ON TABLE healthcare_schema.visits IS 'Records patient visits.';128COMMENT ON COLUMN healthcare_schema.visits.visit_id IS 'Unique identifier for each visit.';129COMMENT ON COLUMN healthcare_schema.visits.patient_id IS 'Foreign key to patients.';130COMMENT ON COLUMN healthcare_schema.visits.admission_date IS 'Admission date.';131COMMENT ON COLUMN healthcare_schema.visits.discharge_date IS 'Discharge date.';132COMMENT ON COLUMN healthcare_schema.visits.department IS 'Legacy text department name (kept for backward compatibility).';133COMMENT ON COLUMN healthcare_schema.visits.department_id IS 'Foreign key to departments master table.';134COMMENT ON COLUMN healthcare_schema.visits.visit_type IS 'Visit type (Inpatient, Outpatient, Emergency).';135COMMENT ON COLUMN healthcare_schema.visits.total_cost IS 'Total visit cost.';136 137COMMENT ON TABLE healthcare_schema.diagnoses IS 'Stores diagnosis information.';138COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_id IS 'Unique identifier for each diagnosis.';139COMMENT ON COLUMN healthcare_schema.diagnoses.visit_id IS 'Foreign key to visits.';140COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_code IS 'ICD diagnosis code.';141COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_description IS 'Diagnosis description.';142COMMENT ON COLUMN healthcare_schema.diagnoses.severity_level IS 'Severity (Low, Medium, High, Critical).';143 144COMMENT ON TABLE healthcare_schema.departments IS 'Hospital departments such as Cardiology, Emergency, etc.';145COMMENT ON COLUMN healthcare_schema.departments.department_id IS 'Unique identifier for each department';146COMMENT ON COLUMN healthcare_schema.departments.department_name IS 'Name of the department';147COMMENT ON COLUMN healthcare_schema.departments.location IS 'Physical location within hospital';148 149COMMENT ON TABLE healthcare_schema.providers IS 'Healthcare professionals including doctors and nurses';150COMMENT ON COLUMN healthcare_schema.providers.provider_id IS 'Unique identifier for provider';151COMMENT ON COLUMN healthcare_schema.providers.first_name IS 'Provider first name';152COMMENT ON COLUMN healthcare_schema.providers.last_name IS 'Provider last name';153COMMENT ON COLUMN healthcare_schema.providers.specialty IS 'Medical specialization';154COMMENT ON COLUMN healthcare_schema.providers.department_id IS 'Department provider belongs to';155COMMENT ON COLUMN healthcare_schema.providers.years_of_experience IS 'Years of professional experience';156 157COMMENT ON TABLE healthcare_schema.procedures IS 'Medical procedures performed during patient visits';158COMMENT ON COLUMN healthcare_schema.procedures.procedure_id IS 'Unique procedure identifier';159COMMENT ON COLUMN healthcare_schema.procedures.visit_id IS 'Associated visit';160COMMENT ON COLUMN healthcare_schema.procedures.provider_id IS 'Provider performing procedure';161COMMENT ON COLUMN healthcare_schema.procedures.procedure_name IS 'Name of procedure';162COMMENT ON COLUMN healthcare_schema.procedures.procedure_date IS 'Date performed';163COMMENT ON COLUMN healthcare_schema.procedures.cost IS 'Cost of procedure';164 165COMMENT ON TABLE healthcare_schema.medications IS 'Master list of medications';166COMMENT ON COLUMN healthcare_schema.medications.medication_id IS 'Unique medication identifier';167COMMENT ON COLUMN healthcare_schema.medications.medication_name IS 'Name of medication';168COMMENT ON COLUMN healthcare_schema.medications.manufacturer IS 'Manufacturer name';169COMMENT ON COLUMN healthcare_schema.medications.unit_cost IS 'Cost per unit';170 171COMMENT ON TABLE healthcare_schema.prescriptions IS 'Prescribed medications for patients';172COMMENT ON COLUMN healthcare_schema.prescriptions.prescription_id IS 'Unique prescription identifier';173COMMENT ON COLUMN healthcare_schema.prescriptions.patient_id IS 'Patient receiving medication';174COMMENT ON COLUMN healthcare_schema.prescriptions.medication_id IS 'Medication prescribed';175COMMENT ON COLUMN healthcare_schema.prescriptions.provider_id IS 'Provider prescribing medication';176COMMENT ON COLUMN healthcare_schema.prescriptions.dosage IS 'Dosage instructions';177COMMENT ON COLUMN healthcare_schema.prescriptions.start_date IS 'Prescription start date';178COMMENT ON COLUMN healthcare_schema.prescriptions.end_date IS 'Prescription end date';179 180COMMENT ON TABLE healthcare_schema.billing IS 'Billing details for each visit';181COMMENT ON COLUMN healthcare_schema.billing.billing_id IS 'Unique billing identifier';182COMMENT ON COLUMN healthcare_schema.billing.visit_id IS 'Visit being billed';183COMMENT ON COLUMN healthcare_schema.billing.total_amount IS 'Total billed amount';184COMMENT ON COLUMN healthcare_schema.billing.insurance_covered_amount IS 'Amount covered by insurance';185COMMENT ON COLUMN healthcare_schema.billing.out_of_pocket_amount IS 'Amount paid by patient';186COMMENT ON COLUMN healthcare_schema.billing.billing_date IS 'Billing date';187 188COMMENT ON TABLE healthcare_schema.insurance_claims IS 'Insurance claim processing details';189COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_id IS 'Unique claim identifier';190COMMENT ON COLUMN healthcare_schema.insurance_claims.visit_id IS 'Associated visit';191COMMENT ON COLUMN healthcare_schema.insurance_claims.patient_id IS 'Patient filing claim';192COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_status IS 'Status (Pending, Approved, Rejected)';193COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_amount IS 'Requested claim amount';194COMMENT ON COLUMN healthcare_schema.insurance_claims.approved_amount IS 'Approved amount';195COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_date IS 'Date of claim submission';196 197ALTER TABLE healthcare_schema.visits198    ADD COLUMN IF NOT EXISTS department_id INT;199 200DO $$201BEGIN202    IF NOT EXISTS (203        SELECT 1204        FROM pg_constraint205        WHERE conname = 'fk_visits_department_id'206          AND connamespace = 'healthcare_schema'::regnamespace207    ) THEN208        ALTER TABLE healthcare_schema.visits209            ADD CONSTRAINT fk_visits_department_id210            FOREIGN KEY (department_id)211            REFERENCES healthcare_schema.departments(department_id);212    END IF;213END $$;214 215-- ============================================================================216-- RETAIL SCHEMA217-- ============================================================================218CREATE SCHEMA IF NOT EXISTS retail_schema;219 220CREATE TABLE IF NOT EXISTS retail_schema.customers (221    customer_id SERIAL PRIMARY KEY,222    first_name VARCHAR(100),223    last_name VARCHAR(100),224    email VARCHAR(150),225    city VARCHAR(100),226    state VARCHAR(100),227    signup_date DATE228);229 230CREATE TABLE IF NOT EXISTS retail_schema.products (231    product_id SERIAL PRIMARY KEY,232    product_name VARCHAR(150),233    category VARCHAR(100),234    category_id INT,235    brand VARCHAR(100),236    price NUMERIC(10,2),237    launch_date DATE238);239ALTER TABLE retail_schema.products240    ADD COLUMN IF NOT EXISTS category_id INT;241 242 243CREATE TABLE IF NOT EXISTS retail_schema.orders (244    order_id SERIAL PRIMARY KEY,245    customer_id INT REFERENCES retail_schema.customers(customer_id),246    product_id INT REFERENCES retail_schema.products(product_id),247    order_date DATE,248    quantity INT,249    total_amount NUMERIC(12,2)250);251 252CREATE TABLE IF NOT EXISTS retail_schema.categories (253    category_id SERIAL PRIMARY KEY,254    category_name VARCHAR(100),255    parent_category_id INT REFERENCES retail_schema.categories(category_id)256);257 258CREATE TABLE IF NOT EXISTS retail_schema.suppliers (259    supplier_id SERIAL PRIMARY KEY,260    supplier_name VARCHAR(100),261    contact_email VARCHAR(100),262    phone VARCHAR(20),263    city VARCHAR(50),264    state VARCHAR(50)265);266 267CREATE TABLE IF NOT EXISTS retail_schema.stores (268    store_id SERIAL PRIMARY KEY,269    store_name VARCHAR(100),270    city VARCHAR(50),271    state VARCHAR(50),272    store_type VARCHAR(50)273);274 275CREATE TABLE IF NOT EXISTS retail_schema.inventory (276    inventory_id SERIAL PRIMARY KEY,277    product_id INT REFERENCES retail_schema.products(product_id),278    store_id INT REFERENCES retail_schema.stores(store_id),279    stock_quantity INT,280    last_updated TIMESTAMP281);282 283CREATE TABLE IF NOT EXISTS retail_schema.shipments (284    shipment_id SERIAL PRIMARY KEY,285    order_id INT REFERENCES retail_schema.orders(order_id),286    shipment_date DATE,287    delivery_date DATE,288    shipment_status VARCHAR(50)289);290 291CREATE TABLE IF NOT EXISTS retail_schema.reviews (292    review_id SERIAL PRIMARY KEY,293    product_id INT REFERENCES retail_schema.products(product_id),294    customer_id INT REFERENCES retail_schema.customers(customer_id),295    rating INT,296    review_text TEXT,297    review_date DATE298);299 300CREATE TABLE IF NOT EXISTS retail_schema.promotions (301    promotion_id SERIAL PRIMARY KEY,302    promotion_name VARCHAR(100),303    discount_percentage DECIMAL(5,2),304    start_date DATE,305    end_date DATE306);307 308CREATE TABLE IF NOT EXISTS retail_schema.retail_business_rules (309    rule_id SERIAL PRIMARY KEY,310    concept_name VARCHAR(150) NOT NULL,311    description TEXT NOT NULL,312    insight TEXT,313    keywords TEXT[],314    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP315);316 317-- Retail comments318COMMENT ON SCHEMA retail_schema IS 'Retail domain: customers, products, orders';319COMMENT ON TABLE retail_schema.customers IS 'Stores customer information.';320COMMENT ON COLUMN retail_schema.customers.customer_id IS 'Unique identifier for each customer.';321COMMENT ON COLUMN retail_schema.customers.first_name IS 'Customer first name.';322COMMENT ON COLUMN retail_schema.customers.last_name IS 'Customer last name.';323COMMENT ON COLUMN retail_schema.customers.email IS 'Customer email.';324COMMENT ON COLUMN retail_schema.customers.city IS 'City of residence.';325COMMENT ON COLUMN retail_schema.customers.state IS 'State of residence.';326COMMENT ON COLUMN retail_schema.customers.signup_date IS 'Signup date.';327 328COMMENT ON TABLE retail_schema.products IS 'Stores product catalog.';329COMMENT ON COLUMN retail_schema.products.product_id IS 'Unique identifier for each product.';330COMMENT ON COLUMN retail_schema.products.product_name IS 'Product name.';331COMMENT ON COLUMN retail_schema.products.category IS 'Legacy text category (kept for backward compatibility).';332COMMENT ON COLUMN retail_schema.products.category_id IS 'Foreign key to categories hierarchy.';333COMMENT ON COLUMN retail_schema.products.brand IS 'Product brand.';334COMMENT ON COLUMN retail_schema.products.price IS 'Product price.';335COMMENT ON COLUMN retail_schema.products.launch_date IS 'Launch date.';336 337COMMENT ON TABLE retail_schema.orders IS 'Records customer orders.';338COMMENT ON COLUMN retail_schema.orders.order_id IS 'Unique identifier for each order.';339COMMENT ON COLUMN retail_schema.orders.customer_id IS 'Foreign key to customers.';340COMMENT ON COLUMN retail_schema.orders.product_id IS 'Foreign key to products.';341COMMENT ON COLUMN retail_schema.orders.order_date IS 'Order date.';342COMMENT ON COLUMN retail_schema.orders.quantity IS 'Quantity ordered.';343COMMENT ON COLUMN retail_schema.orders.total_amount IS 'Total order amount.';344 345COMMENT ON TABLE retail_schema.categories IS 'Product category hierarchy';346COMMENT ON COLUMN retail_schema.categories.category_id IS 'Unique category identifier';347COMMENT ON COLUMN retail_schema.categories.category_name IS 'Category name';348COMMENT ON COLUMN retail_schema.categories.parent_category_id IS 'Parent category for hierarchical grouping';349 350COMMENT ON TABLE retail_schema.suppliers IS 'Suppliers providing products';351COMMENT ON COLUMN retail_schema.suppliers.supplier_id IS 'Unique supplier identifier';352COMMENT ON COLUMN retail_schema.suppliers.supplier_name IS 'Supplier name';353COMMENT ON COLUMN retail_schema.suppliers.contact_email IS 'Supplier contact email';354COMMENT ON COLUMN retail_schema.suppliers.phone IS 'Supplier phone number';355COMMENT ON COLUMN retail_schema.suppliers.city IS 'Supplier city';356COMMENT ON COLUMN retail_schema.suppliers.state IS 'Supplier state';357 358COMMENT ON TABLE retail_schema.stores IS 'Retail store locations';359COMMENT ON COLUMN retail_schema.stores.store_id IS 'Unique store identifier';360COMMENT ON COLUMN retail_schema.stores.store_name IS 'Store name';361COMMENT ON COLUMN retail_schema.stores.city IS 'Store city';362COMMENT ON COLUMN retail_schema.stores.state IS 'Store state';363COMMENT ON COLUMN retail_schema.stores.store_type IS 'Type (online/physical)';364 365COMMENT ON TABLE retail_schema.inventory IS 'Inventory levels per product and store';366COMMENT ON COLUMN retail_schema.inventory.inventory_id IS 'Unique inventory record';367COMMENT ON COLUMN retail_schema.inventory.product_id IS 'Product being tracked';368COMMENT ON COLUMN retail_schema.inventory.store_id IS 'Store holding inventory';369COMMENT ON COLUMN retail_schema.inventory.stock_quantity IS 'Available stock quantity';370COMMENT ON COLUMN retail_schema.inventory.last_updated IS 'Last update timestamp';371 372COMMENT ON TABLE retail_schema.shipments IS 'Tracks shipment and delivery of orders';373COMMENT ON COLUMN retail_schema.shipments.shipment_id IS 'Unique shipment identifier';374COMMENT ON COLUMN retail_schema.shipments.order_id IS 'Order being shipped';375COMMENT ON COLUMN retail_schema.shipments.shipment_date IS 'Date shipped';376COMMENT ON COLUMN retail_schema.shipments.delivery_date IS 'Date delivered';377COMMENT ON COLUMN retail_schema.shipments.shipment_status IS 'Delivery status';378 379COMMENT ON TABLE retail_schema.reviews IS 'Customer reviews and ratings for products';380COMMENT ON COLUMN retail_schema.reviews.review_id IS 'Unique review identifier';381COMMENT ON COLUMN retail_schema.reviews.product_id IS 'Reviewed product';382COMMENT ON COLUMN retail_schema.reviews.customer_id IS 'Customer writing review';383COMMENT ON COLUMN retail_schema.reviews.rating IS 'Rating score (1-5)';384COMMENT ON COLUMN retail_schema.reviews.review_text IS 'Written feedback';385COMMENT ON COLUMN retail_schema.reviews.review_date IS 'Date of review';386 387COMMENT ON TABLE retail_schema.promotions IS 'Marketing promotions and discounts';388COMMENT ON COLUMN retail_schema.promotions.promotion_id IS 'Unique promotion identifier';389COMMENT ON COLUMN retail_schema.promotions.promotion_name IS 'Promotion campaign name';390COMMENT ON COLUMN retail_schema.promotions.discount_percentage IS 'Discount percentage applied';391COMMENT ON COLUMN retail_schema.promotions.start_date IS 'Promotion start date';392COMMENT ON COLUMN retail_schema.promotions.end_date IS 'Promotion end date';393 394ALTER TABLE retail_schema.products395    ADD COLUMN IF NOT EXISTS category_id INT;396 397DO $$398BEGIN399    IF NOT EXISTS (400        SELECT 1401        FROM pg_constraint402        WHERE conname = 'fk_products_category_id'403          AND connamespace = 'retail_schema'::regnamespace404    ) THEN405        ALTER TABLE retail_schema.products406            ADD CONSTRAINT fk_products_category_id407            FOREIGN KEY (category_id)408            REFERENCES retail_schema.categories(category_id);409    END IF;410END $$;411 412-- ============================================================================413-- FINANCE SCHEMA414-- ============================================================================415CREATE SCHEMA IF NOT EXISTS finance_schema;416 417CREATE TABLE IF NOT EXISTS finance_schema.accounts (418    account_id SERIAL PRIMARY KEY,419    customer_name VARCHAR(150),420    customer_id INT,421    account_type VARCHAR(50),422    branch_city VARCHAR(100),423    branch_id INT,424    opening_date DATE,425    current_balance NUMERIC(14,2)426);427ALTER TABLE finance_schema.accounts428    ADD COLUMN IF NOT EXISTS customer_id INT;429ALTER TABLE finance_schema.accounts430    ADD COLUMN IF NOT EXISTS branch_id INT;431 432 433CREATE TABLE IF NOT EXISTS finance_schema.transactions (434    transaction_id SERIAL PRIMARY KEY,435    account_id INT REFERENCES finance_schema.accounts(account_id),436    transaction_date DATE,437    transaction_type VARCHAR(50),438    amount NUMERIC(14,2),439    description VARCHAR(255)440);441 442CREATE TABLE IF NOT EXISTS finance_schema.loans (443    loan_id SERIAL PRIMARY KEY,444    account_id INT REFERENCES finance_schema.accounts(account_id),445    loan_type VARCHAR(100),446    loan_amount NUMERIC(14,2),447    interest_rate NUMERIC(5,2),448    loan_start_date DATE,449    loan_end_date DATE450);451 452CREATE TABLE IF NOT EXISTS finance_schema.customers (453    customer_id SERIAL PRIMARY KEY,454    first_name VARCHAR(50),455    last_name VARCHAR(50),456    email VARCHAR(100),457    phone VARCHAR(20),458    city VARCHAR(50),459    state VARCHAR(50),460    created_at TIMESTAMP461);462 463CREATE TABLE IF NOT EXISTS finance_schema.branches (464    branch_id SERIAL PRIMARY KEY,465    branch_name VARCHAR(100),466    city VARCHAR(50),467    state VARCHAR(50)468);469 470CREATE TABLE IF NOT EXISTS finance_schema.credit_cards (471    card_id SERIAL PRIMARY KEY,472    customer_id INT REFERENCES finance_schema.customers(customer_id),473    card_type VARCHAR(50),474    credit_limit DECIMAL(12,2),475    current_balance DECIMAL(12,2),476    issue_date DATE,477    expiry_date DATE478);479 480CREATE TABLE IF NOT EXISTS finance_schema.payments (481    payment_id SERIAL PRIMARY KEY,482    account_id INT REFERENCES finance_schema.accounts(account_id),483    loan_id INT REFERENCES finance_schema.loans(loan_id),484    payment_amount DECIMAL(12,2),485    payment_date DATE,486    payment_type VARCHAR(50)487);488 489CREATE TABLE IF NOT EXISTS finance_schema.investment_accounts (490    investment_account_id SERIAL PRIMARY KEY,491    customer_id INT REFERENCES finance_schema.customers(customer_id),492    account_type VARCHAR(50),493    total_value DECIMAL(14,2),494    created_at TIMESTAMP495);496 497CREATE TABLE IF NOT EXISTS finance_schema.account_balances_history (498    record_id SERIAL PRIMARY KEY,499    account_id INT REFERENCES finance_schema.accounts(account_id),500    balance DECIMAL(12,2),501    recorded_at TIMESTAMP502);503 504CREATE TABLE IF NOT EXISTS finance_schema.fraud_alerts (505    alert_id SERIAL PRIMARY KEY,506    transaction_id INT REFERENCES finance_schema.transactions(transaction_id),507    alert_type VARCHAR(50),508    alert_status VARCHAR(50),509    created_at TIMESTAMP510);511 512CREATE TABLE IF NOT EXISTS finance_schema.finance_business_rules (513    rule_id SERIAL PRIMARY KEY,514    concept_name VARCHAR(150) NOT NULL,515    description TEXT NOT NULL,516    insight TEXT,517    keywords TEXT[],518    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP519);520 521-- Finance comments522COMMENT ON SCHEMA finance_schema IS 'Finance domain: accounts, transactions, loans';523COMMENT ON TABLE finance_schema.accounts IS 'Stores bank account information.';524COMMENT ON COLUMN finance_schema.accounts.account_id IS 'Unique identifier for each account.';525COMMENT ON COLUMN finance_schema.accounts.customer_name IS 'Legacy customer name text (kept for backward compatibility).';526COMMENT ON COLUMN finance_schema.accounts.customer_id IS 'Foreign key to finance customers master.';527COMMENT ON COLUMN finance_schema.accounts.account_type IS 'Account type (savings, checking).';528COMMENT ON COLUMN finance_schema.accounts.branch_city IS 'Legacy branch city text (kept for backward compatibility).';529COMMENT ON COLUMN finance_schema.accounts.branch_id IS 'Foreign key to branch master.';530COMMENT ON COLUMN finance_schema.accounts.opening_date IS 'Account opening date.';531COMMENT ON COLUMN finance_schema.accounts.current_balance IS 'Current balance.';532 533COMMENT ON TABLE finance_schema.transactions IS 'Records financial transactions.';534COMMENT ON COLUMN finance_schema.transactions.transaction_id IS 'Unique identifier for each transaction.';535COMMENT ON COLUMN finance_schema.transactions.account_id IS 'Foreign key to accounts.';536COMMENT ON COLUMN finance_schema.transactions.transaction_date IS 'Transaction date.';537COMMENT ON COLUMN finance_schema.transactions.transaction_type IS 'Transaction type (debit, credit).';538COMMENT ON COLUMN finance_schema.transactions.amount IS 'Transaction amount.';539COMMENT ON COLUMN finance_schema.transactions.description IS 'Transaction description.';540 541COMMENT ON TABLE finance_schema.loans IS 'Stores loan information.';542COMMENT ON COLUMN finance_schema.loans.loan_id IS 'Unique identifier for each loan.';543COMMENT ON COLUMN finance_schema.loans.account_id IS 'Foreign key to accounts.';544COMMENT ON COLUMN finance_schema.loans.loan_type IS 'Loan type (home, auto, personal).';545COMMENT ON COLUMN finance_schema.loans.loan_amount IS 'Loan principal amount.';546COMMENT ON COLUMN finance_schema.loans.interest_rate IS 'Annual interest rate.';547COMMENT ON COLUMN finance_schema.loans.loan_start_date IS 'Loan start date.';548COMMENT ON COLUMN finance_schema.loans.loan_end_date IS 'Loan end date.';549 550COMMENT ON TABLE finance_schema.customers IS 'Customer master data storing personal and contact details';551COMMENT ON COLUMN finance_schema.customers.customer_id IS 'Unique identifier for each customer';552COMMENT ON COLUMN finance_schema.customers.first_name IS 'Customer first name';553COMMENT ON COLUMN finance_schema.customers.last_name IS 'Customer last name';554COMMENT ON COLUMN finance_schema.customers.email IS 'Customer email address';555COMMENT ON COLUMN finance_schema.customers.phone IS 'Customer phone number';556COMMENT ON COLUMN finance_schema.customers.city IS 'City where customer resides';557COMMENT ON COLUMN finance_schema.customers.state IS 'State where customer resides';558COMMENT ON COLUMN finance_schema.customers.created_at IS 'Timestamp when customer profile was created';559 560COMMENT ON TABLE finance_schema.branches IS 'Bank branch information';561COMMENT ON COLUMN finance_schema.branches.branch_id IS 'Unique identifier for branch';562COMMENT ON COLUMN finance_schema.branches.branch_name IS 'Name of the branch';563COMMENT ON COLUMN finance_schema.branches.city IS 'City where branch is located';564COMMENT ON COLUMN finance_schema.branches.state IS 'State where branch is located';565 566COMMENT ON TABLE finance_schema.credit_cards IS 'Credit card accounts issued to customers';567COMMENT ON COLUMN finance_schema.credit_cards.card_id IS 'Unique credit card identifier';568COMMENT ON COLUMN finance_schema.credit_cards.customer_id IS 'Customer owning the card';569COMMENT ON COLUMN finance_schema.credit_cards.card_type IS 'Type of card (e.g., Visa, Mastercard)';570COMMENT ON COLUMN finance_schema.credit_cards.credit_limit IS 'Maximum allowed spending limit';571COMMENT ON COLUMN finance_schema.credit_cards.current_balance IS 'Outstanding balance on the card';572COMMENT ON COLUMN finance_schema.credit_cards.issue_date IS 'Date when card was issued';573COMMENT ON COLUMN finance_schema.credit_cards.expiry_date IS 'Card expiration date';574 575COMMENT ON TABLE finance_schema.payments IS 'Payments made towards loans or accounts';576COMMENT ON COLUMN finance_schema.payments.payment_id IS 'Unique payment identifier';577COMMENT ON COLUMN finance_schema.payments.account_id IS 'Account from which payment was made';578COMMENT ON COLUMN finance_schema.payments.loan_id IS 'Loan associated with payment (if applicable)';579COMMENT ON COLUMN finance_schema.payments.payment_amount IS 'Amount paid';580COMMENT ON COLUMN finance_schema.payments.payment_date IS 'Date of payment';581COMMENT ON COLUMN finance_schema.payments.payment_type IS 'Type of payment (EMI, credit card, etc.)';582 583COMMENT ON TABLE finance_schema.investment_accounts IS 'Investment portfolios held by customers';584COMMENT ON COLUMN finance_schema.investment_accounts.investment_account_id IS 'Unique investment account identifier';585COMMENT ON COLUMN finance_schema.investment_accounts.customer_id IS 'Customer owning investment account';586COMMENT ON COLUMN finance_schema.investment_accounts.account_type IS 'Type of investment (stocks, mutual funds)';587COMMENT ON COLUMN finance_schema.investment_accounts.total_value IS 'Total portfolio value';588COMMENT ON COLUMN finance_schema.investment_accounts.created_at IS 'Account creation timestamp';589 590COMMENT ON TABLE finance_schema.account_balances_history IS 'Historical record of account balances';591COMMENT ON COLUMN finance_schema.account_balances_history.record_id IS 'Unique record identifier';592COMMENT ON COLUMN finance_schema.account_balances_history.account_id IS 'Account being tracked';593COMMENT ON COLUMN finance_schema.account_balances_history.balance IS 'Balance at given timestamp';594COMMENT ON COLUMN finance_schema.account_balances_history.recorded_at IS 'Timestamp of recorded balance';595 596COMMENT ON TABLE finance_schema.fraud_alerts IS 'Flags suspicious or fraudulent transactions';597COMMENT ON COLUMN finance_schema.fraud_alerts.alert_id IS 'Unique alert identifier';598COMMENT ON COLUMN finance_schema.fraud_alerts.transaction_id IS 'Transaction flagged for fraud';599COMMENT ON COLUMN finance_schema.fraud_alerts.alert_type IS 'Type of fraud detected';600COMMENT ON COLUMN finance_schema.fraud_alerts.alert_status IS 'Status of investigation';601COMMENT ON COLUMN finance_schema.fraud_alerts.created_at IS 'Timestamp when alert was generated';602 603ALTER TABLE finance_schema.accounts604    ADD COLUMN IF NOT EXISTS customer_id INT;605ALTER TABLE finance_schema.accounts606    ADD COLUMN IF NOT EXISTS branch_id INT;607 608DO $$609BEGIN610    IF NOT EXISTS (611        SELECT 1612        FROM pg_constraint613        WHERE conname = 'fk_accounts_customer_id'614          AND connamespace = 'finance_schema'::regnamespace615    ) THEN616        ALTER TABLE finance_schema.accounts617            ADD CONSTRAINT fk_accounts_customer_id618            FOREIGN KEY (customer_id)619            REFERENCES finance_schema.customers(customer_id);620    END IF;621END $$;622 623DO $$624BEGIN625    IF NOT EXISTS (626        SELECT 1627        FROM pg_constraint628        WHERE conname = 'fk_accounts_branch_id'629          AND connamespace = 'finance_schema'::regnamespace630    ) THEN631        ALTER TABLE finance_schema.accounts632            ADD CONSTRAINT fk_accounts_branch_id633            FOREIGN KEY (branch_id)634            REFERENCES finance_schema.branches(branch_id);635    END IF;636END $$;637 638-- ============================================================================639-- APP SCHEMA (sessions, agent outputs)640-- ============================================================================641CREATE SCHEMA IF NOT EXISTS app_schema;642 643CREATE TABLE IF NOT EXISTS app_schema.sessions (644    session_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),645    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,646    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP647);648 649CREATE TABLE IF NOT EXISTS app_schema.intent_agent_output (650    id SERIAL PRIMARY KEY,651    session_id UUID NOT NULL REFERENCES app_schema.sessions(session_id) ON DELETE CASCADE,652    use_case VARCHAR(64) NOT NULL,653    user_input TEXT NOT NULL,654    rephrased_question TEXT,655    keywords TEXT[],656    business_insights TEXT[],657    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP658);659 660CREATE INDEX IF NOT EXISTS idx_intent_agent_output_session_id ON app_schema.intent_agent_output(session_id);661CREATE INDEX IF NOT EXISTS idx_intent_agent_output_use_case ON app_schema.intent_agent_output(use_case);662 663CREATE TABLE IF NOT EXISTS app_schema.table_agent_output (664    id SERIAL PRIMARY KEY,665    intent_output_id INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,666    selected_tables TEXT[] NOT NULL DEFAULT '{}',667    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,668    UNIQUE (intent_output_id)669);670 671CREATE TABLE IF NOT EXISTS app_schema.column_agent_output (672    id SERIAL PRIMARY KEY,673    table_agent_output_id INT NOT NULL REFERENCES app_schema.table_agent_output(id) ON DELETE CASCADE,674    selected_columns JSONB NOT NULL DEFAULT '{}'::jsonb,675    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,676    UNIQUE (table_agent_output_id)677);678 679CREATE TABLE IF NOT EXISTS app_schema.few_shot_agent_output (680    id SERIAL PRIMARY KEY,681    intent_output_id INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,682    few_shot_examples JSONB NOT NULL DEFAULT '[]'::jsonb,683    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,684    UNIQUE (intent_output_id)685);686 687CREATE TABLE IF NOT EXISTS app_schema.gen_sql_agent_output (688    id SERIAL PRIMARY KEY,689    intent_output_id INT NOT NULL REFERENCES app_schema.intent_agent_output(id) ON DELETE CASCADE,690    generated_sql TEXT NOT NULL DEFAULT '',691    reasoning_summary TEXT,692    validation_passed BOOLEAN NOT NULL DEFAULT FALSE,693    validation_error_codes TEXT NOT NULL DEFAULT '',694    validation_error_message TEXT NOT NULL DEFAULT '',695    blocked_keywords TEXT NOT NULL DEFAULT '',696    is_single_statement BOOLEAN NOT NULL DEFAULT FALSE,697    is_select_only BOOLEAN NOT NULL DEFAULT FALSE,698    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,699    UNIQUE (intent_output_id)700);701 702COMMENT ON SCHEMA app_schema IS 'Application/session data for Text2SQL';703COMMENT ON TABLE app_schema.sessions IS 'One row per chat session';704COMMENT ON TABLE app_schema.intent_agent_output IS 'Intent Agent output per query';705COMMENT ON TABLE app_schema.table_agent_output IS 'Table Agent: selected tables';706COMMENT ON TABLE app_schema.column_agent_output IS 'Column Agent: selected columns';707COMMENT ON TABLE app_schema.few_shot_agent_output IS 'Few-Shot Agent: selected few_shot_examples';708COMMENT ON TABLE app_schema.gen_sql_agent_output IS 'Gen-SQL Agent: generated SQL and validation';709 710-- ============================================================================711-- DOMAIN FK METADATA (table_relationships) — required for Table / Column / Gen-SQL agents712-- Same DDL as scripts/create_domain_schema_table_relationships.sql713-- Populate rows with: python scripts/extract_and_load_relationships.py714-- ============================================================================715 716CREATE TABLE IF NOT EXISTS healthcare_schema.table_relationships (717    id                  SERIAL PRIMARY KEY,718    source_table        TEXT NOT NULL,719    source_column       TEXT NOT NULL,720    target_schema       TEXT NOT NULL,721    target_table        TEXT NOT NULL,722    target_column       TEXT NOT NULL,723    relationship_text   TEXT NOT NULL,724    constraint_name     VARCHAR(256),725    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,726    CONSTRAINT uq_healthcare_table_relationships_edge727        UNIQUE (source_table, source_column, target_schema, target_table, target_column)728);729 730CREATE INDEX IF NOT EXISTS idx_healthcare_table_relationships_source731    ON healthcare_schema.table_relationships (source_table);732CREATE INDEX IF NOT EXISTS idx_healthcare_table_relationships_source_col733    ON healthcare_schema.table_relationships (source_table, source_column);734 735COMMENT ON TABLE healthcare_schema.table_relationships IS 'Foreign keys with referencing tables in healthcare_schema; target_schema for referenced side';736COMMENT ON COLUMN healthcare_schema.table_relationships.target_schema IS 'Schema of the referenced (target) table';737COMMENT ON COLUMN healthcare_schema.table_relationships.relationship_text IS 'Canonical line for LLM context';738 739CREATE TABLE IF NOT EXISTS retail_schema.table_relationships (740    id                  SERIAL PRIMARY KEY,741    source_table        TEXT NOT NULL,742    source_column       TEXT NOT NULL,743    target_schema       TEXT NOT NULL,744    target_table        TEXT NOT NULL,745    target_column       TEXT NOT NULL,746    relationship_text   TEXT NOT NULL,747    constraint_name     VARCHAR(256),748    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,749    CONSTRAINT uq_retail_table_relationships_edge750        UNIQUE (source_table, source_column, target_schema, target_table, target_column)751);752 753CREATE INDEX IF NOT EXISTS idx_retail_table_relationships_source754    ON retail_schema.table_relationships (source_table);755CREATE INDEX IF NOT EXISTS idx_retail_table_relationships_source_col756    ON retail_schema.table_relationships (source_table, source_column);757 758COMMENT ON TABLE retail_schema.table_relationships IS 'Foreign keys with referencing tables in retail_schema; target_schema for referenced side';759COMMENT ON COLUMN retail_schema.table_relationships.target_schema IS 'Schema of the referenced (target) table';760COMMENT ON COLUMN retail_schema.table_relationships.relationship_text IS 'Canonical line for LLM context';761 762CREATE TABLE IF NOT EXISTS finance_schema.table_relationships (763    id                  SERIAL PRIMARY KEY,764    source_table        TEXT NOT NULL,765    source_column       TEXT NOT NULL,766    target_schema       TEXT NOT NULL,767    target_table        TEXT NOT NULL,768    target_column       TEXT NOT NULL,769    relationship_text   TEXT NOT NULL,770    constraint_name     VARCHAR(256),771    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,772    CONSTRAINT uq_finance_table_relationships_edge773        UNIQUE (source_table, source_column, target_schema, target_table, target_column)774);775 776CREATE INDEX IF NOT EXISTS idx_finance_table_relationships_source777    ON finance_schema.table_relationships (source_table);778CREATE INDEX IF NOT EXISTS idx_finance_table_relationships_source_col779    ON finance_schema.table_relationships (source_table, source_column);780 781COMMENT ON TABLE finance_schema.table_relationships IS 'Foreign keys with referencing tables in finance_schema; target_schema for referenced side';782COMMENT ON COLUMN finance_schema.table_relationships.target_schema IS 'Schema of the referenced (target) table';783COMMENT ON COLUMN finance_schema.table_relationships.relationship_text IS 'Canonical line for LLM context';784 785-- ============================================================================786-- DONE - Schemas, business tables, app_schema, and FK metadata tables created.787-- Next: load FK rows — python scripts/extract_and_load_relationships.py (requires .env DB access)788-- ============================================================================789