-- database/schema.sql

-- Table: site_metadata
CREATE TABLE site_metadata (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(255) NOT NULL,
    site_title VARCHAR(255) NOT NULL,
    site_title_template VARCHAR(255),
    site_description TEXT,
    site_url VARCHAR(255),
    site_logo VARCHAR(255),
    site_logo_rq VARCHAR(255),
    site_logo_square VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(255),
    whatsapp_number VARCHAR(255),
    address VARCHAR(255),
    social JSON,
    location JSON,
    keywords TEXT,
    hiring_contact_name VARCHAR(255),
    hiring_contact_email VARCHAR(255),
    hiring_contact_whatsapp_message TEXT
);

-- Table: navigation
CREATE TABLE navigation (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    href VARCHAR(255) NOT NULL,
    `order` INT NOT NULL
);

-- Table: pages
CREATE TABLE pages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    page_key VARCHAR(255) UNIQUE NOT NULL,
    seo_title VARCHAR(255),
    seo_description TEXT,
    hero_headline TEXT,
    hero_subheadline TEXT,
    hero_cta_primary_text VARCHAR(255),
    hero_cta_primary_href VARCHAR(255),
    hero_cta_secondary_text VARCHAR(255),
    hero_cta_secondary_href VARCHAR(255),
    mission TEXT,
    vision TEXT,
    timeline JSON,
    team JSON,
    stats JSON
);

-- Table: services
CREATE TABLE services (
    id VARCHAR(255) PRIMARY KEY,
    seo_title VARCHAR(255),
    seo_description TEXT,
    title VARCHAR(255) NOT NULL,
    icon VARCHAR(255),
    image VARCHAR(255),
    video VARCHAR(255),
    short_description TEXT,
    detailed_description TEXT,
    key_offerings JSON,
    cta_text VARCHAR(255),
    cta_whatsapp_message TEXT
);

-- Table: products
CREATE TABLE products (
    id VARCHAR(255) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    price VARCHAR(255),
    short_description TEXT,
    ideal_for TEXT,
    included_modules JSON,
    cta_text VARCHAR(255),
    cta_whatsapp_message TEXT
);

-- Table: projects
CREATE TABLE projects (
    id VARCHAR(255) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    client VARCHAR(255),
    category VARCHAR(255),
    service_id VARCHAR(255),
    cover_image VARCHAR(255),
    short_description TEXT,
    challenge TEXT,
    solution TEXT,
    outcome TEXT,
    technologies JSON,
    gallery JSON,
    cta_text VARCHAR(255),
    cta_whatsapp_message TEXT,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL
);

-- Table: testimonials
CREATE TABLE testimonials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(255),
    company VARCHAR(255),
    avatar VARCHAR(255),
    quote TEXT,
    related_project_id VARCHAR(255),
    FOREIGN KEY (related_project_id) REFERENCES projects(id) ON DELETE SET NULL
);

-- Table: faqs
CREATE TABLE faqs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question TEXT NOT NULL,
    answer TEXT NOT NULL
);

-- Table: team_members
CREATE TABLE team_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    role VARCHAR(255),
    avatar VARCHAR(255),
    bio TEXT,
    social JSON
);

-- Table: tech_stack
CREATE TABLE tech_stack (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    logo VARCHAR(255),
    type VARCHAR(255)
);

-- Table: clients
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    logo VARCHAR(255)
);

-- Table: recognitions
CREATE TABLE recognitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    issuer VARCHAR(255),
    icon VARCHAR(255)
);

-- Table: industries
CREATE TABLE industries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Table: plans
CREATE TABLE plans (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    price_monthly DECIMAL(10,2),
    price_annual DECIMAL(10,2),
    features JSON,
    badge VARCHAR(255),
    highlight BOOLEAN
);

-- Table: selection_process_steps
CREATE TABLE selection_process_steps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    detail TEXT
);

-- Table: careers
CREATE TABLE careers (
    id VARCHAR(255) PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    location VARCHAR(255),
    type VARCHAR(255),
    posted_at DATE,
    summary TEXT,
    responsibilities JSON,
    requirements JSON,
    benefits JSON,
    process JSON,
    apply_cta_text VARCHAR(255),
    apply_cta_whatsapp_message TEXT
);

-- Table: contact_availability_defaults
CREATE TABLE contact_availability_defaults (
    id INT AUTO_INCREMENT PRIMARY KEY,
    time VARCHAR(5) NOT NULL,
    is_available BOOLEAN NOT NULL
);

-- Table: contact_availability_by_date
CREATE TABLE contact_availability_by_date (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    time VARCHAR(5) NOT NULL,
    is_available BOOLEAN NOT NULL
);

-- Table: contact_availability_by_service
CREATE TABLE contact_availability_by_service (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service_id VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    time VARCHAR(5) NOT NULL,
    is_available BOOLEAN NOT NULL,
    FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE CASCADE
);

-- Table: users (for admin login)
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    role VARCHAR(255) DEFAULT 'editor',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
