You cannot select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
86 lines
3.2 KiB
PL/PgSQL
86 lines
3.2 KiB
PL/PgSQL
-- Holidays table (праздничные дни Беларуси)
|
|
CREATE TABLE IF NOT EXISTS holidays (
|
|
id SERIAL PRIMARY KEY,
|
|
date DATE NOT NULL UNIQUE,
|
|
name VARCHAR(200) NOT NULL,
|
|
is_recurring BOOLEAN DEFAULT false, -- повторяется каждый год
|
|
recurring_month INTEGER, -- месяц для повторяющихся
|
|
recurring_day INTEGER, -- день для повторяющихся
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Index for fast date lookup
|
|
CREATE INDEX IF NOT EXISTS idx_holidays_date ON holidays(date);
|
|
|
|
-- Insert Belarus official holidays (recurring)
|
|
-- Эти праздники повторяются каждый год в одну и ту же дату
|
|
INSERT INTO holidays (date, name, is_recurring, recurring_month, recurring_day) VALUES
|
|
('2025-01-01', 'Новый год', true, 1, 1),
|
|
('2025-01-07', 'Рождество (православное)', true, 1, 7),
|
|
('2025-03-08', 'День женщин', true, 3, 8),
|
|
('2025-05-01', 'Праздник труда', true, 5, 1),
|
|
('2025-05-09', 'День Победы', true, 5, 9),
|
|
('2025-07-03', 'День Независимости', true, 7, 3),
|
|
('2025-11-07', 'День Октябрьской революции', true, 11, 7),
|
|
('2025-12-25', 'Рождество (католическое)', true, 12, 25)
|
|
ON CONFLICT (date) DO NOTHING;
|
|
|
|
-- Радуница (переходящая дата) - нужно добавлять вручную каждый год
|
|
-- 2025: 29 апреля
|
|
-- 2026: 21 апреля
|
|
INSERT INTO holidays (date, name, is_recurring, recurring_month, recurring_day) VALUES
|
|
('2025-04-29', 'Радуница', false, NULL, NULL),
|
|
('2026-04-21', 'Радуница', false, NULL, NULL)
|
|
ON CONFLICT (date) DO NOTHING;
|
|
|
|
-- Function to check if a date is a holiday
|
|
CREATE OR REPLACE FUNCTION is_holiday(check_date DATE)
|
|
RETURNS BOOLEAN AS $$
|
|
BEGIN
|
|
-- Check exact date match
|
|
IF EXISTS (SELECT 1 FROM holidays WHERE date = check_date) THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
|
|
-- Check recurring holidays (for future years)
|
|
IF EXISTS (
|
|
SELECT 1 FROM holidays
|
|
WHERE is_recurring = true
|
|
AND recurring_month = EXTRACT(MONTH FROM check_date)
|
|
AND recurring_day = EXTRACT(DAY FROM check_date)
|
|
) THEN
|
|
RETURN TRUE;
|
|
END IF;
|
|
|
|
RETURN FALSE;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Function to get schedule type for a date
|
|
-- Returns: 'weekday', 'saturday', 'sunday', or 'holiday'
|
|
CREATE OR REPLACE FUNCTION get_schedule_type(check_date DATE)
|
|
RETURNS VARCHAR(20) AS $$
|
|
DECLARE
|
|
day_of_week INTEGER;
|
|
BEGIN
|
|
-- Check if it's a holiday first (holidays use sunday schedule)
|
|
IF is_holiday(check_date) THEN
|
|
RETURN 'holiday';
|
|
END IF;
|
|
|
|
-- Get day of week (0 = Sunday, 6 = Saturday)
|
|
day_of_week := EXTRACT(DOW FROM check_date);
|
|
|
|
IF day_of_week = 0 THEN
|
|
RETURN 'sunday';
|
|
ELSIF day_of_week = 6 THEN
|
|
RETURN 'saturday';
|
|
ELSE
|
|
RETURN 'weekday';
|
|
END IF;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Add sync trigger for holidays
|
|
CREATE TRIGGER sync_holidays AFTER INSERT OR UPDATE OR DELETE ON holidays
|
|
FOR EACH ROW EXECUTE FUNCTION log_sync_changes(); |