-- DATABASE REPAIR SCRIPT v2
-- Run this AFTER importing 'infosecl_platform (29).sql'
-- This script fixes missing columns and ensures all tables exist.

USE infosecl_platform;

-- 1. Convert 'organizations' to InnoDB (MyISAM doesn't support Foreign Keys)
-- This fixes the Errno 150 issue.
ALTER TABLE organizations ENGINE=InnoDB;

-- 2. Fix 'alerts' table missing 'organization_id'
DROP PROCEDURE IF EXISTS upgrade_alerts_table;
DELIMITER //
CREATE PROCEDURE upgrade_alerts_table()
BEGIN
    -- Check if column exists
    IF NOT EXISTS (
        SELECT * FROM information_schema.columns 
        WHERE table_schema = DATABASE() 
        AND table_name = 'alerts' 
        AND column_name = 'organization_id'
    ) THEN
        -- Add column first
        ALTER TABLE alerts ADD COLUMN organization_id INT DEFAULT NULL;
        
        -- Add index for performance
        ALTER TABLE alerts ADD INDEX idx_alert_org (organization_id);
        
        -- Add foreign key constraint
        ALTER TABLE alerts ADD CONSTRAINT fk_alert_org 
        FOREIGN KEY (organization_id) REFERENCES organizations(id) 
        ON DELETE SET NULL; -- Changed to SET NULL to prevent accidental alert deletion
    END IF;
END //
DELIMITER ;
CALL upgrade_alerts_table();
DROP PROCEDURE upgrade_alerts_table;

-- 3. Ensure Beta Signups (Waitlist) Table Exists
CREATE TABLE IF NOT EXISTS beta_signups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    source VARCHAR(50) DEFAULT 'web',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_email (email)
) ENGINE=InnoDB;

-- 4. Ensure Newsletter Subscribers Table Exists
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    subscribed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT TRUE
) ENGINE=InnoDB;