--
-- apps table
--
DROP TABLE IF EXISTS analytics.apps;

CREATE TABLE analytics.apps (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    name TEXT NOT NULL,
    app_key TEXT NOT NULL,
    user_id UUID,
    meta JSONB
);

INSERT INTO analytics.apps (name, app_key)
VALUES ('FileFillet', '2a800e87-6bc2-4569-9e55-3fa6ca1c54e0');

ALTER TABLE analytics.apps ENABLE ROW LEVEL SECURITY;

CREATE POLICY apps_select_for_insert
ON analytics.apps
AS PERMISSIVE
FOR SELECT
TO authenticated
USING (
    (select auth.jwt() -> 'user_metadata' ->> 'app_key') = app_key
);

CREATE POLICY apps_select_for_user
ON analytics.apps
AS PERMISSIVE
FOR SELECT
TO authenticated
USING (
    (select auth.uid()) = user_id
);

--
-- installations table
--
DROP TABLE IF EXISTS analytics.installations;

CREATE TABLE analytics.installations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    opted_in BOOL NOT NULL DEFAULT false,
    dev BOOL DEFAULT false,
    locale_country TEXT,
    locale_region TEXT,
    locale_language TEXT,
    locale_currency TEXT,
    device_name TEXT,
    device_type TEXT,
    os_name TEXT,
    os_version TEXT,
    app_version TEXT,
    meta JSONB
);

ALTER TABLE analytics.installations ENABLE ROW LEVEL SECURITY;

CREATE POLICY installations_insert_only_if_app_exists
ON analytics.installations
AS PERMISSIVE
FOR INSERT
TO AUTHENTICATED
WITH CHECK (
    analytics.check_api_key()
);

CREATE POLICY installations_update_only_if_app_exists
ON analytics.installations
AS PERMISSIVE
FOR UPDATE
TO AUTHENTICATED
USING (
    analytics.check_api_key()
);

create policy "installations_select_only_if_app_exists"
on "analytics"."installations"
as PERMISSIVE
for SELECT
to authenticated
using (
  analytics.check_api_key()
);

--
-- events table
--
DROP TABLE IF EXISTS analytics.events;

CREATE TABLE analytics.events (
    id SERIAL PRIMARY KEY,
    occurred_at TIMESTAMP NOT NULL DEFAULT now(),
    installation_id TEXT NOT NULL,
    name TEXT NOT NULL,
    meta jsonb
);

ALTER TABLE analytics.events ENABLE ROW LEVEL SECURITY;

CREATE POLICY events_insert_only_if_app_exists
ON analytics.events
AS PERMISSIVE
FOR INSERT
TO AUTHENTICATED
WITH CHECK (
    analytics.check_api_key()
);

--
-- app sessions table
--
DROP TABLE IF EXISTS analytics.app_sessions;

CREATE TABLE analytics.app_sessions (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    installation_id TEXT not null,
    start_time TIMESTAMP NOT NULL,
    start_reason TEXT not null,
    end_time TIMESTAMP NOT NULL,
    end_reason TEXT not null,
    duration BIGINT GENERATED ALWAYS AS (
        EXTRACT(EPOCH FROM (end_time - start_time)) * 1000
    ) STORED,
    meta jsonb
);

ALTER TABLE analytics.app_sessions ENABLE ROW LEVEL SECURITY;

CREATE POLICY app_sessions_insert_only_if_app_exists
ON analytics.app_sessions
AS PERMISSIVE
FOR INSERT
TO AUTHENTICATED
WITH CHECK (
    analytics.check_api_key()
);

--
-- window sessions table
--
DROP TABLE IF EXISTS analytics.window_sessions;

CREATE TABLE analytics.window_sessions (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now(),
    installation_id TEXT not null,
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP NOT NULL,
    duration BIGINT GENERATED ALWAYS AS (
        EXTRACT(EPOCH FROM (end_time - start_time)) * 1000
    ) STORED,
    meta jsonb
);

ALTER TABLE analytics.window_sessions ENABLE ROW LEVEL SECURITY;

CREATE POLICY window_sessions_insert_only_if_app_exists
ON analytics.window_sessions
AS PERMISSIVE
FOR INSERT
TO AUTHENTICATED
WITH CHECK (
    analytics.check_api_key()
);

--
-- config table
--
DROP TABLE IF EXISTS analytics.configs;

CREATE TABLE analytics.configs (
    id SERIAL PRIMARY KEY,
    updated_at TIMESTAMP NOT NULL DEFAULT now(),
    installation_id TEXT not null,
    config JSONB,
    meta JSONB
);

ALTER TABLE analytics.configs ENABLE ROW LEVEL SECURITY;

CREATE POLICY configs_insert_only_if_app_exists
ON analytics.configs
AS PERMISSIVE
FOR INSERT
TO AUTHENTICATED
WITH CHECK (
    analytics.check_api_key()
);
