-- Migration 018: Subscriber networking — opt-in consent + anonymous discovery
-- Phase 15 — GHL-compete feature (AU Privacy Act APP 3 compliant)

CREATE TABLE IF NOT EXISTS network_profiles (
    id                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    org_id            BIGINT UNSIGNED NOT NULL UNIQUE,
    display_name      VARCHAR(80) NOT NULL DEFAULT '',
    industry          VARCHAR(80) NOT NULL DEFAULT '',
    state             VARCHAR(30) NOT NULL DEFAULT '',
    tagline           TEXT NULL,
    is_discoverable   TINYINT(1) NOT NULL DEFAULT 0,
    anonymity_level   ENUM('full_anon','industry_only','named') NOT NULL DEFAULT 'full_anon',
    consent_given_at  DATETIME NULL,
    created_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_discover  (is_discoverable, industry, state)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS network_connections (
    id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    from_org_id BIGINT UNSIGNED NOT NULL,
    to_org_id   BIGINT UNSIGNED NOT NULL,
    status      ENUM('pending','accepted','declined') NOT NULL DEFAULT 'pending',
    message     TEXT NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_connection (from_org_id, to_org_id),
    KEY idx_to_org (to_org_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
