-- init.sql
-- Run this in your existing database (USE <your_db_name>; first)
-- NOTE: DO NOT run CREATE DATABASE if your host restricts it. Use the DB you created.

-- Use your DB:
USE piyushde_sarvatragym;

CREATE TABLE IF NOT EXISTS plans (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  duration_days INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS members (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  plan_id INT,
  plan_start DATE,
  plan_end DATE,
  is_active TINYINT(1) DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS whatsapp_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  member_id INT,
  phone VARCHAR(20),
  message TEXT,
  sent_at TIMESTAMP NULL,
  status VARCHAR(50),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL
);

INSERT INTO plans (name, duration_days, price) VALUES
('1 Month', 30, 800.00),
('3 Months', 90, 2200.00),
('6 Months', 180, 4000.00),
('1 Year', 365, 7200.00)
ON DUPLICATE KEY UPDATE name=VALUES(name);
