-- Organizations replace per-place shared access: every place belongs to an organization, and access
-- to it is determined by the caller's role/membership in that organization rather than by direct
-- ownership or one-off per-place grants (see the now-dropped place_access table, below).
CREATE TABLE IF NOT EXISTS organizations (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT NOT NULL,
	ownerId INTEGER,
	createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY(ownerId) REFERENCES users(id)
);

-- Membership roles are scoped to the organization only (independent of the platform-wide role in
-- users.role): 1 = View (read-only), 2 = Edit (manage places/readers/ACL/access groups), 3 = Admin
-- (Edit, plus manage organization membership/roles and rename/delete the organization itself).
CREATE TABLE IF NOT EXISTS organization_members (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	orgId INTEGER NOT NULL,
	userId INTEGER NOT NULL,
	role INTEGER NOT NULL DEFAULT 1,
	invitedBy INTEGER,
	createdAt DATETIME DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY(orgId) REFERENCES organizations(id),
	FOREIGN KEY(userId) REFERENCES users(id),
	FOREIGN KEY(invitedBy) REFERENCES users(id),
	UNIQUE(orgId, userId)
);

CREATE INDEX IF NOT EXISTS idx_org_members_userId ON organization_members(userId);

CREATE INDEX IF NOT EXISTS idx_org_members_orgId ON organization_members(orgId);

ALTER TABLE places ADD COLUMN orgId INTEGER REFERENCES organizations(id);

-- Auto-create a default personal organization for every existing user.
INSERT INTO organizations (name, ownerId)
SELECT 'Personal (' || username || ')', id FROM users;

-- Make every user an Admin member of their own new default organization.
INSERT INTO organization_members (orgId, userId, role, invitedBy)
SELECT organizations.id, organizations.ownerId, 3, organizations.ownerId
FROM organizations;

-- Move every personally-owned place into its owner's new default organization.
UPDATE places
SET orgId = (SELECT id FROM organizations WHERE organizations.ownerId = places.ownerId)
WHERE ownerId IS NOT NULL;

-- Carry over any existing per-place shared access grants as Edit members of the place's new
-- organization, then drop the now-unused table - organizations replace this feature entirely.
INSERT OR IGNORE INTO organization_members (orgId, userId, role, invitedBy, createdAt)
SELECT places.orgId, place_access.userId, 2, place_access.grantedBy, place_access.createdAt
FROM place_access
JOIN places ON places.id = place_access.placeId
WHERE places.orgId IS NOT NULL;

DROP TABLE IF EXISTS place_access;