prazy1208/text2sql

1-- Domain schemas for Text2SQL: healthcare, retail, finance2-- Run this BEFORE generate_data.py3 4-- ============================================================================5-- HEALTHCARE SCHEMA6-- ============================================================================7CREATE SCHEMA IF NOT EXISTS healthcare_schema;8 9CREATE TABLE IF NOT EXISTS healthcare_schema.patients (10    patient_id SERIAL PRIMARY KEY,11    first_name VARCHAR(100),12    last_name VARCHAR(100),13    date_of_birth DATE,14    gender VARCHAR(20),15    city VARCHAR(100),16    state VARCHAR(10),17    insurance_type VARCHAR(50),18    registration_date DATE19);20COMMENT ON TABLE healthcare_schema.patients IS 'Stores patient demographic information including name, date of birth, location, and insurance details.';21COMMENT ON COLUMN healthcare_schema.patients.patient_id IS 'Unique system-generated identifier for each patient.';22COMMENT ON COLUMN healthcare_schema.patients.first_name IS 'First name of the patient.';23COMMENT ON COLUMN healthcare_schema.patients.last_name IS 'Last name of the patient.';24COMMENT ON COLUMN healthcare_schema.patients.date_of_birth IS 'Date of birth of the patient.';25COMMENT ON COLUMN healthcare_schema.patients.gender IS 'Gender of the patient (Male, Female, Other).';26COMMENT ON COLUMN healthcare_schema.patients.city IS 'City where the patient resides.';27COMMENT ON COLUMN healthcare_schema.patients.state IS 'State abbreviation where the patient resides.';28COMMENT ON COLUMN healthcare_schema.patients.insurance_type IS 'Type of health insurance (Medicare, Medicaid, Private, Employer, Uninsured).';29COMMENT ON COLUMN healthcare_schema.patients.registration_date IS 'Date when the patient was registered in the system.';30 31CREATE TABLE IF NOT EXISTS healthcare_schema.visits (32    visit_id SERIAL PRIMARY KEY,33    patient_id INTEGER REFERENCES healthcare_schema.patients(patient_id),34    admission_date DATE,35    discharge_date DATE,36    department VARCHAR(100),37    department_id INT,38    visit_type VARCHAR(50),39    total_cost NUMERIC(14,2)40);41ALTER TABLE healthcare_schema.visits42    ADD COLUMN IF NOT EXISTS department_id INT;43 44COMMENT ON TABLE healthcare_schema.visits IS 'Records patient visits including admission, discharge, department, and cost information.';45COMMENT ON COLUMN healthcare_schema.visits.visit_id IS 'Unique system-generated identifier for each visit.';46COMMENT ON COLUMN healthcare_schema.visits.patient_id IS 'Foreign key referencing the patient who made the visit.';47COMMENT ON COLUMN healthcare_schema.visits.admission_date IS 'Date when the patient was admitted.';48COMMENT ON COLUMN healthcare_schema.visits.discharge_date IS 'Date when the patient was discharged.';49COMMENT ON COLUMN healthcare_schema.visits.department IS 'Legacy text department name (kept for backward compatibility).';50COMMENT ON COLUMN healthcare_schema.visits.department_id IS 'Foreign key to departments master table.';51COMMENT ON COLUMN healthcare_schema.visits.visit_type IS 'Type of visit (Inpatient, Outpatient, Emergency, Follow-up).';52COMMENT ON COLUMN healthcare_schema.visits.total_cost IS 'Total cost of the visit.';53 54CREATE TABLE IF NOT EXISTS healthcare_schema.diagnoses (55    diagnosis_id SERIAL PRIMARY KEY,56    visit_id INTEGER REFERENCES healthcare_schema.visits(visit_id),57    diagnosis_code VARCHAR(50),58    diagnosis_description TEXT,59    severity_level VARCHAR(20)60);61COMMENT ON TABLE healthcare_schema.diagnoses IS 'Stores diagnosis information linked to patient visits.';62COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_id IS 'Unique system-generated identifier for each diagnosis.';63COMMENT ON COLUMN healthcare_schema.diagnoses.visit_id IS 'Foreign key referencing the visit associated with this diagnosis.';64COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_code IS 'ICD-10 or similar diagnosis code.';65COMMENT ON COLUMN healthcare_schema.diagnoses.diagnosis_description IS 'Human-readable description of the diagnosis.';66COMMENT ON COLUMN healthcare_schema.diagnoses.severity_level IS 'Severity level (Low, Medium, High, Critical).';67 68CREATE TABLE IF NOT EXISTS healthcare_schema.departments (69    department_id SERIAL PRIMARY KEY,70    department_name VARCHAR(100),71    location VARCHAR(100)72);73COMMENT ON TABLE healthcare_schema.departments IS 'Hospital departments such as Cardiology, Emergency, etc.';74COMMENT ON COLUMN healthcare_schema.departments.department_id IS 'Unique identifier for each department';75COMMENT ON COLUMN healthcare_schema.departments.department_name IS 'Name of the department';76COMMENT ON COLUMN healthcare_schema.departments.location IS 'Physical location within hospital';77 78CREATE TABLE IF NOT EXISTS healthcare_schema.providers (79    provider_id SERIAL PRIMARY KEY,80    first_name VARCHAR(50),81    last_name VARCHAR(50),82    specialty VARCHAR(100),83    department_id INT REFERENCES healthcare_schema.departments(department_id),84    years_of_experience INT85);86COMMENT ON TABLE healthcare_schema.providers IS 'Healthcare professionals including doctors and nurses';87COMMENT ON COLUMN healthcare_schema.providers.provider_id IS 'Unique identifier for provider';88COMMENT ON COLUMN healthcare_schema.providers.first_name IS 'Provider first name';89COMMENT ON COLUMN healthcare_schema.providers.last_name IS 'Provider last name';90COMMENT ON COLUMN healthcare_schema.providers.specialty IS 'Medical specialization';91COMMENT ON COLUMN healthcare_schema.providers.department_id IS 'Department provider belongs to';92COMMENT ON COLUMN healthcare_schema.providers.years_of_experience IS 'Years of professional experience';93 94CREATE TABLE IF NOT EXISTS healthcare_schema.procedures (95    procedure_id SERIAL PRIMARY KEY,96    visit_id INT REFERENCES healthcare_schema.visits(visit_id),97    provider_id INT REFERENCES healthcare_schema.providers(provider_id),98    procedure_name VARCHAR(100),99    procedure_date DATE,100    cost DECIMAL(10,2)101);102COMMENT ON TABLE healthcare_schema.procedures IS 'Medical procedures performed during patient visits';103COMMENT ON COLUMN healthcare_schema.procedures.procedure_id IS 'Unique procedure identifier';104COMMENT ON COLUMN healthcare_schema.procedures.visit_id IS 'Associated visit';105COMMENT ON COLUMN healthcare_schema.procedures.provider_id IS 'Provider performing procedure';106COMMENT ON COLUMN healthcare_schema.procedures.procedure_name IS 'Name of procedure';107COMMENT ON COLUMN healthcare_schema.procedures.procedure_date IS 'Date performed';108COMMENT ON COLUMN healthcare_schema.procedures.cost IS 'Cost of procedure';109 110CREATE TABLE IF NOT EXISTS healthcare_schema.medications (111    medication_id SERIAL PRIMARY KEY,112    medication_name VARCHAR(100),113    manufacturer VARCHAR(100),114    unit_cost DECIMAL(10,2)115);116COMMENT ON TABLE healthcare_schema.medications IS 'Master list of medications';117COMMENT ON COLUMN healthcare_schema.medications.medication_id IS 'Unique medication identifier';118COMMENT ON COLUMN healthcare_schema.medications.medication_name IS 'Name of medication';119COMMENT ON COLUMN healthcare_schema.medications.manufacturer IS 'Manufacturer name';120COMMENT ON COLUMN healthcare_schema.medications.unit_cost IS 'Cost per unit';121 122CREATE TABLE IF NOT EXISTS healthcare_schema.prescriptions (123    prescription_id SERIAL PRIMARY KEY,124    patient_id INT REFERENCES healthcare_schema.patients(patient_id),125    medication_id INT REFERENCES healthcare_schema.medications(medication_id),126    provider_id INT REFERENCES healthcare_schema.providers(provider_id),127    dosage VARCHAR(50),128    start_date DATE,129    end_date DATE130);131COMMENT ON TABLE healthcare_schema.prescriptions IS 'Prescribed medications for patients';132COMMENT ON COLUMN healthcare_schema.prescriptions.prescription_id IS 'Unique prescription identifier';133COMMENT ON COLUMN healthcare_schema.prescriptions.patient_id IS 'Patient receiving medication';134COMMENT ON COLUMN healthcare_schema.prescriptions.medication_id IS 'Medication prescribed';135COMMENT ON COLUMN healthcare_schema.prescriptions.provider_id IS 'Provider prescribing medication';136COMMENT ON COLUMN healthcare_schema.prescriptions.dosage IS 'Dosage instructions';137COMMENT ON COLUMN healthcare_schema.prescriptions.start_date IS 'Prescription start date';138COMMENT ON COLUMN healthcare_schema.prescriptions.end_date IS 'Prescription end date';139 140CREATE TABLE IF NOT EXISTS healthcare_schema.billing (141    billing_id SERIAL PRIMARY KEY,142    visit_id INT REFERENCES healthcare_schema.visits(visit_id),143    total_amount DECIMAL(12,2),144    insurance_covered_amount DECIMAL(12,2),145    out_of_pocket_amount DECIMAL(12,2),146    billing_date DATE147);148COMMENT ON TABLE healthcare_schema.billing IS 'Billing details for each visit';149COMMENT ON COLUMN healthcare_schema.billing.billing_id IS 'Unique billing identifier';150COMMENT ON COLUMN healthcare_schema.billing.visit_id IS 'Visit being billed';151COMMENT ON COLUMN healthcare_schema.billing.total_amount IS 'Total billed amount';152COMMENT ON COLUMN healthcare_schema.billing.insurance_covered_amount IS 'Amount covered by insurance';153COMMENT ON COLUMN healthcare_schema.billing.out_of_pocket_amount IS 'Amount paid by patient';154COMMENT ON COLUMN healthcare_schema.billing.billing_date IS 'Billing date';155 156CREATE TABLE IF NOT EXISTS healthcare_schema.insurance_claims (157    claim_id SERIAL PRIMARY KEY,158    visit_id INT REFERENCES healthcare_schema.visits(visit_id),159    patient_id INT REFERENCES healthcare_schema.patients(patient_id),160    claim_status VARCHAR(50),161    claim_amount DECIMAL(12,2),162    approved_amount DECIMAL(12,2),163    claim_date DATE164);165COMMENT ON TABLE healthcare_schema.insurance_claims IS 'Insurance claim processing details';166COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_id IS 'Unique claim identifier';167COMMENT ON COLUMN healthcare_schema.insurance_claims.visit_id IS 'Associated visit';168COMMENT ON COLUMN healthcare_schema.insurance_claims.patient_id IS 'Patient filing claim';169COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_status IS 'Status (Pending, Approved, Rejected)';170COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_amount IS 'Requested claim amount';171COMMENT ON COLUMN healthcare_schema.insurance_claims.approved_amount IS 'Approved amount';172COMMENT ON COLUMN healthcare_schema.insurance_claims.claim_date IS 'Date of claim submission';173 174ALTER TABLE healthcare_schema.visits175    ADD COLUMN IF NOT EXISTS department_id INT;176 177DO $$178BEGIN179    IF NOT EXISTS (180        SELECT 1181        FROM pg_constraint182        WHERE conname = 'fk_visits_department_id'183          AND connamespace = 'healthcare_schema'::regnamespace184    ) THEN185        ALTER TABLE healthcare_schema.visits186            ADD CONSTRAINT fk_visits_department_id187            FOREIGN KEY (department_id)188            REFERENCES healthcare_schema.departments(department_id);189    END IF;190END $$;191 192-- Business rules table for healthcare193CREATE TABLE IF NOT EXISTS healthcare_schema.healthcare_business_rules (194    rule_id SERIAL PRIMARY KEY,195    concept_name VARCHAR(150),196    description TEXT,197    insight TEXT,198    keywords TEXT[],199    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP200);201COMMENT ON TABLE healthcare_schema.healthcare_business_rules IS 'Stores domain-level healthcare business knowledge used by the Intent Agent.';202 203-- ============================================================================204-- RETAIL SCHEMA205-- ============================================================================206CREATE SCHEMA IF NOT EXISTS retail_schema;207 208CREATE TABLE IF NOT EXISTS retail_schema.customers (209    customer_id SERIAL PRIMARY KEY,210    first_name VARCHAR(100),211    last_name VARCHAR(100),212    email VARCHAR(200),213    city VARCHAR(100),214    state VARCHAR(10),215    signup_date DATE216);217COMMENT ON TABLE retail_schema.customers IS 'Stores customer information including contact details and signup date.';218COMMENT ON COLUMN retail_schema.customers.customer_id IS 'Unique system-generated identifier for each customer.';219COMMENT ON COLUMN retail_schema.customers.first_name IS 'First name of the customer.';220COMMENT ON COLUMN retail_schema.customers.last_name IS 'Last name of the customer.';221COMMENT ON COLUMN retail_schema.customers.email IS 'Email address of the customer.';222COMMENT ON COLUMN retail_schema.customers.city IS 'City where the customer resides.';223COMMENT ON COLUMN retail_schema.customers.state IS 'State abbreviation where the customer resides.';224COMMENT ON COLUMN retail_schema.customers.signup_date IS 'Date when the customer signed up.';225 226CREATE TABLE IF NOT EXISTS retail_schema.products (227    product_id SERIAL PRIMARY KEY,228    product_name VARCHAR(200),229    category VARCHAR(100),230    category_id INT,231    brand VARCHAR(100),232    price NUMERIC(10,2),233    launch_date DATE234);235ALTER TABLE retail_schema.products236    ADD COLUMN IF NOT EXISTS category_id INT;237 238COMMENT ON TABLE retail_schema.products IS 'Stores product catalog information including category, brand, and pricing.';239COMMENT ON COLUMN retail_schema.products.product_id IS 'Unique system-generated identifier for each product.';240COMMENT ON COLUMN retail_schema.products.product_name IS 'Name of the product.';241COMMENT ON COLUMN retail_schema.products.category IS 'Legacy text category (kept for backward compatibility).';242COMMENT ON COLUMN retail_schema.products.category_id IS 'Foreign key to categories hierarchy.';243COMMENT ON COLUMN retail_schema.products.brand IS 'Brand name of the product.';244COMMENT ON COLUMN retail_schema.products.price IS 'Unit price of the product.';245COMMENT ON COLUMN retail_schema.products.launch_date IS 'Date when the product was launched.';246 247CREATE TABLE IF NOT EXISTS retail_schema.orders (248    order_id SERIAL PRIMARY KEY,249    customer_id INTEGER REFERENCES retail_schema.customers(customer_id),250    product_id INTEGER REFERENCES retail_schema.products(product_id),251    order_date DATE,252    quantity INTEGER,253    total_amount NUMERIC(14,2)254);255COMMENT ON TABLE retail_schema.orders IS 'Records customer orders linking customers to products with quantities and amounts.';256COMMENT ON COLUMN retail_schema.orders.order_id IS 'Unique system-generated identifier for each order.';257COMMENT ON COLUMN retail_schema.orders.customer_id IS 'Foreign key referencing the customer who placed the order.';258COMMENT ON COLUMN retail_schema.orders.product_id IS 'Foreign key referencing the ordered product.';259COMMENT ON COLUMN retail_schema.orders.order_date IS 'Date when the order was placed.';260COMMENT ON COLUMN retail_schema.orders.quantity IS 'Number of units ordered.';261COMMENT ON COLUMN retail_schema.orders.total_amount IS 'Total amount for the order.';262 263CREATE TABLE IF NOT EXISTS retail_schema.categories (264    category_id SERIAL PRIMARY KEY,265    category_name VARCHAR(100),266    parent_category_id INT REFERENCES retail_schema.categories(category_id)267);268COMMENT ON TABLE retail_schema.categories IS 'Product category hierarchy';269COMMENT ON COLUMN retail_schema.categories.category_id IS 'Unique category identifier';270COMMENT ON COLUMN retail_schema.categories.category_name IS 'Category name';271COMMENT ON COLUMN retail_schema.categories.parent_category_id IS 'Parent category for hierarchical grouping';272 273CREATE TABLE IF NOT EXISTS retail_schema.suppliers (274    supplier_id SERIAL PRIMARY KEY,275    supplier_name VARCHAR(100),276    contact_email VARCHAR(100),277    phone VARCHAR(20),278    city VARCHAR(50),279    state VARCHAR(50)280);281COMMENT ON TABLE retail_schema.suppliers IS 'Suppliers providing products';282COMMENT ON COLUMN retail_schema.suppliers.supplier_id IS 'Unique supplier identifier';283COMMENT ON COLUMN retail_schema.suppliers.supplier_name IS 'Supplier name';284COMMENT ON COLUMN retail_schema.suppliers.contact_email IS 'Supplier contact email';285COMMENT ON COLUMN retail_schema.suppliers.phone IS 'Supplier phone number';286COMMENT ON COLUMN retail_schema.suppliers.city IS 'Supplier city';287COMMENT ON COLUMN retail_schema.suppliers.state IS 'Supplier state';288 289CREATE TABLE IF NOT EXISTS retail_schema.stores (290    store_id SERIAL PRIMARY KEY,291    store_name VARCHAR(100),292    city VARCHAR(50),293    state VARCHAR(50),294    store_type VARCHAR(50)295);296COMMENT ON TABLE retail_schema.stores IS 'Retail store locations';297COMMENT ON COLUMN retail_schema.stores.store_id IS 'Unique store identifier';298COMMENT ON COLUMN retail_schema.stores.store_name IS 'Store name';299COMMENT ON COLUMN retail_schema.stores.city IS 'Store city';300COMMENT ON COLUMN retail_schema.stores.state IS 'Store state';301COMMENT ON COLUMN retail_schema.stores.store_type IS 'Type (online/physical)';302 303CREATE TABLE IF NOT EXISTS retail_schema.inventory (304    inventory_id SERIAL PRIMARY KEY,305    product_id INT REFERENCES retail_schema.products(product_id),306    store_id INT REFERENCES retail_schema.stores(store_id),307    stock_quantity INT,308    last_updated TIMESTAMP309);310COMMENT ON TABLE retail_schema.inventory IS 'Inventory levels per product and store';311COMMENT ON COLUMN retail_schema.inventory.inventory_id IS 'Unique inventory record';312COMMENT ON COLUMN retail_schema.inventory.product_id IS 'Product being tracked';313COMMENT ON COLUMN retail_schema.inventory.store_id IS 'Store holding inventory';314COMMENT ON COLUMN retail_schema.inventory.stock_quantity IS 'Available stock quantity';315COMMENT ON COLUMN retail_schema.inventory.last_updated IS 'Last update timestamp';316 317CREATE TABLE IF NOT EXISTS retail_schema.shipments (318    shipment_id SERIAL PRIMARY KEY,319    order_id INT REFERENCES retail_schema.orders(order_id),320    shipment_date DATE,321    delivery_date DATE,322    shipment_status VARCHAR(50)323);324COMMENT ON TABLE retail_schema.shipments IS 'Tracks shipment and delivery of orders';325COMMENT ON COLUMN retail_schema.shipments.shipment_id IS 'Unique shipment identifier';326COMMENT ON COLUMN retail_schema.shipments.order_id IS 'Order being shipped';327COMMENT ON COLUMN retail_schema.shipments.shipment_date IS 'Date shipped';328COMMENT ON COLUMN retail_schema.shipments.delivery_date IS 'Date delivered';329COMMENT ON COLUMN retail_schema.shipments.shipment_status IS 'Delivery status';330 331CREATE TABLE IF NOT EXISTS retail_schema.reviews (332    review_id SERIAL PRIMARY KEY,333    product_id INT REFERENCES retail_schema.products(product_id),334    customer_id INT REFERENCES retail_schema.customers(customer_id),335    rating INT,336    review_text TEXT,337    review_date DATE338);339COMMENT ON TABLE retail_schema.reviews IS 'Customer reviews and ratings for products';340COMMENT ON COLUMN retail_schema.reviews.review_id IS 'Unique review identifier';341COMMENT ON COLUMN retail_schema.reviews.product_id IS 'Reviewed product';342COMMENT ON COLUMN retail_schema.reviews.customer_id IS 'Customer writing review';343COMMENT ON COLUMN retail_schema.reviews.rating IS 'Rating score (1-5)';344COMMENT ON COLUMN retail_schema.reviews.review_text IS 'Written feedback';345COMMENT ON COLUMN retail_schema.reviews.review_date IS 'Date of review';346 347CREATE TABLE IF NOT EXISTS retail_schema.promotions (348    promotion_id SERIAL PRIMARY KEY,349    promotion_name VARCHAR(100),350    discount_percentage DECIMAL(5,2),351    start_date DATE,352    end_date DATE353);354COMMENT ON TABLE retail_schema.promotions IS 'Marketing promotions and discounts';355COMMENT ON COLUMN retail_schema.promotions.promotion_id IS 'Unique promotion identifier';356COMMENT ON COLUMN retail_schema.promotions.promotion_name IS 'Promotion campaign name';357COMMENT ON COLUMN retail_schema.promotions.discount_percentage IS 'Discount percentage applied';358COMMENT ON COLUMN retail_schema.promotions.start_date IS 'Promotion start date';359COMMENT ON COLUMN retail_schema.promotions.end_date IS 'Promotion end date';360 361ALTER TABLE retail_schema.products362    ADD COLUMN IF NOT EXISTS category_id INT;363 364DO $$365BEGIN366    IF NOT EXISTS (367        SELECT 1368        FROM pg_constraint369        WHERE conname = 'fk_products_category_id'370          AND connamespace = 'retail_schema'::regnamespace371    ) THEN372        ALTER TABLE retail_schema.products373            ADD CONSTRAINT fk_products_category_id374            FOREIGN KEY (category_id)375            REFERENCES retail_schema.categories(category_id);376    END IF;377END $$;378 379-- Business rules table for retail380CREATE TABLE IF NOT EXISTS retail_schema.retail_business_rules (381    rule_id SERIAL PRIMARY KEY,382    concept_name VARCHAR(150),383    description TEXT,384    insight TEXT,385    keywords TEXT[],386    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP387);388COMMENT ON TABLE retail_schema.retail_business_rules IS 'Stores domain-level retail business knowledge used by the Intent Agent.';389 390-- ============================================================================391-- FINANCE SCHEMA392-- ============================================================================393CREATE SCHEMA IF NOT EXISTS finance_schema;394 395CREATE TABLE IF NOT EXISTS finance_schema.accounts (396    account_id SERIAL PRIMARY KEY,397    customer_name VARCHAR(150),398    customer_id INT,399    account_type VARCHAR(50),400    branch_city VARCHAR(100),401    branch_id INT,402    opening_date DATE,403    current_balance NUMERIC(14,2)404);405ALTER TABLE finance_schema.accounts406    ADD COLUMN IF NOT EXISTS customer_id INT;407ALTER TABLE finance_schema.accounts408    ADD COLUMN IF NOT EXISTS branch_id INT;409 410COMMENT ON TABLE finance_schema.accounts IS 'Stores customer bank account information including account type, branch location, and current balance.';411COMMENT ON COLUMN finance_schema.accounts.account_id IS 'Unique system-generated identifier assigned to each bank account.';412COMMENT ON COLUMN finance_schema.accounts.customer_name IS 'Legacy customer name text (kept for backward compatibility).';413COMMENT ON COLUMN finance_schema.accounts.customer_id IS 'Foreign key to finance customers master.';414COMMENT ON COLUMN finance_schema.accounts.account_type IS 'Type of bank account such as savings or checking.';415COMMENT ON COLUMN finance_schema.accounts.branch_city IS 'Legacy branch city text (kept for backward compatibility).';416COMMENT ON COLUMN finance_schema.accounts.branch_id IS 'Foreign key to branch master.';417COMMENT ON COLUMN finance_schema.accounts.opening_date IS 'Date when the bank account was opened.';418COMMENT ON COLUMN finance_schema.accounts.current_balance IS 'Current available balance in the account.';419 420CREATE TABLE IF NOT EXISTS finance_schema.transactions (421    transaction_id SERIAL PRIMARY KEY,422    account_id INTEGER REFERENCES finance_schema.accounts(account_id),423    transaction_date DATE,424    transaction_type VARCHAR(50),425    amount NUMERIC(14,2),426    description VARCHAR(255)427);428COMMENT ON TABLE finance_schema.transactions IS 'Records financial transactions performed on customer accounts including debits and credits.';429COMMENT ON COLUMN finance_schema.transactions.transaction_id IS 'Unique system-generated identifier for each transaction.';430COMMENT ON COLUMN finance_schema.transactions.account_id IS 'Foreign key referencing the account on which the transaction occurred.';431COMMENT ON COLUMN finance_schema.transactions.transaction_date IS 'Date when the transaction was executed.';432COMMENT ON COLUMN finance_schema.transactions.transaction_type IS 'Type of transaction such as debit or credit.';433COMMENT ON COLUMN finance_schema.transactions.amount IS 'Monetary value of the transaction.';434COMMENT ON COLUMN finance_schema.transactions.description IS 'Short textual explanation describing the purpose of the transaction.';435 436CREATE TABLE IF NOT EXISTS finance_schema.loans (437    loan_id SERIAL PRIMARY KEY,438    account_id INTEGER REFERENCES finance_schema.accounts(account_id),439    loan_type VARCHAR(100),440    loan_amount NUMERIC(14,2),441    interest_rate NUMERIC(5,2),442    loan_start_date DATE,443    loan_end_date DATE444);445COMMENT ON TABLE finance_schema.loans IS 'Stores loan account information associated with customer accounts.';446COMMENT ON COLUMN finance_schema.loans.loan_id IS 'Unique system-generated identifier for each loan record.';447COMMENT ON COLUMN finance_schema.loans.account_id IS 'Foreign key referencing the account associated with the loan.';448COMMENT ON COLUMN finance_schema.loans.loan_type IS 'Category of loan such as home loan, auto loan, or personal loan.';449COMMENT ON COLUMN finance_schema.loans.loan_amount IS 'Total principal amount borrowed under the loan agreement.';450COMMENT ON COLUMN finance_schema.loans.interest_rate IS 'Annual interest rate applied to the loan expressed as a percentage.';451COMMENT ON COLUMN finance_schema.loans.loan_start_date IS 'Date when the loan repayment period began.';452COMMENT ON COLUMN finance_schema.loans.loan_end_date IS 'Date when the loan is scheduled to be fully repaid.';453 454CREATE TABLE IF NOT EXISTS finance_schema.customers (455    customer_id SERIAL PRIMARY KEY,456    first_name VARCHAR(50),457    last_name VARCHAR(50),458    email VARCHAR(100),459    phone VARCHAR(20),460    city VARCHAR(50),461    state VARCHAR(50),462    created_at TIMESTAMP463);464COMMENT ON TABLE finance_schema.customers IS 'Customer master data storing personal and contact details';465COMMENT ON COLUMN finance_schema.customers.customer_id IS 'Unique identifier for each customer';466COMMENT ON COLUMN finance_schema.customers.first_name IS 'Customer first name';467COMMENT ON COLUMN finance_schema.customers.last_name IS 'Customer last name';468COMMENT ON COLUMN finance_schema.customers.email IS 'Customer email address';469COMMENT ON COLUMN finance_schema.customers.phone IS 'Customer phone number';470COMMENT ON COLUMN finance_schema.customers.city IS 'City where customer resides';471COMMENT ON COLUMN finance_schema.customers.state IS 'State where customer resides';472COMMENT ON COLUMN finance_schema.customers.created_at IS 'Timestamp when customer profile was created';473 474CREATE TABLE IF NOT EXISTS finance_schema.branches (475    branch_id SERIAL PRIMARY KEY,476    branch_name VARCHAR(100),477    city VARCHAR(50),478    state VARCHAR(50)479);480COMMENT ON TABLE finance_schema.branches IS 'Bank branch information';481COMMENT ON COLUMN finance_schema.branches.branch_id IS 'Unique identifier for branch';482COMMENT ON COLUMN finance_schema.branches.branch_name IS 'Name of the branch';483COMMENT ON COLUMN finance_schema.branches.city IS 'City where branch is located';484COMMENT ON COLUMN finance_schema.branches.state IS 'State where branch is located';485 486CREATE TABLE IF NOT EXISTS finance_schema.credit_cards (487    card_id SERIAL PRIMARY KEY,488    customer_id INT REFERENCES finance_schema.customers(customer_id),489    card_type VARCHAR(50),490    credit_limit DECIMAL(12,2),491    current_balance DECIMAL(12,2),492    issue_date DATE,493    expiry_date DATE494);495COMMENT ON TABLE finance_schema.credit_cards IS 'Credit card accounts issued to customers';496COMMENT ON COLUMN finance_schema.credit_cards.card_id IS 'Unique credit card identifier';497COMMENT ON COLUMN finance_schema.credit_cards.customer_id IS 'Customer owning the card';498COMMENT ON COLUMN finance_schema.credit_cards.card_type IS 'Type of card (e.g., Visa, Mastercard)';499COMMENT ON COLUMN finance_schema.credit_cards.credit_limit IS 'Maximum allowed spending limit';500COMMENT ON COLUMN finance_schema.credit_cards.current_balance IS 'Outstanding balance on the card';501COMMENT ON COLUMN finance_schema.credit_cards.issue_date IS 'Date when card was issued';502COMMENT ON COLUMN finance_schema.credit_cards.expiry_date IS 'Card expiration date';503 504CREATE TABLE IF NOT EXISTS finance_schema.payments (505    payment_id SERIAL PRIMARY KEY,506    account_id INT REFERENCES finance_schema.accounts(account_id),507    loan_id INT REFERENCES finance_schema.loans(loan_id),508    payment_amount DECIMAL(12,2),509    payment_date DATE,510    payment_type VARCHAR(50)511);512COMMENT ON TABLE finance_schema.payments IS 'Payments made towards loans or accounts';513COMMENT ON COLUMN finance_schema.payments.payment_id IS 'Unique payment identifier';514COMMENT ON COLUMN finance_schema.payments.account_id IS 'Account from which payment was made';515COMMENT ON COLUMN finance_schema.payments.loan_id IS 'Loan associated with payment (if applicable)';516COMMENT ON COLUMN finance_schema.payments.payment_amount IS 'Amount paid';517COMMENT ON COLUMN finance_schema.payments.payment_date IS 'Date of payment';518COMMENT ON COLUMN finance_schema.payments.payment_type IS 'Type of payment (EMI, credit card, etc.)';519 520CREATE TABLE IF NOT EXISTS finance_schema.investment_accounts (521    investment_account_id SERIAL PRIMARY KEY,522    customer_id INT REFERENCES finance_schema.customers(customer_id),523    account_type VARCHAR(50),524    total_value DECIMAL(14,2),525    created_at TIMESTAMP526);527COMMENT ON TABLE finance_schema.investment_accounts IS 'Investment portfolios held by customers';528COMMENT ON COLUMN finance_schema.investment_accounts.investment_account_id IS 'Unique investment account identifier';529COMMENT ON COLUMN finance_schema.investment_accounts.customer_id IS 'Customer owning investment account';530COMMENT ON COLUMN finance_schema.investment_accounts.account_type IS 'Type of investment (stocks, mutual funds)';531COMMENT ON COLUMN finance_schema.investment_accounts.total_value IS 'Total portfolio value';532COMMENT ON COLUMN finance_schema.investment_accounts.created_at IS 'Account creation timestamp';533 534CREATE TABLE IF NOT EXISTS finance_schema.account_balances_history (535    record_id SERIAL PRIMARY KEY,536    account_id INT REFERENCES finance_schema.accounts(account_id),537    balance DECIMAL(12,2),538    recorded_at TIMESTAMP539);540COMMENT ON TABLE finance_schema.account_balances_history IS 'Historical record of account balances';541COMMENT ON COLUMN finance_schema.account_balances_history.record_id IS 'Unique record identifier';542COMMENT ON COLUMN finance_schema.account_balances_history.account_id IS 'Account being tracked';543COMMENT ON COLUMN finance_schema.account_balances_history.balance IS 'Balance at given timestamp';544COMMENT ON COLUMN finance_schema.account_balances_history.recorded_at IS 'Timestamp of recorded balance';545 546CREATE TABLE IF NOT EXISTS finance_schema.fraud_alerts (547    alert_id SERIAL PRIMARY KEY,548    transaction_id INT REFERENCES finance_schema.transactions(transaction_id),549    alert_type VARCHAR(50),550    alert_status VARCHAR(50),551    created_at TIMESTAMP552);553COMMENT ON TABLE finance_schema.fraud_alerts IS 'Flags suspicious or fraudulent transactions';554COMMENT ON COLUMN finance_schema.fraud_alerts.alert_id IS 'Unique alert identifier';555COMMENT ON COLUMN finance_schema.fraud_alerts.transaction_id IS 'Transaction flagged for fraud';556COMMENT ON COLUMN finance_schema.fraud_alerts.alert_type IS 'Type of fraud detected';557COMMENT ON COLUMN finance_schema.fraud_alerts.alert_status IS 'Status of investigation';558COMMENT ON COLUMN finance_schema.fraud_alerts.created_at IS 'Timestamp when alert was generated';559 560ALTER TABLE finance_schema.accounts561    ADD COLUMN IF NOT EXISTS customer_id INT;562ALTER TABLE finance_schema.accounts563    ADD COLUMN IF NOT EXISTS branch_id INT;564 565DO $$566BEGIN567    IF NOT EXISTS (568        SELECT 1569        FROM pg_constraint570        WHERE conname = 'fk_accounts_customer_id'571          AND connamespace = 'finance_schema'::regnamespace572    ) THEN573        ALTER TABLE finance_schema.accounts574            ADD CONSTRAINT fk_accounts_customer_id575            FOREIGN KEY (customer_id)576            REFERENCES finance_schema.customers(customer_id);577    END IF;578END $$;579 580DO $$581BEGIN582    IF NOT EXISTS (583        SELECT 1584        FROM pg_constraint585        WHERE conname = 'fk_accounts_branch_id'586          AND connamespace = 'finance_schema'::regnamespace587    ) THEN588        ALTER TABLE finance_schema.accounts589            ADD CONSTRAINT fk_accounts_branch_id590            FOREIGN KEY (branch_id)591            REFERENCES finance_schema.branches(branch_id);592    END IF;593END $$;594 595-- Business rules table for finance596CREATE TABLE IF NOT EXISTS finance_schema.finance_business_rules (597    rule_id SERIAL PRIMARY KEY,598    concept_name VARCHAR(150),599    description TEXT,600    insight TEXT,601    keywords TEXT[],602    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP603);604COMMENT ON TABLE finance_schema.finance_business_rules IS 'Stores domain-level financial business knowledge used by the Intent Agent.';605 606-- Schema comments607COMMENT ON SCHEMA healthcare_schema IS 'Healthcare domain: patients, visits, diagnoses';608COMMENT ON SCHEMA retail_schema IS 'Retail domain: customers, products, orders';609COMMENT ON SCHEMA finance_schema IS 'Finance domain: accounts, transactions, loans';610