Divisional Secretariat Kattankudy

இலங்கை சனநாயக சோசலிசக் குடியரசு

பிரதேச செயலகம் காத்தான்குடி

The Kattankudy Divisional Secretariat is the primary government administrative office serving the people of Kattankudy under the Ministry of Public Administration, Home Affairs, Provincial Councils and Local Government.

🏛️ Department Direct Lines

-- 1. NEWS
CREATE TABLE IF NOT EXISTS news (
  id         BIGSERIAL PRIMARY KEY,
  type       TEXT NOT NULL DEFAULT 'Official Notice',
  title      TEXT NOT NULL,
  content    TEXT NOT NULL,
  author     TEXT DEFAULT 'DS Office',
  priority   TEXT NOT NULL DEFAULT 'normal',
  pub_date   DATE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. APPOINTMENTS
CREATE TABLE IF NOT EXISTS appointments (
  id         TEXT PRIMARY KEY,
  name       TEXT NOT NULL,
  nic        TEXT NOT NULL,
  service    TEXT NOT NULL,
  appt_date  DATE NOT NULL,
  time_slot  TEXT NOT NULL,
  email      TEXT NOT NULL,
  phone      TEXT,
  address    TEXT,
  language   TEXT DEFAULT 'Tamil',
  notes      TEXT,
  status     TEXT NOT NULL DEFAULT 'pending',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 3. COMPLAINTS
CREATE TABLE IF NOT EXISTS complaints (
  id           TEXT PRIMARY KEY,
  name         TEXT NOT NULL,
  nic          TEXT NOT NULL,
  category     TEXT NOT NULL,
  subject      TEXT NOT NULL,
  details      TEXT NOT NULL,
  email        TEXT NOT NULL,
  phone        TEXT,
  status       TEXT NOT NULL DEFAULT 'open',
  submitted_at DATE NOT NULL DEFAULT CURRENT_DATE,
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 4. REPLIES (appointments & complaints)
CREATE TABLE IF NOT EXISTS replies (
  id              BIGSERIAL PRIMARY KEY,
  ref_type        TEXT NOT NULL,
  ref_id          TEXT NOT NULL,
  message         TEXT NOT NULL,
  replied_by      TEXT NOT NULL DEFAULT 'Admin',
  replied_by_role TEXT DEFAULT 'admin',
  created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS idx_replies_ref
  ON replies (ref_type, ref_id);

-- 5. RLS POLICIES
ALTER TABLE news ENABLE ROW LEVEL SECURITY;
CREATE POLICY "news_all" ON news FOR ALL USING (true) WITH CHECK (true);
ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "appt_all" ON appointments FOR ALL USING (true) WITH CHECK (true);
ALTER TABLE complaints ENABLE ROW LEVEL SECURITY;
CREATE POLICY "comp_all" ON complaints FOR ALL USING (true) WITH CHECK (true);
ALTER TABLE replies ENABLE ROW LEVEL SECURITY;
CREATE POLICY "repl_all" ON replies FOR ALL USING (true) WITH CHECK (true);