-- =========================
-- Drop tables (optional)
-- =========================
DROP TABLE IF EXISTS passwords CASCADE;
DROP TABLE IF EXISTS vaults CASCADE;
DROP TABLE IF EXISTS users CASCADE;
DROP TABLE IF EXISTS refresh_tokens CASCADE;

-- =========================
-- Users
-- =========================
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL UNIQUE,
    password TEXT NOT NULL
);

-- =========================
-- JWT
-- =========================
CREATE TABLE refresh_tokens (
    token_hash VARCHAR(255) PRIMARY KEY,
    user_id INT REFERENCES users(id) ON DELETE CASCADE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


-- =========================
-- Vaults
-- Each vault belongs to one user
-- A user can have zero or many vaults
-- =========================
CREATE TABLE vaults (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    userid BIGINT NOT NULL,
    CONSTRAINT fk_vaults_user
        FOREIGN KEY (userid)
        REFERENCES users(id)
        ON DELETE CASCADE
);

-- =========================
-- Password Entries
-- Each password belongs to one vault
-- =========================
CREATE TABLE passwords (    
    id BIGSERIAL PRIMARY KEY,
    vaultid BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    url TEXT,
    username VARCHAR(255),
    password TEXT NOT NULL,
    iv VARCHAR(255) NOT NULL,
    tag VARCHAR(255),
    notes TEXT,
    CONSTRAINT fk_passwords_vault
        FOREIGN KEY (vaultid)
        REFERENCES vaults(id)
        ON DELETE CASCADE
);

-- =========================
-- Indexes (recommended)
-- =========================
CREATE INDEX idx_vaults_userid ON vaults(userid);
CREATE INDEX idx_passwords_vaultid ON passwords(vaultid);
CREATE INDEX idx_passwords_tag ON passwords(tag);
