-- For MySQL/PostgreSQL:
ALTER TABLE projects MODIFY details JSON;

-- For SQLite:
-- Note: SQLite does not have a dedicated JSON type, but it can store JSON as TEXT and use JSON functions on it.
-- The following steps are a common workaround to "alter" a table in SQLite.

-- 1. Begin a transaction
BEGIN TRANSACTION;

-- 2. Rename the existing table
ALTER TABLE projects RENAME TO projects_old;

-- 3. Create a new table with the desired schema
CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    title VARCHAR NOT NULL,
    description TEXT NOT NULL,
    status VARCHAR NOT NULL,
    details TEXT, -- Storing JSON as TEXT
    created_at DATETIME,
    updated_at DATETIME,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. Copy the data from the old table to the new table
-- This assumes the existing 'details' column contains a JSON string.
INSERT INTO projects (id, user_id, title, description, status, details, created_at, updated_at)
SELECT id, user_id, title, description, status, details, created_at, updated_at
FROM projects_old;

-- 5. Drop the old table
DROP TABLE projects_old;

-- 6. Commit the transaction
COMMIT;