-- 003 — KYC, password reset, inbox, settings, auth/page events

ALTER TABLE organizations
    ADD COLUMN kyc_status ENUM('unverified','submitted','verified','rejected') NOT NULL DEFAULT 'unverified' AFTER abn,
    ADD COLUMN kyc_submitted_at DATETIME NULL AFTER kyc_status,
    ADD COLUMN kyc_reviewed_at DATETIME NULL AFTER kyc_submitted_at,
    ADD COLUMN kyc_reviewer_id BIGINT UNSIGNED NULL AFTER kyc_reviewed_at,
    ADD COLUMN kyc_notes TEXT NULL AFTER kyc_reviewer_id,
    ADD COLUMN business_proof_path VARCHAR(255) NULL AFTER kyc_notes;

ALTER TABLE users
    ADD COLUMN last_ip VARCHAR(45) NULL AFTER last_login_at,
    ADD COLUMN last_user_agent VARCHAR(255) NULL AFTER last_ip,
    ADD COLUMN failed_login_count INT UNSIGNED NOT NULL DEFAULT 0 AFTER last_user_agent,
    ADD COLUMN locked_until DATETIME NULL AFTER failed_login_count;

CREATE TABLE IF NOT EXISTS inbox_messages (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    sender_user_id BIGINT UNSIGNED NULL,
    sender_label VARCHAR(80) NOT NULL DEFAULT 'B-connex',
    subject VARCHAR(200) NOT NULL,
    body MEDIUMTEXT NOT NULL,
    category ENUM('system','announcement','billing','security','marketing') NOT NULL DEFAULT 'system',
    audience ENUM('all','role','org','user') NOT NULL DEFAULT 'user',
    audience_filter VARCHAR(120) NULL,
    sent_email TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_inb_audience (audience)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS inbox_recipients (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    message_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    read_at DATETIME NULL,
    archived_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY ux_inbr (message_id, user_id),
    KEY idx_inbr_user (user_id, read_at),
    CONSTRAINT fk_inbr_msg  FOREIGN KEY (message_id) REFERENCES inbox_messages(id) ON DELETE CASCADE,
    CONSTRAINT fk_inbr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS auth_events (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT UNSIGNED NULL,
    email VARCHAR(190) NULL,
    event ENUM('login_success','login_failed','password_reset_requested','password_reset_used','locked','ip_change','ua_change','email_verified','suspicious') NOT NULL,
    ip VARCHAR(45) NULL,
    user_agent VARCHAR(255) NULL,
    meta_json JSON NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_auth_user (user_id, created_at),
    KEY idx_auth_event (event, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
