-- Shared guardian profiles. Run on the website database before deploying this feature.
-- Existing student multi_input data is retained; profiles are linked on subsequent saves.
CREATE TABLE IF NOT EXISTS guardian_tb (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 parent_name VARCHAR(255) NOT NULL,
 parent_email VARCHAR(255) NULL,
 parent_zalo VARCHAR(255) NULL,
 created_by VARCHAR(255) NOT NULL,
 date_created DATETIME NOT NULL,
 PRIMARY KEY (id), UNIQUE KEY uq_guardian_email (parent_email), UNIQUE KEY uq_guardian_zalo (parent_zalo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS student_guardian_tb (
 student_id INT UNSIGNED NOT NULL,
 guardian_id INT UNSIGNED NOT NULL,
 PRIMARY KEY (student_id), KEY idx_guardian_children (guardian_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;