-- Tạo bảng accounts
CREATE TABLE accounts (
   account_id VARCHAR(20) PRIMARY KEY,
   account_name VARCHAR(100) NOT NULL,
   balance DECIMAL(15,2) NOT NULL DEFAULT 0,
   status ENUM('active', 'frozen', 'closed') DEFAULT 'active',
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
   INDEX idx_balance (balance),
   CONSTRAINT check_balance CHECK (balance >= 0)
);
 
 
-- Tạo bảng transaction_log để ghi nhật ký
CREATE TABLE transaction_log (
   transaction_id INT AUTO_INCREMENT PRIMARY KEY,
   from_account VARCHAR(20),
   to_account VARCHAR(20),
   amount DECIMAL(15,2) NOT NULL,
   transaction_type ENUM('transfer', 'deposit', 'withdrawal') NOT NULL,
   status ENUM('pending', 'completed', 'failed') NOT NULL,
   error_message TEXT,
   created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
   FOREIGN KEY (from_account) REFERENCES accounts(account_id),
   FOREIGN KEY (to_account) REFERENCES accounts(account_id)
);
 
 
-- Insert dữ liệu mẫu
INSERT INTO accounts (account_id, account_name, balance, status) VALUES
('ACC001', 'Nguyễn Văn A', 50000.00, 'active'),
('ACC002', 'Trần Thị B', 25000.00, 'active'),
('ACC003', 'Lê Văn C', 10000.00, 'active'),
('ACC004', 'Phạm Thị D', 5000.00, 'frozen'),
('ACC005', 'Hoàng Văn E', 0.00, 'active');
 
 
-- Kiểm tra dữ liệu
SELECT * FROM accounts;
 
DELIMITER //
 
 
CREATE PROCEDURE TransferMoney(
   IN p_from_account VARCHAR(20),
   IN p_to_account VARCHAR(20),
   IN p_amount DECIMAL(15,2),
   OUT p_status VARCHAR(20),
   OUT p_message TEXT
)
transfer_proc: BEGIN  -- Thêm label 'transfer_proc'
   DECLARE v_from_balance DECIMAL(15,2);
   DECLARE v_from_status VARCHAR(10);
   DECLARE v_to_status VARCHAR(10);
 
   -- Xử lý ngoại lệ
   DECLARE EXIT HANDLER FOR SQLEXCEPTION
   BEGIN
       ROLLBACK;
       SET p_status = 'ERROR';
       SET p_message = 'Database error occurred during transaction';
       RESIGNAL;
   END;
 
   -- Validate input
   IF p_amount <= 0 THEN
       SET p_status = 'ERROR';
       SET p_message = 'Transfer amount must be greater than 0';
       LEAVE transfer_proc;
   END IF;
 
   IF p_from_account = p_to_account THEN
       SET p_status = 'ERROR';
       SET p_message = 'Cannot transfer to the same account';
       LEAVE transfer_proc;
   END IF;
 
   -- Bắt đầu transaction
   START TRANSACTION;
 
   -- Kiểm tra tài khoản nguồn
   SELECT balance, status INTO v_from_balance, v_from_status
   FROM accounts
   WHERE account_id = p_from_account
   FOR UPDATE;  -- Lock row để tránh concurrent modifications
 
   IF v_from_balance IS NULL THEN
       SET p_status = 'ERROR';
       SET p_message = CONCAT('Source account ', p_from_account, ' does not exist');
       ROLLBACK;
       LEAVE transfer_proc;
   END IF;
 
   IF v_from_status != 'active' THEN
       SET p_status = 'ERROR';
       SET p_message = CONCAT('Source account ', p_from_account, ' is not active (status: ', v_from_status, ')');
       ROLLBACK;
       LEAVE transfer_proc;
   END IF;
 
   IF v_from_balance < p_amount THEN
       SET p_status = 'ERROR';
       SET p_message = CONCAT('Insufficient funds. Available: ', v_from_balance, ', Requested: ', p_amount);
       ROLLBACK;
       LEAVE transfer_proc;
   END IF;
 
   -- Kiểm tra tài khoản đích
   SELECT status INTO v_to_status
   FROM accounts
   WHERE account_id = p_to_account
   FOR UPDATE;  -- Lock row
 
   IF v_to_status IS NULL THEN
       SET p_status = 'ERROR';
       SET p_message = CONCAT('Destination account ', p_to_account, ' does not exist');
       ROLLBACK;
       LEAVE transfer_proc;
   END IF;
 
   IF v_to_status != 'active' THEN
       SET p_status = 'ERROR';
       SET p_message = CONCAT('Destination account ', p_to_account, ' is not active (status: ', v_to_status, ')');
       ROLLBACK;
       LEAVE transfer_proc;
   END IF;
 
   -- Thực hiện chuyển tiền
   UPDATE accounts
   SET balance = balance - p_amount
   WHERE account_id = p_from_account;
 
   UPDATE accounts
   SET balance = balance + p_amount
   WHERE account_id = p_to_account;
 
   -- Commit transaction
   COMMIT;
 
   SET p_status = 'SUCCESS';
   SET p_message = CONCAT('Successfully transferred ', p_amount, ' from ', p_from_account, ' to ', p_to_account);
 
