35 lines
1.3 KiB
SQL
35 lines
1.3 KiB
SQL
-- Watering plans for guiding sprinkler placement and timing
|
|
CREATE TABLE IF NOT EXISTS watering_plans (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
|
|
property_id INTEGER REFERENCES properties(id) ON DELETE CASCADE,
|
|
name VARCHAR(255) NOT NULL,
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS watering_plan_points (
|
|
id SERIAL PRIMARY KEY,
|
|
plan_id INTEGER REFERENCES watering_plans(id) ON DELETE CASCADE,
|
|
sequence INTEGER NOT NULL,
|
|
lat DECIMAL(10,8) NOT NULL,
|
|
lng DECIMAL(11,8) NOT NULL,
|
|
duration_minutes INTEGER DEFAULT 0,
|
|
sprinkler_mount VARCHAR(20) CHECK (sprinkler_mount IN ('in_ground','above_ground')),
|
|
sprinkler_head_type VARCHAR(30) CHECK (sprinkler_head_type IN ('rotor_impact','oscillating_fan','spray_fixed','drip')),
|
|
sprinkler_gpm DECIMAL(8,2),
|
|
sprinkler_throw_feet DECIMAL(8,2),
|
|
sprinkler_degrees INTEGER,
|
|
sprinkler_length_feet DECIMAL(8,2),
|
|
sprinkler_width_feet DECIMAL(8,2),
|
|
coverage_sqft DECIMAL(10,2),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_watering_plans_user ON watering_plans(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_watering_points_plan ON watering_plan_points(plan_id);
|
|
|
|
SELECT 'Watering plans tables created' as migration_status;
|
|
|