23 lines
849 B
SQL
23 lines
849 B
SQL
-- Migration: Simplify chicken counting to a single upsert table
|
|
-- Date: 2026-03-05
|
|
|
|
CREATE TABLE IF NOT EXISTS chicken_counting (
|
|
id SERIAL PRIMARY KEY,
|
|
date DATE NOT NULL,
|
|
kandang_id INTEGER NOT NULL REFERENCES kandangs(id) ON DELETE CASCADE,
|
|
filename TEXT,
|
|
total_count INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE(date, kandang_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chicken_counting_date ON chicken_counting(date);
|
|
CREATE INDEX IF NOT EXISTS idx_chicken_counting_kandang_id ON chicken_counting(kandang_id);
|
|
|
|
-- Auto-update updated_at trigger
|
|
CREATE TRIGGER update_chicken_counting_updated_at
|
|
BEFORE UPDATE ON chicken_counting
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|