-- https://www.phpmyadmin.net/
--

START TRANSACTION;

/*CREATE USER 'utilizator'@'localhost' IDENTIFIED BY 'Topoloveni324523!';
GRANT SELECT, INSERT, UPDATE ON test_db.* TO 'utilizator'@'localhost';
FLUSH PRIVILEGES;
*/;


--
-- Database: `test_db`
--

-- --------------------------------------------------------
CREATE TABLE `push_subscriptions` (
  `id` int(11) NOT NULL,
  `subscription_id` varchar(255) NOT NULL,
  `endpoint` varchar(255) NOT NULL,
  `public_key` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Table structure for table `users`
--
CREATE TABLE comments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  parent_id INT DEFAULT NULL,
  rating INT NOT NULL,
  text TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE `comments` ADD COLUMN `user` varchar(255) NOT NULL AFTER `created_at`;
ALTER TABLE `comments` ADD COLUMN `product` varchar(255) NOT NULL AFTER `user`;

CREATE TABLE `reviews` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `review` varchar(255) NOT NULL,
  `date` varchar(255) NOT NULL,
  `order_verified` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `reviews` (`id`, `name`, `review`, `date`, `order_verified`) VALUES
(3, 'sebastian', 'merge ca uns', '11.02.2023', 'yes'),
(4, 'huan', 'merge ca bicla', '13.02.2023', 'yes');


ALTER TABLE `reviews`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `reviews` ADD COLUMN `product_id` varchar(255) NOT NULL AFTER `order_verified`;
ALTER TABLE `reviews` ADD COLUMN `stars` varchar(255) NOT NULL AFTER `product_id`;

ALTER TABLE `reviews`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


ALTER TABLE reviews MODIFY COLUMN review VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


CREATE TABLE `orders` (
  `id` int(11) NOT NULL,
  `usrname` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `quantity` varchar(255) NOT NULL,
  `price` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL,
  `order_number` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `orders` (`id`, `usrname`, `title`, `quantity`, `price`,`status`,`order_number`) VALUES
(3, 'sebi', 'hooded parka', '2', '45', 'active','1'),
(4, 'huan', 'metal sunglasses', '3', '40','active','1');


ALTER TABLE `orders`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `orders`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
ALTER TABLE `orders` ADD COLUMN `awb` varchar(255) NOT NULL AFTER `order_number`;



CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `description` varchar(255) NOT NULL,
  `photos` varchar(255) NOT NULL,
  `photos2` varchar(255) NOT NULL,
  `photos3` varchar(255) NOT NULL,
  `photos4` varchar(255) NOT NULL,
  `product_price` varchar(255) NOT NULL,
  `link` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `products` (`id`, `product_name`, `description`, `photos`, `photos2`, `photos3`, `photos4`, `product_price`, `link`) VALUES
(0, 'AEROREADY SHIRT MERGE', '-Calitate Germana promit nu e din China.', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', '25$', 'product1.html'),
(1, 'WIRELESS EARBUDS', 'Sunte De Canal Non-Stop.', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', 'https://i.ibb.co/jM5H138/Vaza-Trandafiri-Side.png', '100$', 'product2.html');


ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);
ALTER TABLE `products` ADD COLUMN `likes` varchar(255) NOT NULL AFTER `link`;
ALTER TABLE `products` ADD COLUMN `collection` varchar(255) NOT NULL AFTER `likes`;
--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


ALTER TABLE products
ADD COLUMN date_added TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

DELIMITER //

CREATE TRIGGER before_product_insert
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    SET NEW.date_added = CURRENT_TIMESTAMP;
END;

//

DELIMITER ;


CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `user_name` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;




ALTER TABLE `users` ADD COLUMN `email` varchar(255) NOT NULL AFTER `name`;
ALTER TABLE `users` ADD COLUMN `adresa` varchar(255) NOT NULL AFTER `email`;
ALTER TABLE `users` ADD COLUMN `telefon` varchar(255) NOT NULL AFTER `adresa`;
ALTER TABLE `users` ADD COLUMN `nrcomenzi` varchar(255) NOT NULL AFTER `telefon`;
--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `user_name`, `password`, `name`, `email`, `adresa`, `telefon`,`nrcomenzi`) VALUES
(3, 'ela', '202cb962ac59075b964b07152d234b70', 'Ela', 'Ela@gmail.com', 'Bulevardul Dacia nr. 1', '0741234567', '0'),
(4, 'elias', '202cb962ac59075b964b07152d234b70', 'elias', 'Elias@gmail.com', 'Bulevardul Dacia nr. 2', '0724545567', '0');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);
ALTER TABLE users MODIFY COLUMN user_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;


ALTER TABLE reviews CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE products CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE orders CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


