BEGIN;

-- Keep existing Google accounts and sessions valid during the expansion.
ALTER TABLE project_accounts ALTER COLUMN google_subject DROP NOT NULL;
ALTER TABLE project_accounts
    ADD COLUMN username text,
    ADD COLUMN display_name text NOT NULL DEFAULT '',
    ADD COLUMN avatar_url text,
    ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();
CREATE UNIQUE INDEX project_account_username ON project_accounts(project, lower(username))
    WHERE username IS NOT NULL;

ALTER TABLE oauth_attempts
 ADD COLUMN provider text NOT NULL DEFAULT 'google' CHECK (provider IN ('google','slack')),
 ADD COLUMN oidc_nonce text;

CREATE TABLE account_identities (
    project text NOT NULL,
    provider text NOT NULL CHECK (provider IN ('google', 'slack')),
    subject text NOT NULL,
    account_id uuid NOT NULL,
    PRIMARY KEY (project, provider, subject),
    UNIQUE (project, provider, account_id),
    FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
);
INSERT INTO account_identities(project, provider, subject, account_id)
SELECT project, 'google', google_subject, id FROM project_accounts WHERE google_subject IS NOT NULL;

CREATE TABLE email_credentials (
    project text NOT NULL,
    email text NOT NULL CHECK (email = lower(email)),
    account_id uuid NOT NULL,
    password_hash text NOT NULL,
    verified_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, email),
    UNIQUE (project, account_id),
    FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
);
-- Registration is promoted to an account only after the single-use email check.
CREATE TABLE email_registrations (
    project text NOT NULL,
    email text NOT NULL,
    password_hash text NOT NULL,
    token_hash text NOT NULL UNIQUE,
    expires_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, email)
);
CREATE TABLE email_outbox (
    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project text NOT NULL,
    recipient text NOT NULL,
    subject text NOT NULL,
    body text NOT NULL,
    attempts integer NOT NULL DEFAULT 0,
    available_at timestamptz NOT NULL DEFAULT now(),
    lease_until timestamptz,
    lease_id uuid,
    expires_at timestamptz NOT NULL,
    sent_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX email_outbox_pending ON email_outbox(available_at) WHERE sent_at IS NULL;
CREATE TABLE auth_rate_limits (
    key_hash text PRIMARY KEY,
    attempts integer NOT NULL CHECK (attempts > 0),
    expires_at timestamptz NOT NULL
);

CREATE TABLE onboarding_responses (
    project text NOT NULL,
    account_id uuid NOT NULL,
    answers jsonb NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(answers) = 'object'),
    result jsonb NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(result) = 'object'),
    completed_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, account_id),
    FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
);

CREATE TABLE workspaces (
    project text NOT NULL,
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    name text NOT NULL CHECK (length(trim(name)) BETWEEN 1 AND 160),
    owner_id uuid NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, id),
    FOREIGN KEY (project, owner_id) REFERENCES project_accounts
);
CREATE TABLE workspace_members (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    account_id uuid NOT NULL,
    role text NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
    joined_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, workspace_id, account_id),
    FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE,
    FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
);
CREATE INDEX workspace_members_account ON workspace_members(project, account_id);

CREATE TABLE materials (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    kind text NOT NULL CHECK (kind IN ('text', 'article', 'image', 'board', 'card')),
    title text NOT NULL CHECK (length(title) <= 300),
    description text NOT NULL DEFAULT '' CHECK (length(description) <= 10000),
    content jsonb NOT NULL DEFAULT '{}' CHECK (jsonb_typeof(content) = 'object'),
    version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
    created_by uuid NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    modified_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, workspace_id, id),
    FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE,
    FOREIGN KEY (project, created_by) REFERENCES project_accounts
);
CREATE INDEX material_modified ON materials(project, workspace_id, modified_at DESC, id);
CREATE INDEX material_created ON materials(project, workspace_id, created_at DESC, id);
CREATE INDEX material_alphabetical ON materials(project, workspace_id, lower(title), id);
CREATE TABLE material_opens (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    material_id uuid NOT NULL,
    account_id uuid NOT NULL,
    opened_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, workspace_id, material_id, account_id),
    FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
    FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
);
CREATE INDEX material_opens_account ON material_opens(project, workspace_id, account_id, opened_at DESC);

