blinds_express/oldVersion.sql

--
-- PostgreSQL database dump
--

-- Dumped from database version 17.4
-- Dumped by pg_dump version 17.4

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET transaction_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: delete_group_if_empty(); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION public.delete_group_if_empty() RETURNS trigger
    LANGUAGE plpgsql
    AS $$
BEGIN
  -- Check if the group has any members remaining in the junction table
  IF NOT EXISTS (SELECT 1 FROM group_peripherals WHERE group_id = OLD.group_id) THEN
    -- If no members exist, delete the group itself
    DELETE FROM groups WHERE id = OLD.group_id;
  END IF;
  RETURN OLD;
END;
$$;


ALTER FUNCTION public.delete_group_if_empty() OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: device_tokens; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE UNLOGGED TABLE public.device_tokens (
    device_id integer NOT NULL,
    token text NOT NULL,
    connected boolean DEFAULT false,
    socket character varying(255)
);


ALTER TABLE public.device_tokens OWNER TO "BlindMasterApp";

--
-- Name: devices; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE TABLE public.devices (
    id integer NOT NULL,
    user_id integer NOT NULL,
    device_name text NOT NULL,
    max_ports integer DEFAULT 4 NOT NULL
);


ALTER TABLE public.devices OWNER TO "BlindMasterApp";

--
-- Name: COLUMN devices.max_ports; Type: COMMENT; Schema: public; Owner: BlindMasterApp
--

COMMENT ON COLUMN public.devices.max_ports IS 'Maximum number of ports/peripherals this device supports (1 for C6, 4 for multi-port)';


--
-- Name: devices_id_seq; Type: SEQUENCE; Schema: public; Owner: BlindMasterApp
--

CREATE SEQUENCE public.devices_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.devices_id_seq OWNER TO "BlindMasterApp";

--
-- Name: devices_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: BlindMasterApp
--

ALTER SEQUENCE public.devices_id_seq OWNED BY public.devices.id;


--
-- Name: group_peripherals; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE TABLE public.group_peripherals (
    group_id integer NOT NULL,
    peripheral_id integer NOT NULL
);


ALTER TABLE public.group_peripherals OWNER TO "BlindMasterApp";

--
-- Name: groups; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE TABLE public.groups (
    id integer NOT NULL,
    user_id integer NOT NULL,
    name text NOT NULL,
    created_at timestamp without time zone DEFAULT now()
);


ALTER TABLE public.groups OWNER TO "BlindMasterApp";

--
-- Name: groups_id_seq; Type: SEQUENCE; Schema: public; Owner: BlindMasterApp
--

CREATE SEQUENCE public.groups_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.groups_id_seq OWNER TO "BlindMasterApp";

--
-- Name: groups_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: BlindMasterApp
--

ALTER SEQUENCE public.groups_id_seq OWNED BY public.groups.id;


--
-- Name: peripherals; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE TABLE public.peripherals (
    id integer NOT NULL,
    device_id integer NOT NULL,
    peripheral_number integer NOT NULL,
    peripheral_name text NOT NULL,
    calibrated boolean DEFAULT false,
    await_calib boolean DEFAULT false,
    last_pos integer DEFAULT 0,
    last_set timestamp without time zone,
    user_id integer NOT NULL,
    CONSTRAINT peripherals_last_pos_check CHECK (((last_pos >= 0) AND (last_pos <= 10)))
)
WITH (autovacuum_vacuum_scale_factor='0.02');


ALTER TABLE public.peripherals OWNER TO "BlindMasterApp";

--
-- Name: peripherals_id_seq; Type: SEQUENCE; Schema: public; Owner: BlindMasterApp
--

CREATE SEQUENCE public.peripherals_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.peripherals_id_seq OWNER TO "BlindMasterApp";

--
-- Name: peripherals_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: BlindMasterApp
--

ALTER SEQUENCE public.peripherals_id_seq OWNED BY public.peripherals.id;


