-- Icon Foozera database schema
-- Engine: MySQL 8+ / MariaDB 10.4+

CREATE DATABASE IF NOT EXISTS icon_foozera CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE icon_foozera;

-- ---------------------------------------------------------
-- Users
-- ---------------------------------------------------------
CREATE TABLE users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100)        NOT NULL,
    email           VARCHAR(190)        NOT NULL UNIQUE,
    password_hash   VARCHAR(255)        NOT NULL,
    daily_limit     INT UNSIGNED        NOT NULL DEFAULT 200,
    created_at      TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Categories
-- ---------------------------------------------------------
CREATE TABLE categories (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100)        NOT NULL,
    slug            VARCHAR(120)        NOT NULL UNIQUE,
    created_at      TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Icons
-- ---------------------------------------------------------
CREATE TABLE icons (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(150)        NOT NULL,
    slug            VARCHAR(180)        NOT NULL UNIQUE,
    category_id     INT UNSIGNED        NULL,
    svg_path        VARCHAR(255)        NOT NULL,   -- path to master SVG file on disk
    default_color   VARCHAR(7)          NOT NULL DEFAULT '#000000',
    downloads_count INT UNSIGNED        NOT NULL DEFAULT 0,
    is_active       TINYINT(1)          NOT NULL DEFAULT 1,
    created_at      TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FULLTEXT KEY ft_name (name)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Tags (many-to-many with icons, for search)
-- ---------------------------------------------------------
CREATE TABLE tags (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(60)         NOT NULL UNIQUE
) ENGINE=InnoDB;

CREATE TABLE icon_tags (
    icon_id         INT UNSIGNED        NOT NULL,
    tag_id          INT UNSIGNED        NOT NULL,
    PRIMARY KEY (icon_id, tag_id),
    FOREIGN KEY (icon_id) REFERENCES icons(id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Download log
-- Every download (guest or account) writes a row here.
-- Rate limiting is computed by counting rows in the last 24h,
-- grouped by device_id (guests) or user_id (accounts).
-- ---------------------------------------------------------
CREATE TABLE downloads (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    icon_id         INT UNSIGNED        NOT NULL,
    user_id         INT UNSIGNED        NULL,       -- NULL = guest download
    device_id       CHAR(36)            NULL,       -- cookie-based UUID, set for guests (and stored for accounts too)
    ip_address      VARCHAR(45)         NOT NULL,
    format          ENUM('svg','png','jpg') NOT NULL,
    size_px         SMALLINT UNSIGNED   NULL,        -- NULL for svg
    color            VARCHAR(7)         NULL,
    created_at      TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (icon_id) REFERENCES icons(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
    INDEX idx_device_time (device_id, created_at),
    INDEX idx_user_time (user_id, created_at)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Favorites (account feature)
-- ---------------------------------------------------------
CREATE TABLE favorites (
    user_id         INT UNSIGNED        NOT NULL,
    icon_id         INT UNSIGNED        NOT NULL,
    created_at      TIMESTAMP           NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, icon_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (icon_id) REFERENCES icons(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- Seed data (sample category + icon so the site isn't empty)
-- ---------------------------------------------------------
INSERT INTO categories (name, slug) VALUES
    ('Business', 'business'),
    ('Social Media', 'social-media'),
    ('E-commerce', 'e-commerce'),
    ('UI/UX', 'ui-ux'),
    ('Weather', 'weather');