CREATE TABLE tags (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    name text NOT NULL CHECK (length(trim(name)) BETWEEN 1 AND 80),
    color text NOT NULL DEFAULT '',
    PRIMARY KEY (project, workspace_id, id),
    FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE
);
CREATE UNIQUE INDEX tag_name ON tags(project, workspace_id, lower(name));
CREATE TABLE material_tags (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    material_id uuid NOT NULL,
    tag_id uuid NOT NULL,
    PRIMARY KEY (project, workspace_id, material_id, tag_id),
    FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
    FOREIGN KEY (project, workspace_id, tag_id) REFERENCES tags ON DELETE CASCADE
);
CREATE INDEX material_tags_filter ON material_tags(project, workspace_id, tag_id, material_id);

-- A board placement references a reusable material, independently of its content.
CREATE TABLE board_layouts (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    board_id uuid NOT NULL,
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    material_id uuid NOT NULL,
    x double precision NOT NULL CHECK (x BETWEEN -1000000 AND 1000000),
    y double precision NOT NULL CHECK (y BETWEEN -1000000 AND 1000000),
    width double precision NOT NULL CHECK (width BETWEEN 1 AND 100000),
    height double precision NOT NULL CHECK (height BETWEEN 1 AND 100000),
    z_index integer NOT NULL DEFAULT 0,
    version bigint NOT NULL DEFAULT 1 CHECK (version > 0),
    PRIMARY KEY (project, workspace_id, board_id, id),
    FOREIGN KEY (project, workspace_id, board_id) REFERENCES materials ON DELETE CASCADE,
    FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
    CHECK (board_id <> material_id)
);
CREATE TABLE material_grants (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    material_id uuid NOT NULL,
    account_id uuid NOT NULL,
    role text NOT NULL CHECK (role IN ('editor', 'viewer')),
    PRIMARY KEY (project, workspace_id, material_id, account_id),
    FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
    FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
);
CREATE TABLE share_links (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    id uuid NOT NULL DEFAULT gen_random_uuid(),
    material_id uuid,
    token_hash text NOT NULL UNIQUE,
    role text NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
    created_by uuid NOT NULL,
    expires_at timestamptz NOT NULL,
    revoked_at timestamptz,
    max_uses integer CHECK (max_uses BETWEEN 1 AND 100000),
    use_count integer NOT NULL DEFAULT 0 CHECK (use_count >= 0),
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, workspace_id, id),
    FOREIGN KEY (project, workspace_id) REFERENCES workspaces ON DELETE CASCADE,
    FOREIGN KEY (project, workspace_id, material_id) REFERENCES materials ON DELETE CASCADE,
    FOREIGN KEY (project, created_by) REFERENCES project_accounts,
    CHECK (material_id IS NULL OR role <> 'admin')
);
CREATE TABLE share_redemptions (
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    link_id uuid NOT NULL,
    account_id uuid NOT NULL,
    redeemed_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (project, workspace_id, link_id, account_id),
    FOREIGN KEY (project, workspace_id, link_id) REFERENCES share_links ON DELETE CASCADE,
    FOREIGN KEY (project, account_id) REFERENCES project_accounts ON DELETE CASCADE
);
CREATE TABLE link_clicks (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project text NOT NULL,
    workspace_id uuid NOT NULL,
    link_id uuid NOT NULL,
    clicked_at timestamptz NOT NULL DEFAULT now(),
    -- Store the referring origin only, without URL paths, queries or user tokens.
    referrer_origin text NOT NULL DEFAULT '',
    FOREIGN KEY (project, workspace_id, link_id) REFERENCES share_links ON DELETE CASCADE
);
CREATE INDEX link_clicks_summary ON link_clicks(project, workspace_id, link_id, clicked_at);

COMMIT;