begin;

set search_path to public;

-- 2026-05-25 日历能力升级脚本
-- 说明：
-- 1. 创建并补齐业务日历表 business_calendar_day。
-- 2. 用于“同步当年日历”功能落库存储节假日、法定休息日、调休补班、工作日定义。
-- 3. 可重复执行。

create table if not exists business_calendar_day (
  id bigint generated by default as identity primary key,
  calendar_date date not null,
  calendar_year integer not null,
  day_of_week integer not null,
  day_type_code integer not null default 0,
  day_type varchar(32) not null default 'WORKDAY',
  day_name varchar(64) not null,
  holiday_name varchar(100),
  holiday_target varchar(100),
  holiday_wage integer,
  is_weekend boolean not null default false,
  is_rest_day boolean not null default false,
  is_workday boolean not null default true,
  is_holiday boolean not null default false,
  is_legal_holiday boolean not null default false,
  is_makeup_workday boolean not null default false,
  source varchar(50) not null default 'timor',
  synced_by_user_id bigint,
  synced_at timestamptz not null default now(),
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now()
);

alter table if exists business_calendar_day
  add column if not exists calendar_year integer not null default 1970,
  add column if not exists day_of_week integer not null default 1,
  add column if not exists day_type_code integer not null default 0,
  add column if not exists day_type varchar(32) not null default 'WORKDAY',
  add column if not exists day_name varchar(64) not null default '周一',
  add column if not exists holiday_name varchar(100),
  add column if not exists holiday_target varchar(100),
  add column if not exists holiday_wage integer,
  add column if not exists is_weekend boolean not null default false,
  add column if not exists is_rest_day boolean not null default false,
  add column if not exists is_workday boolean not null default true,
  add column if not exists is_holiday boolean not null default false,
  add column if not exists is_legal_holiday boolean not null default false,
  add column if not exists is_makeup_workday boolean not null default false,
  add column if not exists source varchar(50) not null default 'timor',
  add column if not exists synced_by_user_id bigint,
  add column if not exists synced_at timestamptz not null default now();

do $$
begin
  if exists (
    select 1
    from information_schema.tables
    where table_schema = current_schema()
      and table_name = 'business_calendar_day'
  ) and not exists (
    select 1
    from pg_constraint
    where conname = 'uk_business_calendar_day_date'
  ) then
    alter table business_calendar_day
      add constraint uk_business_calendar_day_date unique (calendar_date);
  end if;
end
$$;

create index if not exists idx_business_calendar_day_year_date
  on business_calendar_day (calendar_year asc, calendar_date asc);

create index if not exists idx_business_calendar_day_type
  on business_calendar_day (day_type asc, calendar_date asc);

comment on table business_calendar_day is '业务日历定义表';
comment on column business_calendar_day.calendar_date is '日历日期';
comment on column business_calendar_day.calendar_year is '年份';
comment on column business_calendar_day.day_of_week is '星期，1-7 对应周一到周日';
comment on column business_calendar_day.day_type_code is '日历类型编码：0工作日、1周末休息、2节假日、3调休补班';
comment on column business_calendar_day.day_type is '日历类型';
comment on column business_calendar_day.day_name is '日历展示名称';
comment on column business_calendar_day.holiday_name is '节假日名称';
comment on column business_calendar_day.holiday_target is '调休关联节日';
comment on column business_calendar_day.holiday_wage is '节假日工资倍数';
comment on column business_calendar_day.is_weekend is '是否周末';
comment on column business_calendar_day.is_rest_day is '是否休息日';
comment on column business_calendar_day.is_workday is '是否工作日';
comment on column business_calendar_day.is_holiday is '是否节假日';
comment on column business_calendar_day.is_legal_holiday is '是否法定节假日';
comment on column business_calendar_day.is_makeup_workday is '是否法定调休补班';
comment on column business_calendar_day.source is '日历来源';
comment on column business_calendar_day.synced_by_user_id is '最近同步人ID';
comment on column business_calendar_day.synced_at is '最近同步时间';

commit;