END transfer_proc //
 
 
DELIMITER ;
 
 
-- Test với các trường hợp khác nhau
CALL TransferMoney('ACC001', 'ACC002', 5000.00, @status, @message);
SELECT @status, @message;
 
 
-- Test với tài khoản không tồn tại
CALL TransferMoney('ACC999', 'ACC002', 1000.00, @status, @message);
SELECT @status, @message;
 
 
-- Test với số tiền không hợp lệ
CALL TransferMoney('ACC001', 'ACC002', -100.00, @status, @message);
SELECT @status, @message;

LS0gVOG6oW8gYuG6o25nIGFjY291bnRzCkNSRUFURSBUQUJMRSBhY2NvdW50cyAoCiAgIGFjY291bnRfaWQgVkFSQ0hBUigyMCkgUFJJTUFSWSBLRVksCiAgIGFjY291bnRfbmFtZSBWQVJDSEFSKDEwMCkgTk9UIE5VTEwsCiAgIGJhbGFuY2UgREVDSU1BTCgxNSwyKSBOT1QgTlVMTCBERUZBVUxUIDAsCiAgIHN0YXR1cyBFTlVNKCdhY3RpdmUnLCAnZnJvemVuJywgJ2Nsb3NlZCcpIERFRkFVTFQgJ2FjdGl2ZScsCiAgIGNyZWF0ZWRfYXQgVElNRVNUQU1QIERFRkFVTFQgQ1VSUkVOVF9USU1FU1RBTVAsCiAgIHVwZGF0ZWRfYXQgVElNRVNUQU1QIERFRkFVTFQgQ1VSUkVOVF9USU1FU1RBTVAgT04gVVBEQVRFIENVUlJFTlRfVElNRVNUQU1QLAogICBJTkRFWCBpZHhfYmFsYW5jZSAoYmFsYW5jZSksCiAgIENPTlNUUkFJTlQgY2hlY2tfYmFsYW5jZSBDSEVDSyAoYmFsYW5jZSA+PSAwKQopOwoKCi0tIFThuqFvIGLhuqNuZyB0cmFuc2FjdGlvbl9sb2cgxJHhu4MgZ2hpIG5o4bqtdCBrw70KQ1JFQVRFIFRBQkxFIHRyYW5zYWN0aW9uX2xvZyAoCiAgIHRyYW5zYWN0aW9uX2lkIElOVCBBVVRPX0lOQ1JFTUVOVCBQUklNQVJZIEtFWSwKICAgZnJvbV9hY2NvdW50IFZBUkNIQVIoMjApLAogICB0b19hY2NvdW50IFZBUkNIQVIoMjApLAogICBhbW91bnQgREVDSU1BTCgxNSwyKSBOT1QgTlVMTCwKICAgdHJhbnNhY3Rpb25fdHlwZSBFTlVNKCd0cmFuc2ZlcicsICdkZXBvc2l0JywgJ3dpdGhkcmF3YWwnKSBOT1QgTlVMTCwKICAgc3RhdHVzIEVOVU0oJ3BlbmRpbmcnLCAnY29tcGxldGVkJywgJ2ZhaWxlZCcpIE5PVCBOVUxMLAogICBlcnJvcl9tZXNzYWdlIFRFWFQsCiAgIGNyZWF0ZWRfYXQgVElNRVNUQU1QIERFRkFVTFQgQ1VSUkVOVF9USU1FU1RBTVAsCiAgIEZPUkVJR04gS0VZIChmcm9tX2FjY291bnQpIFJFRkVSRU5DRVMgYWNjb3VudHMoYWNjb3VudF9pZCksCiAgIEZPUkVJR04gS0VZICh0b19hY2NvdW50KSBSRUZFUkVOQ0VTIGFjY291bnRzKGFjY291bnRfaWQpCik7CgoKLS0gSW5zZXJ0IGThu68gbGnhu4d1IG3huqt1CklOU0VSVCBJTlRPIGFjY291bnRzIChhY2NvdW50X2lkLCBhY2NvdW50X25hbWUsIGJhbGFuY2UsIHN0YXR1cykgVkFMVUVTCignQUNDMDAxJywgJ05ndXnhu4VuIFbEg24gQScsIDUwMDAwLjAwLCAnYWN0aXZlJyksCignQUNDMDAyJywgJ1Ry4bqnbiBUaOG7iyBCJywgMjUwMDAuMDAsICdhY3RpdmUnKSwKKCdBQ0MwMDMnLCAnTMOqIFbEg24gQycsIDEwMDAwLjAwLCAnYWN0aXZlJyksCignQUNDMDA0JywgJ1Bo4bqhbSBUaOG7iyBEJywgNTAwMC4wMCwgJ2Zyb3plbicpLAooJ0FDQzAwNScsICdIb8OgbmcgVsSDbiBFJywgMC4wMCwgJ2FjdGl2ZScpOwoKCi0tIEtp4buDbSB0cmEgZOG7ryBsaeG7h3UKU0VMRUNUICogRlJPTSBhY2NvdW50czsKCkRFTElNSVRFUiAvLwoKCkNSRUFURSBQUk9DRURVUkUgVHJhbnNmZXJNb25leSgKICAgSU4gcF9mcm9tX2FjY291bnQgVkFSQ0hBUigyMCksCiAgIElOIHBfdG9fYWNjb3VudCBWQVJDSEFSKDIwKSwKICAgSU4gcF9hbW91bnQgREVDSU1BTCgxNSwyKSwKICAgT1VUIHBfc3RhdHVzIFZBUkNIQVIoMjApLAogICBPVVQgcF9tZXNzYWdlIFRFWFQKKQp0cmFuc2Zlcl9wcm9jOiBCRUdJTiAgLS0gVGjDqm0gbGFiZWwgJ3RyYW5zZmVyX3Byb2MnCiAgIERFQ0xBUkUgdl9mcm9tX2JhbGFuY2UgREVDSU1BTCgxNSwyKTsKICAgREVDTEFSRSB2X2Zyb21fc3RhdHVzIFZBUkNIQVIoMTApOwogICBERUNMQVJFIHZfdG9fc3RhdHVzIFZBUkNIQVIoMTApOwogIAogICAtLSBY4butIGzDvSBuZ2/huqFpIGzhu4cKICAgREVDTEFSRSBFWElUIEhBTkRMRVIgRk9SIFNRTEVYQ0VQVElPTgogICBCRUdJTgogICAgICAgUk9MTEJBQ0s7CiAgICAgICBTRVQgcF9zdGF0dXMgPSAnRVJST1InOwogICAgICAgU0VUIHBfbWVzc2FnZSA9ICdEYXRhYmFzZSBlcnJvciBvY2N1cnJlZCBkdXJpbmcgdHJhbnNhY3Rpb24nOwogICAgICAgUkVTSUdOQUw7CiAgIEVORDsKICAKICAgLS0gVmFsaWRhdGUgaW5wdXQKICAgSUYgcF9hbW91bnQgPD0gMCBUSEVOCiAgICAgICBTRVQgcF9zdGF0dXMgPSAnRVJST1InOwogICAgICAgU0VUIHBfbWVzc2FnZSA9ICdUcmFuc2ZlciBhbW91bnQgbXVzdCBiZSBncmVhdGVyIHRoYW4gMCc7CiAgICAgICBMRUFWRSB0cmFuc2Zlcl9wcm9jOwogICBFTkQgSUY7CiAgCiAgIElGIHBfZnJvbV9hY2NvdW50ID0gcF90b19hY2NvdW50IFRIRU4KICAgICAgIFNFVCBwX3N0YXR1cyA9ICdFUlJPUic7CiAgICAgICBTRVQgcF9tZXNzYWdlID0gJ0Nhbm5vdCB0cmFuc2ZlciB0byB0aGUgc2FtZSBhY2NvdW50JzsKICAgICAgIExFQVZFIHRyYW5zZmVyX3Byb2M7CiAgIEVORCBJRjsKICAKICAgLS0gQuG6r3QgxJHhuqd1IHRyYW5zYWN0aW9uCiAgIFNUQVJUIFRSQU5TQUNUSU9OOwogIAogICAtLSBLaeG7g20gdHJhIHTDoGkga2hv4bqjbiBuZ3Xhu5NuCiAgIFNFTEVDVCBiYWxhbmNlLCBzdGF0dXMgSU5UTyB2X2Zyb21fYmFsYW5jZSwgdl9mcm9tX3N0YXR1cwogICBGUk9NIGFjY291bnRzCiAgIFdIRVJFIGFjY291bnRfaWQgPSBwX2Zyb21fYWNjb3VudAogICBGT1IgVVBEQVRFOyAgLS0gTG9jayByb3cgxJHhu4MgdHLDoW5oIGNvbmN1cnJlbnQgbW9kaWZpY2F0aW9ucwogIAogICBJRiB2X2Zyb21fYmFsYW5jZSBJUyBOVUxMIFRIRU4KICAgICAgIFNFVCBwX3N0YXR1cyA9ICdFUlJPUic7CiAgICAgICBTRVQgcF9tZXNzYWdlID0gQ09OQ0FUKCdTb3VyY2UgYWNjb3VudCAnLCBwX2Zyb21fYWNjb3VudCwgJyBkb2VzIG5vdCBleGlzdCcpOwogICAgICAgUk9MTEJBQ0s7CiAgICAgICBMRUFWRSB0cmFuc2Zlcl9wcm9jOwogICBFTkQgSUY7CiAgCiAgIElGIHZfZnJvbV9zdGF0dXMgIT0gJ2FjdGl2ZScgVEhFTgogICAgICAgU0VUIHBfc3RhdHVzID0gJ0VSUk9SJzsKICAgICAgIFNFVCBwX21lc3NhZ2UgPSBDT05DQVQoJ1NvdXJjZSBhY2NvdW50ICcsIHBfZnJvbV9hY2NvdW50LCAnIGlzIG5vdCBhY3RpdmUgKHN0YXR1czogJywgdl9mcm9tX3N0YXR1cywgJyknKTsKICAgICAgIFJPTExCQUNLOwogICAgICAgTEVBVkUgdHJhbnNmZXJfcHJvYzsKICAgRU5EIElGOwogIAogICBJRiB2X2Zyb21fYmFsYW5jZSA8IHBfYW1vdW50IFRIRU4KICAgICAgIFNFVCBwX3N0YXR1cyA9ICdFUlJPUic7CiAgICAgICBTRVQgcF9tZXNzYWdlID0gQ09OQ0FUKCdJbnN1ZmZpY2llbnQgZnVuZHMuIEF2YWlsYWJsZTogJywgdl9mcm9tX2JhbGFuY2UsICcsIFJlcXVlc3RlZDogJywgcF9hbW91bnQpOwogICAgICAgUk9MTEJBQ0s7CiAgICAgICBMRUFWRSB0cmFuc2Zlcl9wcm9jOwogICBFTkQgSUY7CiAgCiAgIC0tIEtp4buDbSB0cmEgdMOgaSBraG/huqNuIMSRw61jaAogICBTRUxFQ1Qgc3RhdHVzIElOVE8gdl90b19zdGF0dXMKICAgRlJPTSBhY2NvdW50cwogICBXSEVSRSBhY2NvdW50X2lkID0gcF90b19hY2NvdW50CiAgIEZPUiBVUERBVEU7ICAtLSBMb2NrIHJvdwogIAogICBJRiB2X3RvX3N0YXR1cyBJUyBOVUxMIFRIRU4KICAgICAgIFNFVCBwX3N0YXR1cyA9ICdFUlJPUic7CiAgICAgICBTRVQgcF9tZXNzYWdlID0gQ09OQ0FUKCdEZXN0aW5hdGlvbiBhY2NvdW50ICcsIHBfdG9fYWNjb3VudCwgJyBkb2VzIG5vdCBleGlzdCcpOwogICAgICAgUk9MTEJBQ0s7CiAgICAgICBMRUFWRSB0cmFuc2Zlcl9wcm9jOwogICBFTkQgSUY7CiAgCiAgIElGIHZfdG9fc3RhdHVzICE9ICdhY3RpdmUnIFRIRU4KICAgICAgIFNFVCBwX3N0YXR1cyA9ICdFUlJPUic7CiAgICAgICBTRVQgcF9tZXNzYWdlID0gQ09OQ0FUKCdEZXN0aW5hdGlvbiBhY2NvdW50ICcsIHBfdG9fYWNjb3VudCwgJyBpcyBub3QgYWN0aXZlIChzdGF0dXM6ICcsIHZfdG9fc3RhdHVzLCAnKScpOwogICAgICAgUk9MTEJBQ0s7CiAgICAgICBMRUFWRSB0cmFuc2Zlcl9wcm9jOwogICBFTkQgSUY7CiAgCiAgIC0tIFRo4buxYyBoaeG7h24gY2h1eeG7g24gdGnhu4FuCiAgIFVQREFURSBhY2NvdW50cwogICBTRVQgYmFsYW5jZSA9IGJhbGFuY2UgLSBwX2Ftb3VudAogICBXSEVSRSBhY2NvdW50X2lkID0gcF9mcm9tX2FjY291bnQ7CiAgCiAgIFVQREFURSBhY2NvdW50cwogICBTRVQgYmFsYW5jZSA9IGJhbGFuY2UgKyBwX2Ftb3VudAogICBXSEVSRSBhY2NvdW50X2lkID0gcF90b19hY2NvdW50OwogIAogICAtLSBDb21taXQgdHJhbnNhY3Rpb24KICAgQ09NTUlUOwogIAogICBTRVQgcF9zdGF0dXMgPSAnU1VDQ0VTUyc7CiAgIFNFVCBwX21lc3NhZ2UgPSBDT05DQVQoJ1N1Y2Nlc3NmdWxseSB0cmFuc2ZlcnJlZCAnLCBwX2Ftb3VudCwgJyBmcm9tICcsIHBfZnJvbV9hY2NvdW50LCAnIHRvICcsIHBfdG9fYWNjb3VudCk7CiAgCkVORCB0cmFuc2Zlcl9wcm9jIC8vCgoKREVMSU1JVEVSIDsKCgotLSBUZXN0IHbhu5tpIGPDoWMgdHLGsOG7nW5nIGjhu6NwIGtow6FjIG5oYXUKQ0FMTCBUcmFuc2Zlck1vbmV5KCdBQ0MwMDEnLCAnQUNDMDAyJywgNTAwMC4wMCwgQHN0YXR1cywgQG1lc3NhZ2UpOwpTRUxFQ1QgQHN0YXR1cywgQG1lc3NhZ2U7CgoKLS0gVGVzdCB24bubaSB0w6BpIGtob+G6o24ga2jDtG5nIHThu5NuIHThuqFpCkNBTEwgVHJhbnNmZXJNb25leSgnQUNDOTk5JywgJ0FDQzAwMicsIDEwMDAuMDAsIEBzdGF0dXMsIEBtZXNzYWdlKTsKU0VMRUNUIEBzdGF0dXMsIEBtZXNzYWdlOwoKCi0tIFRlc3QgduG7m2kgc+G7kSB0aeG7gW4ga2jDtG5nIGjhu6NwIGzhu4cKQ0FMTCBUcmFuc2Zlck1vbmV5KCdBQ0MwMDEnLCAnQUNDMDAyJywgLTEwMC4wMCwgQHN0YXR1cywgQG1lc3NhZ2UpOwpTRUxFQ1QgQHN0YXR1cywgQG1lc3NhZ2U7