setup-database.bat

@echo off
echo Setting up MySQL database for Disposition Report Application...
echo.
echo Please run the following commands in MySQL:
echo.
echo mysql -h localhost -P 3309 -u root -p
echo.
echo Then copy and paste the following SQL commands:
echo.
echo USE galaxy_compliance_mcbankny;
echo DROP TABLE IF EXISTS screening_result;
echo DROP TABLE IF EXISTS screening_item; 
echo DROP TABLE IF EXISTS screening_request;
echo.
echo CREATE TABLE screening_request (
echo     ofac_reference_no VARCHAR(50) PRIMARY KEY,
echo     transaction_reference_no VARCHAR(100),
echo     source_system VARCHAR(50),
echo     score DECIMAL(5,2),
echo     payment_status VARCHAR(50),
echo     reviewer VARCHAR(100),
echo     approver VARCHAR(100),
echo     description TEXT,
echo     last_updated_date DATETIME,
echo     created_date DATETIME,
echo     comments TEXT
echo );
echo.
echo CREATE TABLE screening_item (
echo     item_id VARCHAR(50) PRIMARY KEY,
echo     ofac_reference_no VARCHAR(50),
echo     type VARCHAR(50),
echo     name VARCHAR(255),
echo     address TEXT,
echo     hits VARCHAR(255),
echo     item_score DECIMAL(5,2),
echo     FOREIGN KEY (ofac_reference_no) REFERENCES screening_request(ofac_reference_no)
echo );
echo.
echo CREATE TABLE screening_result (
echo     result_id VARCHAR(50) PRIMARY KEY,
echo     item_id VARCHAR(50),
echo     program VARCHAR(100),
echo     matching_name VARCHAR(255),
echo     item_reviewer VARCHAR(100),
echo     item_approver VARCHAR(100),
echo     item_comments TEXT,
echo     program_identifier VARCHAR(100),
echo     FOREIGN KEY (item_id) REFERENCES screening_item(item_id)
echo );
echo.
echo -- Insert test data
echo INSERT INTO screening_request VALUES 
echo ('OFAC001', 'TXN001', 'Payments', 95.5, 'PENDING', 'John Doe', 'Jane Smith', 'High risk transaction', '2024-12-16 10:30:00', '2024-12-16 09:00:00', 'Requires manual review'),
echo ('OFAC002', 'TXN002', 'Payments', 87.2, 'APPROVED', 'Alice Johnson', 'Bob Wilson', 'Medium risk transaction', '2024-12-17 14:20:00', '2024-12-17 13:15:00', 'Approved after review'),
echo ('OFAC003', 'TXN003', 'Payments', 92.8, 'REJECTED', 'Charlie Brown', 'Diana Prince', 'High risk customer', '2024-12-18 16:45:00', '2024-12-18 15:30:00', 'Blocked transaction');
echo.
echo INSERT INTO screening_item VALUES 
echo ('ITEM001', 'OFAC001', 'PERSON', 'John Michael Smith', '123 Main St, New York, NY 10001', 'SDN List', 95.5),
echo ('ITEM002', 'OFAC002', 'ENTITY', 'ABC Corporation Ltd', '456 Business Ave, Los Angeles, CA 90210', 'Non-SDN', 87.2),
echo ('ITEM003', 'OFAC003', 'PERSON', 'Maria Elena Rodriguez', '789 Broadway, Chicago, IL 60601', 'SDN List', 92.8);
echo.
echo INSERT INTO screening_result VALUES 
echo ('RESULT001', 'ITEM001', 'SDN', 'John M. Smith', 'Sarah Connor', 'Kyle Reese', 'Strong name match with SDN entry', 'SDN-12345'),
echo ('RESULT002', 'ITEM002', 'Non-SDN Menu', 'ABC Corp Limited', 'Ellen Ripley', 'James Cameron', 'Partial entity name match', 'NONSDN-67890'),
echo ('RESULT003', 'ITEM003', 'SDN', 'Maria Rodriguez', 'Neo Anderson', 'Morpheus Smith', 'High confidence person match', 'SDN-54321');
echo.
echo After running these commands, restart the Spring Boot application.
pause