-- Database schema for InteractiveResume application
-- Designed for a Laravel backend

-- Personal Information (Typically a single record, or could be part of a users table if multi-user)
CREATE TABLE personal_info (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    phone VARCHAR(50) NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    about TEXT NULL,
    avatar_image_url VARCHAR(2048) NULL, -- URL for the avatar image
    hero_background_image_url VARCHAR(2048) NULL, -- URL for the hero background image
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Navigation Links
CREATE TABLE navigation_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    href VARCHAR(255) NOT NULL,
    icon_name VARCHAR(100) NOT NULL, -- Lucide icon name
    sort_order INT UNSIGNED DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Social Links
CREATE TABLE social_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    url VARCHAR(2048) NOT NULL,
    icon_name VARCHAR(100) NOT NULL, -- Lucide icon name
    sort_order INT UNSIGNED DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tech Stack Categories
CREATE TABLE tech_categories (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tech Stack Items
CREATE TABLE tech_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    icon_name VARCHAR(100) NOT NULL, -- Lucide icon name
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES tech_categories(id) ON DELETE CASCADE
);

-- Professional Experience
CREATE TABLE experiences (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role VARCHAR(255) NOT NULL,
    company VARCHAR(255) NOT NULL,
    dates VARCHAR(100) NOT NULL, -- e.g., "2016 – 2024" or "2017 – Present". Consider separate start_date/end_date for more structure.
    icon_name VARCHAR(100) NOT NULL, -- Lucide icon name
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Experience Description Points (One-to-many relationship with experiences)
CREATE TABLE experience_description_points (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    experience_id BIGINT UNSIGNED NOT NULL,
    point TEXT NOT NULL,
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (experience_id) REFERENCES experiences(id) ON DELETE CASCADE
);

-- Projects
CREATE TABLE projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    url VARCHAR(2048) NULL,
    image_url VARCHAR(2048) NOT NULL, -- Placeholder or actual image URL
    data_ai_hint VARCHAR(255) NULL,
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Project Technologies (Tech stack specific to each project)
CREATE TABLE project_technologies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(100) NOT NULL,
    icon_name VARCHAR(100) NOT NULL, -- Lucide icon name
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Education & Certifications
CREATE TABLE education_items (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(255) NOT NULL, -- e.g., "Diploma in Programming and Database" or "International Diploma in Project Management"
    institution VARCHAR(255) NOT NULL,
    dates VARCHAR(100) NOT NULL, -- e.g., "2016 – 2018" or "2013". Consider separate start_date/end_date or year field.
    icon_name VARCHAR(100) NOT NULL, -- Lucide icon name
    type ENUM('education', 'certification', 'course') NOT NULL,
    sort_order INT UNSIGNED DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Contact Form Submissions
CREATE TABLE contact_submissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    is_read BOOLEAN DEFAULT FALSE,
    submitted_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Testimonials (If this section is re-added)
-- CREATE TABLE testimonials (
--     id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
--     quote TEXT NOT NULL,
--     author_name VARCHAR(255) NOT NULL,
--     author_title VARCHAR(255) NULL,
--     author_image_url VARCHAR(2048) NULL,
--     rating TINYINT UNSIGNED NULL CHECK (rating >= 1 AND rating <= 5),
--     data_ai_hint VARCHAR(255) NULL,
--     sort_order INT UNSIGNED DEFAULT 0,
--     is_active BOOLEAN DEFAULT TRUE,
--     created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
--     updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- );

-- Patreon Tiers (If this section is re-added)
-- CREATE TABLE patreon_tiers (
--     id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
--     name VARCHAR(255) NOT NULL,
--     price VARCHAR(50) NOT NULL, -- e.g., "$5/month"
--     benefits TEXT NOT NULL, -- Could be JSON or comma-separated
--     sort_order INT UNSIGNED DEFAULT 0,
--     is_active BOOLEAN DEFAULT TRUE,
--     created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
--     updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- );

-- Example Insert for Personal Info (assuming single record, ID 1)
-- INSERT INTO personal_info (name, title, phone, email, about, avatar_image_url, hero_background_image_url) VALUES
-- ('HUSSAIN ABUHAJAJJ', 'Software Project Manager | Former Full Stack Web Developer', '+47 486 79 240', 'Hussain.h.ff32@gmail.com', 'Experienced Software Project Manager...', 'https://picsum.photos/seed/hero-avatar/200/100', 'https://firebasestorage.googleapis.com/v0/b/ai-project-46175.appspot.com/o/app%2Fvisuals%2F360_F_401117141_8LDBeL2aLThL6c4LEKXTpAdV20k5y7vJ.jpg?alt=media&token=1bb36b04-4183-49ac-b682-b24862a2c069');
-- Update this with actual URLs if they are stored in the DB.
