CREATE TYPE role AS ENUM ('USER', 'MODERATOR', 'ADMIN');
CREATE TYPE spotlight_type AS ENUM ('SPONSOR', 'WEEKLY_CODE');

CREATE TABLE IF NOT EXISTS users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role role NOT NULL DEFAULT 'USER',
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS spotlight (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type spotlight_type NOT NULL,
    title VARCHAR(255) NOT NULL,
    code_body TEXT,
    link TEXT,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    token UUID UNIQUE NOT NULL,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    actor_id UUID REFERENCES users(id) ON DELETE SET NULL,
    action VARCHAR(120) NOT NULL,
    target VARCHAR(255),
    description TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO spotlight (type, title, code_body, link, is_active)
SELECT 'WEEKLY_CODE', 'Haftanın Kodu', 'if ($user["role"] !== "ADMIN") { http_response_code(403); }', NULL, TRUE
WHERE NOT EXISTS (SELECT 1 FROM spotlight);
