30 lines
1.3 KiB
SQL
30 lines
1.3 KiB
SQL
-- Migration: Add manual_feed_sack_entries table
|
|
-- Description: Store manual feed sack count entries separate from IOT camera data
|
|
-- Date: 2026-04-19
|
|
|
|
CREATE TABLE IF NOT EXISTS manual_feed_sack_entries (
|
|
id SERIAL PRIMARY KEY,
|
|
cycle_id VARCHAR(255) NOT NULL,
|
|
day INTEGER NOT NULL,
|
|
date DATE NOT NULL,
|
|
camera_name VARCHAR(255) NOT NULL,
|
|
count INTEGER NOT NULL CHECK(count >= 0),
|
|
created_by VARCHAR(255),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (cycle_id) REFERENCES cycles(id) ON DELETE CASCADE,
|
|
UNIQUE(cycle_id, day, camera_name)
|
|
);
|
|
|
|
-- Create indexes for faster lookups
|
|
CREATE INDEX IF NOT EXISTS idx_manual_feed_sack_cycle ON manual_feed_sack_entries(cycle_id);
|
|
CREATE INDEX IF NOT EXISTS idx_manual_feed_sack_day ON manual_feed_sack_entries(day);
|
|
CREATE INDEX IF NOT EXISTS idx_manual_feed_sack_camera ON manual_feed_sack_entries(camera_name);
|
|
|
|
-- Trigger for automatic updated_at timestamp
|
|
DROP TRIGGER IF EXISTS update_manual_feed_sack_entries_updated_at ON manual_feed_sack_entries;
|
|
CREATE TRIGGER update_manual_feed_sack_entries_updated_at
|
|
BEFORE UPDATE ON manual_feed_sack_entries
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|