CREATE TABLE users (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(190) NOT NULL UNIQUE,
 password_hash VARCHAR(255) NOT NULL,
 role ENUM('admin','user') NOT NULL DEFAULT 'user',
 status ENUM('active','suspended') NOT NULL DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE whatsapp_accounts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(120) NOT NULL,
 phone_number_id VARCHAR(100) NOT NULL,
 business_account_id VARCHAR(100) DEFAULT NULL,
 access_token TEXT NOT NULL,
 status ENUM('active','inactive') DEFAULT 'active',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contacts (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(150) DEFAULT '',
 phone VARCHAR(40) NOT NULL,
 email VARCHAR(190) DEFAULT NULL,
 tags VARCHAR(255) DEFAULT '',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY user_phone (user_id,phone),
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE templates (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(120) NOT NULL,
 language VARCHAR(20) NOT NULL DEFAULT 'en_US',
 category VARCHAR(40) DEFAULT 'UTILITY',
 meta_template_id VARCHAR(100) DEFAULT NULL,
 status VARCHAR(40) DEFAULT 'DRAFT',
 body TEXT NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE campaigns (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 whatsapp_account_id BIGINT UNSIGNED NOT NULL,
 name VARCHAR(150) NOT NULL,
 template_id BIGINT UNSIGNED DEFAULT NULL,
 message_body TEXT,
 scheduled_at DATETIME DEFAULT NULL,
 status ENUM('draft','queued','running','completed','failed','cancelled') DEFAULT 'draft',
 total INT UNSIGNED DEFAULT 0,
 sent INT UNSIGNED DEFAULT 0,
 delivered INT UNSIGNED DEFAULT 0,
 read_count INT UNSIGNED DEFAULT 0,
 failed INT UNSIGNED DEFAULT 0,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (whatsapp_account_id) REFERENCES whatsapp_accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE campaign_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 campaign_id BIGINT UNSIGNED NOT NULL,
 contact_id BIGINT UNSIGNED NOT NULL,
 status ENUM('pending','sent','delivered','read','failed') DEFAULT 'pending',
 meta_message_id VARCHAR(150) DEFAULT NULL,
 error_text TEXT DEFAULT NULL,
 sent_at DATETIME DEFAULT NULL,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 UNIQUE KEY campaign_contact (campaign_id,contact_id),
 FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE,
 FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE inbound_messages (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 whatsapp_account_id BIGINT UNSIGNED DEFAULT NULL,
 wa_message_id VARCHAR(150) DEFAULT NULL,
 sender_phone VARCHAR(50),
 message_type VARCHAR(50),
 message_text TEXT,
 payload JSON,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX wa_idx (wa_message_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE plans (
 id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(80) NOT NULL,
 monthly_price DECIMAL(10,2) NOT NULL DEFAULT 0,
 monthly_messages INT UNSIGNED NOT NULL DEFAULT 100,
 features JSON DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE subscriptions (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 plan_id INT UNSIGNED NOT NULL,
 status ENUM('active','cancelled','expired') DEFAULT 'active',
 starts_at DATETIME DEFAULT CURRENT_TIMESTAMP,
 ends_at DATETIME DEFAULT NULL,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
 FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE usage_logs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 user_id BIGINT UNSIGNED NOT NULL,
 action VARCHAR(80) NOT NULL,
 meta JSON DEFAULT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plans(name,monthly_price,monthly_messages,features) VALUES
('Free',0,100,'["1 WhatsApp account","100 messages/month"]'),
('Starter',499,5000,'["2 WhatsApp accounts","5,000 messages/month","Campaigns"]'),
('Pro',1499,25000,'["5 WhatsApp accounts","25,000 messages/month","Automation","Analytics"]');