-- =====================
-- CRM_100: FULL SCHEMA
-- =====================

CREATE DATABASE IF NOT EXISTS crm CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE crm;

-- Reference
CREATE TABLE IF NOT EXISTS territories(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS categories(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  parent_id INT NULL,
  KEY parent_id (parent_id),
  CONSTRAINT fk_cat_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Partners (clients)
CREATE TABLE IF NOT EXISTS partners(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  territory VARCHAR(120),
  category VARCHAR(200),
  address VARCHAR(255),
  phone VARCHAR(50), phone2 VARCHAR(50), phone3 VARCHAR(50),
  type VARCHAR(120),
  sales_channel VARCHAR(120),
  client_class VARCHAR(120),
  lat DECIMAL(10,6), lng DECIMAL(10,6),
  note TEXT,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_name (name), KEY idx_territory (territory), KEY idx_deleted (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Products
CREATE TABLE IF NOT EXISTS products(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  unit VARCHAR(32) NOT NULL DEFAULT 'ta',
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_prod_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Orders
CREATE TABLE IF NOT EXISTS orders(
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_no INT NOT NULL,
  order_date DATETIME NOT NULL,
  partner_id INT NULL,
  partner_name VARCHAR(200) NULL,
  territory VARCHAR(120) NULL,
  category VARCHAR(200) NULL,
  status ENUM('new','confirmed','in_delivery','delivered','cancelled') NOT NULL DEFAULT 'new',
  delivery_date DATE NULL,
  delivery_address VARCHAR(255) NULL,
  total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  note TEXT NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at DATETIME NULL,
  KEY idx_no (order_no),
  KEY idx_status (status),
  KEY idx_deleted (deleted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_items(
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  product_name VARCHAR(255) NOT NULL,
  unit VARCHAR(32) NOT NULL DEFAULT 'ta',
  price DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  qty INT NOT NULL DEFAULT 1,
  line_total DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  KEY idx_order (order_id),
  CONSTRAINT fk_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS order_status_log(
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id INT NOT NULL,
  status ENUM('new','confirmed','in_delivery','delivered','cancelled') NOT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  changed_by VARCHAR(120) NULL,
  note VARCHAR(255) NULL,
  KEY idx_o (order_id),
  CONSTRAINT fk_osl_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Users & roles
CREATE TABLE IF NOT EXISTS users(
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(120) NOT NULL UNIQUE,
  full_name VARCHAR(200) NOT NULL,
  phone VARCHAR(50) NULL,
  password_hash VARCHAR(255) NOT NULL,
  territory VARCHAR(120) NULL,
  role_primary VARCHAR(60) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS roles(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions(
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(160) NOT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS role_permissions(
  role_id INT NOT NULL,
  permission_id INT NOT NULL,
  PRIMARY KEY (role_id, permission_id),
  CONSTRAINT fk_rp_r FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_rp_p FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_roles(
  user_id INT NOT NULL,
  role_id INT NOT NULL,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT fk_ur_u FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_ur_r FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Settings
CREATE TABLE IF NOT EXISTS settings(
  k VARCHAR(120) PRIMARY KEY,
  v TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Telegram bot per territory
CREATE TABLE IF NOT EXISTS bot_territories(
  id INT AUTO_INCREMENT PRIMARY KEY,
  territory VARCHAR(120) NOT NULL UNIQUE,
  bot_token VARCHAR(200) NOT NULL,
  group_chat_id BIGINT NOT NULL,
  bot_secret VARCHAR(120) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Drafts (open, with audio)
CREATE TABLE IF NOT EXISTS drafts(
  id INT AUTO_INCREMENT PRIMARY KEY,
  kind ENUM('order','partner') NOT NULL,
  meta JSON NULL,
  audio VARCHAR(255) NULL,
  created_by INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed
INSERT IGNORE INTO territories(name) VALUES ('Toshkent'),('Samarqand'),('Buxoro'),('Namangan');
INSERT IGNORE INTO categories(id,name,parent_id) VALUES (1,'Supermarket',NULL),(2,'Do‘kon',NULL),(3,'Kafe',NULL),(4,'Mini-market',1);
INSERT IGNORE INTO users(username,full_name,password_hash,role_primary) VALUES ('admin','Administrator','$2y$10$96ZB8c7RpuYwz9R5cUZ3SeUPKJR0dpIXh6i/EC.kW7e7r9V6bhlzS','admin');
-- password: admin123

INSERT IGNORE INTO products(name,unit,price,is_active) VALUES
('19L ichimlik suvi','dona',20000,1),
('Pomidor','kg',15000,1),
('Banan','kg',18000,1);

-- Permissions & roles
INSERT IGNORE INTO permissions(slug,name) VALUES
('orders.view','Buyurtmalar: ko\'rish'),
('orders.create','Buyurtmalar: yaratish'),
('orders.edit','Buyurtmalar: tahrirlash'),
('orders.delete','Buyurtmalar: o\'chirish'),
('orders.export','Buyurtmalar: eksport'),
('partners.view','Mijozlar: ko\'rish'),
('partners.create','Mijozlar: yaratish'),
('partners.edit','Mijozlar: tahrirlash'),
('partners.delete','Mijozlar: o\'chirish'),
('products.view','Mahsulotlar: ko\'rish'),
('products.edit','Mahsulotlar: tahrirlash'),
('users.manage','Foydalanuvchilar: boshqarish'),
('roles.manage','Rollar: boshqarish'),
('settings.manage','Sozlamalar: boshqarish'),
('trash.restore','O\'chirilganlar: tiklash'),
('drafts.view','Ochiq qolganlar: ko\'rish');

INSERT IGNORE INTO roles(id,name,slug) VALUES (1,'Administrator','admin'),(2,'Operator','operator'),(3,'Kuryer','courier');
INSERT IGNORE INTO role_permissions(role_id, permission_id) SELECT 1, p.id FROM permissions p;
INSERT IGNORE INTO role_permissions(role_id, permission_id) SELECT 2, p.id FROM permissions p WHERE p.slug IN
('orders.view','orders.create','orders.edit','orders.delete','orders.export','partners.view','partners.create','partners.edit','products.view','products.edit','drafts.view');
INSERT IGNORE INTO role_permissions(role_id, permission_id) SELECT 3, p.id FROM permissions p WHERE p.slug IN ('orders.view','drafts.view');
INSERT IGNORE INTO user_roles(user_id, role_id) SELECT id,1 FROM users WHERE username='admin';
