-- Create archive_form table. Parent forms (forms that have at least one child) are stored here.
-- Archived forms: visible only on View Forms page; cannot be updated; cannot be added to audits.

CREATE TABLE IF NOT EXISTS archive_form (
    form_id INT PRIMARY KEY,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (form_id) REFERENCES form_details(form_id) ON DELETE CASCADE
);

-- If you previously ran add_form_is_archived.sql, remove the column from form_details:
-- ALTER TABLE form_details DROP COLUMN IF EXISTS is_archived;

-- Migrate existing parent forms into archive_form (forms that have children)
INSERT IGNORE INTO archive_form (form_id)
SELECT DISTINCT f.form_id
FROM form_details f
WHERE EXISTS (
    SELECT 1 FROM form_details c WHERE c.form_parent_id = f.form_id
);
