CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE roles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    role_name VARCHAR(50) UNIQUE NOT NULL
);

CREATE TABLE profiles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    full_name VARCHAR(100) NOT NULL,
    date_of_birth DATE,
    age INT,
    profession VARCHAR(100),
    profile_image VARCHAR(255),
    role_id INT,
    user_id INT,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE categories (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE blogs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  post_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  blog_image VARCHAR(255),

  -- Category relationship
  category_id INT,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,

  -- Links to other tables
  user_id INT, -- Foreign key to users table
  role_id INT, -- Foreign key to roles table
  profile_id INT, -- Foreign key to profiles table

  -- Denormalized fields for faster access
  username VARCHAR(50), -- From users table
  role_name VARCHAR(50), -- From roles table
  profile_image VARCHAR(255), -- From profiles table
  full_name VARCHAR(100), -- From profiles table

  -- Foreign key relationships
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL,
  FOREIGN KEY (profile_id) REFERENCES profiles(id) ON DELETE CASCADE
);

CREATE TABLE contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone_number VARCHAR(15),
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(255) NOT NULL,
    client_email VARCHAR(255) NOT NULL,
    client_phone_number VARCHAR(15),
    message TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_name VARCHAR(100) NOT NULL,
    client_email VARCHAR(100) NOT NULL UNIQUE,
    client_phone VARCHAR(20) NOT NULL,
    client_type ENUM('primary', 'secondary') NOT NULL,
    client_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE reseller (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reseller_name VARCHAR(100) NOT NULL,
    reseller_email VARCHAR(100) NOT NULL UNIQUE,
    reseller_phone VARCHAR(20) NOT NULL,
    reseller_address VARCHAR(100) NOT NULL,
    reseller_type ENUM('primary', 'secondary') NOT NULL,
    reseller_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);


CREATE TABLE requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contact_id INT NOT NULL,
    category VARCHAR(50) NOT NULL,
    FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
);




