138 lines
6.1 KiB
Go
138 lines
6.1 KiB
Go
package database
|
|
|
|
import (
|
|
"context"
|
|
|
|
"github.com/jackc/pgx/v5/pgxpool"
|
|
)
|
|
|
|
func Migrate(ctx context.Context, pool *pgxpool.Pool) error {
|
|
queries := []string{
|
|
`CREATE TABLE IF NOT EXISTS admins (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS details TEXT NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT true`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()`,
|
|
`CREATE TABLE IF NOT EXISTS product_images (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
file_path VARCHAR(512) NOT NULL,
|
|
is_primary BOOLEAN NOT NULL DEFAULT false,
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_product_images_product ON product_images (product_id)`,
|
|
`CREATE TABLE IF NOT EXISTS site_stats (
|
|
id INT PRIMARY KEY DEFAULT 1 CHECK (id = 1),
|
|
total_visits BIGINT NOT NULL DEFAULT 0,
|
|
today_visits INT NOT NULL DEFAULT 0,
|
|
visit_date DATE NOT NULL DEFAULT CURRENT_DATE
|
|
)`,
|
|
`INSERT INTO site_stats (id) VALUES (1) ON CONFLICT (id) DO NOTHING`,
|
|
`CREATE TABLE IF NOT EXISTS users (
|
|
id SERIAL PRIMARY KEY,
|
|
email VARCHAR(255) UNIQUE NOT NULL,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
name VARCHAR(255) NOT NULL DEFAULT '',
|
|
phone VARCHAR(50) NOT NULL DEFAULT '',
|
|
address TEXT NOT NULL DEFAULT '',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS carts (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT REFERENCES users(id) ON DELETE CASCADE,
|
|
session_key VARCHAR(64) NOT NULL DEFAULT '',
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`CREATE UNIQUE INDEX IF NOT EXISTS idx_carts_user ON carts (user_id) WHERE user_id IS NOT NULL`,
|
|
`CREATE UNIQUE INDEX IF NOT EXISTS idx_carts_session ON carts (session_key) WHERE user_id IS NULL`,
|
|
`CREATE TABLE IF NOT EXISTS cart_items (
|
|
id SERIAL PRIMARY KEY,
|
|
cart_id INT NOT NULL REFERENCES carts(id) ON DELETE CASCADE,
|
|
product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
|
|
UNIQUE (cart_id, product_id)
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS orders (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id INT REFERENCES users(id) ON DELETE SET NULL,
|
|
guest_name VARCHAR(255) NOT NULL DEFAULT '',
|
|
guest_email VARCHAR(255) NOT NULL DEFAULT '',
|
|
guest_phone VARCHAR(50) NOT NULL DEFAULT '',
|
|
address TEXT NOT NULL DEFAULT '',
|
|
total NUMERIC(10,2) NOT NULL DEFAULT 0,
|
|
status VARCHAR(50) NOT NULL DEFAULT 'new',
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`CREATE TABLE IF NOT EXISTS order_items (
|
|
id SERIAL PRIMARY KEY,
|
|
order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
|
|
product_id INT NOT NULL,
|
|
product_name VARCHAR(255) NOT NULL,
|
|
price NUMERIC(10,2) NOT NULL,
|
|
quantity INT NOT NULL CHECK (quantity > 0)
|
|
)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_orders_user ON orders (user_id)`,
|
|
`CREATE TABLE IF NOT EXISTS categories (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL UNIQUE,
|
|
slug VARCHAR(100) NOT NULL UNIQUE,
|
|
description TEXT NOT NULL DEFAULT '',
|
|
sort_order INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS category_id INT REFERENCES categories(id) ON DELETE SET NULL`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS sale_price NUMERIC(10,2)`,
|
|
`INSERT INTO categories (name, slug, sort_order) VALUES
|
|
('Электроника', 'elektronika', 1),
|
|
('Одежда', 'odezhda', 2),
|
|
('Дом и сад', 'dom-i-sad', 3),
|
|
('Аксессуары', 'aksessuary', 4),
|
|
('Разное', 'raznoe', 5)
|
|
ON CONFLICT (slug) DO NOTHING`,
|
|
`UPDATE products p SET category_id = c.id
|
|
FROM categories c WHERE p.category = c.name AND p.category_id IS NULL`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS attr_size VARCHAR(50) NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS attr_color VARCHAR(50) NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS attr_material VARCHAR(100) NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS attr_weight VARCHAR(50) NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE products ADD COLUMN IF NOT EXISTS attr_brand VARCHAR(100) NOT NULL DEFAULT ''`,
|
|
`CREATE TABLE IF NOT EXISTS product_price_history (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
price NUMERIC(10,2) NOT NULL,
|
|
sale_price NUMERIC(10,2),
|
|
recorded_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_price_history_product ON product_price_history (product_id, recorded_at DESC)`,
|
|
`CREATE TABLE IF NOT EXISTS product_reservations (
|
|
id SERIAL PRIMARY KEY,
|
|
product_id INT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
|
|
session_key VARCHAR(64) NOT NULL DEFAULT '',
|
|
user_id INT REFERENCES users(id) ON DELETE SET NULL,
|
|
customer_name VARCHAR(255) NOT NULL DEFAULT '',
|
|
customer_phone VARCHAR(50) NOT NULL DEFAULT '',
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_reservations_product ON product_reservations (product_id, expires_at DESC)`,
|
|
`CREATE INDEX IF NOT EXISTS idx_reservations_session ON product_reservations (session_key)`,
|
|
`ALTER TABLE orders ADD COLUMN IF NOT EXISTS payment_method VARCHAR(20) NOT NULL DEFAULT 'cod'`,
|
|
`ALTER TABLE orders ADD COLUMN IF NOT EXISTS heleket_uuid VARCHAR(64) NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE orders ADD COLUMN IF NOT EXISTS heleket_order_id VARCHAR(128) NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE orders ADD COLUMN IF NOT EXISTS heleket_payment_url TEXT NOT NULL DEFAULT ''`,
|
|
`ALTER TABLE orders ADD COLUMN IF NOT EXISTS heleket_payment_status VARCHAR(50) NOT NULL DEFAULT ''`,
|
|
}
|
|
|
|
for _, q := range queries {
|
|
if _, err := pool.Exec(ctx, q); err != nil {
|
|
return err
|
|
}
|
|
}
|
|
return nil
|
|
}
|