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

CREATE TABLE IF NOT EXISTS intelligence_projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS intelligence_competitors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    website VARCHAR(255) NULL,
    notes TEXT NULL,
    rating INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(project_id)
);

CREATE TABLE IF NOT EXISTS intelligence_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    report_text LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(project_id)
);

CREATE TABLE IF NOT EXISTS intelligence_recommendations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    recommendation LONGTEXT,
    priority VARCHAR(50) DEFAULT 'medium',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(project_id)
);

CREATE TABLE IF NOT EXISTS intelligence_tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(50) DEFAULT 'open',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX(project_id)
);

INSERT IGNORE INTO intelligence_projects
(id,name,description,status)
VALUES
(1,'DreamMiles','Travel Community Plattform','active'),
(2,'FRAGENOVA','Vergleichs- und Beratungsplattform','active'),
(3,'ANNMI','KI und Business Lösungen','active');
