schema.sql

-- 1. Uživatelé (s bezpečným hashem)
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    email       TEXT UNIQUE NOT NULL,
    name        TEXT NOT NULL,
    password    TEXT NOT NULL, -- Bcrypt hash
    plan        TEXT NOT NULL DEFAULT 'free',
    stripe_cid  TEXT,
    api_key     TEXT,
    is_verified BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- 2. Sessions (HttpOnly cookie tokeny)
CREATE TABLE sessions (
    token       TEXT PRIMARY KEY,
    user_id     BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at  TIMESTAMPTZ NOT NULL
);

-- 3. Faktury
CREATE TABLE invoices (
    id           BIGSERIAL PRIMARY KEY,
    user_id      BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    filename     TEXT NOT NULL,
    vendor       TEXT,
    inv_number   TEXT,
    inv_date     TEXT,
    due_date     TEXT,
    amount       NUMERIC(12,2),
    vat_amount   NUMERIC(12,2),
    total        NUMERIC(12,2),
    currency     TEXT DEFAULT 'USD',
    status       TEXT DEFAULT 'done',
    is_duplicate BOOLEAN DEFAULT FALSE,
    confidence   NUMERIC(4,3),
    raw_json     JSONB,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

-- 4. Zapnutí Row-Level Security (RLS)
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- 5. Politiky: Uživatel vidí a upravuje POUZE svá data
CREATE POLICY "Users can view own profile" ON users FOR SELECT USING (auth.uid() = id);
CREATE POLICY "Users can update own profile" ON users FOR UPDATE USING (auth.uid() = id);

CREATE POLICY "Users can view own invoices" ON invoices FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY "Users can insert own invoices" ON invoices FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY "Users can delete own invoices" ON invoices FOR DELETE USING (auth.uid() = user_id);

CREATE INDEX idx_invoices_user_month ON invoices(user_id, created_at);