-- schema.sql – FeatureBoard V4
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS feature_comments;
DROP TABLE IF EXISTS feature_votes;
DROP TABLE IF EXISTS features;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS feature_statuses;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(190) NOT NULL UNIQUE,
  name  VARCHAR(190),
  role  ENUM('user','admin') NOT NULL DEFAULT 'user',
  password_hash VARCHAR(255) NULL,
  last_seen_at DATETIME NULL,
  email_verified_at DATETIME NULL,
  magic_token VARCHAR(64) NULL,
  magic_token_expires DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE settings (
  `key` VARCHAR(64) PRIMARY KEY,
  `val` VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings (`key`,`val`) VALUES ('allowed_domain','helenaagri.com')
ON DUPLICATE KEY UPDATE val=VALUES(val);

CREATE TABLE feature_statuses (
  id TINYINT UNSIGNED PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  sort_order TINYINT UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO feature_statuses (id,name,sort_order) VALUES
 (1,'Backlog',1),
 (2,'Planned',2),
 (3,'In Progress',3),
 (4,'Released',4),
 (5,'Rejected',5)
ON DUPLICATE KEY UPDATE name=VALUES(name), sort_order=VALUES(sort_order);

CREATE TABLE categories (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(80) NOT NULL UNIQUE,
  sort_order INT UNSIGNED NOT NULL DEFAULT 100
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO categories (name,sort_order) VALUES
 ('General',10),
 ('UI/UX',20),
 ('Integrations',30)
ON DUPLICATE KEY UPDATE name=VALUES(name);

CREATE TABLE features (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(190) NOT NULL,
  description TEXT,
  author_id BIGINT UNSIGNED NOT NULL,
  status_id TINYINT UNSIGNED NOT NULL DEFAULT 1,
  category_id INT UNSIGNED NULL,
  duplicate_of BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status_id),
  INDEX idx_author (author_id),
  INDEX idx_category (category_id),
  INDEX idx_duplicate_of (duplicate_of),
  CONSTRAINT fk_features_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_features_status FOREIGN KEY (status_id) REFERENCES feature_statuses(id),
  CONSTRAINT fk_features_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  CONSTRAINT fk_features_duplicate FOREIGN KEY (duplicate_of) REFERENCES features(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE feature_votes (
  feature_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (feature_id, user_id),
  CONSTRAINT fk_votes_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE,
  CONSTRAINT fk_votes_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE feature_comments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  feature_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comments_feature FOREIGN KEY (feature_id) REFERENCES features(id) ON DELETE CASCADE,
  CONSTRAINT fk_comments_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO users (email,name,role) VALUES
 ('featureadmin@local','FeatureAdmin','admin');

SET FOREIGN_KEY_CHECKS = 1;