-- 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();