-- GeoLynx Admin Area — Migration v1
-- Run against the GeoLynx database before deploying user management features.
-- Safe to run multiple times (uses IF NOT EXISTS / ON CONFLICT).

-- ── Login log: add user_id so history can be filtered per user ───────────────
ALTER TABLE users.users_login_log
    ADD COLUMN IF NOT EXISTS user_id bigint REFERENCES users.users(id);

CREATE INDEX IF NOT EXISTS idx_users_login_log_user
    ON users.users_login_log(user_id);

-- ── Login log: session tracking columns ──────────────────────────────────────
-- session_id  : PHP session ID captured at login (after session_regenerate_id)
--               Allows cross-referencing with server logs and identifying
--               concurrent sessions for the same user.
-- last_active_datetime : updated on every page load via updateActiveUser().
--               Combined with timestamp, gives session duration and idle time.
ALTER TABLE users.users_login_log
    ADD COLUMN IF NOT EXISTS session_id          varchar(128),
    ADD COLUMN IF NOT EXISTS last_active_datetime timestamp without time zone;

CREATE INDEX IF NOT EXISTS idx_users_login_log_session
    ON users.users_login_log(session_id);

-- ── Many-to-many: users ↔ roles ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users.user_roles (
    user_id     bigint  NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
    role_id     integer NOT NULL REFERENCES users.roles(role_id) ON DELETE CASCADE,
    granted_datetime timestamp without time zone DEFAULT now(),
    granted_by  bigint  REFERENCES users.users(id),
    PRIMARY KEY (user_id, role_id)
);

CREATE INDEX IF NOT EXISTS idx_user_roles_user
    ON users.user_roles(user_id);

-- ── Module-level permissions per role ────────────────────────────────────────
-- module values : 'projects', 'accounts', 'stocklists' (add new modules by inserting rows)
-- permission_level: 'read' | 'write'
CREATE TABLE IF NOT EXISTS users.role_permissions (
    permission_id   serial PRIMARY KEY,
    role_id         integer NOT NULL REFERENCES users.roles(role_id) ON DELETE CASCADE,
    module          character varying(50) NOT NULL,
    permission_level character varying(10) NOT NULL DEFAULT 'read',
    UNIQUE (role_id, module)
);

CREATE INDEX IF NOT EXISTS idx_role_permissions_role
    ON users.role_permissions(role_id);

-- ── Record-level access overrides ────────────────────────────────────────────
-- Grants a user access to a specific record regardless of their roles.
-- item_type: 'project' | 'account' | 'stocklist'
CREATE TABLE IF NOT EXISTS users.user_item_permissions (
    permission_id    bigserial PRIMARY KEY,
    user_id          bigint  NOT NULL REFERENCES users.users(id) ON DELETE CASCADE,
    item_type        character varying(20) NOT NULL,
    item_id          integer NOT NULL,
    permission_level character varying(10) NOT NULL DEFAULT 'read',
    granted_datetime timestamp without time zone DEFAULT now(),
    granted_by       bigint  REFERENCES users.users(id),
    UNIQUE (user_id, item_type, item_id)
);

CREATE INDEX IF NOT EXISTS idx_user_item_permissions_user
    ON users.user_item_permissions(user_id);

CREATE INDEX IF NOT EXISTS idx_user_item_permissions_item
    ON users.user_item_permissions(item_type, item_id);

-- ── Migrate legacy single-role column → user_roles junction ──────────────────
-- Copies the existing role value from users.users into user_roles.
-- Review the data first, then run this block once.
INSERT INTO users.user_roles (user_id, role_id)
SELECT id, role
FROM users.users
WHERE role IS NOT NULL
  AND role > 0
  AND EXISTS (SELECT 1 FROM users.roles r WHERE r.role_id = users.users.role)
ON CONFLICT (user_id, role_id) DO NOTHING;

-- After confirming user_roles is fully populated, drop the legacy column:
-- ALTER TABLE users.users DROP COLUMN role;
