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)
);