database_setup.sql

-- SANARE DATABASE SETUP
-- Run this in MySQL before starting the application

CREATE DATABASE IF NOT EXISTS sanare;
USE sanare;

-- Users table (End Users)
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(15),
    dob VARCHAR(20),
    gender VARCHAR(10),
    address TEXT,
    location VARCHAR(100),
    account_type VARCHAR(20) DEFAULT 'Normal',
    status VARCHAR(20) DEFAULT 'Pending',
    profile_pic VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Service Provider table
CREATE TABLE IF NOT EXISTS service_provider (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(150),
    mobile VARCHAR(15),
    status VARCHAR(20) DEFAULT 'Pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Datasets table
CREATE TABLE IF NOT EXISTS datasets (
    tid VARCHAR(100) PRIMARY KEY,
    description TEXT,
    subject VARCHAR(100),
    news_country VARCHAR(100),
    date_time VARCHAR(50),
    hashcode VARCHAR(255),
    deschcode VARCHAR(255),
    uploaded_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Attackers table
CREATE TABLE IF NOT EXISTS attackers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    attacker_name VARCHAR(100),
    tid VARCHAR(100),
    attacker_type VARCHAR(100),
    date_time VARCHAR(50)
);

-- Data Recovery table
CREATE TABLE IF NOT EXISTS data_recovery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tid VARCHAR(100),
    recovery_action TEXT,
    status VARCHAR(50),
    date_time VARCHAR(50)
);

-- Cloud Server admin
CREATE TABLE IF NOT EXISTS cloud_server (
    id INT AUTO_INCREMENT PRIMARY KEY,
    servername VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL
);

-- Insert default admin credentials (password: admin123)
INSERT IGNORE INTO cloud_server (servername, password) VALUES ('admin', 'admin123');

-- Insert sample service provider
INSERT IGNORE INTO service_provider (username, password, email, mobile, status)
VALUES ('provider1', 'provider123', 'provider@sanare.com', '9999999999', 'Authorized');

-- Insert sample datasets
INSERT IGNORE INTO datasets (tid, description, subject, news_country, date_time, hashcode, deschcode, uploaded_by) VALUES
('10.42.0.151-104.244.43.227-49848-443-6', 
 'The day after a federal judge in San Francisco blocked part of alleged president Donald Trump threatening sanctuary cities with losing federal funds', 
 'Politics', 'Indonesia', '28-Apr-2022',
 '6e5c2fcb518ae81455f71d50771a9b71ce9b7f92',
 '-1da791bec5a5de161ecfeac438ff01b58ddc1caa',
 'provider1'),
('10.42.0.151-10.42.0.1-46897-53-17',
 'It would seem that the Democrats talking point, that healthcare is a human right, is just a talking point.',
 'Politics', 'Libya', '28-Apr-2022',
 '6e5c2fcb518ae81455f71d50771a9b71ce9b7f92',
 '-373a5379e750fdae61f9be547fb1639a5c6947d2',
 'provider1'),
('10.42.0.211-52.84.29.154-57119-443-6',
 'If you have a pre-existing condition, Obamacare currently protects you from being turned down for insurance on the basis of that condition.',
 'Politics', 'Jordan', '28-Apr-2022',
 '6e5c2fcb518ae81455f71d50771a9b71ce9b7f92',
 '-4fe0f92441ce23688917f6966fa55e95e0fbc28c8',
 'provider1');

-- Insert sample attackers
INSERT IGNORE INTO attackers (attacker_name, tid, attacker_type, date_time) VALUES
('Attacker', '10.42.0.151-10.42.0.1-46097-53-17', 'Malicious Data Attack', '08/01/2024 12:47:19'),
('Hacker', '10.42.0.151-10.42.0.1-46097-53-17', 'Malicious Data Attack', '08/01/2024 16:55:33');

SELECT 'Database setup complete!' AS Status;