-- Multi-tenant task management schema
-- All tenant-scoped tables include organization_id for query-level isolation.

CREATE TYPE user_role AS ENUM ('admin', 'member');
CREATE TYPE task_status AS ENUM ('todo', 'in_progress', 'done', 'cancelled');
CREATE TYPE log_action AS ENUM ('CREATED', 'UPDATED', 'DELETED');

CREATE TABLE organizations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  organization_id INTEGER NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
  email VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255),
  full_name VARCHAR(255) NOT NULL,
  role user_role NOT NULL DEFAULT 'member',
  oauth_provider VARCHAR(50),
  oauth_sub VARCHAR(255),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (organization_id, email)
);

-- One Google (or other OAuth) identity maps to one user row
CREATE UNIQUE INDEX users_oauth_unique ON users (oauth_provider, oauth_sub)
  WHERE oauth_sub IS NOT NULL;

CREATE TABLE tasks (
  id SERIAL PRIMARY KEY,
  organization_id INTEGER NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
  title VARCHAR(500) NOT NULL,
  description TEXT,
  status task_status NOT NULL DEFAULT 'todo',
  created_by INTEGER NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
  assigned_to INTEGER REFERENCES users (id) ON DELETE SET NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE activity_logs (
  id SERIAL PRIMARY KEY,
  organization_id INTEGER NOT NULL REFERENCES organizations (id) ON DELETE CASCADE,
  task_id INTEGER REFERENCES tasks (id) ON DELETE SET NULL,
  action log_action NOT NULL,
  performed_by INTEGER NOT NULL REFERENCES users (id) ON DELETE RESTRICT,
  metadata JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_tasks_org ON tasks (organization_id);
CREATE INDEX idx_tasks_org_created_by ON tasks (organization_id, created_by);
CREATE INDEX idx_tasks_org_status ON tasks (organization_id, status);
CREATE INDEX idx_tasks_org_assigned ON tasks (organization_id, assigned_to);
CREATE INDEX idx_logs_org ON activity_logs (organization_id);
CREATE INDEX idx_logs_task ON activity_logs (task_id);
CREATE INDEX idx_users_org ON users (organization_id);