--
-- Name: schedules; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE TABLE public.schedules (
    id integer NOT NULL,
    user_id integer NOT NULL,
    schedule_type character varying(50) NOT NULL,
    peripheral_id integer,
    device_id integer,
    peripheral_number integer,
    group_id integer,
    target_position integer NOT NULL,
    cron_expression character varying(100) NOT NULL,
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    cron_minute character varying(50) NOT NULL,
    cron_hour character varying(50) NOT NULL,
    cron_days character varying(50) NOT NULL,
    CONSTRAINT peripheral_or_group CHECK (((((schedule_type)::text = 'peripheral'::text) AND (peripheral_id IS NOT NULL) AND (group_id IS NULL)) OR (((schedule_type)::text = 'group'::text) AND (group_id IS NOT NULL) AND (peripheral_id IS NULL)))),
    CONSTRAINT schedule_type_check CHECK (((schedule_type)::text = ANY ((ARRAY['peripheral'::character varying, 'group'::character varying])::text[])))
);


ALTER TABLE public.schedules OWNER TO "BlindMasterApp";

--
-- Name: schedules_id_seq; Type: SEQUENCE; Schema: public; Owner: BlindMasterApp
--

CREATE SEQUENCE public.schedules_id_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE public.schedules_id_seq OWNER TO "BlindMasterApp";

--
-- Name: schedules_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: BlindMasterApp
--

ALTER SEQUENCE public.schedules_id_seq OWNED BY public.schedules.id;


--
-- Name: user_tokens; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE UNLOGGED TABLE public.user_tokens (
    user_id integer NOT NULL,
    token text NOT NULL,
    connected boolean DEFAULT false,
    socket character varying(255)
);


ALTER TABLE public.user_tokens OWNER TO "BlindMasterApp";

--
-- Name: users; Type: TABLE; Schema: public; Owner: BlindMasterApp
--

CREATE TABLE public.users (
    id integer NOT NULL,
    email text NOT NULL,
    password_hash_string text NOT NULL,
    name text
);


ALTER TABLE public.users OWNER TO "BlindMasterApp";

--
-- Name: users_id_seq; Type: SEQUENCE; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE public.users ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.users_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);


--
-- Name: devices id; Type: DEFAULT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.devices ALTER COLUMN id SET DEFAULT nextval('public.devices_id_seq'::regclass);


--
-- Name: groups id; Type: DEFAULT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.groups ALTER COLUMN id SET DEFAULT nextval('public.groups_id_seq'::regclass);


--
-- Name: peripherals id; Type: DEFAULT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.peripherals ALTER COLUMN id SET DEFAULT nextval('public.peripherals_id_seq'::regclass);


--
-- Name: schedules id; Type: DEFAULT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.schedules ALTER COLUMN id SET DEFAULT nextval('public.schedules_id_seq'::regclass);


--
-- Data for Name: device_tokens; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.device_tokens (device_id, token, connected, socket) FROM stdin;
\.


--
-- Data for Name: devices; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.devices (id, user_id, device_name, max_ports) FROM stdin;
44	9	biob	1
\.


--
-- Data for Name: group_peripherals; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.group_peripherals (group_id, peripheral_id) FROM stdin;
6	32
\.


--
-- Data for Name: groups; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.groups (id, user_id, name, created_at) FROM stdin;
6	9	joij	2026-01-02 12:47:17.736192
\.


--
-- Data for Name: peripherals; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.peripherals (id, device_id, peripheral_number, peripheral_name, calibrated, await_calib, last_pos, last_set, user_id) FROM stdin;
32	44	1	Main Blind	t	f	10	2026-01-04 20:38:00.034	9
\.


--
-- Data for Name: schedules; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.schedules (id, user_id, schedule_type, peripheral_id, device_id, peripheral_number, group_id, target_position, cron_expression, created_at, updated_at, cron_minute, cron_hour, cron_days) FROM stdin;
16	9	group	\N	\N	\N	6	0	0 12 * * 5	2026-01-04 14:46:06.294036	2026-01-04 14:46:06.294036	0	12	5
20	9	peripheral	32	44	1	\N	6	57 14 * * 0,3,6	2026-01-04 14:56:24.45907	2026-01-04 14:56:24.45907	57	14	0,3,6
\.


--
-- Data for Name: user_tokens; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.user_tokens (user_id, token, connected, socket) FROM stdin;
\.


