-- 015: Social module v1 (owner decision 2026-10-29)
-- Publishing: controlled by the central qualitative secretariat secretary, who
-- assigns the social.publish permission to trusted users (admin role management).
-- Reading: any authenticated, active user. Moderation: author or social.publish.
-- Notifications: per-user rows; a new post notifies all active accounts.

-- +up
INSERT INTO permissions (code, module, action, description, is_sensitive)
VALUES ('social.publish', 'social', 'publish', 'نشر وتحرير منشورات المنصة الاجتماعية', TRUE)
ON CONFLICT (code) DO NOTHING;

-- the central qualitative secretariat secretaries start with publishing rights
INSERT INTO role_permissions (role_id, permission_id)
SELECT r.id, p.id FROM roles r, permissions p
WHERE r.code IN ('media_secretary','training_secretary','girls_secretary','students_secretary','local_councils_secretary')
  AND p.code = 'social.publish'
ON CONFLICT DO NOTHING;

CREATE TABLE social_posts (
  id BIGSERIAL PRIMARY KEY,
  author_id BIGINT NOT NULL REFERENCES users(id),
  body TEXT NOT NULL CHECK (length(btrim(body)) BETWEEN 3 AND 4000),
  status VARCHAR(20) NOT NULL DEFAULT 'published' CHECK (status IN ('published','removed')),
  removed_by BIGINT NULL REFERENCES users(id),
  removal_reason VARCHAR(500) NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  removed_at TIMESTAMPTZ NULL
);
CREATE INDEX idx_social_posts_feed ON social_posts(status, created_at DESC);

CREATE TABLE notifications (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  type VARCHAR(30) NOT NULL,
  title_ar VARCHAR(200) NOT NULL,
  body_ar VARCHAR(500) NULL,
  link VARCHAR(200) NULL,
  read_at TIMESTAMPTZ NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX idx_notifications_user ON notifications(user_id, read_at);

-- +down
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS social_posts;
DELETE FROM role_permissions WHERE permission_id IN (SELECT id FROM permissions WHERE code = 'social.publish');
DELETE FROM permissions WHERE code = 'social.publish';