-- Raw PHP Device Backup + Billing schema (MySQL)


CREATE TABLE IF NOT EXISTS tenants (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  contact_name VARCHAR(190) NULL,
  phone VARCHAR(64) NULL,
  status ENUM('ACTIVE','SUSPENDED_UNPAID','DISABLED') NOT NULL DEFAULT 'ACTIVE',
  suspended_reason TEXT NULL,
  suspended_at DATETIME NULL,
  enabled_at DATETIME NULL,

  allow_admin_device_delete TINYINT(1) NOT NULL DEFAULT 0,
  allow_admin_backup_delete TINYINT(1) NOT NULL DEFAULT 0,
  purge_files_on_device_delete TINYINT(1) NOT NULL DEFAULT 1,
  allow_admin_manual_backup TINYINT(1) NOT NULL DEFAULT 1,

  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS settings (
  id INT AUTO_INCREMENT PRIMARY KEY,
  scope ENUM('GLOBAL','TENANT') NOT NULL,
  tenant_id INT NULL,
  k VARCHAR(190) NOT NULL,
  v TEXT NOT NULL,
  UNIQUE KEY uniq_scope_key (scope, tenant_id, k),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS pricing_slabs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  min_devices INT NOT NULL,
  max_devices INT NOT NULL,
  monthly_rate INT NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS billing_snapshots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  snapshot_for_period_ym CHAR(7) NOT NULL, -- YYYY-MM (next month)
  snapshot_at DATETIME NOT NULL,
  device_count INT NOT NULL,
  slab_id INT NULL,
  rate_applied INT NOT NULL,
  note_json TEXT NULL,
  UNIQUE KEY uniq_tenant_period (tenant_id, snapshot_for_period_ym),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (slab_id) REFERENCES pricing_slabs(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS invoices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  period_ym CHAR(7) NOT NULL,
  invoice_date DATE NOT NULL,
  due_date DATE NOT NULL,
  status ENUM('UNPAID','PAID','VOID') NOT NULL DEFAULT 'UNPAID',
  total_amount INT NOT NULL DEFAULT 0,
  last_paid_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by_user_id INT NULL,
  UNIQUE KEY uniq_tenant_period (tenant_id, period_ym),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (created_by_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS invoice_items (
  id INT AUTO_INCREMENT PRIMARY KEY,
  invoice_id INT NOT NULL,
  description VARCHAR(255) NOT NULL,
  qty INT NOT NULL DEFAULT 1,
  unit_price INT NOT NULL DEFAULT 0,
  line_total INT NOT NULL DEFAULT 0,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  invoice_id INT NOT NULL,
  provider ENUM('bkash') NOT NULL DEFAULT 'bkash',
  trx_id VARCHAR(190) NULL,
  amount INT NOT NULL,
  status ENUM('INIT','SUCCESS','FAILED') NOT NULL DEFAULT 'INIT',
  paid_at DATETIME NULL,
  raw_response_json TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS backups (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NOT NULL,
  device_id INT NOT NULL,
  trigger_source ENUM('manual','schedule','on_device_add') NOT NULL,
  status ENUM('pending','running','success','failed') NOT NULL DEFAULT 'pending',
  file_path TEXT NULL,
  file_size BIGINT NULL,
  sha256 CHAR(64) NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  duration_sec INT NULL,
  error_text TEXT NULL,
  scheduled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (device_id) REFERENCES devices(id) ON DELETE CASCADE,
  INDEX idx_pending (status, scheduled_at),
  INDEX idx_device_time (device_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NULL,
  user_id INT NULL,
  type VARCHAR(64) NOT NULL,
  title VARCHAR(190) NOT NULL,
  body TEXT NOT NULL,
  link_url TEXT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS audit_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NULL,
  actor_user_id INT NULL,
  action_key VARCHAR(128) NOT NULL,
  entity_type VARCHAR(64) NULL,
  entity_id INT NULL,
  ip VARCHAR(64) NULL,
  user_agent VARCHAR(255) NULL,
  meta_json TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
  FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS telegram_configs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant_id INT NULL,
  bot_token_enc TEXT NOT NULL,
  chat_id VARCHAR(64) NOT NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  notify_backup_success TINYINT(1) NOT NULL DEFAULT 1,
  notify_backup_failed TINYINT(1) NOT NULL DEFAULT 1,
  notify_billing TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_tenant (tenant_id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seed pricing slabs (example)
INSERT INTO pricing_slabs (min_devices,max_devices,monthly_rate,is_active) VALUES
(1,5,1000,1),
(6,10,1500,1),
(11,20,2000,1),
(21,50,3000,1);



-- Seed global site settings (Sweet Online)
INSERT INTO settings (scope, tenant_id, k, v) VALUES
('GLOBAL', NULL, 'site_name', 'Sweet Online'),
('GLOBAL', NULL, 'support_email', 'support@apzone.top'),
('GLOBAL', NULL, 'support_phone', '+8801XXXXXXXXX'),
('GLOBAL', NULL, 'support_address', 'Dhaka, Bangladesh')
ON DUPLICATE KEY UPDATE v=VALUES(v);
