-- Roll call / daily work log for business admins (customer dashboard)

CREATE TABLE roll_calls (
  id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  business_id  BIGINT NOT NULL,
  user_id      BIGINT NOT NULL,
  work_date    DATE NOT NULL,
  start_time   VARCHAR(5) NOT NULL,
  end_time     VARCHAR(5) NOT NULL,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT roll_calls_business_id_fkey
    FOREIGN KEY (business_id) REFERENCES businesses (id) ON DELETE CASCADE,
  CONSTRAINT roll_calls_user_id_fkey
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  CONSTRAINT roll_calls_business_user_date_unique
    UNIQUE (business_id, user_id, work_date),
  CONSTRAINT roll_calls_start_time_format
    CHECK (start_time ~ '^\d{2}:\d{2}$'),
  CONSTRAINT roll_calls_end_time_format
    CHECK (end_time ~ '^\d{2}:\d{2}$')
);

CREATE INDEX idx_roll_calls_business_id ON roll_calls (business_id);
CREATE INDEX idx_roll_calls_user_id ON roll_calls (user_id);
CREATE INDEX idx_roll_calls_business_user_date
  ON roll_calls (business_id, user_id, work_date DESC);

CREATE TRIGGER roll_calls_set_updated_at
  BEFORE UPDATE ON roll_calls
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

CREATE TABLE roll_call_tasks (
  id           BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  roll_call_id BIGINT NOT NULL,
  title        VARCHAR(500) NOT NULL,
  sort_order   INT NOT NULL DEFAULT 0,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT roll_call_tasks_roll_call_id_fkey
    FOREIGN KEY (roll_call_id) REFERENCES roll_calls (id) ON DELETE CASCADE,
  CONSTRAINT roll_call_tasks_title_nonempty
    CHECK (char_length(trim(title)) > 0)
);

CREATE INDEX idx_roll_call_tasks_roll_call_id ON roll_call_tasks (roll_call_id);

INSERT INTO permissions (name, slug, group_name, description) VALUES
  ('View roll calls',   'roll_calls.read',   'roll_calls', 'View roll call / work day logs'),
  ('Create roll calls', 'roll_calls.create', 'roll_calls', 'Create roll call / work day logs'),
  ('Update roll calls', 'roll_calls.update', 'roll_calls', 'Update roll call / work day logs'),
  ('Delete roll calls', 'roll_calls.delete', 'roll_calls', 'Delete roll call / work day logs')
ON CONFLICT (slug) DO NOTHING;

INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id
FROM roles r
JOIN permissions p ON p.slug LIKE 'roll_calls.%'
WHERE r.slug IN ('business_owner', 'owner', 'admin')
ON CONFLICT DO NOTHING;