84 lines
3.0 KiB
PL/PgSQL
84 lines
3.0 KiB
PL/PgSQL
-- Align development schema with the tables expected by the current API.
|
|
-- Earlier project changes were stored in ad hoc SQL files, so Flyway never
|
|
-- applied them to fresh dev databases.
|
|
|
|
-- Support multiple lawn sections per application plan.
|
|
CREATE TABLE IF NOT EXISTS application_plan_sections (
|
|
id SERIAL PRIMARY KEY,
|
|
plan_id INTEGER NOT NULL REFERENCES application_plans(id) ON DELETE CASCADE,
|
|
lawn_section_id INTEGER NOT NULL REFERENCES lawn_sections(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(plan_id, lawn_section_id)
|
|
);
|
|
|
|
INSERT INTO application_plan_sections (plan_id, lawn_section_id)
|
|
SELECT ap.id, ap.lawn_section_id
|
|
FROM application_plans ap
|
|
WHERE ap.lawn_section_id IS NOT NULL
|
|
ON CONFLICT (plan_id, lawn_section_id) DO NOTHING;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_application_plan_sections_plan_id
|
|
ON application_plan_sections(plan_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_application_plan_sections_section_id
|
|
ON application_plan_sections(lawn_section_id);
|
|
|
|
-- Add missing spreader support fields on user equipment.
|
|
ALTER TABLE user_equipment
|
|
ADD COLUMN IF NOT EXISTS brand VARCHAR(100),
|
|
ADD COLUMN IF NOT EXISTS notes TEXT;
|
|
|
|
UPDATE user_equipment
|
|
SET brand = manufacturer
|
|
WHERE brand IS NULL AND manufacturer IS NOT NULL;
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_equipment_brand_model
|
|
ON user_equipment(brand, model);
|
|
|
|
-- Add product spreader settings used by product/admin/application routes.
|
|
CREATE TABLE IF NOT EXISTS product_spreader_settings (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
|
|
user_product_id INTEGER REFERENCES user_products(id) ON DELETE CASCADE,
|
|
equipment_id INTEGER REFERENCES user_equipment(id) ON DELETE CASCADE,
|
|
spreader_brand VARCHAR(100),
|
|
spreader_model VARCHAR(100),
|
|
setting_value VARCHAR(20) NOT NULL,
|
|
rate_description VARCHAR(200),
|
|
notes TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
CHECK (
|
|
(product_id IS NOT NULL AND user_product_id IS NULL) OR
|
|
(product_id IS NULL AND user_product_id IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_product_spreader_settings_product
|
|
ON product_spreader_settings(product_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_product_spreader_settings_user_product
|
|
ON product_spreader_settings(user_product_id);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_product_spreader_settings_brand
|
|
ON product_spreader_settings(spreader_brand);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_product_spreader_settings_equipment
|
|
ON product_spreader_settings(equipment_id);
|
|
|
|
CREATE OR REPLACE FUNCTION update_product_spreader_settings_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
DROP TRIGGER IF EXISTS update_product_spreader_settings_updated_at_trigger
|
|
ON product_spreader_settings;
|
|
|
|
CREATE TRIGGER update_product_spreader_settings_updated_at_trigger
|
|
BEFORE UPDATE ON product_spreader_settings
|
|
FOR EACH ROW
|
|
EXECUTE PROCEDURE update_product_spreader_settings_updated_at();
|