Ultra File Manager

Edit: /home/u523034047/domains/eipcsindia.com/public_html/wap/meta_broadcast.sql

CREATE DATABASE IF NOT EXISTS meta_broadcast;
USE meta_broadcast;

-- admins
CREATE TABLE admin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  name VARCHAR(150),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- contacts
CREATE TABLE contacts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150),
  phone VARCHAR(30) NOT NULL,
  vars JSON NULL,
  status ENUM('active','unsubscribed') DEFAULT 'active',
  added_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- templates
CREATE TABLE templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  language_code VARCHAR(10) DEFAULT 'en',
  components TEXT NOT NULL,
  category ENUM('MARKETING','UTILITY','AUTH') DEFAULT 'MARKETING',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- broadcasts
CREATE TABLE broadcasts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200),
  template_id INT,
  total INT DEFAULT 0,
  sent INT DEFAULT 0,
  failed INT DEFAULT 0,
  status ENUM('pending','running','completed') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (template_id) REFERENCES templates(id) ON DELETE SET NULL
);

-- broadcast_items: stores per-recipient status
CREATE TABLE broadcast_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  broadcast_id INT,
  contact_id INT,
  to_phone VARCHAR(30),
  payload TEXT,
  result TEXT,
  status ENUM('queued','sent','delivered','failed','blocked','unsubscribed') DEFAULT 'queued',
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (broadcast_id) REFERENCES broadcasts(id) ON DELETE CASCADE,
  FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE SET NULL
);

-- logs
CREATE TABLE logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  level VARCHAR(20),
  message TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- seed admin (change password)
INSERT INTO admin (username, password, name) VALUES ('admin', MD5('admin123'), 'Super Admin');

Ukuran: 2.0 KB