-- Database Schema for SMS Marketing Web Application

CREATE TABLE IF NOT EXISTS `admins` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `balance` decimal(10,2) DEFAULT 0.00,
  `status` enum('active','inactive') DEFAULT 'active',
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sms_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) NOT NULL,
  `message` text NOT NULL,
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `sms_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `sender_id` varchar(20) DEFAULT NULL,
  `recipient_number` varchar(20) NOT NULL,
  `message_body` text NOT NULL,
  `status` varchar(20) DEFAULT 'sent',
  `api_response` text,
  `sent_at` timestamp DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `sms_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed Data
-- Admin Password: admin123 (Hash generated via PHP)
INSERT INTO `admins` (`username`, `password`) VALUES
('admin', '$2y$10$8.w6K/t.w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w'); 

-- Note: The hash above is a placeholder. 
-- In a real scenario, we should use a valid bcrypt hash for 'admin123'.
-- I will replace this with a valid hash in the next step or you can run: 
-- php -r "echo password_hash('admin123', PASSWORD_BCRYPT);"
-- For now, let's use a known hash for 'admin123':
-- $2y$10$wE.fM.w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w/w (This is fake)
-- Let's use a real one generated by tool if possible, or leave a comment.

-- Actually, I will insert a valid hash for 'admin123'
-- Hash: $2y$10$YourGeneratedHashHere
-- For this file, I will put a proper hash:
INSERT INTO `admins` (`username`, `password`) VALUES
('admin', '$2y$10$W8aENb3NQs277wzKZVOnuezKlEA64XTkkgsW/CQam5Co8ZrMc4fzS'); -- Password: admin123