-- ASM Database Schema
CREATE DATABASE IF NOT EXISTS asm_db;
USE asm_db;

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

CREATE TABLE IF NOT EXISTS assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asset_name VARCHAR(255) NOT NULL,
    asset_type ENUM('domain', 'subdomain', 'ip', 'bucket', 'certificate', 'software') NOT NULL,
    ip_address VARCHAR(45),
    status VARCHAR(50) DEFAULT 'Active',
    last_scanned TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS anomalies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asset_id INT,
    title VARCHAR(255) NOT NULL,
    severity ENUM('Low', 'Medium', 'High', 'Critical') NOT NULL,
    detected_pattern TEXT NOT NULL,
    ai_explanation TEXT,
    remediation_steps TEXT,
    status ENUM('Open', 'Resolved') DEFAULT 'Open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (asset_id) REFERENCES assets(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS compliance_checks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    standard ENUM('PCI-DSS', 'GDPR', 'HIPAA') NOT NULL,
    control_title VARCHAR(255) NOT NULL,
    is_compliant BOOLEAN DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Seed admin user: admin / darshan@2007
INSERT INTO users (username, password_hash)
VALUES ('admin', '$2y$10$TKh8H1.PfQx37YgCzwiKb.KjNyWgaHb9cbcoQgdIVFlYg7B77UdFm')
ON DUPLICATE KEY UPDATE username=username;

-- Seed compliance checks
INSERT INTO compliance_checks (standard, control_title, is_compliant) VALUES
('PCI-DSS', 'Requirement 1: Network Security Controls & Firewalls', 1),
('PCI-DSS', 'Requirement 2: Secure Configurations & Default Passwords Changed', 1),
('PCI-DSS', 'Requirement 4: Protect Cardholder Data with Strong Cryptography', 0),
('GDPR', 'Article 32: Security of Processing & Encryption', 1),
('GDPR', 'Article 25: Data Protection by Design & Default', 1),
('GDPR', 'Article 33: Timely Notification of Personal Data Breach', 0),
('HIPAA', '164.312(a)(1): Access Control & Authentication', 1),
('HIPAA', '164.312(e)(1): Transmission Security & TLS Enforced', 0);