112 lines
5.8 KiB
SQL
112 lines
5.8 KiB
SQL
-- =============================================================================
|
|
-- SQL Changes for WellDrySense Product
|
|
-- Description: Creates a dedicated 'jobs' table for the Water Damage Mitigation
|
|
-- product without altering any existing tables. This script is
|
|
-- idempotent and safe to run multiple times.
|
|
-- =============================================================================
|
|
|
|
-- 1. Create the new 'jobs' table for the Water Damage Mitigation product.
|
|
CREATE TABLE IF NOT EXISTS public.jobs (
|
|
job_id SERIAL PRIMARY KEY,
|
|
customer_name TEXT,
|
|
mitigation_person_id INTEGER REFERENCES public.person_details(user_id) ON DELETE SET NULL,
|
|
key_person_name TEXT,
|
|
key_person_mobile TEXT,
|
|
key_person_email TEXT,
|
|
address_street TEXT,
|
|
address_city TEXT,
|
|
address_zip TEXT,
|
|
address_state TEXT,
|
|
address_country TEXT,
|
|
lat REAL,
|
|
lng REAL,
|
|
date_from TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
date_to TIMESTAMP WITH TIME ZONE,
|
|
job_status TEXT DEFAULT 'Active' NOT NULL, -- Can be 'Active', 'Stopped', 'Archived'
|
|
devices JSONB, -- Stores an array of device objects, e.g., [{"mac": "...", "location": "..."}]
|
|
alerts_config JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
user_edit INTEGER REFERENCES public.person_details(user_id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Add comments for clarity
|
|
COMMENT ON TABLE public.jobs IS 'Stores job information for the WellDrySense water damage mitigation product.';
|
|
COMMENT ON COLUMN public.jobs.devices IS 'A JSON array of device objects assigned to this job. Structure: [{"mac": "AA:BB:CC:DD:EE:FF", "location": "Kitchen Under Sink"}, ...].';
|
|
COMMENT ON COLUMN public.jobs.job_status IS 'The current lifecycle state of the job: Active, Stopped, or Archived.';
|
|
COMMENT ON COLUMN public.jobs.mitigation_person_id IS 'The user from person_details responsible for this job.';
|
|
|
|
-- 2. Add indexes for efficient querying.
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_job_status ON public.jobs(job_status);
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_mitigation_person_id ON public.jobs(mitigation_person_id);
|
|
|
|
-- 3. Add a GIN index for efficient searching within the 'devices' JSONB column.
|
|
-- This is crucial for the job_available_devices API.
|
|
CREATE INDEX IF NOT EXISTS idx_jobs_devices_gin ON public.jobs USING GIN (devices);
|
|
|
|
-- 4. Grant necessary permissions to the application user.
|
|
GRANT ALL ON TABLE public.jobs TO well_app;
|
|
GRANT USAGE, SELECT ON SEQUENCE jobs_job_id_seq TO well_app;
|
|
|
|
-- --- End of Script ---
|
|
|
|
-- -- =============================================================================
|
|
-- -- SQL Changes for WellDrySense Product (Version 1.1 - Idempotent)
|
|
-- -- File: drysense_db_update.sql
|
|
-- -- Description: Creates and configures the 'jobs' table for the DrySense product.
|
|
-- -- =============================================================================
|
|
|
|
-- -- 1. Create the 'jobs' table if it does not already exist.
|
|
-- CREATE TABLE IF NOT EXISTS public.jobs (
|
|
-- job_id SERIAL PRIMARY KEY,
|
|
-- customer_name TEXT,
|
|
-- mitigation_person_id INTEGER REFERENCES public.person_details(user_id),
|
|
-- key_person_name TEXT,
|
|
-- key_person_mobile TEXT,
|
|
-- key_person_email TEXT,
|
|
-- address_street TEXT,
|
|
-- address_city TEXT,
|
|
-- address_zip TEXT,
|
|
-- address_state TEXT,
|
|
-- address_country TEXT,
|
|
-- lat REAL,
|
|
-- lng REAL,
|
|
-- date_from TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
-- date_to TIMESTAMP WITH TIME ZONE,
|
|
-- job_status TEXT DEFAULT 'Active' NOT NULL, -- e.g., 'Active', 'Stopped', 'Archived'
|
|
-- devices TEXT, -- Storing as a JSON string of device MACs
|
|
-- alerts_config JSONB, -- Store alert settings as a JSON object
|
|
-- created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
-- user_edit INTEGER REFERENCES public.person_details(user_id)
|
|
-- );
|
|
|
|
-- -- Add comments to the table and columns for documentation purposes.
|
|
-- -- These commands are safe to re-run.
|
|
-- COMMENT ON TABLE public.jobs IS 'Stores job information for the WellDrySense water damage mitigation product.';
|
|
-- COMMENT ON COLUMN public.jobs.customer_name IS 'The name of the client for whom the job is being done.';
|
|
-- COMMENT ON COLUMN public.jobs.mitigation_person_id IS 'The user (from person_details) responsible for the job.';
|
|
-- COMMENT ON COLUMN public.jobs.key_person_name IS 'The name of the primary contact person at the client site.';
|
|
-- COMMENT ON COLUMN public.jobs.job_status IS 'Lifecycle status of the job: Active, Stopped, Archived.';
|
|
-- COMMENT ON COLUMN public.jobs.date_to IS 'The date the job was stopped or archived.';
|
|
-- COMMENT ON COLUMN public.jobs.devices IS 'A JSON array of device MAC addresses assigned to this job.';
|
|
-- COMMENT ON COLUMN public.jobs.alerts_config IS 'JSON object storing alert thresholds, e.g., {"temp_abs_high": 30, "hum_rel_above": 15}.';
|
|
|
|
-- -- 2. Add an index for performance if it does not already exist.
|
|
-- CREATE INDEX IF NOT EXISTS idx_jobs_job_status ON public.jobs(job_status);
|
|
|
|
-- -- 3. Rename the 'description' column to 'location_name' in the 'devices' table if it exists.
|
|
-- -- This DO block ensures the ALTER command only runs if the 'description' column exists.
|
|
-- DO $$
|
|
-- BEGIN
|
|
-- IF EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='devices' AND column_name='description') THEN
|
|
-- ALTER TABLE public.devices RENAME COLUMN description TO location_name;
|
|
-- COMMENT ON COLUMN public.devices.location_name IS 'User-defined name for the specific location of the device on a job site (e.g., Kitchen Under Sink).';
|
|
-- END IF;
|
|
-- END $$;
|
|
|
|
-- -- 4. Grant necessary permissions to the application user 'well_app'.
|
|
-- -- These commands are safe to re-run.
|
|
-- GRANT ALL ON TABLE public.jobs TO well_app;
|
|
-- GRANT USAGE, SELECT ON SEQUENCE jobs_job_id_seq TO well_app;
|
|
|
|
-- -- --- End of Script ---
|