CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS public.device_activations (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    reseller_id uuid NULL,
    mac_address text NOT NULL,
    duration_type text NOT NULL DEFAULT 'year',
    credits_used numeric NOT NULL DEFAULT 0,
    activated_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NULL,
    status text NOT NULL DEFAULT 'active',
    notes text NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    device_id text NULL,
    activation_code text NULL,
    playlist_url text NULL,
    epg_url text NULL,
    customer_email text NULL,
    customer_name text NULL,
    bound_at timestamptz NULL
);

CREATE TABLE IF NOT EXISTS public.niceflix_devices (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    mac_address text NOT NULL,
    device_id text NOT NULL,
    device_key text NOT NULL,
    customer_email text NULL,
    customer_name text NULL,
    status text NOT NULL DEFAULT 'trial',
    trial_started_at timestamptz NOT NULL DEFAULT now(),
    expires_at timestamptz NOT NULL DEFAULT (now() + interval '7 days'),
    activation_code text NULL,
    reseller_id uuid NULL,
    last_seen_at timestamptz NOT NULL DEFAULT now(),
    last_ip text NULL,
    user_agent text NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS public.niceflix_playlists (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    device_id uuid NOT NULL REFERENCES public.niceflix_devices(id) ON DELETE CASCADE,
    name text NOT NULL,
    playlist_url text NOT NULL,
    epg_url text NULL,
    is_protected boolean NOT NULL DEFAULT false,
    source text NOT NULL DEFAULT 'client',
    display_order integer NOT NULL DEFAULT 0,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS ux_niceflix_devices_mac_clean
    ON public.niceflix_devices (upper(regexp_replace(mac_address, '[^0-9A-Fa-f]', '', 'g')));

CREATE INDEX IF NOT EXISTS ix_device_activations_mac_clean
    ON public.device_activations (upper(regexp_replace(mac_address, '[^0-9A-Fa-f]', '', 'g')));

CREATE INDEX IF NOT EXISTS ix_niceflix_playlists_device
    ON public.niceflix_playlists (device_id, display_order, created_at);