--
-- Data for Name: users; Type: TABLE DATA; Schema: public; Owner: BlindMasterApp
--

COPY public.users (id, email, password_hash_string, name) FROM stdin;
7	test@test.argon	$argon2id$v=19$m=65536,t=3,p=4$eSNAQlKShPixO68C/bpXrA$pKCHfEkNi7gyTY+HRvk1KRvS1yor53R4m2IbyAwK7qM	testArgon
8	pulipakaaditya@gmail.com	$argon2id$v=19$m=65536,t=3,p=4$NpfdCsdtcD7bf3Osb3zE/w$Asw6cu5iJB575u/riWWfuXtM9Uw7Gi4sGcJpBuMxIb0	adi
9	n@n.n	$argon2id$v=19$m=65536,t=3,p=4$CmQTWJbTIX5kQ6EgAhuhGw$029IVkydHw+iA8V1z34r9JFiKkzUSNWlwEvsA02sTPI	n
\.


--
-- Name: devices_id_seq; Type: SEQUENCE SET; Schema: public; Owner: BlindMasterApp
--

SELECT pg_catalog.setval('public.devices_id_seq', 44, true);


--
-- Name: groups_id_seq; Type: SEQUENCE SET; Schema: public; Owner: BlindMasterApp
--

SELECT pg_catalog.setval('public.groups_id_seq', 6, true);


--
-- Name: peripherals_id_seq; Type: SEQUENCE SET; Schema: public; Owner: BlindMasterApp
--

SELECT pg_catalog.setval('public.peripherals_id_seq', 32, true);


--
-- Name: schedules_id_seq; Type: SEQUENCE SET; Schema: public; Owner: BlindMasterApp
--

SELECT pg_catalog.setval('public.schedules_id_seq', 20, true);


--
-- Name: users_id_seq; Type: SEQUENCE SET; Schema: public; Owner: BlindMasterApp
--

SELECT pg_catalog.setval('public.users_id_seq', 9, true);


--
-- Name: devices devices_pkey; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.devices
    ADD CONSTRAINT devices_pkey PRIMARY KEY (id);


--
-- Name: devices devices_user_id_device_name_key; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.devices
    ADD CONSTRAINT devices_user_id_device_name_key UNIQUE (user_id, device_name);


--
-- Name: group_peripherals group_peripherals_pkey; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.group_peripherals
    ADD CONSTRAINT group_peripherals_pkey PRIMARY KEY (group_id, peripheral_id);


--
-- Name: groups groups_pkey; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_pkey PRIMARY KEY (id);


--
-- Name: groups groups_user_id_name_key; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_user_id_name_key UNIQUE (user_id, name);


--
-- Name: peripherals peripherals_device_id_peripheral_name_key; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.peripherals
    ADD CONSTRAINT peripherals_device_id_peripheral_name_key UNIQUE (device_id, peripheral_name);


--
-- Name: peripherals peripherals_device_id_peripheral_number_key; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.peripherals
    ADD CONSTRAINT peripherals_device_id_peripheral_number_key UNIQUE (device_id, peripheral_number);


--
-- Name: peripherals peripherals_pkey; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.peripherals
    ADD CONSTRAINT peripherals_pkey PRIMARY KEY (id);


--
-- Name: schedules schedules_pkey; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.schedules
    ADD CONSTRAINT schedules_pkey PRIMARY KEY (id);


--
-- Name: users users_email_key; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_email_key UNIQUE (email);


--
-- Name: users users_pkey; Type: CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.users
    ADD CONSTRAINT users_pkey PRIMARY KEY (id);


--
-- Name: idx_device_tokens_active_socket; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_device_tokens_active_socket ON public.device_tokens USING btree (device_id, socket) WHERE (connected = true);


--
-- Name: idx_device_tokens_device_id_connected; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_device_tokens_device_id_connected ON public.device_tokens USING btree (device_id) WHERE (connected = true);


--
-- Name: idx_device_tokens_socket_connected; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_device_tokens_socket_connected ON public.device_tokens USING btree (socket) WHERE (connected = true);


