------------------------------------------------------------
-- 1. Setup: Create tables
------------------------------------------------------------

DROP TABLE IF EXISTS MainData;
DROP TABLE IF EXISTS DeleteLog;

CREATE TABLE MainData (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    info TEXT
);

CREATE TABLE DeleteLog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    deleted_id INTEGER,
    deleted_info TEXT,
    deleted_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

------------------------------------------------------------
-- Insert sample data
------------------------------------------------------------
INSERT INTO MainData (info) VALUES ('Alpha');
INSERT INTO MainData (info) VALUES ('Bravo');
INSERT INTO MainData (info) VALUES ('Charlie');


------------------------------------------------------------
-- 2. MODIFY DATA + UNDO CHANGES
------------------------------------------------------------

-- Begin transaction
BEGIN TRANSACTION;

-- Modify data
UPDATE MainData SET info = 'Modified-Alpha' WHERE id = 1;
UPDATE MainData SET info = 'Modified-Bravo' WHERE id = 2;

-- Undo changes (rollback)
ROLLBACK;

-- Data is now unchanged


------------------------------------------------------------
-- 3. ENCRYPT + DECRYPT DATA
-- Simple reversible XOR encryption for demonstration
------------------------------------------------------------

-- Create encryption function (SQLite user-defined function simulation)
-- XOR each character with key 7
-- NOTE: This is conceptual; real encryption should use AES.
------------------------------------------------------------

-- Encrypt data
UPDATE MainData
SET info = (
    SELECT GROUP_CONCAT(
        printf('%c', unicode(substr(info, n, 1)) ^ 7),
        ''
    )
    FROM generate_series(1, length(info)) AS n
);

-- Decrypt data (XOR again with same key)
UPDATE MainData
SET info = (
    SELECT GROUP_CONCAT(
        printf('%c', unicode(substr(info, n, 1)) ^ 7),
        ''
    )
    FROM generate_series(1, length(info)) AS n
);


------------------------------------------------------------
-- 4. DELETE DATA BUT LOG IT
------------------------------------------------------------

-- Log deleted rows
INSERT INTO DeleteLog (deleted_id, deleted_info)
SELECT id, info FROM MainData;

-- Delete actual data
DELETE FROM MainData;


------------------------------------------------------------
-- 5. Show results
------------------------------------------------------------

SELECT 'MainData (should be empty)' AS section;
SELECT * FROM MainData;

SELECT 'DeleteLog (contains deleted rows)' AS section;
SELECT * FROM DeleteLog;