-- SoulGuard Seal workspace migration (MySQL 8 / MariaDB 10.4+).
-- BACK UP your live database first. Apply ONCE with a privileged migration account.
-- Does not alter existing users, organizations or user_organizations tables.
CREATE TABLE IF NOT EXISTS sg_workspace_roles (
 organization_id BIGINT UNSIGNED NOT NULL,
 user_id BIGINT UNSIGNED NOT NULL,
 role VARCHAR(30) NOT NULL DEFAULT 'contributor',
 granted_by BIGINT UNSIGNED NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (organization_id,user_id),
 INDEX idx_ws_role (organization_id,role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sg_workspace_invites (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 invited_email VARCHAR(190) NOT NULL,
 invited_role VARCHAR(30) NOT NULL,
 token_sha256 CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
 invited_by BIGINT UNSIGNED NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 expires_at DATETIME NOT NULL,
 accepted_at DATETIME NULL,
 accepted_by BIGINT UNSIGNED NULL,
 revoked_at DATETIME NULL,
 UNIQUE KEY uq_ws_invite_token (token_sha256),
 INDEX idx_ws_invite_org (organization_id,created_at),
 INDEX idx_ws_invite_email (invited_email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sg_workspace_audit (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 actor_user_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(100) NOT NULL,
 details VARCHAR(2000) NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_ws_audit_org (organization_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sg_policy_register (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 title VARCHAR(190) NOT NULL,
 category VARCHAR(50) NOT NULL,
 content MEDIUMTEXT NOT NULL,
 status VARCHAR(20) NOT NULL DEFAULT 'draft',
 version_number INT UNSIGNED NOT NULL DEFAULT 1,
 owner_user_id BIGINT UNSIGNED NOT NULL,
 created_by BIGINT UNSIGNED NOT NULL,
 updated_by BIGINT UNSIGNED NOT NULL,
 approved_by BIGINT UNSIGNED NULL,
 approved_at DATETIME NULL,
 review_due DATE NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX idx_sg_policies_org (organization_id,status),
 INDEX idx_sg_policies_review (organization_id,review_due)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sg_policy_history (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_id BIGINT UNSIGNED NOT NULL,
 policy_id BIGINT UNSIGNED NOT NULL,
 actor_user_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(30) NOT NULL,
 old_status VARCHAR(20) NULL,
 new_status VARCHAR(20) NOT NULL,
 version_number INT UNSIGNED NOT NULL,
 content_sha256 CHAR(64) NOT NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 INDEX idx_policy_history (organization_id,policy_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS sg_demo_requests (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 organization_name VARCHAR(180) NOT NULL,
 contact_name VARCHAR(130) NOT NULL,
 email VARCHAR(190) NOT NULL,
 phone VARCHAR(32) NULL,
 industry VARCHAR(80) NULL,
 employees VARCHAR(30) NULL,
 message VARCHAR(2000) NULL,
 status VARCHAR(20) NOT NULL DEFAULT 'new',
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 reviewed_at DATETIME NULL,
 INDEX idx_demo_email_created (email,created_at),
 INDEX idx_demo_status_created (status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;