--
-- Name: idx_device_tokens_token; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_device_tokens_token ON public.device_tokens USING btree (token);


--
-- Name: idx_devices_max_ports; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_devices_max_ports ON public.devices USING btree (max_ports);


--
-- Name: idx_devices_user_id; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_devices_user_id ON public.devices USING btree (user_id);


--
-- Name: idx_group_peripherals_group_id; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_group_peripherals_group_id ON public.group_peripherals USING btree (group_id);


--
-- Name: idx_group_peripherals_peripheral_id; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_group_peripherals_peripheral_id ON public.group_peripherals USING btree (peripheral_id);


--
-- Name: idx_groups_user_id; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_groups_user_id ON public.groups USING btree (user_id);


--
-- Name: idx_peripherals_device_id; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_peripherals_device_id ON public.peripherals USING btree (device_id);


--
-- Name: idx_peripherals_id_user; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_peripherals_id_user ON public.peripherals USING btree (id, user_id);


--
-- Name: idx_schedules_user_id; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_schedules_user_id ON public.schedules USING btree (user_id);


--
-- Name: idx_unique_group_schedule; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE UNIQUE INDEX idx_unique_group_schedule ON public.schedules USING btree (group_id, cron_expression) WHERE ((schedule_type)::text = 'group'::text);


--
-- Name: idx_unique_peripheral_schedule; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE UNIQUE INDEX idx_unique_peripheral_schedule ON public.schedules USING btree (peripheral_id, cron_expression) WHERE ((schedule_type)::text = 'peripheral'::text);


--
-- Name: idx_user_tokens_active_socket; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_user_tokens_active_socket ON public.user_tokens USING btree (user_id, socket) WHERE (connected = true);


--
-- Name: idx_user_tokens_socket_connected; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_user_tokens_socket_connected ON public.user_tokens USING btree (socket) WHERE (connected = true);


--
-- Name: idx_user_tokens_token; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE INDEX idx_user_tokens_token ON public.user_tokens USING btree (token);


--
-- Name: users_id_idx; Type: INDEX; Schema: public; Owner: BlindMasterApp
--

CREATE UNIQUE INDEX users_id_idx ON public.users USING btree (id);


--
-- Name: group_peripherals trigger_cleanup_empty_groups; Type: TRIGGER; Schema: public; Owner: BlindMasterApp
--

CREATE TRIGGER trigger_cleanup_empty_groups AFTER DELETE ON public.group_peripherals FOR EACH ROW EXECUTE FUNCTION public.delete_group_if_empty();


--
-- Name: peripherals fk_device; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.peripherals
    ADD CONSTRAINT fk_device FOREIGN KEY (device_id) REFERENCES public.devices(id) ON DELETE CASCADE;


--
-- Name: peripherals fk_user; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.peripherals
    ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: group_peripherals group_peripherals_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.group_peripherals
    ADD CONSTRAINT group_peripherals_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id) ON DELETE CASCADE;


--
-- Name: group_peripherals group_peripherals_peripheral_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.group_peripherals
    ADD CONSTRAINT group_peripherals_peripheral_id_fkey FOREIGN KEY (peripheral_id) REFERENCES public.peripherals(id) ON DELETE CASCADE;


--
-- Name: groups groups_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.groups
    ADD CONSTRAINT groups_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- Name: schedules schedules_device_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.schedules
    ADD CONSTRAINT schedules_device_id_fkey FOREIGN KEY (device_id) REFERENCES public.devices(id) ON DELETE CASCADE;


--
-- Name: schedules schedules_group_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.schedules
    ADD CONSTRAINT schedules_group_id_fkey FOREIGN KEY (group_id) REFERENCES public.groups(id) ON DELETE CASCADE;


--
-- Name: schedules schedules_peripheral_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.schedules
    ADD CONSTRAINT schedules_peripheral_id_fkey FOREIGN KEY (peripheral_id) REFERENCES public.peripherals(id) ON DELETE CASCADE;


--
-- Name: schedules schedules_user_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: BlindMasterApp
--

ALTER TABLE ONLY public.schedules
    ADD CONSTRAINT schedules_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE;


--
-- PostgreSQL database dump complete
--