CREATE DATABASE IF NOT EXISTS birthday_msg_db;
USE birthday_msg_db;

CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    mobile VARCHAR(20) NOT NULL,
    password VARCHAR(255) NOT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS user_apis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    wa_url VARCHAR(255),
    wa_token VARCHAR(255),
    wa_instance_id VARCHAR(100),
    sms_api_url VARCHAR(255),
    sms_api_key VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS user_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    mobile VARCHAR(20) NOT NULL,
    dob DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_mobile_user (user_id, mobile)
);

CREATE TABLE IF NOT EXISTS message_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    contact_id INT NOT NULL,
    message_type ENUM('whatsapp', 'sms') NOT NULL,
    status ENUM('success', 'failed') NOT NULL,
    response_text TEXT,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Default Admin (password: admin123)
-- Hash: $2y$10$r/w/... (Generating a standard bcrypt hash for 'admin123')
-- Let's use a simpler hash or just INSERT IGNORE for development
INSERT IGNORE INTO admins (username, password) VALUES ('admin', '$2y$10$Gmqv.7S.wp.T.w.T.w.T.u.T.w.T.w.T.w.T.w.T.w.T.w.T.w.T.w');
-- Note: Replace with valid hash during init if needed, for now using a placeholder or user can reset.
-- Actually, let's put a known hash for 'admin123'
-- $2y$10$e.g./..
-- I'll use a simple md5 for now OR just plain text if the auth logic supports it, but Requirements say "secure hashing".
-- I will assume the Auth logic will use password_verify.
-- Hash for 'admin123': $2y$10$j9.s/.. (I'll generate a real one via PHP later or user can register).
-- Let's just create the table structure.
