-- TradeDash Supplier users / location access
--
-- The existing TradeDash `users` and `user_auth_identities` tables remain the
-- source of login credentials. These tables add supplier-specific membership,
-- roles, and location access so the supplier owner/company admin can provision
-- staff accounts and decide which locations each account may use.

CREATE TABLE IF NOT EXISTS supplier_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    uuid CHAR(36) NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    role ENUM('owner', 'company_admin', 'manager', 'staff') NOT NULL DEFAULT 'staff',
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    all_locations TINYINT(1) NOT NULL DEFAULT 0,
    can_manage_users TINYINT(1) NOT NULL DEFAULT 0,
    created_by_user_id BIGINT UNSIGNED 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_supplier_users_uuid (uuid),
    UNIQUE KEY uq_supplier_users_membership (supplier_id, user_id),
    KEY idx_supplier_users_supplier_status (supplier_id, status),
    KEY idx_supplier_users_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS supplier_user_locations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    supplier_user_id BIGINT UNSIGNED NOT NULL,
    supplier_id BIGINT UNSIGNED NOT NULL,
    location_id BIGINT UNSIGNED NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_supplier_user_location (supplier_user_id, location_id),
    KEY idx_supplier_user_locations_supplier_location (supplier_id, location_id),
    KEY idx_supplier_user_locations_location (location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seed each supplier's current primary TradeDash user as the owner. This keeps
-- existing supplier logins working while we migrate the app/web auth paths to
-- supplier_users.
INSERT INTO supplier_users
(
    uuid,
    supplier_id,
    user_id,
    role,
    status,
    all_locations,
    can_manage_users,
    created_by_user_id
)
SELECT
    UUID(),
    s.id,
    s.user_id,
    'owner',
    'active',
    1,
    1,
    s.user_id
FROM suppliers AS s
WHERE s.user_id IS NOT NULL
  AND s.user_id > 0
ON DUPLICATE KEY UPDATE
    role = IF(role = 'owner', role, VALUES(role)),
    all_locations = 1,
    can_manage_users = 1,
    updated_at = CURRENT_TIMESTAMP;