init_database.sql

CREATE DATABASE IF NOT EXISTS if0_39405604_codestore;
USE if0_39405604_codestore;

-- Admin table for authentication
CREATE TABLE IF NOT EXISTS admin_users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    last_login TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert default admin account (password: "password")
INSERT INTO admin_users (username, password_hash) 
VALUES ('admin', '$2y$10$8K1p/a0dL1JvmOo8g6Q3yuB1Zs0tVlmgBVV/hx.OePřbI0eWwi6') 
ON DUPLICATE KEY UPDATE id=id;

-- Modified apps table with additional fields
CREATE TABLE IF NOT EXISTS apps (
    id INT PRIMARY KEY AUTO_INCREMENT,
    app_name VARCHAR(255) NOT NULL,
    developer_name VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    meta_title VARCHAR(255) NOT NULL,
    meta_description TEXT NOT NULL,
    category VARCHAR(100),
    version VARCHAR(50) NOT NULL,
    release_date DATE,
    icon_path VARCHAR(255),
    app_package_path VARCHAR(255),
    screenshots_paths TEXT,
    app_url VARCHAR(255),
    platform VARCHAR(100),
    license_type VARCHAR(50),
    tags VARCHAR(255),
    additional_metadata TEXT,
    views_count INT DEFAULT 0,
    downloads_count INT DEFAULT 0,
    total_rating DECIMAL(3,2) DEFAULT 0.00,
    ratings_count INT DEFAULT 0,
    status ENUM('active', 'pending', 'rejected') DEFAULT 'pending',
    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for storing app ratings
CREATE TABLE IF NOT EXISTS app_ratings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    app_id INT NOT NULL,
    user_ip VARCHAR(45) NOT NULL,
    rating INT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES apps(id),
    UNIQUE KEY unique_rating (app_id, user_ip)
);

-- Table for tracking downloads
CREATE TABLE IF NOT EXISTS app_downloads (
    id INT PRIMARY KEY AUTO_INCREMENT,
    app_id INT NOT NULL,
    user_ip VARCHAR(45) NOT NULL,
    download_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (app_id) REFERENCES apps(id)
);

-- Indexes
CREATE INDEX idx_app_name ON apps(app_name);
CREATE INDEX idx_version ON apps(version);
CREATE INDEX idx_category ON apps(category);
CREATE INDEX idx_submitted_at ON apps(submitted_at);
CREATE INDEX idx_views_count ON apps(views_count);
CREATE INDEX idx_downloads_count ON apps(downloads_count);
CREATE INDEX idx_total_rating ON apps(total_rating);