-- ProjectGive v1.2 membership approval workflow
CREATE TABLE IF NOT EXISTS membership_applications (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 application_ref VARCHAR(40) NOT NULL UNIQUE,
 full_name VARCHAR(180) NOT NULL,
 email VARCHAR(190) NOT NULL,
 phone VARCHAR(40) NOT NULL,
 gender VARCHAR(30) NULL,
 date_of_birth DATE NULL,
 occupation VARCHAR(160) NULL,
 state_of_residence VARCHAR(120) NULL,
 address TEXT NULL,
 reason_for_joining TEXT NULL,
 skills TEXT NULL,
 areas_of_interest TEXT NULL,
 status ENUM('submitted','under_review','assigned','information_required','approved','declined','ended','converted','withdrawn') NOT NULL DEFAULT 'submitted',
 current_reviewer_id BIGINT UNSIGNED NULL,
 internal_comment TEXT NULL,
 converted_member_id BIGINT UNSIGNED NULL,
 ended_at DATETIME NULL,
 ended_by BIGINT UNSIGNED NULL,
 converted_at DATETIME NULL,
 welcome_email_sent_at DATETIME NULL,
 welcome_email_sent_by BIGINT UNSIGNED NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_ma_status(status), INDEX idx_ma_reviewer(current_reviewer_id), INDEX idx_ma_email(email),
 CONSTRAINT fk_ma_reviewer FOREIGN KEY(current_reviewer_id) REFERENCES management_users(id),
 CONSTRAINT fk_ma_member FOREIGN KEY(converted_member_id) REFERENCES members(id),
 CONSTRAINT fk_ma_ended_by FOREIGN KEY(ended_by) REFERENCES management_users(id),
 CONSTRAINT fk_ma_welcome_by FOREIGN KEY(welcome_email_sent_by) REFERENCES management_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_application_actions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 application_id BIGINT UNSIGNED NOT NULL,
 actor_user_id BIGINT UNSIGNED NOT NULL,
 action_name VARCHAR(60) NOT NULL,
 assigned_to_user_id BIGINT UNSIGNED NULL,
 comment TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_maa_app(application_id,created_at),
 CONSTRAINT fk_maa_app FOREIGN KEY(application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
 CONSTRAINT fk_maa_actor FOREIGN KEY(actor_user_id) REFERENCES management_users(id),
 CONSTRAINT fk_maa_assignee FOREIGN KEY(assigned_to_user_id) REFERENCES management_users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;