101 lines
4.4 KiB
SQL
101 lines
4.4 KiB
SQL
-- Ñîçäàíèå òàáëèöû äëÿ ëîãèðîâàíèÿ âñåõ ïëàòåæíûõ îïåðàöèé
|
|
-- Ñîäåðæèò èñ÷åðïûâàþùóþ èíôîðìàöèþ î êëèåíòå, óñëóãå, VIN è ðåçóëüòàòå
|
|
|
|
-- Ñîçäàíèå ïîñëåäîâàòåëüíîñòè äëÿ ID çàïèñåé
|
|
CREATE SEQUENCE payment_logs_seq
|
|
START WITH 1
|
|
INCREMENT BY 1
|
|
CACHE 100;
|
|
|
|
-- Ñîçäàíèå îñíîâíîé òàáëèöû ëîãîâ
|
|
CREATE TABLE payment_logs (
|
|
log_id NUMBER PRIMARY KEY,
|
|
|
|
-- Èíôîðìàöèÿ î ïîëüçîâàòåëå
|
|
user_id NUMBER NOT NULL,
|
|
user_first_name VARCHAR2(255),
|
|
user_last_name VARCHAR2(255),
|
|
user_username VARCHAR2(255),
|
|
user_language_code VARCHAR2(10),
|
|
user_is_premium NUMBER(1) DEFAULT 0,
|
|
|
|
-- Èíôîðìàöèÿ îá óñëóãå
|
|
service_type VARCHAR2(50) NOT NULL, -- 'decode_vin', 'check_salvage', 'get_photos'
|
|
vin_number VARCHAR2(17) NOT NULL,
|
|
|
|
-- Èíôîðìàöèÿ î ïëàòåæå
|
|
payment_amount NUMBER(10,2) NOT NULL,
|
|
transaction_id VARCHAR2(255),
|
|
payment_status VARCHAR2(50) DEFAULT 'completed', -- 'completed', 'pending', 'failed'
|
|
payment_currency VARCHAR2(10) DEFAULT 'XTR',
|
|
|
|
-- Ðåçóëüòàò ïðåäîñòàâëåíèÿ óñëóãè
|
|
service_status VARCHAR2(50), -- 'success', 'failed', 'no_data', 'error'
|
|
data_found_count NUMBER DEFAULT 0, -- êîëè÷åñòâî íàéäåííûõ çàïèñåé/ôîòî
|
|
refund_status VARCHAR2(50) DEFAULT 'no_refund', -- 'no_refund', 'auto_refund', 'manual_refund', 'admin_refund'
|
|
refund_reason VARCHAR2(500),
|
|
|
|
-- Èíôîðìàöèÿ îá àâòîìîáèëå
|
|
vehicle_make VARCHAR2(100),
|
|
vehicle_model VARCHAR2(100),
|
|
vehicle_year VARCHAR2(10),
|
|
|
|
-- Òåõíè÷åñêàÿ èíôîðìàöèÿ
|
|
error_message CLOB,
|
|
created_date DATE DEFAULT SYSDATE,
|
|
ip_address VARCHAR2(45), -- äëÿ áóäóùåãî èñïîëüçîâàíèÿ
|
|
|
|
-- Èíäåêñû äëÿ áûñòðîãî ïîèñêà
|
|
CONSTRAINT chk_service_type CHECK (service_type IN ('decode_vin', 'check_salvage', 'get_photos')),
|
|
CONSTRAINT chk_payment_status CHECK (payment_status IN ('completed', 'pending', 'failed')),
|
|
CONSTRAINT chk_service_status CHECK (service_status IN ('success', 'failed', 'no_data', 'error', 'pending')),
|
|
CONSTRAINT chk_refund_status CHECK (refund_status IN ('no_refund', 'auto_refund', 'manual_refund', 'admin_refund'))
|
|
);
|
|
|
|
-- Ñîçäàíèå èíäåêñîâ äëÿ îïòèìèçàöèè çàïðîñîâ
|
|
CREATE INDEX idx_payment_logs_user_id ON payment_logs(user_id);
|
|
CREATE INDEX idx_payment_logs_vin ON payment_logs(vin_number);
|
|
CREATE INDEX idx_payment_logs_service_type ON payment_logs(service_type);
|
|
CREATE INDEX idx_payment_logs_date ON payment_logs(created_date);
|
|
CREATE INDEX idx_payment_logs_transaction_id ON payment_logs(transaction_id);
|
|
CREATE INDEX idx_payment_logs_status ON payment_logs(payment_status, service_status);
|
|
|
|
-- Êîììåíòàðèè ê òàáëèöå è ïîëÿì
|
|
COMMENT ON TABLE payment_logs IS 'Ëîãèðîâàíèå âñåõ ïëàòåæíûõ îïåðàöèé áîòà ñ äåòàëüíîé èíôîðìàöèåé';
|
|
COMMENT ON COLUMN payment_logs.log_id IS 'Óíèêàëüíûé èäåíòèôèêàòîð çàïèñè';
|
|
COMMENT ON COLUMN payment_logs.user_id IS 'ID ïîëüçîâàòåëÿ Telegram';
|
|
COMMENT ON COLUMN payment_logs.service_type IS 'Òèï óñëóãè: decode_vin, check_salvage, get_photos';
|
|
COMMENT ON COLUMN payment_logs.vin_number IS 'VIN íîìåð àâòîìîáèëÿ';
|
|
COMMENT ON COLUMN payment_logs.payment_amount IS 'Ñóììà ïëàòåæà â Telegram Stars';
|
|
COMMENT ON COLUMN payment_logs.transaction_id IS 'ID òðàíçàêöèè Telegram';
|
|
COMMENT ON COLUMN payment_logs.data_found_count IS 'Êîëè÷åñòâî íàéäåííûõ äàííûõ (çàïèñåé, ôîòî è ò.ä.)';
|
|
COMMENT ON COLUMN payment_logs.refund_status IS 'Ñòàòóñ âîçâðàòà ñðåäñòâ';
|
|
COMMENT ON COLUMN payment_logs.created_date IS 'Äàòà è âðåìÿ ñîçäàíèÿ çàïèñè';
|
|
|
|
-- Ñîçäàíèå ïðåäñòàâëåíèÿ äëÿ àíàëèòèêè
|
|
CREATE OR REPLACE VIEW payment_analytics AS
|
|
SELECT
|
|
service_type,
|
|
COUNT(*) as total_transactions,
|
|
SUM(payment_amount) as total_revenue,
|
|
AVG(payment_amount) as avg_payment,
|
|
COUNT(CASE WHEN refund_status != 'no_refund' THEN 1 END) as refunds_count,
|
|
COUNT(CASE WHEN service_status = 'success' THEN 1 END) as successful_services,
|
|
ROUND(COUNT(CASE WHEN service_status = 'success' THEN 1 END) * 100.0 / COUNT(*), 2) as success_rate,
|
|
TRUNC(created_date) as date_created
|
|
FROM payment_logs
|
|
GROUP BY service_type, TRUNC(created_date)
|
|
ORDER BY date_created DESC, service_type;
|
|
|
|
|
|
-- Ñîçäàíèå òðèããåðà äëÿ àâòîìàòè÷åñêîãî çàïîëíåíèÿ created_date
|
|
CREATE OR REPLACE TRIGGER trg_payment_logs_created_date
|
|
BEFORE INSERT ON payment_logs
|
|
FOR EACH ROW
|
|
BEGIN
|
|
IF :NEW.created_date IS NULL THEN
|
|
:NEW.created_date := SYSDATE;
|
|
END IF;
|
|
END;
|
|
/
|