Documentation

-- ── Works (portfolio gallery) ──────────────────
CREATE TABLE IF NOT EXISTS works (
    id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT        NOT NULL,
    description TEXT        NOT NULL DEFAULT '',
    category    TEXT        NOT NULL DEFAULT 'Website',
    tech        TEXT        NOT NULL DEFAULT '',
    image_url   TEXT        NOT NULL DEFAULT '',
    link        TEXT        NOT NULL DEFAULT '',
    sort_order  INTEGER     NOT NULL DEFAULT 0,
    visible     BOOLEAN     NOT NULL DEFAULT TRUE,
    gallery     JSONB       NOT NULL DEFAULT '[]',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Projects (case-study pages) ─────────────────
CREATE TABLE IF NOT EXISTS projects (
    id          UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    title       TEXT        NOT NULL,
    description TEXT        NOT NULL DEFAULT '',
    category    TEXT        NOT NULL DEFAULT 'Website',
    tech        TEXT        NOT NULL DEFAULT '',
    image_url   TEXT        NOT NULL DEFAULT '',
    link        TEXT        NOT NULL DEFAULT '',
    sort_order  INTEGER     NOT NULL DEFAULT 0,
    visible     BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Inquiries (contact form) ─────────────────────
CREATE TABLE IF NOT EXISTS inquiries (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    name       TEXT        NOT NULL,
    email      TEXT        NOT NULL DEFAULT '',
    message    TEXT        NOT NULL DEFAULT '',
    ip         TEXT        NOT NULL DEFAULT '',
    read       BOOLEAN     NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Reviews (testimonials) ───────────────────────
CREATE TABLE IF NOT EXISTS reviews (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    author     TEXT        NOT NULL,
    role       TEXT        NOT NULL DEFAULT '',
    company    TEXT        NOT NULL DEFAULT '',
    content    TEXT        NOT NULL,
    rating     INTEGER     NOT NULL DEFAULT 5,
    avatar     TEXT        NOT NULL DEFAULT '',
    approved   BOOLEAN     NOT NULL DEFAULT FALSE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Visits (page analytics) ─────────────────────
CREATE TABLE IF NOT EXISTS visits (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    page       TEXT        NOT NULL DEFAULT '/',
    referrer   TEXT        NOT NULL DEFAULT '',
    userAgent  TEXT        NOT NULL DEFAULT '',
    ip         TEXT        NOT NULL DEFAULT '',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Ad Clicks (link tracking) ───────────────────
CREATE TABLE IF NOT EXISTS ad_clicks (
    id         UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    label      TEXT        NOT NULL,
    url        TEXT        NOT NULL,
    page       TEXT        NOT NULL DEFAULT '/',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Searches (search tracking) ──────────────────
CREATE TABLE IF NOT EXISTS searches (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    query         TEXT        NOT NULL UNIQUE,
    count         INTEGER     NOT NULL DEFAULT 1,
    last_searched TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ── Settings (social links, config) ─────────────
CREATE TABLE IF NOT EXISTS settings (
    key   TEXT  PRIMARY KEY,
    value JSONB NOT NULL DEFAULT '{}'
);

INSERT INTO settings (key, value) VALUES
    ('social_links', '{"instagram":"","github":"","linkedin":"","twitter":"","youtube":"","behance":"","dribbble":"","whatsapp":""}'),
    ('coming_soon',  'false')
ON CONFLICT (key) DO NOTHING;

-- ── Row Level Security ───────────────────────────
ALTER TABLE works     ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects  ENABLE ROW LEVEL SECURITY;
ALTER TABLE inquiries ENABLE ROW LEVEL SECURITY;
ALTER TABLE reviews   ENABLE ROW LEVEL SECURITY;
ALTER TABLE visits    ENABLE ROW LEVEL SECURITY;
ALTER TABLE ad_clicks ENABLE ROW LEVEL SECURITY;
ALTER TABLE searches  ENABLE ROW LEVEL SECURITY;
ALTER TABLE settings  ENABLE ROW LEVEL SECURITY;

CREATE POLICY "works_public_read"       ON works      FOR SELECT USING (visible = TRUE);
CREATE POLICY "works_service_all"       ON works      USING (auth.role() = 'service_role');
CREATE POLICY "projects_public_read"    ON projects   FOR SELECT USING (visible = TRUE);
CREATE POLICY "projects_service_all"    ON projects   USING (auth.role() = 'service_role');
CREATE POLICY "inquiries_public_insert" ON inquiries  FOR INSERT WITH CHECK (TRUE);
CREATE POLICY "inquiries_service_all"   ON inquiries  USING (auth.role() = 'service_role');
CREATE POLICY "reviews_public_read"     ON reviews    FOR SELECT USING (approved = TRUE);
CREATE POLICY "reviews_service_all"     ON reviews    USING (auth.role() = 'service_role');
CREATE POLICY "visits_public_insert"    ON visits     FOR INSERT WITH CHECK (TRUE);
CREATE POLICY "visits_service_all"      ON visits     USING (auth.role() = 'service_role');
CREATE POLICY "ad_clicks_public_insert" ON ad_clicks  FOR INSERT WITH CHECK (TRUE);
CREATE POLICY "ad_clicks_service_all"   ON ad_clicks  USING (auth.role() = 'service_role');
CREATE POLICY "searches_public_insert"  ON searches   FOR INSERT WITH CHECK (TRUE);
CREATE POLICY "searches_service_all"    ON searches   USING (auth.role() = 'service_role');
CREATE POLICY "settings_public_read"    ON settings   FOR SELECT USING (TRUE);
CREATE POLICY "settings_service_all"    ON settings   USING (auth.role() = 'service_role');

-- ── Storage Bucket (for image uploads) ──────────
INSERT INTO storage.buckets (id, name, public)
VALUES ('project-photos', 'project-photos', TRUE)
ON CONFLICT (id) DO NOTHING;

CREATE POLICY "photos_public_read"    ON storage.objects FOR SELECT USING (bucket_id = 'project-photos');
CREATE POLICY "photos_service_upload" ON storage.objects FOR INSERT WITH CHECK (bucket_id = 'project-photos' AND auth.role() = 'service_role');
CREATE POLICY "photos_service_delete" ON storage.objects FOR DELETE USING (bucket_id = 'project-photos' AND auth.role() = 'service_role');