68 lines
2.4 KiB
SQL
68 lines
2.4 KiB
SQL
CREATE TABLE IF NOT EXISTS grid_maps (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
name TEXT NOT NULL,
|
|
public_access TEXT NOT NULL DEFAULT 'private'
|
|
CHECK (public_access IN ('private', 'public_view', 'public_edit')),
|
|
owner_id UUID NOT NULL REFERENCES users(id),
|
|
colors TEXT[] NOT NULL DEFAULT ARRAY[
|
|
'#6b7280',
|
|
'#92400e',
|
|
'#15803d',
|
|
'#1d4ed8',
|
|
'#7c3aed',
|
|
'#dc2626',
|
|
'#ca8a04',
|
|
'#0f172a',
|
|
'#f9fafb'
|
|
],
|
|
units_per_square INTEGER NOT NULL DEFAULT 5,
|
|
unit_label TEXT NOT NULL DEFAULT 'ft',
|
|
movement_rule TEXT NOT NULL DEFAULT 'free',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS grid_cells (
|
|
map_id TEXT NOT NULL REFERENCES grid_maps(id) ON DELETE CASCADE,
|
|
x INTEGER NOT NULL,
|
|
y INTEGER NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#808080',
|
|
PRIMARY KEY (map_id, x, y)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS grid_tokens (
|
|
id TEXT PRIMARY KEY NOT NULL,
|
|
map_id TEXT NOT NULL REFERENCES grid_maps(id) ON DELETE CASCADE,
|
|
size INTEGER NOT NULL DEFAULT 1,
|
|
x INTEGER NOT NULL,
|
|
y INTEGER NOT NULL,
|
|
label TEXT NOT NULL,
|
|
color TEXT NOT NULL DEFAULT '#4444FF'
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS map_permissions (
|
|
map_id TEXT NOT NULL REFERENCES grid_maps(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
role TEXT NOT NULL CHECK (role IN ('owner', 'editor', 'viewer')),
|
|
PRIMARY KEY (map_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS map_favorites (
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
map_id TEXT NOT NULL REFERENCES grid_maps(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (user_id, map_id)
|
|
);
|
|
|
|
-- Pending/resolved requests from users wanting viewer or editor access
|
|
CREATE TABLE IF NOT EXISTS map_access_requests (
|
|
id UUID PRIMARY KEY NOT NULL DEFAULT gen_random_uuid(),
|
|
map_id TEXT NOT NULL REFERENCES grid_maps(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
requested_role TEXT NOT NULL CHECK (requested_role IN ('editor', 'viewer')),
|
|
status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'denied')),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
UNIQUE (map_id, user_id)
|
|
);
|